Using the JDBC Driver

This chapter discusses how to establish a JDBC connection between your application and InterSystems IRIS, and how to use the JDBC driver’s extension methods and properties.

Establishing JDBC Connections — describes how to establish and control connections using DriverManager or DataSource.
Using Column-wise Binding for Bulk Inserts — describes extension methods that make batch inserts faster and easier to use.
Connection Pooling — describes options for connection pooling and monitoring.
Optimization and Testing Options — provides information on logging, shared memory, and statement pooling.

Connecting Your Application to InterSystems IRISOpens in a new tab also provides instructions, including sample code, for connecting to an InterSystems IRIS server from a Java application using JDBC.

Establishing JDBC Connections

This section describes how to establish and control connections using DriverManager or DataSource.

Defining a JDBC Connection URL — describes how to specify the parameters that define a JDBC connection.
Using IRISDataSource to Connect — describes using IRISDataSource to load the driver and create a java.sql.Connection object.
Using DriverManager to Connect — describes using the DriverManager class to create a connection.

Defining a JDBC Connection URL

A java.sql.Connection URL supplies the connection with information about the host address, port number, and namespace to be accessed. The InterSystems JDBC driver also allows you to use several optional URL parameters.

Required URL Parameters

The minimal required URL syntax is:

   jdbc:IRIS://<host>:<port>/<namespace>

where the required parameters are defined as follows:

host — IP address or Fully Qualified Domain Name (FQDN). For example, both 127.0.0.1 and localhost indicate the local machine.
port — TCP port number on which the InterSystems IRIS SuperServer is listening. The default is 1972. For more information, see DefaultPort in the Configuration Parameter File Reference).
namespace — InterSystems IRIS namespace to be accessed.

For example, the following URL specifies host as 127.0.0.1, port as 1972, and namespace as User:

   jdbc:IRIS://127.0.0.1:1972/User

Optional URL Parameters

In addition to host, port, and namespace, you can also specify several optional URL parameters. The full syntax is:

   jdbc:IRIS://<host>:<port>/<namespace>/<logfile>:<eventclass>:<nodelay>:<ssl>

where the optional parameters are defined as follows:

logfile — specifies a JDBC log file (see “JDBC Logging”).
eventclass — sets the transaction Event Class for this IRISDataSource object.
nodelay — sets the TCP_NODELAY option if connecting via an IRISDataSource object. Toggling this flag can affect the performance of the application. Valid values are true and false. If not set, it defaults to true.
ssl — enables TLS for both IRISDriver and IRISDataSource (see “About Configurations” in the Security Administration Guide). Valid values are true and false. If not set, it defaults to false.

Each of these optional URL parameters can be defined individually, without specifying the others. For example, the following URL sets only the required parameters and the nodelay option:

   jdbc:IRIS://127.0.0.1:1972/User/::false

Other connection properties can be specified by passing them to DriverManager in a Properties object (see “Using DriverManager to Connect”).

Using IRISDataSource to Connect

Use com.intersystems.jdbc.IRISDataSource to load the driver and then create the java.sql.Connection object. This is the preferred method for connecting to a database and is fully supported by InterSystems IRIS.

Opening a connection with IRISDataSource

The following example loads the driver, and then uses IRISDataSource to create the connection and specify username and password:

  try{
    IRISDataSource ds = new IRISDataSource();
    ds.setURL("jdbc:IRIS://127.0.0.1:1972/User");
    ds.setUser("_system");
    ds.setPassword("SYS");
    Connection dbconnection = ds.getConnection();
  }
catch (SQLException e){
  System.out.println(e.getMessage());
}
catch (ClassNotFoundException e){
  System.out.println(e.getMessage());
}

This example deliberately uses the literal address, 127.0.0.1, rather than localhost. On any system where the hostname resolves the same for IPv4 and IPv6, Java may attempt to connect via IPv6 if you use localhost.

Note:

The IRISDataSource class provides an extended set of connection property accessors (such as setURL(), setUser() and setPassword() in this example). See “Class IRISDataSource” in the Quick Reference for a complete list of accessors, and see “Connection Parameter Options” later in this reference for more information on all connection properties

Using DriverManager to Connect

The DriverManager class can also be used to create a connection. The following code demonstrates one possible way to do so:

Class.forName ("com.intersystems.jdbc.IRISDriver").newInstance();
   String  url="jdbc:IRIS://127.0.0.1:1972/User";
   String  username = "_SYSTEM";
   String  password = "SYS";
   dbconnection = DriverManager.getConnection(url,username,password);

You can also specify connection properties for DriverManager in a Properties object, as demonstrated in the following code:

String  url="jdbc:IRIS://127.0.0.1:1972/User";
   java.sql.Driver drv = java.sql.DriverManager.getDriver(url);

   java.util.Properties props = new Properties();
   props.put("user",username);
   props.put("password",password);
   java.sql.Connection dbconnection = drv.connect(url, props);

See “Connection Parameter Options” later in this reference for a detailed list of available properties.

Using Column-wise Binding for Bulk Inserts

In JDBC, bulk inserts of prepopulated data are typically done by calling addBatch() in a loop, which is not optimal if the data is already in an array and ready to be sent to the server. InterSystems IRIS offers an extension that allows you to bypass the loop and pass in an entire array with one setObject() call.

For example, typical code calls setObject() for each item like this:

// Typical AddBatch() loop
  for (int i=0;i<10000;i++){
    statement.setObject(1,objOne);
    statement.setObject(2,objTwo);
    statement.setObject(3,objThree);
    statement.addBatch();
  }
  statement.executeBatch();

Your code becomes faster and simpler when all items are loaded into an Object array and the entire array is added with one call:

// Adding an ArrayList named objArray with a single call
  IRISPreparedStatement.setObject(objArray);
  statement.addBatch();
  statement.executeBatch();

Columnwise binding assumes that the first parameter that is bound as an arraylist, will use the arraylist size (if more than one) to represents the number of rows in the batch. The other parameters bound as arraylists must be the same size or only have one value specified (ie. a user defined default value) or we will throw an exception when addbatch() is called: "Unmatched columnwise parameter values: #rows rows expected, but found only #count in # parameter!"

For example: given 3 parameters and 10 rows to be bound, you can bind 10 values to the arraylist in parameter 1, and parameters 2 and 3 must also have 10 values in their arraylists or only one value (if specifying a default value for all rows). It is expected to fill in all or one, anything else will trigger an exception.

Here is an example that demonstrates the same operation with row-wise and column-wise binding:

bindRowWise()

public static void bindRowWise() throws Exception {

  int rowCount = cName.size();
  String insert = "INSERT INTO CUSTOMER VALUES(?,?,?,?)";
  try {
    PreparedStatement ps = conn.prepareStatement(insert);
    for (int i=0;i<rowCount;i++){
      ps.setObject(1,cName.get(i));
      ps.setObject(2,cAddress.get(i));
      ps.setObject(3,cPhone.get(i));
      ps.setObject(4,cAcctBal.get(i));
      ps.addBatch();
    }
    ps.executeBatch();
  }
  catch (Exception e) {
    System.out.println("\nException in RowBinding()\n"+e);
  }

} // end bindRowWise()

bindColumnWise()

public static void bindColumnWise() throws Exception {

  String insert = "INSERT INTO CUSTOMER VALUES(?,?,?,?)";
  try {
    PreparedStatement ps = conn.prepareStatement(insert);
    ps.setObject(1, new ArrayList<>(cName)    );
    ps.setObject(2, new ArrayList<>(cAddress) );
    ps.setObject(3, new ArrayList<>(cPhone)   );
    ps.setObject(4, new ArrayList<>(cAcctBal) );
    ps.addBatch();
    ps.executeBatch();
  catch (Exception e) {
    System.out.println("\nException in bindColumnWise()\n"+e);
  }

} // end bindColumnWise()

Connection Pooling

Using IRISConnectionPoolDataSource Methods

The IRISConnectionPoolDataSource class implements ConnectionPoolDataSource and extends it with a set of proprietary extensions that can be useful for testing and monitoring pooled connections. The following extensions are available:

getConnectionWaitTimeout() returns the number of seconds that a Connection Pool Manager will wait for any connections to become available.
getMaxPoolSize() returns the maximum number of connections allowed.
getPoolCount() returns the current number of entries in the connection pool.
restartConnectionPool() closes all physical connections and empties the connection pool.
setMaxPoolSize() takes an int value that specifies the maximum number of connections to allow in the pool. Defaults to 40.
setConnectionWaitTimeout() takes an int value that specifies the connection wait timeout interval in seconds. If no connections are available after the timout period expires, an exception is thrown. Defaults to 0, indicating that the connection will either be immediately made available, or an exception will be thrown indicating that the pool is full.

The IRISConnectionPoolDataSource class also inherits the proprietary extensions implemented in IRISDataSource (see “Connection Parameter Options” in the Quick Reference).

Here are the steps for using this class with InterSystems IRIS:

Import the needed packages:

import com.intersystems.jdbc.*;
import java.sql.*;

Instantiate an IRISConnectionPoolDataSource object. Use the reStart() method to close all of the physical connections and empty the pool. Use setURL() (inherited from IRISDataSource) to set the database URL (see Defining a JDBC Connection URL) for the pool's connections.
```
IRISConnectionPoolDataSource pds = new IRISConnectionPoolDataSource();
   pds.restartConnectionPool();
   pds.setURL("jdbc:IRIS://127.0.0.1:1972/User");
   pds.setUser("_system");
   pds.setPassword("SYS");
```

Initially, getPoolCount() returns 0.

System.out.println(pds.getPoolCount()); //outputs 0.

Use getConnection() to retrieve a database connection from the pool.
```
Connection dbConnection = pds.getConnection();
```
Caution:

InterSystems JDBC driver connections must always be obtained by calling the getConnection() method of IRISDataSource, which is enhanced to provide automatic, transparent connection pooling. The ConnectionPoolDataSource.getPooledConnection() methods are implemented because they are required by the JDBC standard, but they should never be called directly.

Close the connection. Now getPoolCount() returns 1.

dbConnection.close();
System.out.println(pds.getPoolCount()); //outputs 1

Optimization and Testing Options

This section contains specialized information that may be useful during development and testing.

JDBC Logging — describes how to enable logging when testing JDBC applications.
Shared Memory Connections — describes how a connection works when the server and client are on the same machine.
Statement Pooling — describes how to store optimized statements in a cache the first time they are used.

JDBC Logging

If your applications encounter any problems, you can monitor by enabling 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.

Note:

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

To enable logging for JDBC when connecting to InterSystems IRIS, 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:IRIS://127.0.0.1:1972/USER

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

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

This log records the interaction from the perspective of the InterSystems IRIS 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:IRIS://127.0.0.1:1972/USER/+myjdbc.log

Shared Memory Connections

InterSystems IRIS uses a shared memory connection rather than TCP/IP when a Java application is running on the same machine as an InterSystems IRIS server instance. This section explains how shared memory works, and how to disable it for development and testing purposes.

Shared memory connections maximize performance by avoiding potentially expensive calls into the kernel network stack, thus providing optimal low latency and high throughput for JDBC operations.

If a connection specifies server address localhost or 127.0.0.1, shared memory will be used by default. TCP/IP will be used if the actual machine address is specified. The connection will automatically fall back to TCP/IP if the shared memory device fails or is not available.

Shared memory can be disabled in the connection string by setting the SharedMemory property to false. The following example creates a connection that will not use shared memory even though the server address is specified as 127.0.0.1:

  Properties props = new Properties();
  props.setProperty("SharedMemory", "false");
  props.setProperty("user", "_system");
  props.setProperty("password", "SYS");
  IRISConnection conn = (IRISConnection)DriverManager.getConnection("jdbc:IRIS://127.0.0.1:1972/USER/ ",props);

Accessors DataSource.getSharedMemory() and DataSource.setSharedMemory() can be used to read and set the current connection mode. The IRISConnection.isUsingSharedMemory() method can also be used to test the connection mode.

Shared memory is not used for TLS or Kerberos connections. The JDBC log will include information on whether a shared memory connection was attempted and if it was successful (see “JDBC Logging”).

Note:

Shared memory connections do not work across container boundaries

InterSystems does not currently support shared memory connections between two different containers. If a client tries to connect across container boundaries using localhost or 127.0.0.1, the connection mode will default to shared memory, causing it to fail. This applies regardless of whether the Docker --network host option is specified. You can guarantee a TCP/IP connection between containers either by specifying the actual hostname for the server address, or by disabling shared memory in the connection string (as demonstrated above).

Shared memory connections can be used without problems when the server and client are in the same container.

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.

InterSystems JDBC implemented statement pooling long before the concept became part of the JDBC specification. While the InterSystems IRIS driver uses techniques similar to those recommended by the specification, the actual pooling implementation is highly optimized. Unlike most implementations, InterSystems JDBC has three different statement pooling caches. One roughly corresponds to statement pooling as defined by the JDBC specification, while the other two are InterSystems IRIS specific optimizations. As required, InterSystems JDBC statement pooling is completely transparent to the user.

The InterSystems JDBC implementation supports Statement methods setPoolable() and isPoolable() as hints to whether the statement in question should be pooled. InterSystems IRIS 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 IRISConnectionPoolDataSource. The optional javax.sql.StatementEventListener interface is unsupported (and irrelevant) for the same reason.

Configuration and Requirements

InterSystems Java Connectivity Options