docs.intersystems.com
Home  /  Application Development: Language Bindings and Gateways  /  Using Java JDBC with InterSystems Products  /  Using the JDBC Driver


Using Java JDBC with InterSystems Products
Using the JDBC Driver
[Back]  [Next] 
InterSystems: The power behind what matters   
Search:  


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:
   jdbc:IRIS://<host>:<port>/<namespace>
where the parameters are defined as follows:
For example, the following URL specifies host as 127.0.0.1, port as 51773, and namespace as User:
   jdbc:IRIS://127.0.0.1:51773/User
Optional Parameters
In addition to host, port, and namespace, you can also specify several optional parameters. The full syntax is:
   jdbc:IRIS://<host>:<port>/<namespace>/<logfile>:<eventclass>:<nodelay>:<ssl>
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:
   jdbc:IRIS://127.0.0.1:51773/User/::false
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:
   jdbc:IRIS://<host>:<port>/<namespace>/<options>?username=<string1>&password=<string2>
For example, the following URL string sets the required parameters, the nodelay option, and then the username and password:
   "jdbc:IRIS://127.0.0.1:51773/User/::false?username=_SYSTEM&password=SYS"
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 DriverManager to Connect
The standard 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:51773/User";
   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://127.0.0.1:51773/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 JDBC Connection Properties for a complete list of the properties used by the InterSystems JDBC driver.
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™.
Here are the steps for using IRISDataSource:
  1. Import the java.sql and com.intersystems.jdbc packages:
    import java.sql.*;
    import com.intersystems.jdbc.*;
  2. Load the driver, then use IRISDataSource to create the connection and specify username and password:
    try{
       IRISDataSource ds = new IRISDataSource();
       ds.setURL("jdbc:IRIS://127.0.0.1:51773/User");
       ds.setUser("_system");
       ds.setPassword("SYS");
       Connection dbconnection = ds.getConnection();
    }
    Note:
    On some systems, Java may attempt to connect via IPv6 if you use localhost in the URL rather than the literal address, 127.0.0.1. This applies on any system where the hostname resolves the same for IPv4 and IPv6.
  3. Create a java.sql.Statement using the connection. The Statement object can be used to execute queries.
    Statement stmt = dbconnection.createStatement()
  4. Catch the exceptions thrown by the above methods:
    catch (SQLException e){
      System.out.println(e.getMessage());
    }
    catch (ClassNotFoundException e){
      System.out.println(e.getMessage());
    }
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.
Note:
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();
       pds.restartConnectionPool();
       pds.setURL("jdbc:IRIS://127.0.0.1:51773/User");
       pds.setUser("_system");
       pds.setPassword("SYS");
  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();
    Caution:
    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.
    dbConnection.close();
    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:
user
Required. String indicating Username. Default = ""
See accessors getUser(), setUser()
password
Required. String indicating Password. Default = ""
See accessors getPassword(), setPassword()
TCP_NODELAY
Optional. Boolean indicating TCP/IP NoDelay Flag. Default = true.
See accessors getNodelay(), setNodelay()
SO_SNDBUF
Optional. Integer indicating TCP/IP SO_SNDBUF value (SendBufferSize). Default = 0 (use system default value).
SO_RCVBUF
Optional. Integer indicating TCP/IP SO_RCVBUF value (ReceiveBufferSize). Default = 0 (use system default value).
TransactionIsolationLevel
Optional. java.sql.Connection constant indicating Transaction Isolation Level. Valid values are TRANSACTION_READ_UNCOMMITTED (the default) or TRANSACTION_READ_COMMITTED.
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.
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:51773/USER
To enable logging, change this to the following and then reconnect:
   jdbc:IRIS://127.0.0.1:51773/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:51773/USER/+myjdbc.log