Connect to an Oracle TimesTen In-Memory Database using Java and JDBC

This document will detail establishing a connection to an Oracle TimesTen In-Memory Database using Java and JDBC.

Below is a complete program to connect to an Oracle TimesTen In-Memory Database using Java and JDBC. This program connects to a remote database and displays the contents of a table.

import com.timesten.jdbc.TimesTenDataSource;
import com.timesten.jdbc.TimesTenConnection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TTSimpleConnection {
 
  public static void main(String[] args) {
    try {
      // create the TimesTen data source and set the connection URL
      TimesTenDataSource ttds = new TimesTenDataSource();
      ttds.setUrl(/java/connect-to-an-oracle-timesten-in-memory-database-using-java-and-jdbc/"jdbc:timesten:client:dsn=sample;UID=odtest;PWD=password");
      
      // connect to the TimesTen database
      TimesTenConnection ttcon = (TimesTenConnection) ttds.getConnection();
      
      // create and execute the statement
      Statement stmt = ttcon.createStatement();
      ResultSet rset = stmt.executeQuery("select * from t");
      
      // process the result set
      while(rset.next()) {
        System.out.println("Value: " + rset.getInt(1));
      }
    } catch(SQLException e) {
      e.printStackTrace();
    }
  }
}

The above program is not unlike any other JDBC program but a few items worth mentioning follow bellow.

The Oracle TimesTen JDBC Driver

The JDBC driver for Oracle TimesTen 11g (11.2.1.6.1) is included with the TimesTen install media. The jar files can be found in OTT_HOME/lib directory. The jar file ttjdbc5.jar is for the Java SDK version 5 and ttjdbc6.jar is for version 6.

The JDBC URL format

The JDBC URL provides a compact way in which to provide the connection properties the TimesTenDataSource object. The TimesTen JDBC URL has the following format.

jdbc:timesten:{direct|client}:dsn=DSNname;[DSNattributes;]

Connections to TimesTen databases can be either direct or client with direct being the default connection type. A direct connection can only be made from a program that is running on same server as the TimesTen database. Client connections can be made from programs running remotely or on server hosting the database.

In the example program the JDBC URL is jdbc:timesten:client:dsn=sample;UID=odtest;PWD=password and specifies a client connection. Either of the following URL could be used for direct connection: jdbc:timesten:direct:dsn=sample;UID=odtest;PWD=password or jdbc:timesten::dsn=sample;UID=odtest;PWD=password.

Following the connection type is the Data Source Name (DSN) for the TimesTen database. This DSN must be defined in the ODBC source for your operating system (ODBC.INI for Unix/Linux and ODBC Data Source Manager for Windows). This is unlike Oracle Database JDBC connections were all the connection information can be contained in the URL. For information on setting up a client DSN for TimesTen database see the posts: Create an ODBC Client DSN for an Oracle TimesTen Database on Linux and Creating an ODBC Client DSN for an Oracle TimesTen Database in Windows

Following the DSN are optional data store attributes. In the URL in the example the user id (UID) and password (PWD) are provided. A complete list of the attributes can be found in the documentation OracleTimesTen In-Memory Database Reference: Data Store Attributes.

This example should get you started developing Java problems utilizing Oracle TimesTen. For more information see the Oracle TimesTen In-Memory Database Java Developers Guide.