docs.intersystems.com
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   
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
Other connection properties can be specified by passing them to DriverManager in a Properties object (see Using DriverManager to Connect).
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 in the following section. 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 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:51773/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());
}
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.
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: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.
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. This connection avoids potentially expensive calls into the kernel network stack, 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:51773/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 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).
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.
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:
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().
key recovery password
Optional. String containing current Key Recovery Password setting. Default = null.
See accessors getKeyRecoveryPassword(), setKeyRecoveryPassword().
password
Required. String containing password. Default = null.
See accessors getPassword(), setPassword()
service principal name
Optional. String indicating Service Principal Name. Default = null.
See accessors getServicePrincipalName(), setServicePrincipalName()
SharedMemory
Optional. Boolean indicating whether or not to always use shared memory for localhost and 127.0.0.1. Default = null.
See accessors getSharedMemory(), setSharedMemory()
SO_RCVBUF
Optional. Integer indicating TCP/IP SO_RCVBUF value (ReceiveBufferSize). Default = 0 (use system default value).
SO_SNDBUF
Optional. Integer indicating TCP/IP SO_SNDBUF value (SendBufferSize). Default = 0 (use system default value).
SSL configuration name
Optional. String containing current SSL Configuration Name for this object. Default = null.
See accessors getSSLConfigurationName(), setSSLConfigurationName().
TCP_NODELAY
Optional. Boolean indicating TCP/IP TCP_NODELAY flag (Nodelay). Default = true.
See accessors getNodelay(), setNodelay()
TransactionIsolationLevel
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()
user
Required. String containing username. Default = null.
See accessors getUser(), setUser()
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


Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-07-17 06:06:46