Chapter 22

Working with Databases Using JDBC

Business applications usually store data in the databases. In most of the enterprise applications, Relational Database Management Systems (RDBMSes) are used as data storage. They store the data records in tables. Each record (such as that of an employee) is represented by a table row, which consists of one or more columns or record fields (e.g., name, address, hire date). RDBMSes understand the SQL language.

The most popular RDBMSes are Oracle, DB2, Sybase, Microsoft SQL Server, and MySQL Server. All samples from this lesson will use JavaDB, which is included with your JDK installation.

Java includes two packages that contain classes required for work with DBMSes: java.sql and javax.sql. The former contains commonly used classes such as Connection, Statement, and ResultSet. The latter is used for supporting database connectivity on the server side, containing classes such as DataSource, and RowSet.

The JDBC API is not DBMS-specific — if you write a program that uses JDBC classes to retrieve/update data in Oracle, you’ll be using the same classes to work with MySQL Server or DB2. You just need the JDBC drivers from the corresponding DBMS vendor — the drivers hide their database specifics behind the same public JDBC API.

JDBC drivers either pass SQL statements from Java to a DBMS for execution or simply execute a program stored inside a DBMS (called a stored procedure). If some data has been retrieved as the result of these actions, your Java program will handle it by making appropriate calls to the JDBC API. Over the past 15 years the JDBC specification has been evolving and, at the time of this writing, most drivers comply with JDBC version 4.0.

In this lesson all communications with the DBMS are made by supplying SQL statements to the JDBC API. There is an alternative way of working with data, by using one of the object-relational mapping (ORM) frameworks. In Lesson 36 you’ll get familiar with a popular ORM framework called Hibernate.

JDBC Driver Types

A JDBC driver plays the role of the middleman between a Java program and a DBMS. Drivers are available from database vendors, from Oracle, and from third-party vendors of Java application servers.

There are four general types of JDBC drivers.

A Type 1 driver is a JDBC-ODBC bridge that enables Java programs to work with the database using ODBC drivers from Microsoft. The drawbacks of ODBC drivers are that they are slower than the others, must be installed and configured on each user’s machine, and work only on Windows machines. The Type 1 JDBC drivers are rarely used.

A Type 2 driver consists of Java classes that work in conjunction with the non-Java native drivers provided by the DBMS vendor. These drivers work much faster than Type 1, but also require installation and configuration on the machine on which Java programs run.

A Type 3 driver is called a middleware driver and can be provided by vendors of application servers. It consists of two parts: the client portion performs a DBMS-independent SQL call, which is then translated to a specific DBMS protocol by the server portion of the driver.

A Type 4 driver is a pure Java driver, which usually comes as a .jar file and performs direct calls to the database server. It does not need any configuration on the client’s machine, other than including the name of the main driver’s class in your Java code. That’s why it’s also known as the thin driver. For example, Java applets can be packaged with this type of driver, which can be automatically downloaded to the user’s machine along with the applets themselves.

For simplicity I’ll be using JDBC drivers of Type 4 in this lesson, but many production systems can deploy Type 3 drivers to provide better performance.

Creating a Database with Derby

In this lesson I’ll be using an open-source DBMS called JavaDB (also known as Derby), which is packaged with Java 6. It’s a small DBMS that you wouldn’t use for serious production systems, but it’s great for learning JDBC or to use for many small systems. If you’ve installed Java SDK as instructed in Lesson 1 (see Figure 1-2), you already have JavaDB installed in the folder c:Program FilesSunJavaDB. Modify your system variable PATH so it starts with the following code:

c:Program FilesSunJavaDBin;

To make sure that you change PATH properly, open a command window and enter sysinfo. This command should print information about your Java environment, and you should find a reference to Derby and JDBC 4.0, as shown in Figure 22-1.

Figure 22-1

If you don’t have JavaDB installed, download and install it from http://db.apache.org/derby. Derby is well documented and if you haven’t had a chance to work with relational DBMSes, download and read the “Getting Started with Derby” manual.

Derby has an interactive command-line utility called ij that you can use to create databases and tables and populate them with data, among other actions. I’ll show you how to create a sample database and a table to store data about employees. First open a command window and issue the command startNetworkServer. Figure 22-2 shows the message that Derby has started and is ready to accept connections on port 1527.

Figure 22-2

Open another command window and start ij. First, connect to the database Lesson22 by issuing the following command:

connect 'jdbc:derby://localhost:1527/Lesson22;create=true';

This command will try to connect to the database Lesson22, and will create it if no such database is found. The next command will create a database table, Employee, to store records that consist of three fields: EMPNO, ENAME, and JOB_TITLE. The first field will be stored as an integer, and the other two as simple text (varchars) allowing 50 and 150 characters respectively.

CREATE TABLE Employee (
    EMPNO int NOT NULL,
    ENAME varchar (50) NOT NULL,
    JOB_TITLE varchar (150) NOT NULL   
);

Finally, to populate the table with some data, issue the INSERT command in ij:

INSERT INTO Employee values (7369,'John Smith', 'Clerk'), (7499,
'Joe Allen','Salesman'), (7521,'Mary Lou','Director'),

Figure 22-3 is a snapshot of the command window after the database Lesson22 and the table Employee have been created, and the three records have been inserted there.

Figure 22-3

If you want to ensure that the records were successfully created in the database, issue the SELECT SQL statement to retrieve the data:

Select * from Employee;

You’ll see the output of the above SQL statement in the next section. If you are not familiar with the syntax of SQL, refer to the tutorial at www.w3schools.com/sql/default.asp.

Sample JDBC Program

In this section I’ll show you the steps that you can perform to retrieve the data in any Java program that works with a relational database using JDBC. A sample program will implement all of these steps to display the list of employees from the database table Employee.

Class.forName("oracle.jdbc.driver.OracleDriver");

DriverManager.getConnection(url, user, password);  

DriverManager.getConnection("jdbc:derby:Lesson22");

Connection.createStatement();

Statement.executeQuery("Select ...");

Statement.updateQuery("Insert ...");

while (ResultSet.next()) {...} 

All these steps are implemented in the class EmployeeList, shown in Listing 22-1, which prints the records from the table Employee. Even though you don’t need to explicitly load the driver for Derby with Class.forName(), the location of the driver class has to be known to your program, otherwise you’ll get a “No suitable driver” error. Either add derbyclient.jar to the CLASSPATH system variable, or just add it as an external .jar to your Eclipse project (see the Java Build Panel, available from the Eclipse project Properties menu).

Listing 22-1: The EmployeeList program

package com.practicaljava.lesson22;
 
import java.sql.*;
class EmployeeList {
public static void main(String argv[]) {
 
   Connection conn=null;
   Statement stmt=null;
   ResultSet rs=null;
 
  try {
 
    // Load the JDBC driver - Class 
    // This can be skipped for Derby, but derbyclient.jar 
    //has to be in the CLASSPATH   
 
    // Class.forName("org.apache.derby.jdbc.ClientDriver");
 
    conn = DriverManager.getConnection("jdbc:derby://localhost:1527/Lesson22"); 
 
    // Create an SQL query 
    String sqlQuery = "SELECT * from Employee"; 
 
    // Create an instance of the Statement object
    stmt = conn.createStatement(); 
 
    // Execute SQL and get obtain the ResultSet object
    rs = stmt.executeQuery(sqlQuery);  
 
    // Process the result set - print Employees
    while (rs.next()){ 
    int empNo = rs.getInt("EMPNO");
        String eName = rs.getString("ENAME");
        String job = rs.getString("JOB_TITLE");
 
System.out.println(""+ empNo + ", " + eName + ", " + job ); 
    }
   } catch( SQLException se ) {
      System.out.println ("SQLError: " + se.getMessage ()
           + " code: " + se.getErrorCode());
   } catch( Exception e ) {
 
      System.out.println(e.getMessage()); 
      e.printStackTrace(); 
   } finally{
 
       // clean up the system resources
       try{
rs.close();     
stmt.close(); 
conn.close();  
       } catch(Exception e){
           e.printStackTrace();
       } 
   }
 }
}

The output of the program EmployeeList should look like this:

7369, John Smith, CLERK
7499, Joe Allen, SALESMAN
7521, Mary Lou, Director

When you execute any SQL statements, always include processing of errors that may be returned by the DBMS. Catching the SQLException is the right way to get the error message. Note that the code in Listing 22-1 calls the method getErrorCode() to extract the database-specific error code from the SQLException object.

Processing Result Sets

Let’s take a closer look at the code in Listing 22-1. After rs = stmt.executeQuery(sqlQuery), the cursor rs points at the very first record (row) of the result set in memory. Each row contains as many fields (columns) as were specified in the SQL Select statement. Each of the values is extracted by an appropriate method based on the data type of the field. The names of these methods are self-explanatory: rs.getString(), rs.getInt(), and so on. If you know the name of a column from the result, use it as a method argument:

int empNo = rs.getInt("EMPNO"); 
String eName = rs.getString("ENAME");

JDBC drivers are smart enough to convert the data from the database types to the corresponding Java types: For example, Derby’s varchar will become Java’s String.

You could have gotten the values of each field by specifying the relative position of the column from the result set:

int empNo = rs.getInt(1);
String eName = rs.getString(2);

Besides having names, columns in the result set are numbered starting with 1. In some cases numbers are your only choice; for example, the following SQL query does not list column names to select (the asterisk means “all”):

stmt.executeQuery("Select count(*) from EMP");

The class EmployeeList just prints the retrieved data in a loop. You can also place the result set in a Java collection object for further processing. The ResultSet object holds the database connection and is not serializable. That’s why common practice for programming server-side operations with DBMSes is to create a class representing a row from the result set and populate, say, an ArrayList or other Java collection with its instances.

Listing 22-2 shows an example of such a class, which can represent one employee record. Classes that hold only the value of some data are often called value objects. Because in distributed applications such objects may need to be transferred between different computers, they are also known as Data Transfer Objects (DTOs).

Listing 22-2: The EmployeeDTO

class EmployeeDTO{
 
  //private properties 
  private int empNo;
  private String eName;
  private String jobTitle;
  
  //setters 
  public void setEmpNo(int val){empNo=val;}
  public void setEName(String val){eName=val;}
  public void setJobTitle(String val){jobTitle=val;}
 
  // getters 
  public int getEmpNo(){return empNo;}
  public String getEName(){return eName;}
  public String getJob(){return jobTitle;}
}

EmployeeDTO declares private variables to store the data but access to this data is performed via public setters and getters, the methods that allow external code to set and get the appropriate values. This technique can be useful when some application-specific logic has to be applied at the moment when some code needs to get or modify the properties of the class EmployeeDTO.

For example, you can place some authorization code inside the setter to ensure that the external object has enough permissions to change the property jobTitle. If the business logic of obtaining such authorization changes in the future, you need to modify only the code inside the setter, but the external code will remain unchanged.

The next code snippet shows how the class EmployeeList can prepare a collection of EmployeeDTO objects while processing the result set retrieved by the SQL Select statement.

class EmployeeList {
...
 // Create an object for collection of employees
 ArrayList<EmployeeDTO> employees = new ArrayList<EmployeeDTO>(); 
 
 // Process ResultSet and populate the collection
 
 while (rs.next()){ 
  EmployeeDTO currentEmp = new EmployeeDTO();    
  currentEmp.setEmpNo(rs.getInt("EMPNO"));
  currentEmp.setEName(rs.getString("ENAME"));
  currentEmp.setJob(rs.getString("JOB_TITLE"));
 
  employees.add(currEmp);
 }
...
}

If this code is deployed on the server’s JVM and you need to send the data to another computer that runs, say, a Swing client, you can consider applying Java serialization here for sending a collection of employees to the front. But make sure that the class EmployeeDTO implements the Serializable interface.

The PreparedStatement Class

Listing 22-1 uses the class Statement to create an object capable of executing SQL. But this is not the only way to supply SQL to the JDBC API. The class PreparedStatement is a subclass of Statement, but it pre-compiles the SQL statement before executing it.

With PreparedStatement you can create SQL with parameters that are dynamically passed by the program. Suppose you need to execute the query "SELECT * from EMP WHERE empno=..." multiple times, providing the empno values from the array empNumbers[]. If you use the class Statement, the variable sqlQuery will have to be pre-compiled on each iteration of the loop:

for (int i=0;i<empNumbers.length; i++){
  sqlQuery="SELECT * from Employee WHERE empno=" + employees[i];
  stmt.executeQuery(sqlQuery); 
}

The class PreparedStatement offers a more efficient solution:

PreparedStatement stmt=conn.prepareStatement( 
                       " SELECT * from Employee WHERE empno=?");
 
for (int i=0;i<empNumbers.length; i++){
 
  // pass the array's value that substitutes the question mark
  stmt.setInt(1,employees[i];) 
  stmt.executeQuery(sqlQuery); 
}

In this case the SQL statement is compiled only once and parameters are provided by the appropriate setXXX() method depending on the data type. The SQL statement may have several parameters (question marks), and the first argument of the setter enables you to specify each parameter’s number. For example:

PreparedStatement stmt=conn.prepareStatement( 
      "SELECT * from Employee WHERE empno=? and ename=?");
 
for (int i=0;i<empNumbers.length; i++){
  stmt.setInt(1,empNumbers[i];) 
  stmt.setString(2,empNames[i];) 
  stmt.executeQuery(sqlQuery); 
}

If you need to pass a NULL value as a parameter, use the method setNull().

The CallableStatement Class

This class extends PreparedStatement and is used for executing stored procedures from Java. Let’s say there is a stored procedure entitled changeEmpTitle that takes two parameters: empno and title. Here’s the code to execute this stored procedure:

CallableStatement stmt = conn.prepareCall("{call changeEmpTitle(?,?)}");
 
stmt.setInt(1,7566);
stmt.setString (2,"Salesman");
stmt.executeUpdate();

If a stored procedure returns some values using output parameters, each of the OUT data types has to be registered before the statement is executed. The next code snippet shows you an example of executing a stored procedure that has two parameters: The first is an input parameter, and the second is an output parameter by which the stored procedure can return the result of its execution to the Java program:

CallableStatement stmt = conn.prepareCall( 
                 ("{call getEmpTitle(?,?) }");
stmt.setInt(1, 7566);
stmt.registerOutParameter(2,java.sql.Types.VARCHAR);
stmt.executeQuery();
String title=stmt.getString(2);

The ResultSetMetaData Class

JDBC enables you to process result sets when the number of returned values is unknown. Imagine that you need to write a program that can accept any SQL Select statement, execute it, and display the retrieved data. With the class ResultSetMetaData you can dynamically find out how many columns there are in the result set, as well as their names and data types. The following code fragment gets the number of the database table columns in the result set and for each of them identifies and prints the column name and type:

String sqlQuery = "select * from Employee";
ResultSet rs = stmt.executeQuery(query);
 
ResultSetMetaData rsMeta = rs.getMetaData();
int colCount = rsMeta.getColumnCount();
 
for (int i = 1; i <= colCount; i++)  {
  System.out.println(
      " Column name: " + rsMeta.getColumnName(i) + 
      " Column type: " + rsMeta.getColumnTypeName(i));
}

This simple but powerful technique is used internally by ORM frameworks (see Lesson 36) that can “magically” generate database models and automatically generate Java classes representing database entities.

Listing 22-3 shows a Java program called ShowAnyData that prints a result set based on any SQL Select statement passed from the command line. For example, it can be started as follows:

java ShowAnyData "Select * from Employee"

Listing 22-3: Using ResultSetMetaData

import java.sql.*;
class ShowAnyData {
 
 public static void main(String args[]) {
   Connection conn=null;
   Statement stmt=null;
   ResultSet rs=null;
 
 if (args.length==0){
 
    System.out.println("Usage: java ShowAnyData SQLSelectStatement");
    System.out.println("For example: java ShowAnyData "Select * from Employee"");
    System.exit(1);
  }
 
   try {
                  // Class.forName("org.apache.derby.jdbc.ClientDriver");
      conn = DriverManager.getConnection( 
                            "jdbc:derby://localhost:1527/Lesson22"); 
 
      stmt = conn.createStatement(); 
     
    // Execute SQL query passed from command line 
    rs = stmt.executeQuery(args[0]);  
 
    // Find out the number of columns, their names and display the data
    ResultSetMetaData rsMeta = rs.getMetaData();
    int colCount = rsMeta.getColumnCount();
 
     for (int i = 1; i <= colCount; i++)  {
      System.out.print(rsMeta.getColumnName(i) + " "); 
    }
 
    System.out.println();
 
    while (rs.next()){ 
       for (int i = 1; i <= colCount; i++)  {
         System.out.print(rs.getString(i) + " "); 
       }
       System.out.print("
");   // new line character
    }
 
   } catch( SQLException se ) {
      System.out.println ("SQLError: " + se.getMessage ()
           + " code: " + se.getErrorCode ());
   } catch( Exception e ) {
      System.out.println(e.getMessage()); 
      e.printStackTrace(); 
   } finally{
 
       // clean up the system resources
       try{
                     rs.close();     
                     stmt.close(); 
                     conn.close();  
       } catch(Exception e){
           e.printStackTrace();
       } 
   }
 }
}

The output of the ShowAnyData program will be the same as that of EmployeeList shown in Listing 22-1. But this program can execute any SQL statement as long as you are specifying valid database objects. Note that the code in ShowAnyData first ensures that you have passed the command-line argument. If you run this program from a command line, don’t forget to include the SQL statement in double quotes. In Eclipse you can specify a command-line argument by selecting the Run Configuration panel in the Arguments tab.

Scrollable Result Sets and RowSet

In all the preceding examples the code traversed the result set using the method next(), which moves only the cursor forward. Another option is to create a scrollable result set so the cursor can be moved back and forth if need be. There is a two-argument version of the method createStatement(). The first argument specifies the type of scrolling (TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, or TYPE_SCROLL_SENSITIVE) and the second makes the result set updateable or read-only (CONCUR_READ_ONLY or CONCUR_UPDATABLE). For example,

Statement stmt = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);  
ResultSet rs = stmt.executeQuery("SELECT * from Employee");

The TYPE_FORWARD_ONLY parameter allows only forward movement of the cursor. The difference between TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE is in whether scrolling reflects changes that have been made to the result set. The next example sets the cursor at the end of the result set and moves the cursor backward:

rs.afterLast();    
while (rs.previous()){ 
 int empNo = rs.getInt("EMPNO");
 String eName = rs.getString("ENAME");
 String job = rs.getString("JOB");
 System.out.println(""+ empNo + ", " + eName + ", " + job_title); 
}

You can also move the cursor to a specific row by using the following self-explanatory methods:

rs.absolute(25);  // moves the cursor to the 25th row
rs.relative(-4);  // moves the cursor to the 21st row 
rs.first();       
rs.last();
rs.beforeFirst();

If the result set is updatable (CONCUR_UPDATABLE) you can modify the underlying database table while scrolling. For example, the following statements will update the job title of the employee based on the current cursor’s position:

rs.updateString("JOB_TITLE","Manager");
rs.updateRow(); 

Scrollable result sets enable you to traverse the result set in both directions, but they have a drawback: They hold the database connection, which may be required by another thread or program. The package javax.sql includes the interface RowSet, which is a subclass of ResultSet. RowSet gets the data from the database, then disconnects, but still allows Java to work with the data. The package javax.sql.rowset has several concrete classes that implement RowSet, such as CachedRowSet, FilteredRowSet, and WebRowSet. The latter can turn RowSet into an XML stream to be sent to another tier in the distributed application.

Transactional Updates

Sometimes several database modifications have to be processed as one transaction, and if one of the updates fails, the whole transaction has to be rolled back. These database operations have to be explicitly committed (finalized) in case of success. If you set the auto-commit parameter on the database connection to false, the database transaction will not be committed until the code explicitly calls the method commit(), as in the following example:

try{
  conn.setAutoCommit(false);
 
  Statement stmt = con.createStatement();
  
  stmt.addBatch("insert into Orders " +
              "values(123, 'Buy','IBM',200)");
  stmt.addBatch("insert into OrderDetail " +
              "values('JSmith', 'Broker131', '05/20/02')");
  stmt.executeBatch();
 
  conn.commit();    // Transaction succeded
 
}catch(SQLException e){
  conn.rollback();  // Transaction failed
  e.printStackTrace(); 
}

In the preceding code snippet two Insert statements have to be executed as one transaction, and if any of them fails, an exception will be thrown and the method rollback() will undo all the changes, including those that succeeded.

Connection Pools and DataSources

Up until now you’ve been running all sample Java programs on your own computer. But imagine a distributed application in which multiple clients make requests to the same server, which has to process their SQL queries. Because obtaining a connection to the database is a slow process, it would be very inefficient to start every SQL request by obtaining a database connection and disconnecting after the request is complete. Such applications should reuse the same opened connection for multiple requests.

The package javax.sql includes the interface DataSource, which is an alternative to DriverManager. Vendors of JDBC drivers for servers implement this interface, and a DataSource is typically preconfigured for a certain number of connections (the connection pool). It is published in a directory using the JNDI interface. In such a setup, all clients’ requests will get their database connections from this DataSource object, eliminating the need to open and close a new connection for each request. I’ll provide an example of working with DataSource objects in Lesson 31 after explaining how JNDI works.

Try It

In this assignment you’ll modify the class Portfolio from Lesson 22, which was just printing some hard-coded statements. Now you’ll create and populate the database table Portfolio and then read and display the data from there.

Lesson Requirements

You should have Java installed.

You can download the code and resources for this Try It from the book’s web page at www.wrox.com. You can find them in the Lesson22 folder in the download.

Hint

Obtaining a database connection is a slow operation, and doing it from inside the method run() every time you start a new thread is not the best solution. Consider creating a database connection up front and passing it to the thread before starting it.

Step-by-Step

create table Portfolio(
id NUMBER NOT NULL,
symbol VARCHAR2(10) NOT NULL,                  
quantity VARCHAR2(10) NOT NULL,                  
price NUMBER NOT NULL, PRIMARY KEY (id)                  
);

insert into Portfolio  values (1,'IBM',500,105.50),
 (2,'AMZN',1000,15.25),(3,'AAPL',2000,272.50);

Please select Lesson 22 on the DVD with the print book, or watch online at www.wrox.com/go/fainjava to view the video that accompanies this lesson.