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 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 need to specify only the location of the JDBC driver.

For more information about configuring trace for your JDBC driver, see JDBC tracing.

JDBC driver configuration with Maven or Gradle

If you use Maven or Gradle build tools to build your application, you can configure a dependency to copy the JDBC driver files to the ${server.config.dir}/jdbc directory. The driver files must be copied after the server is created but before the application is deployed so that the database instance is available to the application at run time.

With Maven, you can use the copyDependencies element for the Open Liberty Maven plug-in to create a dependency for 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 driver files are copied to the jdbc directory that is in the ${server.config.dir} directory:

<plugin>
<groupId>io.openliberty.tools</groupId>
<artifactId>liberty-maven-plugin</artifactId>
<version>3.3.4</version>
  <configuration>
    <copyDependencies>
      <dependencyGroup>
       	<location>jdbc</location>
     	  <dependency>
            <groupId>com.ibm.db2</groupId>
            <artifactId>jcc</artifactId>
            <version>11.5.6.0</version>
      	  </dependency>
      </dependencyGroup>
     </copyDependencies>
   </configuration>
</plugin>

This example uses version 3.3.4 of the Liberty Maven plug-in. To use a different version, update the version value to specify the version that you want to use. For more information, see Copying dependencies with the Open Liberty Maven plug-in.

The Open Liberty Gradle plug-in does not directly support configuring the JDBC driver library. With Gradle, you must manually add a Copy task to your build.gradle file to manage the JDBC dependency.

If you use Gradle to build your application, first add a copy task to your build.gradle file. Then, add a dependency for that task to the deploy task so that the copy task runs after the server is created but before the application is deployed:

configurations {
  jdbcLib
}
dependencies {
  jdbcLib 'com.ibm.db2:jcc:11.5.6.0'
}
task copyJDBC(type: Copy) {
  from configurations.jdbcLib
  into '$buildDir/wlp/usr/servers/<YourServerName>/jdbc'
  include '*.jar'
}
deploy.dependsOn 'copyJDBC'

In this build.gradle file example, a configurations instance that is called jdbcLib is declared with a dependency for the driver. The copyJDBC task specifies that the driver JAR files are copied into the jdbc folder in the server configuration directory. Finally, a deploy dependency specifies that the driver must be copied to the server before the application is deployed so it can be available at run time.

Data source configuration

You can configure any JDBC driver with Open Liberty, which includes 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 either a single properties or properties.{JDBC_VENDOR_TYPE} subelement under the dataSource element. Use a properties.{JDBC_VENDOR_TYPE} subelement if Liberty provides one for the JDBC driver that you are using. Otherwise, use a properties subelement. For more information about the vendor-specific properties.{JDBC_VENDOR_TYPE} subelements that Liberty provides, see the dataSource element.

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

<dataSource jndiName="jdbc/myDB" jdbcDriverRef="myDriver"/>
    <properties someProperty="someValue" anotherProperty="5" />
</dataSource>

For an example of a vendor-specific properties subelement, consider the IBM Db2 JDBC driver, for which Open Liberty provides the properties.db2.jcc subelement. The following application 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 in your server.xml file by specifying the properties.db2.jcc subelement, as shown in the following example:

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

After you configure a data source, you can quickly test the configuration to see whether your Open Liberty server can access your database. For more information, see Validating a connection to a database.

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 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>

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>

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>

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>

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>

Oracle RAC

Oracle Real Application Clusters (RAC) is an option that brings together two or more database instances, which are known as nodes, to form a clustered database that behaves as a single system. Using the Oracle JDBC driver, you can configure failover support, load balancing, or both, in an Oracle RAC environment. Oracle RAC provides both high availability and flexible scalability and is typically used along with Oracle UCP.

The following example shows a sample data source configuration for an Oracle RAC database that consists of two nodes:

<dataSource jndiName="jdbc/oracleUCPDS">
  <jdbcDriver libraryRef="OracleUCPLib" />
  <properties.oracle
  URL="jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=ON)(LOAD_BALANCE=OFF)(ADDRESS=(PROTOCOL=TCP)(HOST=example-host-1)(PORT=example-port-1))
  (ADDRESS=(PROTOCOL=TCP)(HOST=example-host-2)(PORT=example-port-2))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=name)))"
  user="username" password="password" />
</dataSource>

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

In this example, example-host-1 and example-port-1 represent the host and port values for the first node, and example-host-2 and example-port-2 represent host and port values for the second node. The FAILOVER and LOAD_BALANCE Oracle parameters specify global configuration for both nodes. For more information about Oracle database parameters, see the Oracle RAC documentation. If you are not using Oracle services, then the value for SERVICE_NAME is your database name. If you are using Oracle services, then the value for SERVICE_NAME is the name of the service. You are not required to specify the Oracle login credentials as Oracle properties, other methods of database authentication also work. Some Oracle RAC functions require the use of Oracle UCP, which is available only in Open Liberty version 19.0.0.4 and later.

Configuration of 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 the 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 use 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 basic form of data source lacks functions that are used by Liberty for optimizing connection pooling, such as connection and statement event listeners. Additionally, it is not able to participate in two-phase 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 later, or if 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 or earlier 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");

See also