Java JDBC package

What is JDBC

JDBC stands for Java Database Connectivity. This Java package provides tools for remote connection to a relational database, for running SQL queries on the database, and for retrieving and manipulating results of such queries. A program that uses JDBC acts as a client which is connecting to a database server. In our case you will be running your program on the machine puma, remotely connecting to the databsse server birch.

How to compile and run a JDBC program

A JDBC program uses a database driver to "communicate" with the database. The database driver is a program, usually already precompiled, which needs to installed on the client machine. We will use McKoi JDBC driver which you can download in the file mckoidb.jar. In the examples below I assume that your program is in the file Myjdbc.java in the same directory as the driver.

Your program can be compiled as usual, for instance:


javac Myjdbc.java

If your program is an application, to run it, you need to type:


java -cp mckoidb.jar:. Myjdbc
Here Myjdbc is the class that you are running. The -cp variable specifies the class path: the path to the classes used in this program. The classes include the precompiled classes in mkoidb.jar and those in the current directory (your classes). The current directory is refered to as .(dot). The symbol : separates the two paths.

Example of using JDBC

The example below assumes that the database already exists and has a table Students.

import java.sql.*;

       public class ConnectToServerDemo {

         public static void main(String[] args) {

           // Register the Mckoi JDBC Driver
           try {
             Class.forName("com.mckoi.JDBCDriver").newInstance();
           }
           catch (Exception e) {
             System.out.println(
               "Unable to register the JDBC Driver.\n" +
               "Make sure the JDBC driver is in the\n" +
               "classpath.\n");
             System.exit(1);
           }

           // This URL specifies we are connecting with a database server
           // on localhost.
           String url = "jdbc:mckoi://birch/";

           // The username / password to connect under.
           String username = "cs349";
           String password = "cs349";

           // Make a connection with the database.
           Connection con;
           try {
             con = DriverManager.getConnection(url, username, password);
           }
           catch (SQLException e) {
             System.out.println(
               "Unable to make a connection to the database.\n" +
               "The reason: " + e.getMessage());
             System.exit(1);
             return;
           }

           try {
	       
	     Statement stmt = con.createStatement();
           
	     // using executeQuery():
	     ResultSet rs = stmt.executeQuery(
                  "SELECT * FROM Students"); 
	     
	     // moving forward in the result set:
	     while (rs.next()) {
                  int id = rs.getInt("ID");
                  String first = rs.getString("First_Name");
		  String last = rs.getString("Last_Name");
		  String year = rs.getString("Year");
		  float gpa = rs.getFloat("GPA");
		  Date date = rs.getDate("Date_of_birth");
                  System.out.print(id + "   " + first + "   " + last + "   ");
		  System.out.println(year + "   " + gpa + "   " + date);
		  }

             // Close the connection when finished
             con.close();

           }
           catch (SQLException e) {
             System.out.println(
               "An error occured\n" +
               "The SQLException message is: " + e.getMessage());
             return;
           }

         }

       }

Registering the driver and opening the connection

The first thing that you need to do in every program that uses JDBC is to create a new instance of the driver and to register it. This is done in the statement Class.forName("com.mckoi.JDBCDriver").newInstance(); Different databases use different drivers If you are accessing another database, you need to download its driver and find out what to specify as the driver name.

The method throws an exception if the driver cannot be found or cannot be registered. If you don't specify the correct class path to the driver, then you will the error message that you specify in the catch clause.

If the driver has been successfully registered, the next step is to establish a connection to the database. A connection is implemented via a Connection object. A new connection is returned from the method getConnection() of the DriverManager class. To open a connection, you need to supply the correct URL, user name, and password. The method throws SQL Exception if for some reason the connection cannot be established.

The connection is used to pass SQL statements to the database. The connection must be closed in the end.

This part of the program is standard, you can use it in any JDBC program.

Retrieving data from a database via SELECT

To retrieve data, you need to create a Statement object associated with the current connection. The results of the query will be stored in a ResultSet object. The line that passes the query to the database and stores the results is

 ResultSet rs = stmt.executeQuery("SELECT * FROM Students");
You can pass any SELECT query to the database using executeQuery(). The method next() of a result set moves the cursor to the next row in the result set. The cursor indicates which row the result set is currently at. Initially the cursor is before the first row, so to get to the first row it needs to execute the method next(0 once. In the current version of JDBC (JDBC 2.0) there is also a way to move backward in a result set by using the method previous().

A row in the reslut set consists of values of several columns. To extract each value, you need to know its type and use the appropriateget method. In the example above, the line


int id = rs.getInt("ID");
extracts an integer value from the column ID. There are methods for extracting all SQL data types. Notes that the type CHAR(x) is converted into a Java class String, and the type DATE into a Java class Date.

Instead of giving the name of the column as a parameter, you can also specify the number of the column:


int id = rs.getInt(1);
is equivalent to the above statement.

It is possible that the result set returns only some columns of the table, but not all. In this case the numbers refer to the columns in the result set, not in the table. For instance, if we select only First_Name and Last_Name, the statements to extract the strings would be


String first = rs.getString(1);
String last = rs.getString(2);
even though ID is the column 1 in the table Students. If you are extracting only some columns, you can still refer to the columns by name:

String first = rs.getString("First_Name");
String last = rs.getString("Last_Name");
If the select statement returns a number, s.a. the result of SELECT COUNT(*) ..., then the number is treated as a set with one column. To extract the number, use:

int count = rs.getInt(1);

Storing information in the database

Storing and changing data in a databases is similar, except for two things:
  1. use executeUpdate() instead of executeQuery(),
  2. since there is no result returned, there is no need for a result set.
For instance, the following code fragment inserts a new row into Students table.

	       Statement stmt = con.createStatement();
           
	       // using executeUpdate():
	       stmt.executeUpdate("INSERT INTO Students " +
                  "VALUES (345, 'Clara', 'Green', '04', 3.6, '1980-06-23')");

Important technical detail: note the space before the closing quotation mark in the string concatenation. This space is required, otherwise SQL will be looking for the table StudentsVALUES (the result of concatentation of "Students" and "VALUES").

You can use excuteUpdate() for creating and altering tables as well.

Using prepared statements

A prepared statement is a statement which is a statement which is sent to the database so that the query contained in the statement can be precompiled and stored by the database. Invoking a prepared statement that has been precompiled makes the query excute faster.

A precompiled statement can have parameters whose values can be different for different executions of the query. Below is an example which performs a SELECT statement, searching for students who have taken a particular course in a particular semester. The values of the parameters (the course name and the semester) can be reset several times in the same program.


	       PreparedStatement selectByCourse = con.prepareStatement(
			"SELECT First_Name, Last_Name FROM " +
			"Students, Courses, Enrollment " +
			"WHERE (Students.ID = Enrollment.Student_ID AND " +
			"Courses.ID = Enrollment.Course_ID AND " +
			"Courses.Course_Name LIKE ? AND " +
			"Courses.Semester LIKE ? )");

	       selectByCourse.setString(1, "CS111%");
	       selectByCourse.setString(2, "S02%");

	       ResultSet rs = selectByCourse.executeQuery();

	       while (rs.next()) {
                  String first = rs.getString(1);
		  String last = rs.getString(2);
		  System.out.println(first + "   " + last);
	       }

As a side issue, notice that we have to match the course name and the semester (which are strings) using LIKE, not =, and the pattern is followed by the %, s.a. "CS111%". This is because the SQL data types are declared as CHAR(x), not VARCHAR, so the strings stored in the database are longer than those we are matching them against.

Using transactions

JDBC transaction mechanism allows to consider one or several SQL statements to be a transaction. Statements in a transaction are followed by either COMMIT command, in which case the effect of all of the transaction statements takes place, or by a ROLLBACK command, in which case none of the statements in a transaction takes place. Below is an example:


	       // the transaction will not commit unless explicitly committed
	       // or rolled back
	       con.setAutoCommit(false);

	       Statement stmt = con.createStatement();
           
	       // using executeUpdate():
	       stmt.executeUpdate("UPDATE Students " +
				  "SET GPA = 3.9 WHERE ID = 333");

	       // change to con.commit() if you want to commit
	       con.rollback();
	      
	       con.setAutoCommit(true);

The Java statement con.setAutoCommit(false); changes the default behaviour of JDBC: while by default each SQL statement takes effect immediately, after the change no SQL statement will take place until explicitly committed.

In this case the transaction consists of just one statement, and in the end the command ROLLBACK is performed, so no actual update to the database takes place. If we replace con.rollback(); by Con.commit():, then the update would take place. Notice that in the end we restore the default JDBC behaviour by the command con.setAutoCommit(true);.

If a program consists of several transactions, then the structure of the program would be as follows:


	       // the transaction will not commit unless explicitly committed
	       // or rolled back
	       con.setAutoCommit(false);

	       // statements of the first transaction

	       // con.commit() or con.rollback()

	       // statements of the second transaction

	       // con.commit() or con.rollback()

	       // etc.
	      
	       con.setAutoCommit(true);

One can also change the isolation level of a transaction. For instance:


	       con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

sets the isolatin level of the transaction to READ COMMITTED (see the lecture on SQL for material on transaction isolation levels). See the Connection API for details.

When you should use rollback: if an SQL exception has occurred during a transaction, you should use ROLLBACK to restore the database to the original state. Otherwise part of a transaction may have taken effect, but you don't have any way of knowing which changes have and which haven't been made.


Some material on this page has been adopted from a subset of online sources listed here
This page has been created and is maintained by Elena Machkasova
Comments and suggestions are welcome at emachkas@wellesley.edu

Spring Semester 2002