Java JDBC package

What is JDBC

How to compile and run a JDBC program

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

Example of using JDBC

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

 ResultSet rs = stmt.executeQuery("SELECT * FROM Students");

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");

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);

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);

String first = rs.getString("First_Name");
String last = rs.getString("Last_Name");

int count = rs.getInt(1);

Storing information in the database

1. use executeUpdate() instead of executeQuery(),
2. since there is no result returned, there is no need for a result set.

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

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

Using prepared statements

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

	       // 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);

	       con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);