Home / Using Java with the InterSystems JDBC Driver / Using the JDBC Driver

Using Java with the InterSystems JDBC Driver
Using the JDBC Driver
Previous section           Next section
InterSystems: The power behind what matters   

This chapter gives a detailed description of the various ways to establish a JDBC connection between your application and InterSystems IRIS.
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 parameters.
Required Parameters
The minimal required URL syntax is:
where the parameters are defined as follows:
For example, the following URL specifies host as, port as 51773, and namespace as User:
Optional Parameters
In addition to host, port, and namespace, you can also specify several optional parameters. The full syntax is:
where the optional parameters are defined as follows:
Each of these optional parameters can be defined individually, without specifying the others. For example, the following URL sets only the required parameters and the nodelay option:
Setting the Port Parameter at the Command Line
The com.intersystems.port property can be used to set the port parameter of the URL at the command line. Even if a program hard-codes the port number in the connection string, it can be changed in the command line. For example, assume that program myJdbcProgram sets the port to 51773 in a hard-coded connection string. The following command line will still allow it to run on port 9523:
   java -cp .:../lib/intersystems-jdbc-3.0.0.jar -Dcom.intersystems.port=9523 myJdbcProgram
The current value of this property can be retrieved programmatically with the following code:
   String myport = java.lang.System.getProperty ("com.intersystems.port");
Alternate Username and Password Parameters
For the preferred ways to specify username and password, see Using IRISDataSource to Connect and Using DriverManager to Connect at the beginning of this chapter. However, it is also possible to specify the username and password in the URL string, although this is discouraged.
If password and username are supplied as part of the URL string, they will be used in order to connect. Otherwise, other mechanisms already in place will be invoked. The syntax is:
For example, the following URL string sets the required parameters, the nodelay option, and then the username and password:
The username and password strings are case sensitive.
Establishing JDBC Connections
This section describes how to establish and control connections using DriverManager or DataSource, and how to control connection pooling.
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:
    IRISDataSource ds = new IRISDataSource();
    Connection dbconnection = ds.getConnection();
catch (SQLException e){
catch (ClassNotFoundException e){
On some systems, Java may attempt to connect via IPv6 if you use localhost in the URL rather than the literal address, This applies on any system where the hostname resolves the same for IPv4 and IPv6.
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://";
   String  username = "_SYSTEM";
   String  password = "SYS";
   dbconnection = DriverManager.getConnection(url,username,password);
You can also pass connection properties to DriverManager in a Properties object, as demonstrated in the following code:
String  url="jdbc:IRIS://";
   java.sql.Driver drv = java.sql.DriverManager.getDriver(url);

   java.util.Properties props = new Properties();
   java.sql.Connection dbconnection = drv.connect(url, props);
See JDBC Connection Properties for a complete list of the properties used by the InterSystems JDBC driver.
Using Shared Memory Connections
The standard JDBC connection to a remote InterSystems IRIS instance is over TCP/IP. To maximize performance, InterSystems IRIS also offers a shared memory connection for Java applications running on the same machine as an InterSystems IRIS instance.
A shared memory connection is a temporary device using virtual memory, which is shared by a JDBC client and an instance of InterSystems IRIS running on the same physical machine. This connection does not require potentially expensive calls into the kernel network stack. By using a channel directly from the JDBC client to InterSystems IRIS, it provides the ultimate in low latency and high throughput for JDBC operations.
A connection will use shared memory by default if it is available. The connection will automatically fall back to TCP/IP if the shared memory device fails or is not available. Shared memory is not used for SSL 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).
For now, shared memory connections are available for JDBC only on Microsoft Windows. In future releases, shared memory connections will be available on other platforms, including Linux and Mac OS.
Using a Connection Pool
The com.intersystems.jdbc.IRISConnectionPoolDataSource class implements the javax.sql.ConnectionPoolDataSource interface, providing a connection pool for your Java client applications.
This implementation is intended only for testing and development. It should not be used in production.
Here are the steps for using a connection pool with InterSystems IRIS:
  1. Import the needed packages:
    import com.intersystems.jdbc.*;
    import java.sql.*;
  2. Instantiate an IRISConnectionPoolDataSource object. Use the reStart() method to close all of the physical connections and empty the pool. Use setURL() to set the database URL (see Defining a JDBC Connection URL) for the pool's connections.
    IRISConnectionPoolDataSource pds = new IRISConnectionPoolDataSource();
  3. Initially, getPoolCount returns 0.
    System.out.println(pds.getPoolCount()); //outputs 0.
  4. Use IRISConnectionPoolDataSource.getConnection() to retrieve a database connection from the pool.
    Connection dbConnection = pds.getConnection();
    InterSystems IRIS driver connections must always be obtained by calling the getConnection() method (inherited from IRISDataSource). Do not use the getPooledConnection() methods, which are for use only within the InterSystems IRIS driver.
  5. Close the connection. Now getPoolCount returns 1.
    System.out.println(pds.getPoolCount()); //outputs 1
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. See Cached Queries in SQL Optimization Guide for an explanation of InterSystems IRIS statement caching. 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.
Connection Properties
The InterSystems JDBC driver supports several connection properties, which can be set by passing them to DriverManager (as described in Using DriverManager to Connect) or calling connection property accessors (see javax.sql.DataSource in the reference chapter for detailed descriptions).
The following properties are supported:
Required. String containing username. Default = null
See accessors getUser(), setUser()
Required. String containing password. Default = null
See accessors getPassword(), setPassword()
Optional. Boolean indicating TCP/IP NoDelay Flag. Default = true.
See accessors getNodelay(), setNodelay()
Optional. Integer indicating TCP/IP SO_SNDBUF value (SendBufferSize). Default = 0 (use system default value).
Optional. Integer indicating TCP/IP SO_RCVBUF value (ReceiveBufferSize). Default = 0 (use system default value).
Optional. java.sql.Connection constant indicating Transaction Isolation Level. Valid values are TRANSACTION_READ_UNCOMMITTED or TRANSACTION_READ_COMMITTED. Default = null (use system default value TRANSACTION_READ_UNCOMMITTED).
See accessors getDefaultTransactionIsolation(), setDefaultTransactionIsolation()
service principal name
Optional. String indicating Service Principal Name. Default = null.
See accessors getServicePrincipalName(), setServicePrincipalName()
connection security level
Optional. Integer indicating Connection Security Level. Valid levels are 0, 1, 2, 3, or 10. Default = 0.
0 - Instance Authentication (Password)
1 - Kerberos (authentication only)
2 - Kerberos with Packet Integrity
3 - Kerberos with Encryption
10 - SSL/TLS
See accessors getConnectionSecurityLevel(), setConnectionSecurityLevel()
Listing Connection Properties
Code similar to the following can be used to list the available properties for any compliant JDBC driver:
   java.sql.Driver drv = java.sql.DriverManager.getDriver(url);
   java.sql.Connection dbconnection = drv.connect(url, user, password);
   java.sql.DatabaseMetaData meta = dbconnection.getMetaData();
   System.out.println ("\n\n\nDriver Info: ==========================");
   System.out.println (meta.getDriverName());
   System.out.println ("release " + meta.getDriverVersion() + "\n");

   java.util.Properties props = new Properties();
   DriverPropertyInfo[] info = drv.getPropertyInfo(url,props);
   for(int i = 0; i <info.length; i++) {
      System.out.println ("\n" + info[i].name);
      if (info[i].required) {System.out.print("   Required");}
         else {System.out.print ("   Optional");}
      System.out.println (", default = " + info[i].value);
      if (info[i].description != null)
         System.out.println ("   Description:" + info[i].description);
      if (info[i].choices != null) {
         System.out.println ("   Valid values: ");
         for(int j = 0; j < info[i].choices.length; j++)
            System.out.println("      " + info[i].choices[j]);
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.
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:
To enable logging, change this to the following and then reconnect:
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:

Previous section           Next section
View this book as PDF
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-02-16 01:08:11