Using Caché with JDBC
Accessing JDBC Databases
[Home] [Back] [Next]
InterSystems: The power behind what matters   

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

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:
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();
      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.
      System.out.println("\n Old name = " + rs.getString("Name"));
      rs.updateString("Name", "Bill. Buffalo");
      System.out.println("\n New name = " + rs.getString("Name") + "\n");
Close objects and catch any exceptions.
    } catch (Exception ex) {
      System.out.println("TinyJDBC caught exception: " 
             + ex.getClass().getName() + ": " + ex.getMessage());
  } // end main()
} // end class TinyJDBC
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 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:
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
This statement uses Implicit Join syntax (the –> operator), which provides a simple way to access the Company class referenced by Sample.Employee.
The prepared statement is implemented just like a regular statement:
Using Callable Statements to Execute Stored Procedures
The following code executes ByName, a Caché stored procedure contained in Sample.Person:
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")
This stored procedure is not defined in Sample.Person. In order to try this example, you must first open Sample.Person 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.Person file (before the class definition, as shown here):
include %occResultSet
Class Sample.Person Extends (%Persistent, %Populate, %XML.Adaptor)
{ ... 
Remember to recompile Sample.Person after making these changes.
The following code executes the stored procedure and iterates through both of the returned result sets:
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.Person, 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));
    return sb.toString();
Inserting Data and Retrieving Generated Keys
The following code inserts a new row into Sample.Person and retrieves the generated ID key.
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 following code creates and uses a scrollable result set:
Updating a Scrollable Result Set
The following code updates an open result set and saves the changes to the database:
Using Transactions
The Caché JDBC driver supports the JDBC transaction API.
Transaction Handling Methods
Here is a brief summary of the java.sql.Connection methods used for transaction handling:
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.
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.
Executing rollback aborts a transaction and restores any values changed by the transaction back to their original state.
Sets the isolation level for a transaction. Caché supports the following JDBC transaction isolation levels:
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.
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:
To enable logging, change this to the following and then reconnect:
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:
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:

Send us comments on this page
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA