Introduction

JDBC with UCP and Jetty onTimesTen XE

This blog shows how to configure Java JDBC with UCP on Jetty with TimesTen XE.  The following versions were used:

The combination of JDBC + UCP + Jetty + TimesTen XE is simple, reliable, fast and free.

 

 

Jetty

Jetty

Jetty is an open source Java web server and servlet container.  Jetty has been around since 1995.  Jetty is popular as it is simple, fast and stable.

Jetty supports useful features like HTTP/2, WebSockets, the Java Servlet API, JSP, AJP, JASPI, JMX, JNDI, and OSGi

 

 

JDBC

JDBC

JDBC [Java Database Connectivity] has been around since 1997 and is the most popular Java SQL interface for relational databases.

 

 

UCP

Universal Connection Pool for JDBC

Oracle UCP [Universal Connection Pool] is a generic JDBC connection pool that works with any JDBC driver.

JDBC connection pools can greatly benefit Java applications which have many intermittant database connctions.  Connection pools pre-create a set of connections which can be used for a short period of time and returned to the pool after use.  Getting an existing connection from a pool is much faster than creating a new database connection from scratch.

A bonus of the connection pool architecture is that it tends to put less strain on both the database server and application servers.

Using Oracle UCP with an Oracle database is well documented.

 

 

 

TimesTen XE

TimesTen XE is fast

Oracle TimesTen XE In-Memory Database is a very fast, free, relational database that supports SQL and PLSQL via APIs like JDBC, ODBC and OCI.

Although TimesTen XE is an in-memory database, everything is persisted to the file system and it supports ACID transactions.

Simple SQL primary key lookups can take less than 2 microseconds and complex joins, analytics, windowing functions and cubes are also supported.

 

 

Configuration

  • Install TimesTen XE on the same host as your Jetty server
  • Create a TimesTen instance and set the TimesTen environment via the ttenv.sh script
    • This will set Linux environment variables like TIMESTEN_HOME, LD_LIBRARY_PATH, CLASSPATH
  • The TimesTen database definition [DSN] will be defined in the $TIMESTEN_HOME/conf/sys.odbc.ini config file
  • When you start the Jetty server, make sure that both the TIMESTEN_HOME and LD_LIBRARY_PATH environment variables are set

  • When a web application is deployed to the Jetty server, the WEB-INF/lib directory should include both the TimesTen JDBC driver JAR file [ttjdbc8.jar] and the JAR file for the Universal Connection Pool.  This configuration assume that that all database operations will be contained within the application

  • Before using a JDBC cconnection, the web application will need to configure UCP for use  with the TimesTen JDBC driver and the TimesTen database defined in the $TIMESTEN_HOME/conf/sys.odbc.ini config file.  This example Java class creates a pooled UCP connection a TimesTen database. The database name is DEFAULT and the username is SCOTT

 

 

This is the WAR layout for the an example application:

$ jar tvf jetty-base/webapps/tt-jetty-ucp.war

    39 Thu Jun 23 14:35:22 PDT 2022 META-INF/MANIFEST.MF

     0 Thu Jun 23 14:35:16 PDT 2022 META-INF/

  2388 Wed Jun 29 08:17:16 PDT 2022 TTTest.jsp

     0 Thu Jun 23 14:35:12 PDT 2022 WEB-INF/

     0 Wed Jun 29 08:12:48 PDT 2022 WEB-INF/classes/

     0 Wed Jun 29 08:12:48 PDT 2022 WEB-INF/classes/com/

     0 Wed Jun 29 08:13:08 PDT 2022 WEB-INF/classes/com/timesten/

     0 Wed Jun 29 08:13:08 PDT 2022 WEB-INF/classes/com/timesten/util/

  1441 Wed Jun 29 12:55:28 PDT 2022 WEB-INF/classes/com/timesten/util/DatabaseConnectionUtil.java

  1857 Wed Jun 29 13:02:06 PDT 2022 WEB-INF/classes/com/timesten/util/DatabaseConnectionUtil.class

     0 Wed Jun 29 13:01:52 PDT 2022 WEB-INF/lib/

321899 Wed Jun 29 12:52:20 PDT 2022 WEB-INF/lib/ttjdbc8.jar

1690767 Wed Jun 29 13:00:20 PDT 2022 WEB-INF/lib/ucp.jar

   681 Wed Jun 29 10:38:36 PDT 2022 WEB-INF/web.xml

 

 

Sample Java class which creates a pooled UCP connection for a TimesTen database

package com.timesten.util;

 

import java.sql.Connection;

import java.sql.SQLException;

import oracle.ucp.jdbc.PoolDataSourceFactory;

import oracle.ucp.jdbc.PoolDataSource;

 

public class DatabaseConnectionUtil {

      

       private static PoolDataSource pds =

                      PoolDataSourceFactory.getPoolDataSource();

 

       private String connectionFactoryClassName =

                      "com.timesten.jdbc.ObservableConnectionDS";

      

       private String url = "timesten:jdbc:direct:DEFAULT";

       private String user = "SCOTT";

       private String password = "tiger";

      

       private int initPoolSize = 10;

       private int minPoolSize = 10;

       private int maxPoolSize = 100;

      

      

       public void setAttributes (String connectionFactoryClassName,

                      String url, String user, String password,

                      int initPoolSize, int minPoolSize, int maxPoolSize) {

             

              this.connectionFactoryClassName = connectionFactoryClassName;

              this.url = url;

              this.user = user;

              this.password = password;

              this.initPoolSize = initPoolSize;

              this.minPoolSize = minPoolSize;

              this.maxPoolSize = maxPoolSize;

       }

 

       public Connection getConnection()

                      throws SQLException {

             

        pds.setConnectionFactoryClassName(connectionFactoryClassName);

        pds.setURL(url);

        pds.setUser(user);

        pds.setPassword(password);

       

        pds.setInitialPoolSize(initPoolSize);

        pds.setMinPoolSize(minPoolSize);

        pds.setMaxPoolSize(maxPoolSize);

             

              Connection conn = pds.getConnection();

              return conn;

       }

}

 

 

Example of a trivial JSP/JDBC application using UCP and TimesTen XE with Jetty

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>TimesTen Jetty-UCP Test Application</title>

</head>

<body>

<h2>TimesTen Jetty UCP Test Application</h2>

 

 

      <%!

            

        private String printTTInfo ()

          throws SQLException

        {  

          Connection conn = null;

          PreparedStatement pstmt = null;

          ResultSet rs = null;

          String value = null;

 

          try

          {

            // Connection via UCP

            DatabaseConnectionUtil connUtil = new DatabaseConnectionUtil();

            conn = connUtil.getConnection();

           

            

            pstmt = conn.prepareStatement (

                      "SELECT 'Connected to TimesTen at ' || SYSDATE || " +

                "' as user ' || USER FROM DUAL");

            rs = pstmt.executeQuery ();

           

            rs.next ();

            value = rs.getString (1);

          

          }

          catch (Exception ex)

          {

            ex.printStackTrace();

            value = "<P>Exception: " + ex + "</P>\n";

          }

          finally

          {

            if (rs!= null) rs.close();

            if (pstmt!= null) pstmt.close();

            if (conn!= null) conn.close();

          }

         

          return value;

        }

          

      %>

 

      <%

        out.println ("<P>" + printTTInfo ());

      %>  

 

</body>

</html>

 

 

 

Summary

  • Oracle TimesTen XE supports JDBC and pooled connections
  • TimesTen XE works with Oracle Universal Connection Pool
  • TimesTen XE JDBC apps using UCP can be deployed to Jetty
  • The combination of Jetty, UCP and TimesTen XE JDBC enables a simple, fast and free runtime environment for Java applications

 

 

Learn more about TimesTen XE:

 

 

More TimesTen XE Blogs

 

 

Disclaimer: These are my personal thoughts and do not represent Oracle’s official viewpoint in any way, shape, or form.