JSQLTool is a java 1.4 stand alone application, having a swing based front-end, used to connect to any database for which is available an ODBC entry or a JDBC Driver.
JSQLTool can be used to:
connect to a database, using a JDBC or ODBC Driver
see tables list, views list and synonyms list
see entity details, for each table/view/synonym; details include: structure, data, indexes, constraints, SQL script and triggers
table data can be visually ordered/filtered; you can add/edit/delete records directly on the table data grid
table data can be exported as insert SQL statements, as text or in an Excel File Format (XLS)
edit SQL statements in a SQL shell, by means of it you can execute queries or other statements and see query results; queries may contain parameters
previous SQL statements are recorded to allow re-call and re-execution
Many other features are available, according to the database type: edit/drop contraints, execution query explain plan, trace sessions.
Table detail is pluggable: developers can add other table plugins to easly extends the application.
The whole application is designed to support multi-language and internationalization: at the moment two languages are supported: english and italian.
In the following sections you can see how connect to a database, how to use "Schema Browser" and "SQL Editor" windows and the others features JSQLTool provides.
JDBC Drivers must be included in the application classpath, i.e. you have manually to edit run.bat or run.sh file, by adding the JDBC Driver jar files.
For example, if you want to connect to an Oracle Database, you have to add "ojdbc14.jar" file to the .bat/.sh file:
javaw -cp .;images.jar;jsqltool.jar;jcalendar.jar;poi-2.0-RC2-20040102.jar;jgraph.jar;itext-1.4.3.jar;ojdbc14.jar org.jsqltool.MainApp
If you want to connect to a MS SQLServer Database, edit .bat/.sh file, by adding the files mssqlserver.jar, msbase.jar and msutil.jar (JDBC Driver for MS SQLServer Database):
javaw -cp .;images.jar;jsqltool.jar;jcalendar.jar;poi-2.0-RC2-20040102.jar;jgraph.jar;itext-1.4.3.jar;mssqlserver.jar;msbase.jar;msutil.jar org.jsqltool.MainApp
After having set the classpath according to the JDBC Driver, you can start the application.
When JSQLTool is run, the first window that appears is "JSQLTool Connection Manager" window. It shows all database connections already configured inside the application.
In this window you can create new connection definitions, delete or modify them.
The window allows the selection of a connection for creating the database connection. After the connection selection, JSQLTool shows the "Schema Brower" window (see later).
By clicking on "New Connection" button, you can define a new database connection.
By means of a database type combo-box you can configure four type of database connections:
an Oracle Database connection
a MS SQLServer Database connection
an ODBC Database connection
a generic JDBC Driver Database connection
Each database connection definition has a specific profile ".ini" file, stored in "/profile" folder: in this file are stored
connection settings (password is encrypted for security reasons), table filtering/sorting settings and previous queries executed by the user.
"Schema Browser" window shows the list of tables, views and synonyms for the catalog currently selected.
You can select a different catalog from the catalog combo-box above the table/view/synonym lists.
You can find a table/view/synonym in the list in a fast way, by means of the "Find" input control: when you digit a piece of a name,
this will be located and selected in the list.
You can define in "Skip with chars" input control a set of characters whose presence in a name will skip that name from the list.
This is useful for example in an Oracle Database schema to exclude all system tables from the table list, whose names contain $ and / symbols.
When you select a table/view/synonym from the list, you can view its detail on the right. The table detail includes:
Columns - i.e. the table structure, in terms of name, type, length, mandatory and default value
Clicking the right mouse will open a popup menu to execute the commands: "Add Column", "Drop Column" and "Drop Table"
Contraints - i.e. constraints applied to table columns
Indexes - i.e. indexes define to table columns
Data - i.e. the table content; this panel is very powerful: in allows to sort columns by clicking on the column header or
sort/filter data by clicking on the "Filter Data" button. This setting is automatically saved in the connection profile file,
so the next time you re-connect to the same connection and see the table content, filters/sorters are re-applied.
There is a toolbar above the data grid; it provides:
Filter/Sort button
Insert button - to insert a new record in the table; when use lost focus from the selected new row, the insert is fired
Delete button - to delete the selected records
Cancel Edit button - to cancel edit changes or a new record not yet inserted
Refresh button - to reload the current block of records
4 navigation buttons - record in grid are loaded in blocks of 100 records. You can retrieve the next/previous block of records by using up/down or page up/down buttons.
You can use "Commit" button to commit changes to the schema and "Rollback" button to rollback any changes.
By clicking on the data grid with the right mouse button, you will see a popup menu that shows a list of commands:
Filter the selected column by a specified value
Remove filter applied to the selected column
Import File into - used to import a file in a BLOB field
Export to File - used to export to file the BLOB field content
Copy Row - to duplicate the selected row on grid
Record Count - to count the number of records in the table (according to the filter settings currently applied)
Data Export - to export the table content. Export allows to export records in the clipboard (as insert SQL statements of as text values) or save them in a file (text file or Excel .xls file)
Script - i.e. SQL script that generates the entity (including indexes and foreign keys); actually constraints are not supported (f.k. excluded)
Triggers - i.e. the list of triggers linked to the table. Actually it supports only Oracle databases.
SQL Editor allows to write a SQL statement (a query or any other type of statement), execute it and see the results.
You can execute all editor content or select a piece of text and execute only it.
SQL text is colored according to SQL syntax.
You can right click on the SQL editor to format SQL text.
You can press CTRL+SPACE to open a tables selection window to speed up table name digitation.
You can press . key after a table name, to open a columns selection window to speed up column name digitation.
All SQL Statements previously executed are stored in the connection profile file (the last 20 statements).
The "Old SQL Statements" buttons views the list of previous SQL statements: you can select a statement from the list: it will be copied in the editor.
You can specify parameters in the SQL, using "?" symbol: when you execute the statement, you will be prompted to fill the parameters.
For Oracle Database you can click the query "Explain Plan" button to show the execution performance of the query.
You can see execution time in the bottom of the panel.
The toolbar button "Import sql script" allows to execute a SQL script file in "batch" mode (to speed up the execution of big sql script files)
Database Schema window allows to select one or more database entities from the lists (table/view/synonym lists) and create an entity-relationship diagram
from that selection. Database entities are showed in the diagram on the right by clicking with the mouse inside the diagram, after selecting at least one database entity in the left.
You can remove entities from the diagram by selecting them and pressing backspace key.
You can easly drag 'n drop entities placed in the diagram.
You can create many diagrams from the entities lists and save those diagrams in profile files. The toolbar above the diagram allows to:
Load a profile file, previously saved, related to a set of database entities to show as a diagram
Save in a profile file the set of database entities currently showrd in the diagram
Print the diagram
Print the diagram by fitting in one page the whole diagram
Export to file (in PDF format) the diagram, by fitting it in one page
The application menu provides the following commands:
File - create a new connection, end the current or all opened connections, data replication between two database schema (by selecting a list of tables to replicate), exit from the application
SQL Window - contains the same buttons as the SQL Editor window
Database - allows to view other SQL Editor windows or other Schema Browser windows. The menu contains also the command "Trace Session" (for Oracle Databases) to view current database sessions.
View - contains the Options command, used to modify application settings
Window - to switch from one window to another or to close one/all windows.
