Chapter 21: Java Database Connection (JDBC)

21.1: Connecting to a Database

For your program to connect to a database, you need to do the following things:

* Load in the Java driver of a database


Just like a printer driver or scanner driver, a database driver provides the user (Here it is Java program) with a standard interface, and hides the implementation details of an individual database from the user. The producer of a database software package has to provide a Java database driver to enable Java program to interact with this database.

It seems to be a convention that a database driver written in Java declares itself as in package "jdbc.DatabaseName". E.g., the driver of database SimpleText will have its first line as

   package jdbc.SimpleText;

and the driver of Oracle will have

   package jdbc.Oracle;

Therefore, you should expect to see a directory somewhere under the database directory named "jdbc\Database_Name" containing a driver class file named after the database name, e.g.




and you must point your CLASSPATH environment variable to the parent directory of "jdbc".

Therefore, the class loading line will become




* ODBC driver

A special case is ODBC, which is a technology to allow generic access to disparate database systems on Windows and Unix platform. Java provides a class "sun.jdbc.odbc.JdbcOdbcDriver" as driver for this technology. So the class loading line should be:


Besides loading the ODBC driver, you should also register the database as a ODBC data source. Procedures are:

  1. Control Panel => ODBC Data Sources icon;
  2. Click User DSN tab, then Add
  3. Select the correct database driver;
  4. Fill in the Data Source Name (DSN), could be any name you like;
  5. Enter a description if you like;
  6. Click the Select
  7. Click the Advanced

* Set up connection to the Database

Class java.sql.DriverManager's method getConnection returns an object implementing interface java.sql.Connection:

   Connection cn = DriverManager.getConnection
       ("protocol_&_databaseName", "userName", "password" );

To set up a connection to a database, you need to provide three things:

  1. The protocol for communication between Java and the database, which is always jdbc;
  2. The sub-protocol - when using ODBC, it's odbc
  3. The name of the database - when using ODBC, it's the Data Source Name you have assigned previously

* Examples

Now suppose the database name is "SimpleText", the driver is "jdbc.SimpleText.SimpleTextDriver", you should say:

   Class.forName( "jdbc.SimpleText.SimpleTextDriver" );
   Connection cn = ConnectionManager.getConnection( "jdbc:SimpleText", "", "" );

Suppose you are using Access and connecting to it with ODBC, the database filename is "Books.mdb", the User DSN is "BooksDB", then you should say:

   Connection cn = ConnectionManager.getConnection( "jdbc:odbc:BooksDB", "", "");

* Get a Statement

After loading in the database driver and set up a connection, the next step is to call Connection's method createStatement to get an object implementing interface java.sql.Statement, then call Statement's method execute (to execute a SQL query which you don't know whether it will return an object implementing interface java.sql.ResultSet), executeQuery (to execute a SQL query which returns a ResultSet, such as "select") or executeUpdate (to execute a SQL query which does not return a ResultSet, such as "insert").

   try {
       Statement statement = connection.createStatement();
       String query = "SELECT QTY FROM CARSTOCK WHERE NAME='" + car + "'";
       ResultSet resultSet = statement.executeQuery(query);
       if(resultSet == null) return false;;        // pointing to the first row of returned result
       long stock = resultSet.getLong(1); // get the value of column 1
       if(stock == 0) return false;
       query = "DELETE FROM CARSTOCK WHERE NAME='" + car + "'";
       query = "INSERT INTO CARSTOCK VALUES('" + car + "', " + stock + ")";
   catch(SQLException e)
      System.out.println("**** SQLException:\n" + e.getMessage());

Make your own free website on