JDBC

The Java JDBC API allows for database systems to be accessed from Java. To access a particular database, you need a JDBC driver for that database system. Some drivers which I have used are for common databases are directly linked to below:

To use any driver, you must put the package in your Java CLASSPATH. For example, to add the Postgres JDBC driver in a bash shell you would execute

export CLASSPATH=pgjdbc2.jar:$CLASSPATH

Then in your Java program you must load the driver by:

Class.forName(mydriver);

Once the driver has been loaded by a running Java application, any number of connections to the database may be made by:

Connection con=DriverManager.getConnection(myurl,myusername,mypassword);

where myusername and mypassword are valid login details for database named mydatabase running a RDBMS server called myserver.

For example, to connect to the films database on the department's Postgres server using the public access acount:

Class.forName("jdbc.postgresql.org");
Connection con=DriverManager.getConnection("jdbc:postgresql://db.doc.ic.ac.uk/films","lab","lab");

Remote JDBC access to databases behind firewalls

Most ports on the Imperial college network are protected by the college firewallm, but you may still gain access to these databases using ssh provided you have a username and password on a machine behind the firewall that can access the databases. For example, under linux, you would issue the following:

After being prompted for your password, the command exits, but will have setup a port forwarding from port number 5432 on your local machine, to access port 5432 db.doc.ic.ac.uk from shell1.doc.ic.ac.uk. Provided that shell1.doc.ic.ac.uk can access db.doc.ic.ac.uk you will now have access the DoC Postgres database, but you will use localhost in the URL rather than db.doc.ic.ac.uk, since the port forwarding makes the Postgres database appear to be on the local machine.

Which port you use depends on the database you are accessing, and is normally an installation configuration option. You also do not have to use the same port on the local and remote ends (the remote end must match the port used by the database server, the local end can be any free port on your local machine). The following table lists the default port used by some common DBMS servers.

Links

• CSG's help page on using JDBC
• JDBC API

General Purpose Tools

A number of tools are available which will utilize JDBC drivers to provide database independent access to databases:

• The DBVisualizer tool provides a simple GUI to view the schema of databases and to execute SQL queries on those databases.