Establishing JDBC Connections
This chapter provides some detailed examples of Java code that uses the Caché JDBC driver to accomplish basic tasks.
-
Using CacheDataSource to Connect — describes using CacheDataSource to load the driver and create a java.sql.Connection object, which is used to create the Statement object.
-
Using DriverManager to Connect — describes using the standard DriverManager class to create a connection.
-
Using a Connection Pool — describes using the CacheConnectionPoolDataSource class to control the connection pool for your Java client applications.
-
Defining a JDBC Connection URL — describes how to specify the host address, port number, and namespace to be accessed. The Caché JDBC driver also allows you to specify several other parameters.
-
Caché JDBC Connection Properties — describes how to specify several standard connection properties.
Using CacheDataSource to Connect
Use com.intersys.jdbc.CacheDataSource 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 Caché.
In earlier versions of JDBC, it was necessary to load the driver before connecting. For example:
Class.forName ("com.intersys.jdbc.CacheDriver").newInstance();
Connection dbconnection = DriverManager.getConnection(url,user,password);
JDBC versions 4.0 and higher use driver autoloading, which makes the Class.forName()call unnecessary for either DriverManager or CacheDataSource.
Here are the steps for using CacheDataSource:
-
Import the java.sql and com.intersys.jdbc packages:
import java.sql.*; import com.intersys.jdbc.*;
-
Load the driver, then use CacheDataSource to create the connection and specify username and password:
try{ CacheDataSource ds = new CacheDataSource(); ds.setURL("jdbc:Cache://127.0.0.1:1972/Samples"); 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.
-
Create a java.sql.Statement using the connection. The Statement object can be used to execute queries.
Statement stmt = dbconnection.createStatement()
-
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 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.intersys.jdbc.CacheDriver").newInstance();
String url="jdbc:Cache://127.0.0.1:1972/SAMPLES";
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:Cache://127.0.0.1:1972/SAMPLES";
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 Caché JDBC Connection Properties for a complete list of the properties used by the Caché JDBC driver.
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 Caché JDBC driver also allows you to use several optional parameters.
Required Parameters
The URL specifies the host address, port number, and namespace to be accessed.
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 Caché SuperServer is listening. The default is 1972 (or the first available number higher than 56772 if more than one instance of Caché is installed — see DefaultPort in the Caché Parameter File Reference). |
namespace | Caché namespace to be accessed. For example, Samples is the namespace containing Caché sample programs. |
The minimal required URL syntax is:
jdbc:Cache://<host>:<port>/<namespace>
For example, the following URL specifies host as 127.0.0.1, port as 1972, and namespace as Samples:
jdbc:Cache://127.0.0.1:1972/Samples
Optional Parameters
The Caché JDBC driver also allows you to specify several optional parameters. The full syntax is:
jdbc:Cache://<host>:<port>/<namespace>/<logfile>:<eventclass>:<nodelay>:<ssl>
where the optional parameters are defined as follows:
logfile | specifies a JDBC log file (see “Enabling Logging for JDBC”). |
eventclass | sets the transaction Event Class for this CacheDataSource object. See the CacheDataSource setEventClass() method for a complete description. |
nodelay | sets the TCP_NODELAY option if connecting via a CacheDataSource object. Toggling this flag can affect the performance of the application. Valid values are true and false. If not set, it defaults to true. Also see the getNodelay() and setNodelay() methods of CacheDataSource. |
ssl | enables SSL/TLS for both CacheDriver and CacheDataSource (see “Using SSL/TLS with Caché” in the Caché Security Administration Guide). Valid values are true and false. If not set, it defaults to false. |
Each of these optional parameters can be defined individually, without setting the others. For example, the following URL sets only the required parameters and the nodelay option:
jdbc:Cache://127.0.0.1:1972/Samples/::false
Setting the Port Parameter at the Command Line
The com.intersys.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 1972 in a hard-coded connection string. The following command line will still allow it to run on port 9523:
java -cp .:../lib/cache-db-2.0.0.jar -Dcom.intersys.port=9523 myJdbcProgram
The current value of this property can be retrieved programmatically with the following code:
String myport = java.lang.System.getProperty ("com.intersys.port");
Alternate Username and Password Parameters
For the preferred ways to specify username and password, see “Using CacheDataSource 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:Cache://<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:Cache://127.0.0.1:1972/Samples/::false?username=_SYSTEM&password=SYS"
The username and password strings are case-sensitive.
Using a Connection Pool
The com.intersys.jdbc.CacheConnectionPoolDataSource 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 Caché:
-
Import the needed packages:
import com.intersys.jdbc.*; import java.sql.*;
-
Instantiate a CacheConnectionPoolDataSource 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.
CacheConnectionPoolDataSource pds = new CacheConnectionPoolDataSource(); pds.restartConnectionPool(); pds.setURL("jdbc:Cache://127.0.0.1:1972/Samples"); pds.setUser("_system"); pds.setPassword("SYS");
-
Initially, getPoolCount returns 0.
System.out.println(pds.getPoolCount()); //outputs 0.
-
Use CacheConnectionPoolDataSource.getConnection() to retrieve a database connection from the pool.
Connection dbConnection = pds.getConnection();
Caution:Caché driver connections must always be obtained by calling the getConnection() method (inherited from CacheDataSource). Do not use the getPooledConnection() methods, which are for use only within the Caché driver.
-
Close the connection. Now getPoolCount returns 1.
dbConnection.close(); System.out.println(pds.getPoolCount()); //outputs 1
Caché JDBC Connection Properties
The Caché JDBC driver supports several connection properties, which can be set by passing them to DriverManager (as described in Using DriverManager to Connect).
The following properties are supported:
user | Required. String indicating Username. Default = "" |
password | Required. String indicating Password. Default = "" |
TCP_NODELAY | Optional. Boolean indicating TCP/IP NoDelay Flag. Default = true. |
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. |
service principal | Optional. String indicating Service Principal Name. Default = null. |
connection security level | Optional. Integer indicating Connection Security Level. Valid levels are 0, 1, 2, 3, or 10. Default = 0.
0 - Caché login (Password) 1 - Kerberos (authentication only) 2 - Kerberos with Packet Integrity 3 - Kerberos with Encryption 10 - SSL/TLS |
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]);
}
}