Copia collegamentoCollegamento copiato negli appunti!
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.
Copia collegamentoCollegamento copiato negli appunti!
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:
Expand
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.
Database specific implementations of javax.sql.DataSource, javax.sql.XADataSource, and javax.sql.ConnectionPoolDataSource
It might be confusing that a genericjavax.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.
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.DriverAdapterCPDSadapter, while DBCP2 implements the application server contract with one of:
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.ConnectionPoolDataSourcejavax.sql.PooledConnection SPI.
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).
Copia collegamentoCollegamento copiato negli appunti!
The recommended pattern is:
Create or obtain a database-specificjavax.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-specificjavax.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 ...")) {
// ....
// 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 ...")) {
// ....
Copy to ClipboardCopied!Toggle word wrapToggle overflow
In a Fuse environment, there are many configuration options and there is no requirement to use DBCP2.
Copia collegamentoCollegamento copiato negli appunti!
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:
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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_.
Copia collegamentoCollegamento copiato negli appunti!
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:
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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.
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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 tothe 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.
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:
Copia collegamentoCollegamento copiato negli appunti!
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);
public DataSource createDataSource(Properties props);
public XADataSource createXADataSource(Properties props);
public ConnectionPoolDataSource createConnectionPoolDataSource(Properties props);
Copy to ClipboardCopied!Toggle word wrapToggle overflow
Tracks org.ops4j.datasourcefactory 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:
Copy to ClipboardCopied!Toggle word wrapToggle overflow
Install the pax-jdbc bundle and the pax-jdbc-config bundle that tracks org.osgi.service.jdbc.DataSourceFactory services andorg.ops4j.datasourcefactory PIDs:
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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
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
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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
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
Copy to ClipboardCopied!Toggle word wrapToggle overflow
Copia collegamentoCollegamento copiato negli appunti!
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:
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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.
Copia collegamentoCollegamento copiato negli appunti!
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
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
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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
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
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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
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
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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.
Copia collegamentoCollegamento copiato negli appunti!
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:
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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.
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 does not provide XA recovery options. It 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.
Copia collegamentoCollegamento copiato negli appunti!
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:
The PostgreSQL driver itself implements the OSGi JDBC service and produces:
javax.sql.DataSourceorg.postgresql.jdbc2.optional.PoolingDataSource (if there are pool-related properties specified) or org.postgresql.jdbc2.optional.SimpleDataSource
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 proprietaryorg.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory interface:
public interface PooledDataSourceFactory {
javax.sql.DataSource create(org.osgi.service.jdbc.DataSourceFactory dsf, Properties config)
}
public interface PooledDataSourceFactory {
javax.sql.DataSource create(org.osgi.service.jdbc.DataSourceFactory dsf, Properties config)
}
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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.
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.
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):
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.datasourcefactory 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)
Following, is a realistic example (except useSSL=false) of a configuration of a DBCP2 pool (org.ops4j.datasource-mysqlfactory PID) that uses convenient syntax with jdbc.-prefixed properties:
Configuration for pax-jdbc-config to choose and configure specific org.osgi.service.jdbc.DataSourceFactory
Hints for pax-jdbc-config to use org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory
dbcp2 specific configuration of org.apache.commons.pool2.impl.GenericObjectPoolConfig
dbcp2 specific configuration of org.apache.commons.dbcp2.PoolableConnectionFactory
# 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.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
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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:
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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.
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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
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
Copy to ClipboardCopied!Toggle word wrapToggle overflow
Copia collegamentoCollegamento copiato negli appunti!
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.
Copia collegamentoCollegamento copiato negli appunti!
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.
Copia collegamentoCollegamento copiato negli appunti!
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 includes a quickstarts directory with various examples of different aspects of Fuse. In the following examples, the $FUSE_HOME/quickstarts/persistence directory is referred to as $PQ_HOME.
Copia collegamentoCollegamento copiato negli appunti!
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.
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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
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
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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.
Copia collegamentoCollegamento copiato negli appunti!
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>
<!--
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>
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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.
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¤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
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
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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.
Copia collegamentoCollegamento copiato negli appunti!
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.
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>
<!--
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>
Copy to ClipboardCopied!Toggle word wrapToggle overflow
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.
Following is the same example in a Fuse/Karaf shell:
Copia collegamentoCollegamento copiato negli appunti!
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.
Copia collegamentoCollegamento copiato negli appunti!
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.
Copia collegamentoCollegamento copiato negli appunti!
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:
Copy to ClipboardCopied!Toggle word wrapToggle overflow
As you can see, the data source is available under the osgi:service/jdbc/mysqlds JNDI name.
But in case of JPA in OSGi and in particular in an aries-jpa implementation, which provides an OSGi bridge from Karaf to JPA providers, you must use full JNDI names. Following is the sample META-INF/persistence.xml fragment that specifies data source references:
Aiutiamo gli utenti Red Hat a innovarsi e raggiungere i propri obiettivi con i nostri prodotti e servizi grazie a contenuti di cui possono fidarsi. Esplora i nostri ultimi aggiornamenti.
Rendiamo l’open source più inclusivo
Red Hat si impegna a sostituire il linguaggio problematico nel codice, nella documentazione e nelle proprietà web. Per maggiori dettagli, visita il Blog di Red Hat.
Informazioni su Red Hat
Forniamo soluzioni consolidate che rendono più semplice per le aziende lavorare su piattaforme e ambienti diversi, dal datacenter centrale all'edge della rete.
Red Hat Summit
'%20d='M201.5%20305.5c-13.8%200-24.9-11.1-24.9-24.6%200-13.8%2011.1-24.9%2024.9-24.9%2013.6%200%2024.6%2011.1%2024.6%2024.9%200%2013.6-11.1%2024.6-24.6%2024.6zM504%20256c0%20137-111%20248-248%20248S8%20393%208%20256%20119%208%20256%208s248%20111%20248%20248zm-132.3-41.2c-9.4%200-17.7%203.9-23.8%2010-22.4-15.5-52.6-25.5-86.1-26.6l17.4-78.3%2055.4%2012.5c0%2013.6%2011.1%2024.6%2024.6%2024.6%2013.8%200%2024.9-11.3%2024.9-24.9s-11.1-24.9-24.9-24.9c-9.7%200-18%205.8-22.1%2013.8l-61.2-13.6c-3-.8-6.1%201.4-6.9%204.4l-19.1%2086.4c-33.2%201.4-63.1%2011.3-85.5%2026.8-6.1-6.4-14.7-10.2-24.1-10.2-34.9%200-46.3%2046.9-14.4%2062.8-1.1%205-1.7%2010.2-1.7%2015.5%200%2052.6%2059.2%2095.2%20132%2095.2%2073.1%200%20132.3-42.6%20132.3-95.2%200-5.3-.6-10.8-1.9-15.8%2031.3-16%2019.8-62.5-14.9-62.5zM302.8%20331c-18.2%2018.2-76.1%2017.9-93.6%200-2.2-2.2-6.1-2.2-8.3%200-2.5%202.5-2.5%206.4%200%208.6%2022.8%2022.8%2087.3%2022.8%20110.2%200%202.5-2.2%202.5-6.1%200-8.6-2.2-2.2-6.1-2.2-8.3%200zm7.7-75c-13.6%200-24.6%2011.1-24.6%2024.9%200%2013.6%2011.1%2024.6%2024.6%2024.6%2013.8%200%2024.9-11.1%2024.9-24.6%200-13.8-11-24.9-24.9-24.9z'/%3e%3c/svg%3e){kind=small}