Skip to main content

Accessing JDBC Databases

This chapter provides examples of Java code using the Caché JDBC driver to query databases and work with the results.

Note:

In the examples given in this chapter, several methods throw exceptions of type SQLException. The required try catch blocks are omitted for clarity.

A Simple JDBC Application

This section describes a very simple JDBC application that demonstrates the use of some of the most common JDBC classes:

  • A CacheDataSource object is used to create a Connection object that links the JDBC application to the Caché database.

  • The Connection object is used to create a PreparedStatement object that can execute a dynamic SQL query.

  • The PreparedStatement query returns a ResultSet object that contains the requested rows.

  • The ResultSet object has methods that can be used to move to a specific row and read or update specified columns in the row.

All of these classes are discussed in more detail later in the chapter.

The TinyJDBC Application

To begin, import the JDBC packages and open a try block:

import java.sql.*;
import javax.sql.*;
import com.intersys.jdbc.*;

public class TinyJDBC{
  public static void main() {
    try {

Use CacheDataSource to open a connection ( for details, see Using CacheDataSource to Connect):

      Class.forName ("com.intersys.jdbc.CacheDriver").newInstance();
      CacheDataSource ds = new CacheDataSource();
      ds.setURL("jdbc:Cache://127.0.0.1:1972/SAMPLES");
      Connection dbconn = ds.getConnection("_SYSTEM","SYS");

Execute a query and get a scrollable, updatable result set.

      String sql="Select Name from Sample.Person Order By Name";
      int scroll=ResultSet.TYPE_SCROLL_SENSITIVE;
      int update=ResultSet.CONCUR_UPDATABLE;

      PreparedStatement pstmt = dbconn.prepareStatement(sql,scroll,update);
      java.sql.ResultSet rs = pstmt.executeQuery();

Move to the first row of the result set and change the name.

      rs.first();
      System.out.println("\n Old name = " + rs.getString("Name"));
      rs.updateString("Name", "Bill. Buffalo");
      rs.updateRow();
      System.out.println("\n New name = " + rs.getString("Name") + "\n");

Close objects and catch any exceptions.

      pstmt.close();
      rs.close();
      dbconn.close();
    } catch (Exception ex) {
      System.out.println("TinyJDBC caught exception: " 
             + ex.getClass().getName() + ": " + ex.getMessage());
    }
  } // end main()
} // end class TinyJDBC
Important:

In the rest of this chapter, examples will be presented as fragments of code, rather than whole applications. These examples are intended to demonstrate some basic features as briefly and clearly as possible. It will be assumed that a connection has already been opened, and that all code fragments are within an appropriate try/catch statement. It is also assumed that the reader is aware of the standard good coding practices that are not illustrated here.

Using Statements

The sql.java package provides three classes used to query databases and return a ResultSet:Statement, PreparedStatement, and CallableStatement. All three classes are instantiated by calls to Connection methods. The following sections discuss how to use these classes:

Using Statement to Execute a SELECT

The following code executes an SQL SELECT on Caché using the Statement class:

  • Create a query string and execute it using the java.sql.Statement execute() method:

       String stQuery="SELECT ID, Name from Sample.Person";
       java.sql.ResultSet rs = stmt.executeQuery(stQuery);
    
    

    You should always use the fully qualified name java.sql.ResultSet to avoid clashes with com.intersys.classes.ResultSet

  • Process and display the query results:

       ResultSetMetaData rsmd = rs.getMetaData();
       int colnum = rsmd.getColumnCount();
       while (rs.next()) {
          for (int i=1; i<=colnum; i++) 
             System.out.print(rs.getString(i) + "  ");
       }
    
    

Executing a Prepared Statement

The following query uses a prepared statement to return a list of all employees with names beginning in “A” through “E” who work for a company with a name starting in “M” through “Z”:

   Select ID, Name, Company->Name from Sample.Employee 
   Where Name < ? and Company->Name > ? 
   Order By Company->Name
Note:

This statement uses Implicit Join syntax (the –> operator), which provides a simple way to access the Company class referenced by Sample.EmployeeOpens in a new tab.

The prepared statement is implemented just like a regular statement:

  • Create the string containing the query and use it to initialize the PreparedStatement object, then set the values of the query parameters and execute the query:

       String sql=
          "Select ID, Name, Company->Name from Sample.Employee " +
          "Where Name < ? and Company->Name > ? " +
          "Order By Company->Name";
       PreparedStatement pstmt = dbconnection.prepareStatement(sql); 
    
       pstmt.setString(1,"F");
       pstmt.setString(2,"L");
       java.sql.ResultSet rs = pstmt.executeQuery();
    
    
  • Retrieve and display the result set:

       java.sql.ResultSet rs = pstmt.executeQuery();
       ResultSetMetaData rsmd = rs.getMetaData();
       int colnum = rsmd.getColumnCount();
       while (rs.next()) {
          for (int i=1; i<=colnum; i++) {
             System.out.print(rs.getString(i) + "  ");
          }
          System.out.println();
       }
    
    

Using Callable Statements to Execute Stored Procedures

The following code executes ByName, a Caché stored procedure contained in Sample.PersonOpens in a new tab:

  • Create a java.sql.CallableStatement object and initialize it with the name of the stored procedure. The SqlName of the procedure is SP_Sample_By_Name, which is how it must be referred to in the Java client code:

       String sql="call Sample.SP_Sample_By_Name(?)"
       CallableStatement cs = dbconnection.prepareCall(sql);
    
    
  • Set the value of the query parameter and execute the query, then iterate through the result set and display the data:

       cs.setString(1,"A");
       java.sql.ResultSet rs = cs.executeQuery();
    
       ResultSetMetaData rsmd = rs.getMetaData();
       int colnum = rsmd.getColumnCount();
       while (rs.next()) {
          for (int i=1; i<=colnum; i++) 
             System.out.print(rs.getString(i) + "  ");
       }
       System.out.println();
    
    

Returning Multiple Result Sets

Caché allows you to define a stored procedure that returns multiple result sets. The Caché JDBC driver supports the execution of such stored procedures. Here is an example of a Caché stored procedure that returns two result sets (note that the two query results have different column structures):

   /// This class method produces two result sets.
   ClassMethod DRS(st) [ ReturnResultsets, SqlProc ]
   {
    $$$ResultSet("select Name from Sample.Person where Name %STARTSWITH :st")
    $$$ResultSet("select Name, DOB from Sample.Person where Name %STARTSWITH :st")
    Quit
   }

Note:

This stored procedure is not defined in Sample.PersonOpens in a new tab. In order to try this example, you must first open Sample.PersonOpens in a new tab in Atelier and add the class method shown above. The $$$ResultSet routine prepares and executes a SQL statement (which must be a string literal, available at compile time) and returns the resultset. To use it, you must add the declaration include %occResultSet at the start of the Sample.PersonOpens in a new tab file (before the class definition, as shown here):

include %occResultSet
Class Sample.Person Extends (%Persistent, %Populate, %XML.Adaptor)
{ ... 

Remember to recompile Sample.PersonOpens in a new tab after making these changes.

The following code executes the stored procedure and iterates through both of the returned result sets:

  • Create the java.sql.CallableStatement object and initialize it using the name of the stored procedure. Set the query parameters and use execute to execute the query:

       CallableStatement cs = dbconnection.prepareCall("call Sample.Person_DRS(?)");
       cs.setString(1,"A");
       boolean success=cs.execute();
    
    
  • Iterate through the pair of result sets displaying the data. Note that getMoreResults moves to the Statement object's next result set while getResultSet retrieves the current result set.

       if(success) do{
          java.sql.ResultSet rs = cs.getResultSet();
          ResultSetMetaData rsmd = rs.getMetaData();
          for (int j=1; j<rsmd.getColumnCount() + 1; j++)
             System.out.print(rsmd.getColumnName(j)+ "\t\t");
          System.out.println();
          int colnum = rsmd.getColumnCount();
          while (rs.next()) {
             for (int i=1; i<=colnum; i++) 
                System.out.print(rs.getString(i) + " \t ");
             System.out.println();
          }
          System.out.println();
        } while (cs.getMoreResults());
    
    
Note:

By default getMoreResults closes the current result set before moving to the next. The Caché JDBC Driver does not support keeping the current result set open after moving to the next.

Statement Pooling

JDBC 4.0 adds an additional infrastructure, statement pooling, which stores optimized statements in a cache the first time they are used. Statement pools are maintained by connection pools, allowing pooled statements to be shared between connections. All the implementation details are completely transparent to the user, and it is up to the driver to provide the required functionality.

Caché JDBC implemented statement pooling long before the concept became part of the JDBC specification. While the Caché driver uses techniques similar to those recommended by the specification, the actual pooling implementation is highly optimized. Unlike most implementations, Caché JDBC has three different statement pooling caches. One roughly corresponds to statement pooling as defined by the JDBC specification, while the other two are Caché specific optimizations. See Cached Queries in Caché SQL Optimization Guide for an explanation of Caché statement caching. As required, Caché JDBC statement pooling is completely transparent to the user.

The Caché JDBC implementation supports Statement methods setPoolable() and isPoolable() as hints to whether the statement in question should be pooled. Caché uses its own heuristics to determine appropriate sizes for all three of its statement pools, and therefore does not support limiting the size of a statement pool by setting the maxStatements property in ConnectionPoolDataSource. The optional javax.sql.StatementEventListener interface is unsupported (and irrelevant) for the same reason.

Inserting and Updating Data

There are several ways to insert and update Caché data using JDBC:

The makeTestSSN() Method

In this section, several examples insert new rows into Sample.PersonOpens in a new tab, which requires SSN (Social Security Number) as a unique key. The following method is used in these examples to generate a random SSN of the form nnn-nn-nnnn:

  public static String makeTestSSN() {
    java.util.Random random = new java.util.Random();
    StringBuffer sb = new StringBuffer();
    for (int i=1; i<=9; i++) sb.append(random.nextInt(10));
    sb.insert(5,'-');
    sb.insert(3,'-');
    return sb.toString();
  }

Inserting Data and Retrieving Generated Keys

The following code inserts a new row into Sample.PersonOpens in a new tab and retrieves the generated ID key.

  • Create the PreparedStatement object, initialize it with the SQL string, and specify that generated keys are to be returned:

       String sqlIn="INSERT INTO Sample.Person (Name,SSN,DOB) " + "VALUES(?,?,?)";
       int keys=Statement.RETURN_GENERATED_KEYS;
       PreparedStatement pstmt = dbconnection.prepareStatement(sqlIn, keys);
    
    
  • Set the values for the query parameters and execute the update (see The makeTestSSN() Method for an explanation of the makeTestSSN() call):

       String SSN = makeTestSSN(); // generate a random SSN
       java.sql.Date DOB = java.sql.Date.valueOf("1973-02-01");
    
       pstmt.setString(1,"Smith,John"); // Name
       pstmt.setString(2,SSN); // Social Security Number
       pstmt.setDate(3,DOB); // Date of Birth
       pstmt.executeUpdate();
    
    
  • Each time you insert a new row, the system automatically generates an object ID for the row. The generated ID key is retrieved into a result set and displayed along with the SSN:

       java.sql.ResultSet rsKeys = pstmt.getGeneratedKeys();
       rsKeys.next();
       String newID=rsKeys.getString(1);
       System.out.println("new ID for SSN " + SSN + " is " + newID);
    
    

    Although this code assumes that the ID will be the first and only generated key in rsKeys, this is not always a safe assumption in real life.

  • Retrieve the new row by ID and display it (Age is a calculated value based on DOB).

       String sqlOut="SELECT ID,Name,Age,SSN FROM Sample.Person WHERE ID="+newID;
       pstmt = dbconnection.prepareStatement(sqlOut);
       java.sql.ResultSet rsPerson = pstmt.executeQuery();
    
       int colnum = rsPerson.getMetaData().getColumnCount();
       rsPerson.next();
       for (int i=1; i<=colnum; i++) 
          System.out.print(rsPerson.getString(i) + "  ");
       System.out.println();
    
    

Scrolling a Result Set

The Caché JDBC driver supports scrollable result sets, which allow your Java applications to move both forward and backward through the resultset data. The prepareStatement() method uses following parameters to determine how the result set will function:

  • The resultSetType parameter determines how changes are displayed:

    • ResultSet.TYPE_SCROLL_SENSITIVE creates a scrollable result set that displays changes made to the underlying data by other processes.

    • ResultSet.TYPE_SCROLL_INSENSITIVE creates a scrollable result set that only displays changes made by the current process.

  • The resultSetConcurrency parameter must be set to ResultSet.CONCUR_UPDATABLE if you intend to update the result set.

The following code creates and uses a scrollable result set:

  • Create a PreparedStatement object, set the query parameters, and execute the query:

       String sql="Select ID, Name, SSN from Sample.Person "+
          " Where Name > ? Order By Name";
       int scroll=ResultSet.TYPE_SCROLL_INSENSITIVE;
       int update=ResultSet.CONCUR_UPDATABLE;
    
       PreparedStatement pstmt = dbconnection.prepareStatement(sql,scroll,update);
       pstmt.setString(1,"S");
       java.sql.ResultSet rs = pstmt.executeQuery();
    
    
  • The application can scroll backwards as well as forwards through this result set. Use afterLast to move the result set's cursor to after the last row. Use previous to scroll backwards.

       rs.afterLast(); 
       int colnum = rs.getMetaData().getColumnCount();
       while (rs.previous()) {
          for (int i=1; i<=colnum; i++) 
             System.out.print(rs.getString(i) + "  ");
          System.out.println();
       }
    
    
  • Move to a specific row using absolute. This code displays the contents of the third row:

       rs.absolute(3);
       for (int i=1; i<=colnum; i++) 
          System.out.print(rs.getString(i) + "  ");
       System.out.println();
    
    
  • Move to a specific row relative to the current row using relative. The following code moves to the first row, then scrolls down two rows to display the third row again:

       rs.first();
       rs.relative(2);
       for (int i=1; i<=colnum; i++) 
          System.out.print(rs.getString(i) + "  ");
       System.out.println();
    
    

Updating a Scrollable Result Set

The following code updates an open result set and saves the changes to the database:

  • Create a PreparedStatement object, set the query parameters, and execute the query:

       String sql="Select Name, SSN from Sample.Person "+ 
          " Where Name > ? Order By Name";
       int scroll=ResultSet.TYPE_SCROLL_SENSITIVE;
       int update=ResultSet.CONCUR_UPDATABLE;
    
       PreparedStatement pstmt = dbconnection.prepareStatement(sql,scroll,update);
       pstmt.setString(1,"S");
       java.sql.ResultSet rs = pstmt.executeQuery();
    
    

    A result set that is going to have new rows inserted should not include the Caché ID column. ID values are defined automatically by Caché.

  • To update a row, move the cursor to that row and update the desired columns, then invoke updateRow:

       rs.last();
       rs.updateString("Name", "Avery. Tara R");
       rs.updateRow();
    
    
  • To insert a row, move the cursor to the “insert row” and then update that row's columns. Be sure that all non-nullable columns are updated (see The makeTestSSN() Method for an explanation of the makeTestSSN() call). Finally, invoke insertRow:

       rs.moveToInsertRow();
       rs.updateString(1, "Abelson,Alan");
       rs.updateString(2, makeTestSSN());
       rs.insertRow();
    
    

Using Transactions

The Caché JDBC driver supports the JDBC transaction API.

  • In order to group SQL statements into a transaction, you must first disable autocommit mode using setAutoCommit():

       dbconnection.setAutoCommit(false);
    
    
  • Use commit() to commit to the database all SQL statements executed since the last execution of commit() or rollback:

       pstmt1.execute();
       pstmt2.execute();
       pstmt3.execute();
       dbconnection.commit();
    
    
  • Use rollback() to roll back all of the transactions in a transactions. Here the rollback() is invoked if SQLException is thrown by any SQL statement in the transaction:

       catch(SQLException ex) {
          if (dbconnection != null) {
             try {
                dbconnection.rollback();
             } catch (SQLException excep){
                // (handle exception)
             }
          }
       }
    
    

Transaction Handling Methods

Here is a brief summary of the java.sql.Connection methods used for transaction handling:

setAutoCommit()

By default Connection objects are in autocommit mode. In this mode an SQL statement is committed as soon as it is executed. To group multiple SQL statements into a transaction, first use setAutoCommit(false) to take the Connection object out of autocommit mode. Use setAutoCommit(true) to reset the Connection object to autocommit mode.

commit()

Executing commit() commits all SQL statements executed since the last execution of either commit() or rollback(). Note that no exception will be thrown if you call commit() without first setting autocommit to false.

rollback()

Executing rollback aborts a transaction and restores any values changed by the transaction back to their original state.

setTransactionIsolation()

Sets the isolation level for a transaction. Caché supports the following JDBC transaction isolation levels:

  • Connection.TRANSACTION_READ_UNCOMMITTED — Level 1. Permits dirty reads, non-repeatable reads, and phantom reads.

  • Connection.TRANSACTION_READ_COMMITTED — Level 2. Prevents dirty reads, but allows non-repeatable and phantom reads.

getIsolationLevel()

Returns the current transaction isolation level for the Connection object.

Logging for JDBC Applications

If your applications encounter any problems, you can monitor by enabling the appropriate logging:

Run your application, ensuring that you trigger the error condition, then check all the logs for error messages or any other unusual activity. The cause of the error is often obvious.

When using the SQL Gateway with JDBC, you should be able to find out more about logging by consulting the documentation for the remote database to which you are connecting.

Caution:

Enable logging only when you need to perform troubleshooting. You should not enable logging during normal operation, because it will dramatically slow down performance.

Enabling Logging for JDBC

To enable logging for JDBC when connecting to Caché, add a log file name to the end of your JDBC connection string. When you connect, the driver will save a log file that will be saved to the working directory of the application.

For example, suppose your original connection string is as follows:

jdbc:Cache://127.0.0.1:1972/USER

To enable logging, change this to the following and then reconnect:

jdbc:Cache://127.0.0.1:1972/USER/myjdbc.log

This log records the interaction from the perspective of the Caché database.

If the specified log file already exists, new log entries will be appended to it by default. To delete the existing file and create a new one, prefix the log file name with a plus character ( + ). For example, the following string would delete myjdbc.log (if it exists) and create a new log file with the same name:

jdbc:Cache://127.0.0.1:1972/USER/+myjdbc.log

See Defining a JDBC Connection URL for a complete list of connection parameters.

Enabling Logging for the JDBC SQL Gateway

The Caché SQL Gateway can also generate a log when used with JDBC. To enable this logging:

  • In the Management Portal, go to System Administration > Configuration > Connectivity > JDBC Gateway Settings.

  • Specify a value for JDBC Gateway Log. This should be the name of a log file (for example, jdbcSqlGateway.log) that will record the interaction between the gateway and the database.

FeedbackOpens in a new tab