Documentation Logo
 To do
 Project page


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.

How connect to a database

JDBC Drivers must be included in the application classpath, i.e. you have manually to edit run.bat or 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.

Schema Browser

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

"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

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

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

Other Features

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.
  • About - to view JSQLTool info and plugins list.

(c) 2006 by Mauro Carniel