Chapter 6. Using JDBC data sources
The following topics discuss the use of JDBC data sources in the Fuse OSGi runtime:
- Section 6.1, “About the Connection interface”
- Section 6.2, “Overview of JDBC data sources”
- Section 6.3, “Configuring JDBC data sources”
- Section 6.4, “Using the OSGi JDBC service”
- Section 6.5, “Using JDBC console commands”
- Section 6.6, “Using encrypted configuration values”
- Section 6.7, “Using JDBC connection pools”
- Section 6.8, “Deploying data sources as artifacts”
- Section 6.9, “Using data sources with the Java™ persistence API”
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 variousconnect*()
methods ofcom.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 thegetConnection()
method is used. This indirection enables management of connection pools at the level of an application server. The connection obtained by usinggetConnection()
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 ajavax.transaction.xa.XAResource
object that is associated with XA-aware connection for use withjavax.transaction.TransactionManager
. Becausejavax.sql.XAConnection
extendsjavax.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 |
---|---|
|
|
|
|
|
|
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 obtainingjava.sql.Connection
instances. The fact that mostjavax.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 genericjavax.sql.DataSource
object that was obtained from JNDI and implemented by an application server (probably using a library such ascommons-dbcp2
). On the other end, application code does not interface withjavax.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 obtainjavax.sql.XAConnection
andjavax.transaction.xa.XAResource
. Same asjavax.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.
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
, andjavax.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
orminIdle
used to configure an internal instance oforg.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 usingjava.sql.Driver.connect()
internally.A non-managed pooling data source creates
java.sql.Connection
instances using the passedorg.apache.commons.pool2.ObjectPool
object.A managed pooling data source wraps
java.sql.Connection
instances insideorg.apache.commons.dbcp2.managed.ManagedConnection
objects that ensure thatjavax.transaction.Transaction.enlistResource()
is called if needed in the JTA context. But still the actual connection that is wrapped is obtained from anyorg.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-specificjavax.sql.XADataSource
objects. Bean properties are used to create an internal instance oforg.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
).
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
orjavax.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 ofjava.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
andpassword
to thecreateDataSource()
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.
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.
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 thepax-jdbc-config
bundle that tracksorg.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 thejavax.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.12.0.fuse-7_12_0-00019-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:
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) ororg.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:
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.
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 |
---|---|---|
|
|
|
|
|
|
|
|
|
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 oforg.apache.commons.pool2.impl.GenericObjectPoolConfig
(both xa and non-xa scenario) -
factory.*
: bean properties oforg.apache.commons.dbcp2.managed.PoolableManagedConnectionFactory
(xa) ororg.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 |
---|---|---|
| The connection user name to be passed to our JDBC driver to establish a connection. | |
| The connection password to be passed to our JDBC driver to establish a connection. | |
| The connection URL to be passed to our JDBC driver to establish a connection. | |
| The fully qualified Java class name of the JDBC driver to be used. | |
| 0 | The initial number of connections that are created when the pool is started. |
| 8 | The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit. |
| 8 | The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit. |
| 0 | The minimum number of connections that can remain idle in the pool, without extra ones being created, or zero to create none. |
| 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. |
| 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. | |
| 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. |
| 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. |
| 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. |
| false | The indication of whether objects will be validated before being returned to the pool. |
| 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. |
| -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. |
| 3 | The number of objects to examine during each run of the idle object evictor thread (if any). |
| 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
andpax-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 thejavax.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¤tSchema=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="(&(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.
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¤tSchema=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¤tSchema=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¤tSchema=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 withJTA
transactions. -
<non-jta-data-source>
- This is a JNDI reference to JTA-enabled data source to use outside ofJTA
transactions. This data source is usually also used in the initialization phase, for example, with thehibernate.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
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