Debugging JDBC Connections with SQuirreL

Most Java applications use JDBC drivers to connect to a persistent store or database. Different databases however use different JDBC drivers and connection URL formats, and it can be very useful in case of problems to have a way to test that you are using the correct type and version of JDBC driver for your database and the correct connection parameters (connection URL, login credentials, etc) without having to write any code. Squirrel SQL is a universal Java database client that can be used for this purpose.

Squirrel SQL is open source, entirely written in Java, and can be used to connect to any database (SQL or NoSQL) for which there exists a JDBC driver. In this article we will explore how quick and simple it is to test a JDBC connection with Squirrel SQL.

Installation

The first step is to download the Squirrel SQL installer which can be found here:

http://squirrel-sql.sourceforge.net/

Note: if the URL has changed just type “Squirrel SQL client” on your favourite search engine to find it.

The installer is an executable JAR file, therefore you need to have a Java Runtime Environment installed on your machine. To run the installer, double-click on the JAR file or type on the command line:

$ java -jar squirrel-sql-<version>-install.jar

Follow the setup wizard to select the installation directory, keeping the defaults on the plugin selection screen. I also recommend selecting the option to create a desktop shortcut.

Squirrel SQL shortcut icon

Setup

The installation directory will contain the squirrel-sql startup script (.bat in Windows or .sh in Linux). Run the script for your platform or double click the desktop icon to run Squirrel SQL.

Squirrel SQL main screen

Squirrel SQL main screen

We will now use Squirrel SQL to connect to an Oracle database. The same steps can be followed to connect to any type of database, SQL or NoSQL, for which there is a JDBC driver.

Of course you need to know some essential details about the database you are running such as version, the host name or IP address and the port number. If you don’t know how to collect such information consult your database documentation.

The database I am going to connect to in this example is an Oracle Database 11g Express Edition Release 11.2.0.2.0 running on localhost. The Oracle instance identifier (SID) is “xe” listening to port 1521.

Knowing the version of the database you are running will help you identify the JDBC driver you need. Normally database vendors provide a JDBC driver download website. Oracle’s is located at the following address:

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

The site lists the drivers for the various Oracle versions. In this case I need to download the driver for version 11g release 2. I will select the JDBC thin driver (type 4) because it is platform independent as it is entirely written in Java and use Java sockets to connect directly to the database. If you are connecting to one specific database the Type 4 thin driver is normally the recommended choice.

Download the JDBC driver for your database and save it locally. The driver I have downloaded is a Java Archive (JAR) file named ojdbc6.jar.

JDBC driver file

Installing the driver

Connecting to a database with Squirrel SQL is quite simple. First you install the JDBC driver for your database, then you create an “alias” where you configure the parameters to connect to a specific instance. Once connected you can browse the database objects or issue SQL commands.

To install the JDBC driver, click on the Drivers tab on the left hand side of Squirrel’s main screen. You will see a list of drivers appearing in alphabetical order, where you need to locate the driver for your database. In this example, we will select (with a double click) “Oracle Thin Driver”.

Squirrel SQL Driver Setup

Squirrel SQL Driver Setup

The following dialog displays showing the driver’s name, example URL and class name:

Driver Setup Dialog

Driver Setup Dialog

Now select the “Extra Class Path” tab, and click on the [Add] button. Browse to the location where you saved the JDBC driver you downloaded in the previous step, and click [Open]. The driver will be listed. Click OK to complete the installation. You will be returned to the driver’s list, and if all is OK a check mark will appear next to the installed driver.

Making the connection

Now that the JDBC driver is installed, the last step is to create an alias to connect to the database instance you are running.

To accomplish this task select the “Aliases” tab on the left-hand side of Squirrel’s main screen, then click on the blue + (plus sign) icon. The “Add Alias” dialog will display:

Coonection Setup Dialog

Coonection Setup Dialog

From the driver’s drop-down list select the JDBC driver you installed in the previous step. Give the alias an arbitrary name that describes the instance you are connecting to, fill the missing details in the JDBC URL (hostname, port number, database name), enter user name and password, then click [Test] to test the connection. Adjust the parameters if necessary until the test succeeds. You now have the correct driver and connection parameters to use in your application or to configure a data source in your application server.

Leave a Reply

Your email address will not be published. Required fields are marked *