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 server.xml
file 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.dependsOn
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 you can configure 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.
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.
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. Thejavax.sql.XADataSource
data source type is a superset of the capabilities that are provided by thejavax.sql.DataSource
andjavax.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
Thejava.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. Likejavax.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 theServiceLoader
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:
javax.sql.XADataSource
javax.sql.ConnectionPoolDataSource
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:
javax.sql.ConnectionPoolDataSource
javax.sql.DataSource
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");