Chapter 6. Using JDBC data sources

The following topics discuss the use of JDBC data sources in the Fuse OSGi runtime:

6.1. About the Connection interface

The most important object used to perform data manipulation is an implementation of the java.sql.Connection interface. From the perspective of Fuse configuration, it is important to learn how to obtain a Connection object.

The libraries that contain the relevant objects are:

PostgreSQL: mvn:org.postgresql/postgresql/42.2.5
MySQL: mvn:mysql/mysql-connector-java/5.1.34

The existing implementations (contained in driver JARs) provide:

PostgreSQL: org.postgresql.jdbc.PgConnection
MySQL: com.mysql.jdbc.JDBC4Connection (see also the various connect*() methods of com.mysql.jdbc.Driver)

These implementations contain database-specific logic to perform DML, DDL, and simple transaction management.

In theory, it is possible to manually create these connection objects, but there are two JDBC methods that hide the details to provide a cleaner API:

java.sql.Driver.connect() - This method was used in standalone applications a long time ago.
javax.sql.DataSource.getConnection() - This is the preferred method for using the factory pattern. A similar method is used to obtain JMS connections from a JMS connection factory.

The driver manager approach is not discussed here. It is enough to state that this method is just a tiny layer above a plain constructor for a given connection object.

In addition to java.sql.Connection, which effectively implements database-specific communication protocols, there are two other specialized connection interfaces:

javax.sql.PooledConnection represents a physical connection. Your code does not interact with this pooled connection directly. Instead, the connection obtained from the getConnection() method is used. This indirection enables management of connection pools at the level of an application server. The connection obtained by using getConnection() is usually a proxy. When such a proxy connection is closed, the physical connection is not closed and instead it becomes available again in the managed connection pool.
javax.sql.XAConnection allows obtaining a javax.transaction.xa.XAResource object that is associated with XA-aware connection for use with javax.transaction.TransactionManager. Because javax.sql.XAConnection extends javax.sql.PooledConnection, it also provides the `getConnection() method, which provides access to a JDBC connection object with typical DML/DQL methods.

6.2. Overview of JDBC data sources

The JDBC 1.4 standard introduced the javax.sql.DataSource interface, which acted as a factory for java.sql.Connection objects. Usually such data sources were bound to a JNDI registry and were located inside or injected into Java EE components such as servlets or EJBs. The key aspect is that these data sources were configured inside the application server and referenced in deployed applications by name.

The following connection objects have their own data sources:

Data Source	Connection
`javax.sql.DataSource`	`java.sql.Connection`
`javax.sql.ConnectionPoolDataSource`	`javax.sql.PooledConnection`
`javax.sql.XADataSource`	`javax.sql.XAConnection`

The most important differences between each of the above data sources is as follows:

javax.sql.DataSource is most importantly a factory-like object for obtaining java.sql.Connection instances. The fact that most javax.sql.DataSource implementations usually perform connection pooling should not change the picture. This is the only interface that should be used by application code. It does not matter which of the following you are implementing:
- Direct JDBC access
- JPA persistence unit configuration (either <jta-data-source> or <non-jta-data-source>)
- Popular library such as Apache Camel or Spring Framework
javax.sql.ConnectionPoolDataSource is most importantly a bridge between a generic (non database-specific) connection pool/data source and a database-specific data source. It may be treated as an SPI interface. Application code usually deals with a generic javax.sql.DataSource object that was obtained from JNDI and implemented by an application server (probably using a library such as commons-dbcp2). On the other end, application code does not interface with javax.sql.ConnectionPoolDataSource directly. It is used between an application server and a database-specific driver. The following sequence diagram shows this:

javax.sql.XADataSource is a way to obtain javax.sql.XAConnection and javax.transaction.xa.XAResource. Same as javax.sql.ConnectionPoolDataSource, it’s used between application server and database-specific driver. Here’s slightly modified diagram with different actors, this time including JTA Transaction Manager:

As shown in two previous diagrams, you interact with the App Server, which is a generalized entity in which you can configure javax.sql.DataSource and javax.transaction.UserTransaction instances. Such instances may be accessed either by means of JNDI or by injection using CDI or another dependency mechanism.

Important

The important point is that even if the application uses XA transactions and/or connection pooling, the application interacts with javax.sql.DataSource and not the two other JDBC data source interfaces.

6.2.1. Database specific and generic data sources

The JDBC data source implementations fall into two categories:

Generic javax.sql.DataSource implementations such as :
- Apache Commons DBCP(2)
- Apache Tomcat JDBC (based on DBCP)
Database specific implementations of javax.sql.DataSource, javax.sql.XADataSource, and javax.sql.ConnectionPoolDataSource

It might be confusing that a generic javax.sql.DataSource implementation cannot create database-specific connections on its own. Even if a generic data source could use java.sql.Driver.connect() or java.sql.DriverManager.getConnection(), it is usually better/cleaner to configure this generic data source with a database-specific javax.sql.DataSource implementation.

When a generic data source is going to interact with JTA, it must be configured with a database-specific implementation of javax.sql.XADataSource.

To close the picture, a generic data source usually does not need a database-specific implementation of javax.sql.ConnectionPoolDataSource to perform connection pooling. Existing pools usually handle pooling without standard JDBC interfaces (javax.sql.ConnectionPoolDataSource and javax.sql.PooledConnection) and instead use their own custom implementation.

6.2.2. Some generic data sources

Consider a sample, well-known, generic data source, Apache Commons DBCP(2).

javax.sql.XADataSource implementations

DBCP2 does not include any implementation of javax.sql.XADataSource, which is expected.

javax.sql.ConnectionPoolDataSource implementations

DBCP2 does include an implementation of javax.sql.ConnectionPoolDataSource: org.apache.commons.dbcp2.cpdsadapter.DriverAdapterCPDS. It creates javax.sql.PooledConnection objects by calling java.sql.DriverManager.getConnection(). This pool should not be used directly and it should be treated as an adapter for drivers that:

Do not provide their own javax.sql.ConnectionPoolDataSource implementation
You want to use according to JDBC recommendations for connection pools

As shown in the sequence diagram above, the driver provides javax.sql.ConnectionPoolDataSource directly or with the help of an org.apache.commons.dbcp2.cpdsadapter.DriverAdapterCPDS adapter, while DBCP2 implements the application server contract with one of:

org.apache.commons.dbcp2.datasources.PerUserPoolDataSource
org.apache.commons.dbcp2.datasources.SharedPoolDataSource

Both these pools take an instance of javax.sql.ConnectionPoolDataSource at the configuration stage.

This is the most important and interesting part of DBCP2:

javax.sql.DataSource implementations

To implement the connection pooling feature, you do not have to follow JDBC recommendations to use javax.sql.ConnectionPoolDataSource javax.sql.PooledConnection SPI.

Here is a list of normal data sources of DBCP2:

org.apache.commons.dbcp2.BasicDataSource
org.apache.commons.dbcp2.managed.BasicManagedDataSource
org.apache.commons.dbcp2.PoolingDataSource
org.apache.commons.dbcp2.managed.ManagedDataSource

There are two axes here:

basic vs pooling

This axis determines the pooling configuration aspect.

Both kinds of data sources perform pooling of java.sql.Connection objects. The only difference is that:

A basic data source is configured by using bean properties such as maxTotal or minIdle used to configure an internal instance of org.apache.commons.pool2.impl.GenericObjectPool.
A pooling data source is configured with an externally created/configured org.apache.commons.pool2.ObjectPool.

managed vs non-managed

This axis determines the connection creation aspect and the JTA behavior:

A non-managed basic data source creates java.sql.Connection instances by using java.sql.Driver.connect() internally.
A non-managed pooling data source creates java.sql.Connection instances using the passed org.apache.commons.pool2.ObjectPool object.
A managed pooling data source wraps java.sql.Connection instances inside org.apache.commons.dbcp2.managed.ManagedConnection objects that ensure that javax.transaction.Transaction.enlistResource() is called if needed in the JTA context. But still the actual connection that is wrapped is obtained from any org.apache.commons.pool2.ObjectPool object that the pool is configured with.
A managed basic data source frees you from configuring a dedicated org.apache.commons.pool2.ObjectPool. Instead, it is enough to configure existing, real, database-specific javax.sql.XADataSource objects. Bean properties are used to create an internal instance of org.apache.commons.pool2.impl.GenericObjectPool, which is then passed to an internal instance of a managed pooling data source (org.apache.commons.dbcp2.managed.ManagedDataSource).

Note

The only thing that DBCP2 cannot do is XA transaction recovery. DBCP2 correctly enlists XAResources in active JTA transactions, but it is not performing the recovery. This should be done separately and the configuration is usually specific to the chosen transaction manager implementation (such as Narayana).

6.2.3. Pattern to use

The recommended pattern is:

Create or obtain a database-specific javax.sql.DataSource or javax.sql.XADataSource instance with database-specific configuration (URL, credentials, and so on) that can create connections/XA connections.
Create or obtain a non database-specific javax.sql.DataSource instance (internally configured with the above, database-specific data source) with non database-specific configuration (connection pooling, transaction manager, and so on).
Use javax.sql.DataSource to get an instance of java.sql.Connection and perform JDBC operations.

Here is a canonical example:

// Database-specific, non-pooling, non-enlisting javax.sql.XADataSource
PGXADataSource postgresql = new org.postgresql.xa.PGXADataSource();
// Database-specific configuration
postgresql.setUrl("jdbc:postgresql://localhost:5432/reportdb");
postgresql.setUser("fuse");
postgresql.setPassword("fuse");
postgresql.setCurrentSchema("report");
postgresql.setConnectTimeout(5);
// ...

// Non database-specific, pooling, enlisting javax.sql.DataSource
BasicManagedDataSource pool = new org.apache.commons.dbcp2.managed.BasicManagedDataSource();
// Delegate to database-specific XADatasource
pool.setXaDataSourceInstance(postgresql);
// Delegate to JTA transaction manager
pool.setTransactionManager(transactionManager);
// Non database-specific configuration
pool.setMinIdle(3);
pool.setMaxTotal(10);
pool.setValidationQuery("select schema_name, schema_owner from information_schema.schemata");
// ...

// JDBC code:
javax.sql.DataSource applicationDataSource = pool;

try (Connection c = applicationDataSource.getConnection()) {
    try (Statement st = c.createStatement()) {
        try (ResultSet rs = st.executeQuery("select ...")) {
            // ....

In a Fuse environment, there are many configuration options and there is no requirement to use DBCP2.

6.3. Configuring JDBC data sources

As discussed in OSGi transaction architecture, some services must be registered in the OSGi service registry. Just as you can find (lookup) a transaction manager instance by using, for example, the javax.transaction.UserTransaction interface, you can do the same with JDBC data sources by using the javax.sql.DataSource interface. The requirements are:

Database-specific data source that can communicate with the target database
Generic data source where you can configure pooling and possibly transaction management (XA)

In an OSGi environment, such as Fuse, data sources become accessible from applications if they are registered as OSGi services. Fundamentally, it is done as follows:

org.osgi.framework.BundleContext.registerService(javax.sql.DataSource.class,
                                                 dataSourceObject,
                                                 properties);
org.osgi.framework.BundleContext.registerService(javax.sql.XADataSource.class,
                                                 xaDataSourceObject,
                                                 properties);

There are two methods for registering such services:

Publishing data sources by using the jdbc:ds-create Karaf console command. This is the configuration method.
Publishing data sources by using methods such as Blueprint, OSGi Declarative Services (SCR) or just a BundleContext.registerService() API call. This method requires a dedicated OSGi bundle that contains the code and/or metadata. This is the_deployment method_.

6.4. Using the OSGi JDBC service

Chapter 125 of the OSGi Enterprise R6 specification defines a single interface in the org.osgi.service.jdbc package. This is how OSGi handles data sources:

public interface DataSourceFactory {

    java.sql.Driver createDriver(Properties props);

    javax.sql.DataSource createDataSource(Properties props);

    javax.sql.ConnectionPoolDataSource createConnectionPoolDataSource(Properties props);

    javax.sql.XADataSource createXADataSource(Properties props);
}

As mentioned before, plain java.sql.Connection connections may be obtained directly from java.sql.Driver.

Generic org.osgi.service.jdbc.DataSourceFactory

The simplest implementation of org.osgi.service.jdbc.DataSourceFactory is org.ops4j.pax.jdbc.impl.DriverDataSourceFactory provided by mvn:org.ops4j.pax.jdbc/pax-jdbc/1.3.0 bundle. All it does is track bundles that may include the /META-INF/services/java.sql.Driver descriptor for the standard Java™ ServiceLoader utility. If you install any standard JDBC driver, the pax-jdbc bundle registers a DataSourceFactory that can be used (not directly) to obtain connections by means of a java.sql.Driver.connect() call.

karaf@root()> install -s mvn:org.osgi/org.osgi.service.jdbc/1.0.0
Bundle ID: 223
karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc/1.3.0
Bundle ID: 224
karaf@root()> install -s mvn:org.postgresql/postgresql/42.2.5
Bundle ID: 225
karaf@root()> install -s mvn:mysql/mysql-connector-java/5.1.34
Bundle ID: 226

karaf@root()> bundle:services -p org.postgresql.jdbc42

PostgreSQL JDBC Driver JDBC42 (225) provides:
---------------------------------------------
objectClass = [org.osgi.service.jdbc.DataSourceFactory]
osgi.jdbc.driver.class = org.postgresql.Driver
osgi.jdbc.driver.name = PostgreSQL JDBC Driver
osgi.jdbc.driver.version = 42.2.5
service.bundleid = 225
service.id = 242
service.scope = singleton

karaf@root()> bundle:services -p com.mysql.jdbc

Oracle Corporation's JDBC Driver for MySQL (226) provides:
----------------------------------------------------------
objectClass = [org.osgi.service.jdbc.DataSourceFactory]
osgi.jdbc.driver.class = com.mysql.jdbc.Driver
osgi.jdbc.driver.name = com.mysql.jdbc
osgi.jdbc.driver.version = 5.1.34
service.bundleid = 226
service.id = 243
service.scope = singleton
-----
objectClass = [org.osgi.service.jdbc.DataSourceFactory]
osgi.jdbc.driver.class = com.mysql.fabric.jdbc.FabricMySQLDriver
osgi.jdbc.driver.name = com.mysql.jdbc
osgi.jdbc.driver.version = 5.1.34
service.bundleid = 226
service.id = 244
service.scope = singleton

karaf@root()> service:list org.osgi.service.jdbc.DataSourceFactory
[org.osgi.service.jdbc.DataSourceFactory]
-----------------------------------------
 osgi.jdbc.driver.class = org.postgresql.Driver
 osgi.jdbc.driver.name = PostgreSQL JDBC Driver
 osgi.jdbc.driver.version = 42.2.5
 service.bundleid = 225
 service.id = 242
 service.scope = singleton
Provided by :
 PostgreSQL JDBC Driver JDBC42 (225)

[org.osgi.service.jdbc.DataSourceFactory]
-----------------------------------------
 osgi.jdbc.driver.class = com.mysql.jdbc.Driver
 osgi.jdbc.driver.name = com.mysql.jdbc
 osgi.jdbc.driver.version = 5.1.34
 service.bundleid = 226
 service.id = 243
 service.scope = singleton
Provided by :
 Oracle Corporation's JDBC Driver for MySQL (226)

[org.osgi.service.jdbc.DataSourceFactory]
-----------------------------------------
 osgi.jdbc.driver.class = com.mysql.fabric.jdbc.FabricMySQLDriver
 osgi.jdbc.driver.name = com.mysql.jdbc
 osgi.jdbc.driver.version = 5.1.34
 service.bundleid = 226
 service.id = 244
 service.scope = singleton
Provided by :
 Oracle Corporation's JDBC Driver for MySQL (226)

With the above commands, the javax.sql.DataSource service is still not registered, but you are one step closer. The above intermediary org.osgi.service.jdbc.DataSourceFactory services can be used to obtain:

java.sql.Driver
javax.sql.DataSource by passing properties: url, user and password to the createDataSource() method.

You cannot obtain javax.sql.ConnectionPoolDataSource or javax.sql.XADataSource from the generic org.osgi.service.jdbc.DataSourceFactory created by a non database-specific pax-jdbc bundle.

Note

The mvn:org.postgresql/postgresql/42.2.5 bundle correctly implements the OSGi JDBC specification and registers an org.osgi.service.jdbc.DataSourceFactory instance with all methods that are implemented, including the ones that create XA and ConnectionPool data sources.

Dedicated, database-specific org.osgi.service.jdbc.DataSourceFactory implementations

There are additional bundles such as the following:

mvn:org.ops4j.pax.jdbc/pax-jdbc-mysql/1.3.0
mvn:org.ops4j.pax.jdbc/pax-jdbc-db2/1.3.0
…

These bundles register database-specific org.osgi.service.jdbc.DataSourceFactory services that can return all kinds of factories, including javax.sql.ConnectionPoolDataSource and javax.sql.XADataSource. For example:

karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-mysql/1.3.0
Bundle ID: 227

karaf@root()> bundle:services -p org.ops4j.pax.jdbc.mysql

OPS4J Pax JDBC MySQL Driver Adapter (227) provides:
---------------------------------------------------
objectClass = [org.osgi.service.jdbc.DataSourceFactory]
osgi.jdbc.driver.class = com.mysql.jdbc.Driver
osgi.jdbc.driver.name = mysql
service.bundleid = 227
service.id = 245
service.scope = singleton

karaf@root()> service:list org.osgi.service.jdbc.DataSourceFactory
...
[org.osgi.service.jdbc.DataSourceFactory]
-----------------------------------------
 osgi.jdbc.driver.class = com.mysql.jdbc.Driver
 osgi.jdbc.driver.name = mysql
 service.bundleid = 227
 service.id = 245
 service.scope = singleton
Provided by :
 OPS4J Pax JDBC MySQL Driver Adapter (227)

6.4.1. PAX-JDBC configuration service

With pax-jdbc (or pax-jdbc-mysql, pax-jdbc-oracle, …) bundles, you can have org.osgi.service.jdbc.DataSourceFactory services registered that can be used to obtain data sources for a given database (see Section 6.2.1, “Database specific and generic data sources”). But you do not have actual data sources yet.

The mvn:org.ops4j.pax.jdbc/pax-jdbc-config/1.3.0 bundle provides a managed service factory that does two things:

Tracks org.osgi.service.jdbc.DataSourceFactory OSGi services in order to invoke its methods:

public DataSource createDataSource(Properties props);
public XADataSource createXADataSource(Properties props);
public ConnectionPoolDataSource createConnectionPoolDataSource(Properties props);

Tracks org.ops4j.datasource factory PIDs to collect properties that are required by the above methods. If you create a factory configuration by using any method available to the Configuration Admin service, for example, by creating a ${karaf.etc}/org.ops4j.datasource-mysql.cfg file, you can perform the final step to expose an actual database-specific data source.

Here is a detailed, canonical step-by-step guide for starting from a fresh installation of Fuse.

Note

You explicitly install bundles instead of features, to show exactly which bundles are needed. For convenience, the PAX JDBC project provides features for several database products and configuration approaches.

Install a JDBC driver with /META-INF/services/java.sql.Driver:

karaf@root()> install -s mvn:mysql/mysql-connector-java/5.1.34
Bundle ID: 223

Install the OSGi JDBC service bundle and pax-jdbc-mysql bundle that registers intermediaryorg.osgi.service.jdbc.DataSourceFactory:

karaf@root()> install -s mvn:org.osgi/org.osgi.service.jdbc/1.0.0
Bundle ID: 224
karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-mysql/1.3.0
Bundle ID: 225

karaf@root()> service:list org.osgi.service.jdbc.DataSourceFactory
[org.osgi.service.jdbc.DataSourceFactory]
-----------------------------------------
 osgi.jdbc.driver.class = com.mysql.jdbc.Driver
 osgi.jdbc.driver.name = mysql
 service.bundleid = 225
 service.id = 242
 service.scope = singleton
Provided by :
 OPS4J Pax JDBC MySQL Driver Adapter (225)

Install the pax-jdbc bundle and the pax-jdbc-config bundle that tracks org.osgi.service.jdbc.DataSourceFactory services andorg.ops4j.datasource factory PIDs:

karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc/1.3.0
Bundle ID: 226
karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-pool-common/1.3.0
Bundle ID: 227
karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-config/1.3.0
Bundle ID: 228

karaf@root()> bundle:services -p org.ops4j.pax.jdbc.config

OPS4J Pax JDBC Config (228) provides:
-------------------------------------
objectClass = [org.osgi.service.cm.ManagedServiceFactory]
service.bundleid = 228
service.id = 245
service.pid = org.ops4j.datasource
service.scope = singleton

Create the factory configuration (assume a MySQL server is running):

karaf@root()> config:edit --factory --alias mysql org.ops4j.datasource
karaf@root()> config:property-set osgi.jdbc.driver.name mysql
karaf@root()> config:property-set dataSourceName mysqlds
karaf@root()> config:property-set url jdbc:mysql://localhost:3306/reportdb
karaf@root()> config:property-set user fuse
karaf@root()> config:property-set password fuse
karaf@root()> config:update

karaf@root()> config:list '(service.factoryPid=org.ops4j.datasource)'
----------------------------------------------------------------
Pid:            org.ops4j.datasource.a7941498-9b62-4ed7-94f3-8c7ac9365313
FactoryPid:     org.ops4j.datasource
BundleLocation: ?
Properties:
   dataSourceName = mysqlds
   felix.fileinstall.filename = file:${karaf.etc}/org.ops4j.datasource-mysql.cfg
   osgi.jdbc.driver.name = mysql
   password = fuse
   service.factoryPid = org.ops4j.datasource
   service.pid = org.ops4j.datasource.a7941498-9b62-4ed7-94f3-8c7ac9365313
   url = jdbc:mysql://localhost:3306/reportdb
   user = fuse

Check if pax-jdbc-config processed the configuration into the javax.sql.DataSource service:

karaf@root()> service:list javax.sql.DataSource
[javax.sql.DataSource]
----------------------
 dataSourceName = mysqlds
 felix.fileinstall.filename = file:${karaf.etc}/org.ops4j.datasource-mysql.cfg
 osgi.jdbc.driver.name = mysql
 osgi.jndi.service.name = mysqlds
 password = fuse
 pax.jdbc.managed = true
 service.bundleid = 228
 service.factoryPid = org.ops4j.datasource
 service.id = 246
 service.pid = org.ops4j.datasource.a7941498-9b62-4ed7-94f3-8c7ac9365313
 service.scope = singleton
 url = jdbc:mysql://localhost:3306/reportdb
 user = fuse
Provided by :
 OPS4J Pax JDBC Config (228)

You now have an actual database-specific (no pooling yet) data source. You can already inject it where it is needed. For example, you can use Karaf commands to query the database:

karaf@root()> feature:install -v jdbc
Adding features: jdbc/[4.2.0.fuse-000237-redhat-1,4.2.0.fuse-000237-redhat-1]
...
karaf@root()> jdbc:ds-list
Mon May 14 08:46:22 CEST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Name    │ Product │ Version │ URL                                  │ Status
────────┼─────────┼─────────┼──────────────────────────────────────┼───────
mysqlds │ MySQL   │ 5.7.21  │ jdbc:mysql://localhost:3306/reportdb │ OK

karaf@root()> jdbc:query mysqlds 'select * from incident'
Mon May 14 08:46:46 CEST 2018 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
date                  │ summary    │ name   │ details                       │ id │ email
──────────────────────┼────────────┼────────┼───────────────────────────────┼────┼─────────────────
2018-02-20 08:00:00.0 │ Incident 1 │ User 1 │ This is a report incident 001 │ 1  │ user1@redhat.com
2018-02-20 08:10:00.0 │ Incident 2 │ User 2 │ This is a report incident 002 │ 2  │ user2@redhat.com
2018-02-20 08:20:00.0 │ Incident 3 │ User 3 │ This is a report incident 003 │ 3  │ user3@redhat.com
2018-02-20 08:30:00.0 │ Incident 4 │ User 4 │ This is a report incident 004 │ 4  │ user4@redhat.com

In the above example, you can see a MySQL warning. This is not a problem. Any property (not only OSGi JDBC specific ones) may be provided:

karaf@root()> config:property-set --pid org.ops4j.datasource.a7941498-9b62-4ed7-94f3-8c7ac9365313 useSSL false

karaf@root()> jdbc:ds-list
Name    │ Product │ Version │ URL                                  │ Status
────────┼─────────┼─────────┼──────────────────────────────────────┼───────
mysqlds │ MySQL   │ 5.7.21  │ jdbc:mysql://localhost:3306/reportdb │ OK

6.4.2. Summary of handled properties

Properties from the configuration of the admin factory PID are passed to the relevant org.osgi.service.jdbc.DataSourceFactory implementation.

Generic

org.ops4j.pax.jdbc.impl.DriverDataSourceFactory properties:

url
user
password

DB2

org.ops4j.pax.jdbc.db2.impl.DB2DataSourceFactory properties include all bean properties of these implementation classes:

com.ibm.db2.jcc.DB2SimpleDataSource
com.ibm.db2.jcc.DB2ConnectionPoolDataSource
com.ibm.db2.jcc.DB2XADataSource

PostgreSQL

Nnative org.postgresql.osgi.PGDataSourceFactory properties include all properties that are specified in org.postgresql.PGProperty.

HSQLDB

org.ops4j.pax.jdbc.hsqldb.impl.HsqldbDataSourceFactory properties:

url
user
password
databaseName
All bean properties of
- org.hsqldb.jdbc.JDBCDataSource
- org.hsqldb.jdbc.pool.JDBCPooledDataSource
- org.hsqldb.jdbc.pool.JDBCXADataSource

SQL Server and Sybase

org.ops4j.pax.jdbc.jtds.impl.JTDSDataSourceFactory properties include all bean properties of net.sourceforge.jtds.jdbcx.JtdsDataSource.

SQL Server

org.ops4j.pax.jdbc.mssql.impl.MSSQLDataSourceFactory properties:

url
user
password
databaseName
serverName
portNumber
All bean properties of
- com.microsoft.sqlserver.jdbc.SQLServerDataSource
- com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource
- com.microsoft.sqlserver.jdbc.SQLServerXADataSource

MySQL

org.ops4j.pax.jdbc.mysql.impl.MysqlDataSourceFactory properties:

url
user
password
databaseName
serverName
portNumber
All bean properties of
- com.mysql.jdbc.jdbc2.optional.MysqlDataSource
- com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
- com.mysql.jdbc.jdbc2.optional.MysqlXADataSource

Oracle

org.ops4j.pax.jdbc.oracle.impl.OracleDataSourceFactory properties:

url
databaseName
serverName
user
password
All bean properties of
- oracle.jdbc.pool.OracleDataSource
- oracle.jdbc.pool.OracleConnectionPoolDataSource
- oracle.jdbc.xa.client.OracleXADataSource

SQLite

org.ops4j.pax.jdbc.sqlite.impl.SqliteDataSourceFactory properties:

url
databaseName
All bean properties of org.sqlite.SQLiteDataSource

6.4.3. How the pax-jdb-config bundle handles properties

The pax-jdbc-config bundle handles properties that prefixed with jdbc.. All of these properties will have this prefix removed and the remaining names will be passed over.

Here is the example, again, starting with a fresh installation of Fuse:

karaf@root()> install -s mvn:mysql/mysql-connector-java/5.1.34
Bundle ID: 223
karaf@root()> install -s mvn:org.osgi/org.osgi.service.jdbc/1.0.0
Bundle ID: 224
karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-mysql/1.3.0
Bundle ID: 225
karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc/1.3.0
Bundle ID: 226
karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-pool-common/1.3.0
Bundle ID: 227
karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-config/1.3.0
Bundle ID: 228

karaf@root()> config:edit --factory --alias mysql org.ops4j.datasource
karaf@root()> config:property-set osgi.jdbc.driver.name mysql
karaf@root()> config:property-set dataSourceName mysqlds
karaf@root()> config:property-set dataSourceType DataSource
karaf@root()> config:property-set jdbc.url jdbc:mysql://localhost:3306/reportdb
karaf@root()> config:property-set jdbc.user fuse
karaf@root()> config:property-set jdbc.password fuse
karaf@root()> config:property-set jdbc.useSSL false
karaf@root()> config:update

karaf@root()> config:list '(service.factoryPid=org.ops4j.datasource)'
----------------------------------------------------------------
Pid:            org.ops4j.datasource.7c3ee718-7309-46a0-ae3a-64b38b17a0a3
FactoryPid:     org.ops4j.datasource
BundleLocation: ?
Properties:
   dataSourceName = mysqlds
   dataSourceType = DataSource
   felix.fileinstall.filename = file:/data/servers/7.6.0.fuse-760025-redhat-00001/etc/org.ops4j.datasource-mysql.cfg
   jdbc.password = fuse
   jdbc.url = jdbc:mysql://localhost:3306/reportdb
   jdbc.useSSL = false
   jdbc.user = fuse
   osgi.jdbc.driver.name = mysql
   service.factoryPid = org.ops4j.datasource
   service.pid = org.ops4j.datasource.7c3ee718-7309-46a0-ae3a-64b38b17a0a3

karaf@root()> service:list javax.sql.DataSource
[javax.sql.DataSource]
----------------------
 dataSourceName = mysqlds
 dataSourceType = DataSource
 felix.fileinstall.filename = file:${karaf.etc}/org.ops4j.datasource-mysql.cfg
 jdbc.password = fuse
 jdbc.url = jdbc:mysql://localhost:3306/reportdb
 jdbc.user = fuse
 jdbc.useSSL = false
 osgi.jdbc.driver.name = mysql
 osgi.jndi.service.name = mysqlds
 pax.jdbc.managed = true
 service.bundleid = 228
 service.factoryPid = org.ops4j.datasource
 service.id = 246
 service.pid = org.ops4j.datasource.7c3ee718-7309-46a0-ae3a-64b38b17a0a3
 service.scope = singleton
Provided by :
 OPS4J Pax JDBC Config (228)

The pax-jdbc-config bundle requires these properties:

osgi.jdbc.driver.name
dataSourceName
dataSourceType

to locate and invoke relevant org.osgi.service.jdbc.DataSourceFactory methods. Properties that are prefixed with jdbc. are passed (after removing the prefix) to, for example, org.osgi.service.jdbc.DataSourceFactory.createDataSource(properties). However, these properties are added, without the prefix removed, as properties of, for example, the javax.sql.DataSource OSGi service.

6.5. Using JDBC console commands

Fuse provides the jdbc feature, which includes shell commands in the jdbc:* scope. A previous example showed the use of jdbc:query. There are also commands that hide the need to create Configuration Admin configurations.

Starting with a fresh instance of Fuse, you can register a database-specific data source with a generic DataSourceFactory service as follows:

karaf@root()> feature:install jdbc

karaf@root()> jdbc:ds-factories
Name │ Class │ Version
─────┼───────┼────────

karaf@root()> install -s mvn:mysql/mysql-connector-java/5.1.34
Bundle ID: 228

karaf@root()> jdbc:ds-factories
Name           │ Class                                   │ Version
───────────────┼─────────────────────────────────────────┼────────
com.mysql.jdbc │ com.mysql.jdbc.Driver                   │ 5.1.34
com.mysql.jdbc │ com.mysql.fabric.jdbc.FabricMySQLDriver │ 5.1.34

Here is an example of registering a MySQL-specific DataSourceFactory service:

karaf@root()> feature:repo-add mvn:org.ops4j.pax.jdbc/pax-jdbc-features/1.3.0/xml/features-gpl
Adding feature url mvn:org.ops4j.pax.jdbc/pax-jdbc-features/1.3.0/xml/features-gpl

karaf@root()> feature:install pax-jdbc-mysql

karaf@root()> la -l|grep mysql

232 │ Active   │  80 │ 5.1.34                   │ mvn:mysql/mysql-connector-java/5.1.34

233 │ Active   │  80 │ 1.3.0                    │ mvn:org.ops4j.pax.jdbc/pax-jdbc-mysql/1.3.0

karaf@root()> jdbc:ds-factories
Name           │ Class                                   │ Version
───────────────┼─────────────────────────────────────────┼────────
com.mysql.jdbc │ com.mysql.jdbc.Driver                   │ 5.1.34
mysql          │ com.mysql.jdbc.Driver                   │
com.mysql.jdbc │ com.mysql.fabric.jdbc.FabricMySQLDriver │ 5.1.34

The above table may be confusing, but as mentioned above, only one of the pax-jdbc-database bundles may register org.osgi.service.jdbc.DataSourceFactory instances that can create standard/XA/connection pool data sources that do not simply delegate to java.sql.Driver.connect().

The following example creates and checks a MySQL data source:

karaf@root()> jdbc:ds-create -dt DataSource -dn mysql -url 'jdbc:mysql://localhost:3306/reportdb?useSSL=false' -u fuse -p fuse mysqlds

karaf@root()> jdbc:ds-list
Name    │ Product │ Version │ URL                                               │ Status
────────┼─────────┼─────────┼───────────────────────────────────────────────────┼───────
mysqlds │ MySQL   │ 5.7.21  │ jdbc:mysql://localhost:3306/reportdb?useSSL=false │ OK

karaf@root()> jdbc:query mysqlds 'select * from incident'
date                  │ summary    │ name   │ details                       │ id │ email
──────────────────────┼────────────┼────────┼───────────────────────────────┼────┼─────────────────
2018-02-20 08:00:00.0 │ Incident 1 │ User 1 │ This is a report incident 001 │ 1  │ user1@redhat.com
2018-02-20 08:10:00.0 │ Incident 2 │ User 2 │ This is a report incident 002 │ 2  │ user2@redhat.com
2018-02-20 08:20:00.0 │ Incident 3 │ User 3 │ This is a report incident 003 │ 3  │ user3@redhat.com
2018-02-20 08:30:00.0 │ Incident 4 │ User 4 │ This is a report incident 004 │ 4  │ user4@redhat.com

karaf@root()> config:list '(service.factoryPid=org.ops4j.datasource)'
----------------------------------------------------------------
Pid:            org.ops4j.datasource.55b18993-de4e-4e0b-abb2-a4c13da7f78b
FactoryPid:     org.ops4j.datasource
BundleLocation: mvn:org.ops4j.pax.jdbc/pax-jdbc-config/1.3.0
Properties:
   dataSourceName = mysqlds
   dataSourceType = DataSource
   osgi.jdbc.driver.name = mysql
   password = fuse
   service.factoryPid = org.ops4j.datasource
   service.pid = org.ops4j.datasource.55b18993-de4e-4e0b-abb2-a4c13da7f78b
   url = jdbc:mysql://localhost:3306/reportdb?useSSL=false
   user = fuse

As can see, the org.ops4j.datasource factory PID is created for you. However it is not automatically stored in ${karaf.etc}, which is possible with config:update.

6.6. Using encrypted configuration values

The pax-jdbc-config feature is able to process Configuration Admin configurations in which values are encrypted. A popular solution is to use Jasypt encryption services, which are also used by Blueprint.

If there are any org.jasypt.encryption.StringEncryptor services registered in OSGi with any alias service property, you can refrence it in a data source factory PID and use encrypted passwords. Here is an example:

felix.fileinstall.filename = */etc/org.ops4j.datasource-mysql.cfg
dataSourceName = mysqlds
dataSourceType = DataSource
decryptor = my-jasypt-decryptor
osgi.jdbc.driver.name = mysql
url = jdbc:mysql://localhost:3306/reportdb?useSSL=false
user = fuse
password = ENC(<encrypted-password>)

The service filter used to find the decryptor service is (&(objectClass=org.jasypt.encryption.StringEncryptor)(alias=<alias>)), where <alias> is the value of the decryptor property from the data source configuration factory PID.

6.7. Using JDBC connection pools

This section provides an introduction to using JDBC connection pools and then shows how to use these connection pool modules:

Important

This chapter presents exhaustive information about the internals of data source management. While information about the DBCP2 connection pool feature is provided, keep in mind that this connection pool provides proper JTA enlisting capabilities, but not XA Recovery.

To ensure that XA recovery is in place, use the pax-jdbc-pool-transx or pax-jdbc-pool-narayana connection pool module.

6.7.1. Introduction to using JDBC connection pools

Previous examples showed how to register a database-specific data source factory. Because data source itself is a factory for connections, org.osgi.service.jdbc.DataSourceFactory may be treated as a meta factory that should be able to produce three kinds of data sources, plus, as a bonus, a java.sql.Driver):

javax.sql.DataSource
javax.sql.ConnectionPoolDataSource
javax.sql.XADataSource

For example, pax-jdbc-mysql registers an org.ops4j.pax.jdbc.mysql.impl.MysqlDataSourceFactory that produces:

javax.sql.DataSource com.mysql.jdbc.jdbc2.optional.MysqlDataSource
javax.sql.ConnectionPoolDataSource com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
javax.sql.XADataSource com.mysql.jdbc.jdbc2.optional.MysqlXADataSource
java.sql.Driver com.mysql.jdbc.Driver

The PostgreSQL driver itself implements the OSGi JDBC service and produces:

javax.sql.DataSource org.postgresql.jdbc2.optional.PoolingDataSource (if there are pool-related properties specified) or org.postgresql.jdbc2.optional.SimpleDataSource
javax.sql.ConnectionPoolDataSource org.postgresql.jdbc2.optional.ConnectionPool
javax.sql.XADataSource org.postgresql.xa.PGXADataSource
java.sql.Driver org.postgresql.Driver

As shown in the canonical DataSource example, any pooling, generic data source, if it is going to work in a JTA environment, needs a database specific data source to actually obtain (XA) connections.

We already have the latter, and we need actual, generic, reliable connection pool.

The canonical DataSource example shows how to configure a generic pool with a database-specific data source. The pax-jdbc-pool-* bundles work smoothly with the above described org.osgi.service.jdbc.DataSourceFactory services.

Just as the OSGI Enterprise R6 JDBC specification provides the org.osgi.service.jdbc.DataSourceFactory standard interface, pax-jdbc-pool-common provides proprietary org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory interface:

public interface PooledDataSourceFactory {

    javax.sql.DataSource create(org.osgi.service.jdbc.DataSourceFactory dsf, Properties config)

}

This interface is perfectly conformant with what this important note that was presented previously and is worth repeating:

Important

Even if the application uses XA transactions and/or connection pooling, the application interacts with javax.sql.DataSource and not the two other JDBC data source interfaces.

This interface simply creates a pooling data source out of a database-specific, non-pooling datas ource. Or more precisely, it is a data source factory (meta factory) that turns a factory of database-specific data sources into a factory of pooling data sources.

Note

There is nothing that prevents an application from configuring pooling for a javax.sql.DataSource object by using an org.osgi.service.jdbc.DataSourceFactory service that already returns pooling for javax.sql.DataSource objects.

The following table shows which bundles register pooled data source factories. In the table, instances of o.o.p.j.p represent org.ops4j.pax.jdbc.pool.

Bundle	`PooledDataSourceFactory`	Pool Key
`pax-jdbc-pool-narayana`	`o.o.p.j.p.narayana.impl.Dbcp(XA)PooledDataSourceFactory`	`narayana`
`pax-jdbc-pool-dbcp2`	`o.o.p.j.p.dbcp2.impl.Dbcp(XA)PooledDataSourceFactory`	`dbcp2`
`pax-jdbc-pool-transx`	`o.o.p.j.p.transx.impl.Transx(Xa)PooledDataSourceFactory`	`transx`

The above bundles install only data source factories and not the data sources themselves. The application needs something that calls the javax.sql.DataSource create(org.osgi.service.jdbc.DataSourceFactory dsf, Properties config) method.

6.7.2. Using the `dbcp2` connection pool module

The section about generic data sources provides an example of how to use and configure the Apache Commons DBCP module. This section shows how to do this in the Fuse OSGi environment.

Consider the Section 6.4.1, “PAX-JDBC configuration service” bundle. In addition to tracking the following:

org.osgi.service.jdbc.DataSourceFactory services
org.ops4j.datasource factory PIDs

The bundle also tracks instances of org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory that are registered by one of the pax-jdbc-pool-* bundles.

If the factory configuration contains the pool property, then the ultimate data source registered by the pax-jdbc-config bundle is the database-specific datas ource, but wrapped inside one of the following if pool=dbcp2):

org.apache.commons.dbcp2.PoolingDataSource
org.apache.commons.dbcp2.managed.ManagedDataSource

This is consistent with the generic data source example. In addition to the pool property, and the boolean xa property, which selects a non-xa or an xa data source, the org.ops4j.datasource factory PID may contain prefixed properties:

pool.*
factory.*

Where each property is used depends on which pax-jdbc-pool-* bundle isused. For DBCP2, it is:

pool.*: bean properties of org.apache.commons.pool2.impl.GenericObjectPoolConfig (both xa and non-xa scenario)
factory.*: bean properties of org.apache.commons.dbcp2.managed.PoolableManagedConnectionFactory (xa) or org.apache.commons.dbcp2.PoolableConnectionFactory (non-xa)

6.7.2.1. Configuration properties for BasicDataSource

The following table lists the generic configuration properties for BasicDataSource.

Parameter	Default	Description
`username`		The connection user name to be passed to our JDBC driver to establish a connection.
`password`		The connection password to be passed to our JDBC driver to establish a connection.
`url`		The connection URL to be passed to our JDBC driver to establish a connection.
`driverClassName`		The fully qualified Java class name of the JDBC driver to be used.
`initialSize`	0	The initial number of connections that are created when the pool is started.
`maxTotal`	8	The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit.
`maxIdle`	8	The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit.
`minIdle`	0	The minimum number of connections that can remain idle in the pool, without extra ones being created, or zero to create none.
`maxWaitMillis`	indefinitely	The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or -1 to wait indefinitely.
`validationQuery`		The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query MUST be an SQL SELECT statement that returns at least one row. If not specified, connections will be validation by calling the isValid() method.
`validationQueryTimeout`	no timeout	The timeout in seconds before connection validation queries fail. If set to a positive value, this value is passed to the driver via the setQueryTimeout method of the Statement used to execute the validation query.
`testOnCreate`	false	The indication of whether objects will be validated after creation. If the object fails to validate, the borrow attempt that triggered the object creation will fail.
`testOnBorrow`	true	The indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another.
`testOnReturn`	false	The indication of whether objects will be validated before being returned to the pool.
`testWhileIdle`	false	The indication of whether objects will be validated by the idle object evictor (if any). If an object fails to validate, it will be dropped from the pool.
`timeBetweenEvictionRunsMillis`	-1	The number of milliseconds to sleep between runs of the idle object evictor thread. When non-positive, no idle object evictor thread will be run.
`numTestsPerEvictionRun`	3	The number of objects to examine during each run of the idle object evictor thread (if any).
`minEvictableIdleTimeMillis`	1000 * 60 * 30	The minimum amount of time an object may sit idle in the pool before it is eligible for eviction by the idle object evictor (if any).

6.7.2.2. Example of how to configure DBCP2 pool

The following is a realistic example (except useSSL=false) of a configuration of a DBCP2 pool (org.ops4j.datasource-mysql factory PID) that uses convenient syntax with jdbc.-prefixed properties:

# Configuration for pax-jdbc-config to choose and configure specific org.osgi.service.jdbc.DataSourceFactory
dataSourceName = mysqlds
dataSourceType = DataSource
osgi.jdbc.driver.name = mysql
jdbc.url = jdbc:mysql://localhost:3306/reportdb
jdbc.user = fuse
jdbc.password = fuse
jdbc.useSSL = false

# Hints for pax-jdbc-config to use org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory
pool = dbcp2
xa = false

# dbcp2 specific configuration of org.apache.commons.pool2.impl.GenericObjectPoolConfig
pool.minIdle = 10
pool.maxTotal = 100
pool.initialSize = 8
pool.blockWhenExhausted = true
pool.maxWaitMillis = 2000
pool.testOnBorrow = true
pool.testWhileIdle = false
pool.timeBetweenEvictionRunsMillis = 120000
pool.evictionPolicyClassName = org.apache.commons.pool2.impl.DefaultEvictionPolicy

# dbcp2 specific configuration of org.apache.commons.dbcp2.PoolableConnectionFactory
factory.maxConnLifetimeMillis = 30000
factory.validationQuery  = select schema_name from information_schema.schemata
factory.validationQueryTimeout = 2

In the above configuration, pool and xa keys are hints (service filter properties) to choose one of the registered org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory services. In the case of DBCP2, this is:

karaf@root()> feature:install pax-jdbc-pool-dbcp2

karaf@root()> bundle:services -p org.ops4j.pax.jdbc.pool.dbcp2

OPS4J Pax JDBC Pooling DBCP2 (230) provides:
--------------------------------------------
objectClass = [org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory]
pool = dbcp2
service.bundleid = 230
service.id = 337
service.scope = singleton
xa = false
-----
objectClass = [org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory]
pool = dbcp2
service.bundleid = 230
service.id = 338
service.scope = singleton
xa = true

For completeness, here is a full example with connection pool configuration added to the previous example. Again, this assumes that you are starting with a fresh Fuse installation.

Install a JDBC driver:

karaf@root()> install -s mvn:mysql/mysql-connector-java/5.1.34
Bundle ID: 223

Install the jdbc, pax-jdbc-mysql and pax-jdbc-pool-dbcp2 features:

karaf@root()> feature:repo-add mvn:org.ops4j.pax.jdbc/pax-jdbc-features/1.3.0/xml/features-gpl
Adding feature url mvn:org.ops4j.pax.jdbc/pax-jdbc-features/1.3.0/xml/features-gpl

karaf@root()> feature:install jdbc pax-jdbc-mysql pax-jdbc-pool-dbcp2

karaf@root()> service:list org.osgi.service.jdbc.DataSourceFactory
...
[org.osgi.service.jdbc.DataSourceFactory]
-----------------------------------------
 osgi.jdbc.driver.class = com.mysql.jdbc.Driver
 osgi.jdbc.driver.name = mysql
 service.bundleid = 232
 service.id = 328
 service.scope = singleton
Provided by :
 OPS4J Pax JDBC MySQL Driver Adapter (232)

karaf@root()> service:list org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory
[org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory]
--------------------------------------------------------
 pool = dbcp2
 service.bundleid = 233
 service.id = 324
 service.scope = singleton
 xa = false
Provided by :
 OPS4J Pax JDBC Pooling DBCP2 (233)

[org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory]
--------------------------------------------------------
 pool = dbcp2
 service.bundleid = 233
 service.id = 332
 service.scope = singleton
 xa = true
Provided by :
 OPS4J Pax JDBC Pooling DBCP2 (233)

Create the factory configuration:

karaf@root()> config:edit --factory --alias mysql org.ops4j.datasource
karaf@root()> config:property-set osgi.jdbc.driver.name mysql
karaf@root()> config:property-set dataSourceName mysqlds
karaf@root()> config:property-set dataSourceType DataSource
karaf@root()> config:property-set jdbc.url jdbc:mysql://localhost:3306/reportdb
karaf@root()> config:property-set jdbc.user fuse
karaf@root()> config:property-set jdbc.password fuse
karaf@root()> config:property-set jdbc.useSSL false
karaf@root()> config:property-set pool dbcp2
karaf@root()> config:property-set xa false
karaf@root()> config:property-set pool.minIdle 2
karaf@root()> config:property-set pool.maxTotal 10
karaf@root()> config:property-set pool.blockWhenExhausted true
karaf@root()> config:property-set pool.maxWaitMillis 2000
karaf@root()> config:property-set pool.testOnBorrow true
karaf@root()> config:property-set pool.testWhileIdle alse
karaf@root()> config:property-set pool.timeBetweenEvictionRunsMillis 120000
karaf@root()> config:property-set factory.validationQuery 'select schema_name from information_schema.schemata'
karaf@root()> config:property-set factory.validationQueryTimeout 2
karaf@root()> config:update

Check if pax-jdbc-config processed the configuration into the javax.sql.DataSource service:

karaf@root()> service:list javax.sql.DataSource
[javax.sql.DataSource]
----------------------
 dataSourceName = mysqlds
 dataSourceType = DataSource
 factory.validationQuery = select schema_name from information_schema.schemata
 factory.validationQueryTimeout = 2
 felix.fileinstall.filename = file:${karaf.etc}/org.ops4j.datasource-mysql.cfg
 jdbc.password = fuse
 jdbc.url = jdbc:mysql://localhost:3306/reportdb
 jdbc.user = fuse
 jdbc.useSSL = false
 osgi.jdbc.driver.name = mysql
 osgi.jndi.service.name = mysqlds
 pax.jdbc.managed = true
 pool.blockWhenExhausted = true
 pool.maxTotal = 10
 pool.maxWaitMillis = 2000
 pool.minIdle = 2
 pool.testOnBorrow = true
 pool.testWhileIdle = alse
 pool.timeBetweenEvictionRunsMillis = 120000
 service.bundleid = 225
 service.factoryPid = org.ops4j.datasource
 service.id = 338
 service.pid = org.ops4j.datasource.fd7aa3a1-695b-4342-b0d6-23d018a46fbb
 service.scope = singleton
Provided by :
 OPS4J Pax JDBC Config (225)

Use the data source:

karaf@root()> jdbc:query mysqlds 'select * from incident'
date                  │ summary    │ name   │ details                       │ id │ email
──────────────────────┼────────────┼────────┼───────────────────────────────┼────┼─────────────────
2018-02-20 08:00:00.0 │ Incident 1 │ User 1 │ This is a report incident 001 │ 1  │ user1@redhat.com
2018-02-20 08:10:00.0 │ Incident 2 │ User 2 │ This is a report incident 002 │ 2  │ user2@redhat.com
2018-02-20 08:20:00.0 │ Incident 3 │ User 3 │ This is a report incident 003 │ 3  │ user3@redhat.com
2018-02-20 08:30:00.0 │ Incident 4 │ User 4 │ This is a report incident 004 │ 4  │ user4@redhat.com

6.7.3. Using the `narayana` connection pool module

The pax-jdbc-pool-narayna module does almost everything as pax-jdbc-pool-dbcp2. It installs the DBCP2-specific org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory, for both XA and non-XA scenarios. The only difference is that in XA scenarios there is an additional integration point. The org.jboss.tm.XAResourceRecovery OSGi service is registered to be picked up by com.arjuna.ats.arjuna.recovery.RecoveryManager, which is part of the Narayana transaction manager.

6.7.4. Using the `transx` connection pool module

The pax-jdbc-pool-transx bundle bases its implementation of org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory services on the pax-transx-jdbc bundle. The pax-transx-jdbc bundle creates javax.sql.DataSource pools by using the org.ops4j.pax.transx.jdbc.ManagedDataSourceBuilder facility. This is a JCA (Java™ Connector Architecture) solution and it is described in later.

6.8. Deploying data sources as artifacts

This chapter introduced OSGi JDBC services, showed how pax-jdbc bundles help with registration of database-specific and generic data sources, and how it all looks from the perspective of OSGi services and Configuration Admin configurations. While configuration of both categories of data sources may be done by using Configuration Admin factory PIDs (with help from the pax-jdbc-config bundle), it is usually preferred to use the deployment method.

In the deployment method, javax.sql.DataSource services are registered directly by application code, usually inside a Blueprint container. Blueprint XML may be part of an ordinary OSGi bundle, installable by using a mvn: URI and stored in a Maven repository (local or remote). It is much easier to version-control such bundles by comparing them to Configuration Admin configurations.

The pax-jdbc-config bundle version 1.3.0 adds a deployment method for data source configuration. An application developer registers the javax.sql.(XA)DataSource service (usually by using Bluerpint XML) and specifies service properties. The pax-jdbc-config bundle detects such registered database-specific data sources and (using service properties) wraps the service inside a generic, non database-specific, connection pool.

For completeness, following are three deployment methods that use Blueprint XML. Fuse provides a quickstarts download with various examples of different aspects of Fuse. You can download the quickstarts zip file from the Fuse Software Downloads page.

Extract the contents of the quickstarts zip file to a local folder.

In the following examples, the quickstarts/persistence directory is referred to as $PQ_HOME.

6.8.1. Manual deployment of data sources

This example of manual deployment of data sources uses a docker-based PostgreSQL installation. In this method, the pax-jdbc-config is not needed. Application code is responsible for registration of both database-specific and generic data sources.

These three bundles are needed:

mvn:org.postgresql/postgresql/42.2.5
mvn:org.apache.commons/commons-pool2/2.5.0
mvn:org.apache.commons/commons-dbcp2/2.1.1

<!--
    Database-specific, non-pooling, non-enlisting javax.sql.XADataSource
-->
<bean id="postgresql" class="org.postgresql.xa.PGXADataSource">
    <property name="url" value="jdbc:postgresql://localhost:5432/reportdb" />
    <property name="user" value="fuse" />
    <property name="password" value="fuse" />
    <property name="currentSchema" value="report" />
    <property name="connectTimeout" value="5" />
</bean>

<!--
    Fuse/Karaf exports this service from fuse-pax-transx-tm-narayana bundle
-->
<reference id="tm" interface="javax.transaction.TransactionManager" />

<!--
    Non database-specific, generic, pooling, enlisting javax.sql.DataSource
-->
<bean id="pool" class="org.apache.commons.dbcp2.managed.BasicManagedDataSource">
    <property name="xaDataSourceInstance" ref="postgresql" />
    <property name="transactionManager" ref="tm" />
    <property name="minIdle" value="3" />
    <property name="maxTotal" value="10" />
    <property name="validationQuery" value="select schema_name, schema_owner from information_schema.schemata" />
</bean>

<!--
    Expose datasource to use by application code (like Camel, Spring, ...)
-->
<service interface="javax.sql.DataSource" ref="pool">
    <service-properties>
        <entry key="osgi.jndi.service.name" value="jdbc/postgresql" />
    </service-properties>
</service>

The above Blueprint XML fragment matches the canonical DataSource example. Here are the shell commands that show how it should be used:

karaf@root()> install -s mvn:org.postgresql/postgresql/42.2.5
Bundle ID: 233
karaf@root()> install -s mvn:org.apache.commons/commons-pool2/2.5.0
Bundle ID: 224
karaf@root()> install -s mvn:org.apache.commons/commons-dbcp2/2.1.1
Bundle ID: 225
karaf@root()> install -s blueprint:file://$PQ_HOME/databases/blueprints/postgresql-manual.xml
Bundle ID: 226

karaf@root()> bundle:services -p 226

Bundle 226 provides:
--------------------
objectClass = [javax.sql.DataSource]
osgi.jndi.service.name = jdbc/postgresql
osgi.service.blueprint.compname = pool
service.bundleid = 226
service.id = 242
service.scope = bundle
-----
objectClass = [org.osgi.service.blueprint.container.BlueprintContainer]
osgi.blueprint.container.symbolicname = postgresql-manual.xml
osgi.blueprint.container.version = 0.0.0
service.bundleid = 226
service.id = 243
service.scope = singleton

karaf@root()> feature:install jdbc

karaf@root()> jdbc:ds-list
Name            │ Product    │ Version                       │ URL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           │ Status
────────────────┼────────────┼───────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼───────
jdbc/postgresql │ PostgreSQL │ 10.3 (Debian 10.3-1.pgdg90+1) │ jdbc:postgresql://localhost:5432/reportdb?prepareThreshold=5&preparedStatementCacheQueries=256&preparedStatementCacheSizeMiB=5&databaseMetadataCacheFields=65536&databaseMetadataCacheFieldsMiB=5&defaultRowFetchSize=0&binaryTransfer=true&readOnly=false&binaryTransferEnable=&binaryTransferDisable=&unknownLength=2147483647&logUnclosedConnections=false&disableColumnSanitiser=false&tcpKeepAlive=false&loginTimeout=0&connectTimeout=5&socketTimeout=0&cancelSignalTimeout=10&receiveBufferSize=-1&sendBufferSize=-1&ApplicationName=PostgreSQL JDBC Driver&jaasLogin=true&useSpnego=false&gsslib=auto&sspiServiceClass=POSTGRES&allowEncodingChanges=false&currentSchema=report&targetServerType=any&loadBalanceHosts=false&hostRecheckSeconds=10&preferQueryMode=extended&autosave=never&reWriteBatchedInserts=false │ OK

karaf@root()> jdbc:query jdbc/postgresql 'select * from incident';
date                │ summary    │ name   │ details                       │ id │ email
────────────────────┼────────────┼────────┼───────────────────────────────┼────┼─────────────────
2018-02-20 08:00:00 │ Incident 1 │ User 1 │ This is a report incident 001 │ 1  │ user1@redhat.com
2018-02-20 08:10:00 │ Incident 2 │ User 2 │ This is a report incident 002 │ 2  │ user2@redhat.com
2018-02-20 08:20:00 │ Incident 3 │ User 3 │ This is a report incident 003 │ 3  │ user3@redhat.com
2018-02-20 08:30:00 │ Incident 4 │ User 4 │ This is a report incident 004 │ 4  │ user4@redhat.com

As shown in the above listing, the Blueprint bundle exports the javax.sql.DataSource service, which is a generic, non database-specific, connection pool. The database-specific javax.sql.XADataSource bundle is not registered as an OSGi service, because Blueprint XML does not have an explicit <service ref="postgresql"> declaration.

6.8.2. Factory deployment of data sources

Factory deployment of data sources uses the pax-jdbc-config bundle in a canonical way. This is a bit different from the method that was recommended in Fuse 6.x, which required specification of the pooling configuration as service properties.

Here is the Blueprint XML example:

<!--
    A database-specific org.osgi.service.jdbc.DataSourceFactory that can create DataSource/XADataSource/
    /ConnectionPoolDataSource/Driver using properties. It is registered by pax-jdbc-* or for example
    mvn:org.postgresql/postgresql/42.2.5 bundle natively.
-->
<reference id="dataSourceFactory"
        interface="org.osgi.service.jdbc.DataSourceFactory"
        filter="(osgi.jdbc.driver.class=org.postgresql.Driver)" />

<!--
    Non database-specific org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory that can create
    pooled data sources using some org.osgi.service.jdbc.DataSourceFactory. dbcp2 pool is registered
    by pax-jdbc-pool-dbcp2 bundle.
-->
<reference id="pooledDataSourceFactory"
        interface="org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory"
        filter="(&amp;(pool=dbcp2)(xa=true))" />

<!--
    Finally, use both factories to expose pooled, xa-aware data source.
-->
<bean id="pool" factory-ref="pooledDataSourceFactory" factory-method="create">
    <argument ref="dataSourceFactory" />
    <argument>
        <props>
            <!--
                Properties needed by postgresql-specific org.osgi.service.jdbc.DataSourceFactory.
                Cannot prepend them with 'jdbc.' prefix as the DataSourceFactory is implemented directly
                by PostgreSQL driver, not by pax-jdbc-* bundle.
            -->
            <prop key="url" value="jdbc:postgresql://localhost:5432/reportdb" />
            <prop key="user" value="fuse" />
            <prop key="password" value="fuse" />
            <prop key="currentSchema" value="report" />
            <prop key="connectTimeout" value="5" />
            <!-- Properties needed by dbcp2-specific org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory -->
            <prop key="pool.minIdle" value="2" />
            <prop key="pool.maxTotal" value="10" />
            <prop key="pool.blockWhenExhausted" value="true" />
            <prop key="pool.maxWaitMillis" value="2000" />
            <prop key="pool.testOnBorrow" value="true" />
            <prop key="pool.testWhileIdle" value="false" />
            <prop key="factory.validationQuery" value="select schema_name from information_schema.schemata" />
            <prop key="factory.validationQueryTimeout" value="2" />
        </props>
    </argument>
</bean>

<!--
    Expose data source for use by application code (such as  Camel, Spring, ...).
-->
<service interface="javax.sql.DataSource" ref="pool">
    <service-properties>
        <entry key="osgi.jndi.service.name" value="jdbc/postgresql" />
    </service-properties>
</service>

This example uses factory beans that create data sources by using data source factories. You do not need to explicitly reference the javax.transaction.TransactionManager service, as this is tracked internally by the XA-aware PooledDataSourceFactory.

The following is the same example but in a Fuse/Karaf shell.

Note

To have the native org.osgi.service.jdbc.DataSourcFactory bundle registered, install mvn:org.osgi/org.osgi.service.jdbc/1.0.0 and then install a PostgreSQL driver.

karaf@root()> feature:install jdbc pax-jdbc-config pax-jdbc-pool-dbcp2
karaf@root()> install -s mvn:org.postgresql/postgresql/42.2.5
Bundle ID: 232
karaf@root()> install -s blueprint:file://$PQ_HOME/databases/blueprints/postgresql-pax-jdbc-factory-dbcp2.xml
Bundle ID: 233
karaf@root()> bundle:services -p 233

Bundle 233 provides:
--------------------
objectClass = [javax.sql.DataSource]
osgi.jndi.service.name = jdbc/postgresql
osgi.service.blueprint.compname = pool
service.bundleid = 233
service.id = 336
service.scope = bundle
-----
objectClass = [org.osgi.service.blueprint.container.BlueprintContainer]
osgi.blueprint.container.symbolicname = postgresql-pax-jdbc-factory-dbcp2.xml
osgi.blueprint.container.version = 0.0.0
service.bundleid = 233
service.id = 337
service.scope = singleton

karaf@root()> jdbc:ds-list
Name            │ Product    │ Version                       │ URL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           │ Status
────────────────┼────────────┼───────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼───────
jdbc/postgresql │ PostgreSQL │ 10.3 (Debian 10.3-1.pgdg90+1) │ jdbc:postgresql://localhost:5432/reportdb?prepareThreshold=5&preparedStatementCacheQueries=256&preparedStatementCacheSizeMiB=5&databaseMetadataCacheFields=65536&databaseMetadataCacheFieldsMiB=5&defaultRowFetchSize=0&binaryTransfer=true&readOnly=false&binaryTransferEnable=&binaryTransferDisable=&unknownLength=2147483647&logUnclosedConnections=false&disableColumnSanitiser=false&tcpKeepAlive=false&loginTimeout=0&connectTimeout=5&socketTimeout=0&cancelSignalTimeout=10&receiveBufferSize=-1&sendBufferSize=-1&ApplicationName=PostgreSQL JDBC Driver&jaasLogin=true&useSpnego=false&gsslib=auto&sspiServiceClass=POSTGRES&allowEncodingChanges=false&currentSchema=report&targetServerType=any&loadBalanceHosts=false&hostRecheckSeconds=10&preferQueryMode=extended&autosave=never&reWriteBatchedInserts=false │ OK

karaf@root()> jdbc:query jdbc/postgresql 'select * from incident';
date                │ summary    │ name   │ details                       │ id │ email
────────────────────┼────────────┼────────┼───────────────────────────────┼────┼─────────────────
2018-02-20 08:00:00 │ Incident 1 │ User 1 │ This is a report incident 001 │ 1  │ user1@redhat.com
2018-02-20 08:10:00 │ Incident 2 │ User 2 │ This is a report incident 002 │ 2  │ user2@redhat.com
2018-02-20 08:20:00 │ Incident 3 │ User 3 │ This is a report incident 003 │ 3  │ user3@redhat.com
2018-02-20 08:30:00 │ Incident 4 │ User 4 │ This is a report incident 004 │ 4  │ user4@redhat.com

As shown in the above listing, the Blueprint bundle exports the javax.sql.DataSource service, which is a generic, non database-specific, connection pool. The database-specific javax.sql.XADataSource is not registered as an OSGi service, because Blueprint XML does not have an explicit <service ref="postgresql"> declaration.

6.8.3. Mixed deployment of data sources

In a mixed deployment of data sources, the pax-jdbc-config 1.3.0 bundle adds another way of wrapping database-specific data sources within pooling data sources by using service properties. This method matches the way it worked in Fuse 6.x.

The following is the Blueprint XML example:

<!--
    Database-specific, non-pooling, non-enlisting javax.sql.XADataSource
-->
<bean id="postgresql" class="org.postgresql.xa.PGXADataSource">
    <property name="url" value="jdbc:postgresql://localhost:5432/reportdb" />
    <property name="user" value="fuse" />
    <property name="password" value="fuse" />
    <property name="currentSchema" value="report" />
    <property name="connectTimeout" value="5" />
</bean>

<!--
    Expose database-specific data source with service properties.
    No need to expose pooling, enlisting, non database-specific javax.sql.DataSource. It is registered
    automatically by pax-jdbc-config with the same properties as this <service>, but with higher service.ranking.
-->
<service id="pool" ref="postgresql" interface="javax.sql.XADataSource">
    <service-properties>
        <!-- "pool" key is needed for pax-jdbc-config to wrap database-specific data source inside connection pool -->
        <entry key="pool" value="dbcp2" />
        <entry key="osgi.jndi.service.name" value="jdbc/postgresql" />
        <!-- Other properties that configure given connection pool, as indicated by pool=dbcp2 -->
        <entry key="pool.minIdle" value="2" />
        <entry key="pool.maxTotal" value="10" />
        <entry key="pool.blockWhenExhausted" value="true" />
        <entry key="pool.maxWaitMillis" value="2000" />
        <entry key="pool.testOnBorrow" value="true" />
        <entry key="pool.testWhileIdle" value="false" />
        <entry key="factory.validationQuery" value="select schema_name from information_schema.schemata" />
        <entry key="factory.validationQueryTimeout" value="2" />
    </service-properties>
</service>

In the above example, only a database-specific data source is manually registered. The pool=dbcp2 service property is a hint for the data source tracker that is managed by the pax-jdbc-config bundle. Data source services with this service property are wrapped within a pooling data source, in this example, pax-jdbc-pool-dbcp2.

The following is the same example in a Fuse/Karaf shell:

karaf@root()> feature:install jdbc pax-jdbc-config pax-jdbc-pool-dbcp2
karaf@root()> install -s mvn:org.postgresql/postgresql/42.2.5
Bundle ID: 232
karaf@root()> install -s blueprint:file://$PQ_HOME/databases/blueprints/postgresql-pax-jdbc-discovery.xml
Bundle ID: 233
karaf@root()> bundle:services -p 233

Bundle 233 provides:
--------------------
factory.validationQuery = select schema_name from information_schema.schemata
factory.validationQueryTimeout = 2
objectClass = [javax.sql.XADataSource]
osgi.jndi.service.name = jdbc/postgresql
osgi.service.blueprint.compname = postgresql
pool = dbcp2
pool.blockWhenExhausted = true
pool.maxTotal = 10
pool.maxWaitMillis = 2000
pool.minIdle = 2
pool.testOnBorrow = true
pool.testWhileIdle = false
service.bundleid = 233
service.id = 336
service.scope = bundle
-----
objectClass = [org.osgi.service.blueprint.container.BlueprintContainer]
osgi.blueprint.container.symbolicname = postgresql-pax-jdbc-discovery.xml
osgi.blueprint.container.version = 0.0.0
service.bundleid = 233
service.id = 338
service.scope = singleton

karaf@root()> service:list javax.sql.XADataSource
[javax.sql.XADataSource]
------------------------
 factory.validationQuery = select schema_name from information_schema.schemata
 factory.validationQueryTimeout = 2
 osgi.jndi.service.name = jdbc/postgresql
 osgi.service.blueprint.compname = postgresql
 pool = dbcp2
 pool.blockWhenExhausted = true
 pool.maxTotal = 10
 pool.maxWaitMillis = 2000
 pool.minIdle = 2
 pool.testOnBorrow = true
 pool.testWhileIdle = false
 service.bundleid = 233
 service.id = 336
 service.scope = bundle
Provided by :
 Bundle 233
Used by:
 OPS4J Pax JDBC Config (224)

karaf@root()> service:list javax.sql.DataSource
[javax.sql.DataSource]
----------------------
 factory.validationQuery = select schema_name from information_schema.schemata
 factory.validationQueryTimeout = 2
 osgi.jndi.service.name = jdbc/postgresql
 osgi.service.blueprint.compname = postgresql
 pax.jdbc.managed = true
 pax.jdbc.service.id.ref = 336
 pool.blockWhenExhausted = true
 pool.maxTotal = 10
 pool.maxWaitMillis = 2000
 pool.minIdle = 2
 pool.testOnBorrow = true
 pool.testWhileIdle = false
 service.bundleid = 224
 service.id = 337
 service.ranking = 1000
 service.scope = singleton
Provided by :
 OPS4J Pax JDBC Config (224)

karaf@root()> jdbc:ds-list
Name            │ Product    │ Version                       │ URL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           │ Status
────────────────┼────────────┼───────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼───────
jdbc/postgresql │ PostgreSQL │ 10.3 (Debian 10.3-1.pgdg90+1) │ jdbc:postgresql://localhost:5432/reportdb?prepareThreshold=5&preparedStatementCacheQueries=256&preparedStatementCacheSizeMiB=5&databaseMetadataCacheFields=65536&databaseMetadataCacheFieldsMiB=5&defaultRowFetchSize=0&binaryTransfer=true&readOnly=false&binaryTransferEnable=&binaryTransferDisable=&unknownLength=2147483647&logUnclosedConnections=false&disableColumnSanitiser=false&tcpKeepAlive=false&loginTimeout=0&connectTimeout=5&socketTimeout=0&cancelSignalTimeout=10&receiveBufferSize=-1&sendBufferSize=-1&ApplicationName=PostgreSQL JDBC Driver&jaasLogin=true&useSpnego=false&gsslib=auto&sspiServiceClass=POSTGRES&allowEncodingChanges=false&currentSchema=report&targetServerType=any&loadBalanceHosts=false&hostRecheckSeconds=10&preferQueryMode=extended&autosave=never&reWriteBatchedInserts=false │ OK
jdbc/postgresql │ PostgreSQL │ 10.3 (Debian 10.3-1.pgdg90+1) │ jdbc:postgresql://localhost:5432/reportdb?prepareThreshold=5&preparedStatementCacheQueries=256&preparedStatementCacheSizeMiB=5&databaseMetadataCacheFields=65536&databaseMetadataCacheFieldsMiB=5&defaultRowFetchSize=0&binaryTransfer=true&readOnly=false&binaryTransferEnable=&binaryTransferDisable=&unknownLength=2147483647&logUnclosedConnections=false&disableColumnSanitiser=false&tcpKeepAlive=false&loginTimeout=0&connectTimeout=5&socketTimeout=0&cancelSignalTimeout=10&receiveBufferSize=-1&sendBufferSize=-1&ApplicationName=PostgreSQL JDBC Driver&jaasLogin=true&useSpnego=false&gsslib=auto&sspiServiceClass=POSTGRES&allowEncodingChanges=false&currentSchema=report&targetServerType=any&loadBalanceHosts=false&hostRecheckSeconds=10&preferQueryMode=extended&autosave=never&reWriteBatchedInserts=false │ OK

karaf@root()> jdbc:query jdbc/postgresql 'select * from incident'
date                │ summary    │ name   │ details                       │ id │ email
────────────────────┼────────────┼────────┼───────────────────────────────┼────┼─────────────────
2018-02-20 08:00:00 │ Incident 1 │ User 1 │ This is a report incident 001 │ 1  │ user1@redhat.com
2018-02-20 08:10:00 │ Incident 2 │ User 2 │ This is a report incident 002 │ 2  │ user2@redhat.com
2018-02-20 08:20:00 │ Incident 3 │ User 3 │ This is a report incident 003 │ 3  │ user3@redhat.com
2018-02-20 08:30:00 │ Incident 4 │ User 4 │ This is a report incident 004 │ 4  │ user4@redhat.com

In this listing, as you can see in the jdbc:ds-list output, there are two data sources, the original data source and the wrapper data source.

javax.sql.XADataSource is registered from the Blueprint bundle and has the pool = dbcp2 property declared.

javax.sql.DataSource is registered from the pax-jdbc-config bundle and:

Does not have the pool = dbcp2 property (it was removed when registering the wrapper data source).
Has the service.ranking = 1000 property, so it is always the preferred version when, for example, looking for data source by name.
Has the pax.jdbc.managed = true property, so it is not tried to be wrapped again.
Has the pax.jdbc.service.id.ref = 336 property, to indicate the original data source service that is wrapped inside the connection pool.

6.9. Using data sources with the Java™ persistence API

From the perspective of transaction management, it is important to understand how data sources are used with the Java™ Persistence API (JPA). This section does not describe the details of the JPA specification itself, nor the details about Hibernate, which is the most known JPA implementation. Instead, this section shows how to point JPA persistent units to data sources.

6.9.1. About data source references

The META-INF/persistence.xml descriptor (see the JPA 2.1 specification, 8.2.1.5 jta-data-source, non-jta-data-source) defines two kinds of data source references:

<jta-data-source> - This is a JNDI reference to JTA-enabled data source to use with JTA transactions.
<non-jta-data-source> - This is a JNDI reference to JTA-enabled data source to use outside of JTA transactions. This data source is usually also used in the initialization phase, for example, with the hibernate.hbm2ddl.auto property that configures Hibernate to auto-create database schema.

These two data sources are not related to javax.sql.DataSource or javax.sql.XADataSource! This is common misconception when developing JPA applications. Both JNDI names must refer to JNDI-bound javax.sql.DataSource services.

6.9.2. Referring to JNDI names

When you register an OSGi service with the osgi.jndi.service.name property, it is bound in the OSGi JNDI service. In an OSGi runtime (such as Fuse/Karaf), JNDI is not a simple dictionary of name value pairs. Referring to objects by means of JNDI names in OSGi involves service lookups and other, more complex OSGi mechanisms, such as service hooks.

In a fresh Fuse installation, the following listing shows how data sources are registered in JNDI:

karaf@root()> install -s mvn:mysql/mysql-connector-java/5.1.34
Bundle ID: 223
karaf@root()> install -s mvn:org.osgi/org.osgi.service.jdbc/1.0.0
Bundle ID: 224
karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-mysql/1.3.0
Bundle ID: 225
karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc/1.3.0
Bundle ID: 226
karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-pool-common/1.3.0
Bundle ID: 227
karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-config/1.3.0
Bundle ID: 228

karaf@root()> config:edit --factory --alias mysql org.ops4j.datasource
karaf@root()> config:property-set osgi.jdbc.driver.name mysql
karaf@root()> config:property-set dataSourceName mysqlds
karaf@root()> config:property-set osgi.jndi.service.name jdbc/mysqlds
karaf@root()> config:property-set dataSourceType DataSource
karaf@root()> config:property-set jdbc.url jdbc:mysql://localhost:3306/reportdb
karaf@root()> config:property-set jdbc.user fuse
karaf@root()> config:property-set jdbc.password fuse
karaf@root()> config:property-set jdbc.useSSL false
karaf@root()> config:update

karaf@root()> feature:install jndi

karaf@root()> jndi:names
JNDI Name                 │ Class Name
──────────────────────────┼───────────────────────────────────────────────
osgi:service/jndi         │ org.apache.karaf.jndi.internal.JndiServiceImpl
osgi:service/jdbc/mysqlds │ com.mysql.jdbc.jdbc2.optional.MysqlDataSource

As you can see, the data source is available under the osgi:service/jdbc/mysqlds JNDI name.

But in case of JPA in OSGi, you must use full JNDI names. The following is the sample META-INF/persistence.xml fragment that specifies data source references:

<jta-data-source>
    osgi:service/javax.sql.DataSource/(osgi.jndi.service.name=jdbc/mysqlds)
</jta-data-source>
<non-jta-data-source>
    osgi:service/javax.sql.DataSource/(osgi.jndi.service.name=jdbc/mysqlds)
</non-jta-data-source>

Without the above configuration, you might get this error:

Persistence unit "pu-name" refers to a non OSGi service DataSource

Chapter 6. Using JDBC data sources

6.1. About the Connection interface

6.2. Overview of JDBC data sources

6.2.1. Database specific and generic data sources

6.2.2. Some generic data sources

6.2.3. Pattern to use

6.3. Configuring JDBC data sources

6.4. Using the OSGi JDBC service

6.4.1. PAX-JDBC configuration service

6.4.2. Summary of handled properties

6.4.3. How the pax-jdb-config bundle handles properties

6.5. Using JDBC console commands

6.6. Using encrypted configuration values

6.7. Using JDBC connection pools

6.7.1. Introduction to using JDBC connection pools

6.7.2. Using the `dbcp2` connection pool module

6.7.2.1. Configuration properties for BasicDataSource

6.7.2.2. Example of how to configure DBCP2 pool

6.7.3. Using the `narayana` connection pool module

6.7.4. Using the `transx` connection pool module

6.8. Deploying data sources as artifacts

6.8.1. Manual deployment of data sources

6.8.2. Factory deployment of data sources

6.8.3. Mixed deployment of data sources

6.9. Using data sources with the Java™ persistence API

6.9.1. About data source references

6.9.2. Referring to JNDI names

Learn

Try, buy, & sell

Communities

About Red Hat Documentation

Making open source more inclusive

About Red Hat

Red Hat legal and privacy links

Red Hat legal and privacy links

Chapter 6. Using JDBC data sources

6.1. About the Connection interface

6.2. Overview of JDBC data sources

6.2.1. Database specific and generic data sources

6.2.2. Some generic data sources

6.2.3. Pattern to use

6.3. Configuring JDBC data sources

6.4. Using the OSGi JDBC service

6.4.1. PAX-JDBC configuration service

6.4.2. Summary of handled properties

6.4.3. How the pax-jdb-config bundle handles properties

6.5. Using JDBC console commands

6.6. Using encrypted configuration values

6.7. Using JDBC connection pools

6.7.1. Introduction to using JDBC connection pools

6.7.2. Using the dbcp2 connection pool module

6.7.2.1. Configuration properties for BasicDataSource

6.7.2.2. Example of how to configure DBCP2 pool

6.7.3. Using the narayana connection pool module

6.7.4. Using the transx connection pool module

6.8. Deploying data sources as artifacts

6.8.1. Manual deployment of data sources

6.8.2. Factory deployment of data sources

6.8.3. Mixed deployment of data sources

6.9. Using data sources with the Java™ persistence API

6.9.1. About data source references

6.9.2. Referring to JNDI names

Learn

Try, buy, & sell

Communities

About Red Hat Documentation

Making open source more inclusive

About Red Hat

Red Hat legal and privacy links

Red Hat legal and privacy links

6.7.2. Using the `dbcp2` connection pool module

6.7.3. Using the `narayana` connection pool module

6.7.4. Using the `transx` connection pool module