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

   
   Class.forName("full_driver_class_name");

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.

   
   jdbc\SimpleText\SimpleTextDriver.class

or

   jdbc\Oracle\OracleDriver.class

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

Therefore, the class loading line will become

   
   Class.forName("jdbc.SimpleText.SimpleTextDriver");

or

   Class.forName("jdbc.Oracle.OracleDriver");

* 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:

   
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

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:

   
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   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;
       resultSet.next();        // pointing to the first row of returned result
       long stock = resultSet.getLong(1); // get the value of column 1
       if(stock == 0) return false;
   
       stock--;
       query = "DELETE FROM CARSTOCK WHERE NAME='" + car + "'";
       statement.executeUpdate(query);
   
       query = "INSERT INTO CARSTOCK VALUES('" + car + "', " + stock + ")";
       statement.executeUpdate(query);
   }
   catch(SQLException e)
   {
      System.out.println("**** SQLException:\n" + e.getMessage());
      System.exit(1);
   }