Relational database connections with JDBC

Java applications connect to and interact with relational databases through the Java Database Connectivity (JDBC) API. You can configure a data source and a JDBC driver so an application that is running on your Open Liberty server can connect with a relational database.

Applications communicate with relational databases to retrieve many different kinds of information, such as flight schedules, product inventories, and customer purchase histories. The JDBC API provides an adapter layer between applications and relational databases by providing details about a database to an application in a standardized way. In Open Liberty, interactions with the JDBC API are configured by the Java Database Connectivity feature.

JDBC driver library configuration

To connect with a relational database, you need a JDBC driver, which is typically provided by the database vendor. You can configure JDBC drivers to define data sources, from which you obtain connections to the database. To configure a JDBC data source in your Open Liberty server configuration, you must enable the Java Database Connectivity feature and specify a library that contains your JDBC driver. In the following example, the library element specifies the location of the directory that contains a JDBC driver JAR file:

<featureManager>
    <feature>jdbc-4.3</feature>
</featureManager>

<library id="jdbcLib">
    <fileset dir="jdbc" includes="*.jar"/>
</library>

Open Liberty recognizes the implementation class names of various data source types for commonly used JDBC drivers. In most cases, you can specify only the location of the JDBC driver.

If you use Maven to build your application, you can download and deploy the JDBC driver by adding code that is similar to the following example to your pom.xml file. In this example, the com.ibm.db2:jcc:11.5.4.0 dependency is copied to the ${server.config.dir}/jdbc directory at build time to deploy the IBM Db2 JDBC driver:

<plugin>
<groupId>io.openliberty.tools</groupId>
<artifactId>liberty-maven-plugin</artifactId>
<version>3.3-M2</version>
  <configuration>
    <copyDependencies>
      <dependency>
        <filter>com.ibm.db2:jcc:11.5.4.0</filter>
        <location>jdbc</location>
      </dependency>
    </copyDependencies>
  </configuration>
</plugin>

Data source configuration

You can configure any JDBC driver with Open Liberty, which has built-in configuration for many common vendor databases. The following example shows the basic pattern to configure a data source in your server.xml file:

<library id="jdbcLib">
    <fileset dir="jdbc" includes="*.jar"/>
</library>

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties serverName="localhost" portNumber="5432"
                databaseName="myDB"
                user="exampleUser" password="examplePassword"/>
</dataSource>

In this example, the dataSource element references the library that contains the JDBC driver JAR file and specifies several JDBC vendor properties with the properties attribute. Every JDBC driver provides a different collection of properties that can be configured on its dataSource implementation classes. If the JDBC driver data source has setter methods with a String or primitive parameter, you can configure these properties by specifying the properties attribute in the dataSource element.

The following example shows the basic configuration to specify JDBC vendor properties in the dataSource element:

<properties someProperty="someValue" anotherProperty="5" />

For example, the following code shows the currentLockTimeout property on the IBM Db2 JDBC driver data source classes:

public void setCurrentLockTimeout(int lockTimeout);
public int getCurrentLockTimeout();

You can configure this setting with the following configuration in your server.xml file:

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties serverName="localhost" portNumber="1234" databaseName="myDB"
                user="exampleUser" password="examplePassword"
                currentLockTimeout="30s"/>
</dataSource>

Configuration of the default data source

If you enable any Java EE or Jakarta EE features in Open Liberty, you can configure a default data source. To configure a default data source, set the ID of the dataSource element to DefaultDataSource, as shown in the following example:

<dataSource id="DefaultDataSource">
<jdbcDriver libraryRef="jdbcLib"/>
<properties serverName="localhost" portNumber="5432"
            databaseName="myDB"
            user="exampleUser" password="examplePassword"/>
</dataSource>

Common data source configuration examples

The following examples show sample configurations for commonly used vendor databases. For applicable vendors, examples are provided for how to configure the database locally in a Docker container for testing and development purposes:

PostgreSQL configuration

Get the PostgreSQL JDBC Driver from Maven Central. The following example shows a sample data source configuration for a PostgreSQL database:

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties.postgresql serverName="localhost" portNumber="5432"
                databaseName="myDB"
                user="exampleUser"
                password="examplePassword"/>
</dataSource>

To run a Postgres Docker container locally, run the following command:

docker run -it --rm=true --memory-swappiness=0 --ulimit memlock=-1:-1 \
           --name postgres-liberty \
           -e POSTGRES_USER=exampleUser \
           -e POSTGRES_PASSWORD=examplePassword \
           -e POSTGRES_DB=myDB \
           -p 5432:5432 \
           postgres:10.5

IBM Db2

Get the IBM Data Server Driver For JDBC and SQLJ from Maven Central. The following example shows a sample data source configuration for an IBM Db2 database:

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties.db2.jcc serverName="localhost" portNumber="50000"
                databaseName="test"
                user="db2inst1"
                password="foobar1234"/>
</dataSource>

To run an IBM Db2 Docker container locally, run the following command:

docker run --ulimit memlock=-1:-1 -it --rm=true --memory-swappiness=0 \
           --name db2-liberty \
           -e AUTOCONFIG=false -e ARCHIVE_LOGS=false -e LICENSE=accept \
           -e DBNAME=test \
           -e Db2INSTANCE=db2inst1 \
           -e Db2INST1_PASSWORD=foobar1234 \
           -p 50000:50000 \
           --privileged \
           ibmcom/db2:11.5.0.0a

Microsoft SQL Server

Get the Microsoft JDBC Driver For SQL Server from Maven Central. The following example shows a sample data source configuration for a Microsoft SQL Server database:

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties.microsoft.sqlserver serverName="localhost" portNumber="1433"
                databaseName="tempdb"
                user="sa"
                password="examplePassw0rd"/>

</dataSource>

To run a Microsoft SQL Server Docker container locally, run the following command:

docker run --ulimit memlock=-1:-1 -it --rm=true --memory-swappiness=0 \
           --name mssql-liberty \
           -e ACCEPT_EULA=Y \
           -e SA_PASSWORD=examplePassw0rd \
           -p 1433:1433 \
           mcr.microsoft.com/mssql/server:2019-GA-ubuntu-16.04

MySQL

Get the MySQL Connector/J JDBC driver from Maven Central. The following example shows a sample data source configuration for a MySQL database:

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties serverName="localhost" portNumber="3306"
                databaseName="myDb"
                user="exampleUser"
                password="examplePassword"/>
</dataSource>

To run a MySQL Docker container locally, run the following command:

docker run --ulimit memlock=-1:-1 -it --rm=true --memory-swappiness=0 \
           --name mysql-liberty \
           -e MYSQL_DATABASE=myDB \
           -e MYSQL_USER=exampleUser \
           -e MYSQL_PASSWORD=examplePassword \
           -p 3306:3306 \
           mysql:8

Embedded Derby

Get the Apache Derby Database Engine and Embedded JDBC Driver from Maven Central. The following example shows a sample data source configuration for a Derby database in an embedded environment:

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties.derby.embedded databaseName="memory:myDB" createDatabase="create"/>
</dataSource>

Oracle

Get the Oracle JDBC driver from Maven Central. The following example shows a sample data source configuration for an Oracle database:

<dataSource jndiName="jdbc/myDB">
    <jdbcDriver libraryRef="jdbcLib"/>
    <properties.oracle URL="jdbc:oracle:thin:@//localhost:1521/myDB"
                user="exampleUser"
                password="examplePassword"/>
</dataSource>

Oracle UCP

Oracle Universal Connection Pool (UCP) is a stand-alone JDBC connection pool. When you use Oracle UCP with Open Liberty, you are using the Oracle UCP connection pool instead of the Open Liberty built-in connection pooling functions. Some of the Oracle high availability database functions require the use of Oracle UCP. Support for Oracle UCP was added in Open Liberty version 19.0.0.4.

Oracle UCP might require some properties, such as user and password, to be set in the properties.oracle.ucp element. Because the Open Liberty connection pool is unavailable, some of the Open Liberty data source and connection manager configuration values are ignored. For most of those data source and connection manager properties, Oracle UCP provides equivalent functions. For more information, see the properties.oracle.ucp element documentation.

Get the Oracle UCP JDBC driver from Maven Central. The following example shows a sample data source configuration for Oracle UCP:

<dataSource jndiName="jdbc/oracleUCPDS" >
    <jdbcDriver libraryRef="OracleUCPLib" />
    <properties.oracle.ucp URL="jdbc:oracle:thin:@//localhost:1521/SAMPLEDB" />
</dataSource>

<library id="OracleUCPLib">
    <fileset dir="Oracle/Drivers" includes="ojdbcx.jar ucp.jar"/>
</library>

Configuring databases that are unknown to Open Liberty

The following example shows a sample data source configuration for a relational database that Open Liberty does not recognize by default. Specify the type of the data source by using the type attribute of the dataSource element. The value for the type attribute can be one of the interface class names that are described in the Data source types section. Then, specify the mapping of interface class name to the driver implementation of that class on the jdbcDriver element, as shown in the following example:

<dataSource id="myDB" jndiName="jdbc/myDB" type="javax.sql.XADataSource">
    <jdbcDriver libraryRef="jdbcLib"
               javax.sql.XADataSource="com.example.jdbc.SampleXADataSource"/>
    <properties serverName="localhost" portNumber="1234"
                databaseName="myDB"
                user="exampleUser"
                password="examplePassword"/>
</dataSource>

For more information, see the Java Database Connectivity feature.

Data source types

To access a database from your Open Liberty application, your application code must implement the javax.sql.DataSource interface. Open Liberty provides a managed implementation of this interface, which is backed by the data source or driver implementation that your JDBC driver provides. For Open Liberty, your JDBC driver must provide at least one of the following types of data sources or a java.sql.Driver driver implementation with the ServiceLoader facility:

  • javax.sql.DataSource This type of data source is the basic form. It does not provide the interoperability that enhances connection pooling and cannot participate as a two-phase capable resource in transactions that involve multiple resources.

  • javax.sql.ConnectionPoolDataSource This type of data source is enabled for connection pooling. It cannot participate as a two-phase capable resource in transactions that involve multiple resources.

  • javax.sql.XADataSource This type of data source is enabled for connection pooling and is able to participate as a two-phase capable resource in transactions that involve multiple resources. The javax.sql.XADataSource data source type is essentially a superset of the capabilities that are provided by the javax.sql.DataSource and javax.sql.ConnectionPoolDataSource data source types. However, some JDBC vendors might have subtle differences in behavior or limitations that are not spelled out in the JDBC specification.

  • java.sql.Driver The java.sql.Driver driver implementation provides a basic way to connect to a database. This implementation requires a URL and is typically used in Java SE applications. Like javax.sql.DataSource, it does not provide interoperability that enhances connection pooling and cannot participate as a two-phase capable resource in transactions that involve multiple resources. To work with Open Liberty, this implementation must provide the ServiceLoader facility, which Open Liberty uses to discover JDBC driver implementations for a URL.

If the type attribute is not specified, Open Liberty looks for the data source type in a conditional order and chooses the first type that is available.

If you use the Java Database Connectivity feature 4.3 or higher, or you are referencing the default data source, Open Liberty looks for the data source type in the following order:

  1. javax.sql.XADataSource

  2. javax.sql.ConnectionPoolDataSource

  3. javax.sql.DataSource

If you use the Java Database Connectivity feature 4.2, 4.1, or 4.0 and you are not referencing the default data source, Open Liberty looks for the data source type in the following order:

  1. javax.sql.ConnectionPoolDataSource

  2. javax.sql.DataSource

  3. javax.sql.XADataSource

Application configuration for relational database connections

To use a data source that is configured in your server.xml file, you can either inject the data source or specify a lookup in your application code. The following examples assume that a jndiName value of jdbc/myDB is specified in the dataSource element in the server.xml file.

In a web component or enterprise bean component, you can inject the data source with application code similar to the following example:

@Resource(lookup = "jdbc/myDB")
DataSource myDB;

If the myDB value is configured as the default data source, you can omit the lookup object, as shown in the following example:

@Resource
DataSource myDB;

When the Java Naming and Directory Interface feature is enabled, you can reference the data source from your application by Java Naming and Directory Interface (JNDI) lookup. The following example shows a JNDI lookup for the myDB data source value:

DataSource myDB = InitialContext.doLookup("jdbc/myDB");

If the myDB value is configured as the default data source, the JNDI lookup can specify a java:comp/DefaultDataSource value instead of the JNDI name, as shown in the following example:

DataSource myDB = InitialContext.doLookup("java:comp/DefaultDataSource");