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 JavaTM 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 various connect*() methods of com.mysql.jdbc.Driver ) These implementations contain database-specific logic to perform DML, DDL, and simple transaction management. In theory, it is possible to manually create these connection objects, but there are two JDBC methods that hide the details to provide a cleaner API: java.sql.Driver.connect() - This method was used in standalone applications a long time ago. javax.sql.DataSource.getConnection() - This is the preferred method for using the factory pattern. A similar method is used to obtain JMS connections from a JMS connection factory. The driver manager approach is not discussed here. It is enough to state that this method is just a tiny layer above a plain constructor for a given connection object. In addition to java.sql.Connection , which effectively implements database-specific communication protocols, there are two other specialized connection interfaces: javax.sql.PooledConnection represents a physical connection. Your code does not interact with this pooled connection directly. Instead, the connection obtained from the getConnection() method is used. This indirection enables management of connection pools at the level of an application server. The connection obtained by using getConnection() is usually a proxy. When such a proxy connection is closed, the physical connection is not closed and instead it becomes available again in the managed connection pool. javax.sql.XAConnection allows obtaining a javax.transaction.xa.XAResource object that is associated with XA-aware connection for use with javax.transaction.TransactionManager . Because javax.sql.XAConnection extends javax.sql.PooledConnection, it also provides the `getConnection() method, which provides access to a JDBC connection object with typical DML/DQL methods. 6.2. Overview of JDBC data sources The JDBC 1.4 standard introduced the javax.sql.DataSource interface, which acted as a factory for java.sql.Connection objects. Usually such data sources were bound to a JNDI registry and were located inside or injected into Java EE components such as servlets or EJBs. The key aspect is that these data sources were configured inside the application server and referenced in deployed applications by name. The following connection objects have their own data sources : Data Source Connection javax.sql.DataSource java.sql.Connection javax.sql.ConnectionPoolDataSource javax.sql.PooledConnection javax.sql.XADataSource javax.sql.XAConnection The most important differences between each of the above data sources is as follows: javax.sql.DataSource is most importantly a factory -like object for obtaining java.sql.Connection instances. The fact that most javax.sql.DataSource implementations usually perform connection pooling should not change the picture. This is the only interface that should be used by application code. It does not matter which of the following you are implementing: Direct JDBC access JPA persistence unit configuration ( either <jta-data-source> or <non-jta-data-source> ) Popular library such as Apache Camel or Spring Framework javax.sql.ConnectionPoolDataSource is most importantly a bridge between a generic (non database-specific) connection pool/data source and a database-specific data source. It may be treated as an SPI interface. Application code usually deals with a generic javax.sql.DataSource object that was obtained from JNDI and implemented by an application server (probably using a library such as commons-dbcp2 ). On the other end, application code does not interface with javax.sql.ConnectionPoolDataSource directly. It is used between an application server and a database-specific driver. The following sequence diagram shows this: javax.sql.XADataSource is a way to obtain javax.sql.XAConnection and javax.transaction.xa.XAResource . Same as javax.sql.ConnectionPoolDataSource , it's used between application server and database-specific driver. Here's slightly modified diagram with different actors, this time including JTA Transaction Manager: As shown in two diagrams, you interact with the App Server , which is a generalized entity in which you can configure javax.sql.DataSource and javax.transaction.UserTransaction instances. Such instances may be accessed either by means of JNDI or by injection using CDI or another dependency mechanism. Important The important point is that even if the application uses XA transactions and/or connection pooling, the application interacts with javax.sql.DataSource and not the two other JDBC data source interfaces. 6.2.1. Database specific and generic data sources The JDBC data source implementations fall into two categories: Generic javax.sql.DataSource implementations such as : Apache Commons DBCP(2) Apache Tomcat JDBC (based on DBCP) Database specific implementations of javax.sql.DataSource , javax.sql.XADataSource , and javax.sql.ConnectionPoolDataSource It might be confusing that a generic javax.sql.DataSource implementation cannot create database-specific connections on its own. Even if a generic data source could use java.sql.Driver.connect() or java.sql.DriverManager.getConnection() , it is usually better/cleaner to configure this generic data source with a database-specific javax.sql.DataSource implementation. When a generic data source is going to interact with JTA, it must be configured with a database-specific implementation of javax.sql.XADataSource . To close the picture, a generic data source usually does not need a database-specific implementation of javax.sql.ConnectionPoolDataSource to perform connection pooling. Existing pools usually handle pooling without standard JDBC interfaces ( javax.sql.ConnectionPoolDataSource and javax.sql.PooledConnection ) and instead use their own custom implementation. 6.2.2. Some generic data sources Consider a sample, well-known, generic data source, Apache Commons DBCP(2) . javax.sql.XADataSource implementations DBCP2 does not include any implementation of javax.sql.XADataSource , which is expected. javax.sql.ConnectionPoolDataSource implementations DBCP2 does include an implementation of javax.sql.ConnectionPoolDataSource : org.apache.commons.dbcp2.cpdsadapter.DriverAdapterCPDS . It creates javax.sql.PooledConnection objects by calling java.sql.DriverManager.getConnection() . This pool should not be used directly and it should be treated as an adapter for drivers that: Do not provide their own javax.sql.ConnectionPoolDataSource implementation You want to use according to JDBC recommendations for connection pools As shown in the sequence diagram above, the driver provides javax.sql.ConnectionPoolDataSource directly or with the help of an org.apache.commons.dbcp2.cpdsadapter.DriverAdapterCPDS adapter , while DBCP2 implements the application server contract with one of: org.apache.commons.dbcp2.datasources.PerUserPoolDataSource org.apache.commons.dbcp2.datasources.SharedPoolDataSource Both these pools take an instance of javax.sql.ConnectionPoolDataSource at the configuration stage. This is the most important and interesting part of DBCP2: javax.sql.DataSource implementations To implement the connection pooling feature, you do not have to follow JDBC recommendations to use javax.sql.ConnectionPoolDataSource javax.sql.PooledConnection SPI. Here is a list of normal data sources of DBCP2: org.apache.commons.dbcp2.BasicDataSource org.apache.commons.dbcp2.managed.BasicManagedDataSource org.apache.commons.dbcp2.PoolingDataSource org.apache.commons.dbcp2.managed.ManagedDataSource There are two axes here: basic vs pooling This axis determines the pooling configuration aspect. Both kinds of data sources perform pooling of java.sql.Connection objects. The only difference is that: A basic data source is configured by using bean properties such as maxTotal or minIdle used to configure an internal instance of org.apache.commons.pool2.impl.GenericObjectPool . A pooling data source is configured with an externally created/configured org.apache.commons.pool2.ObjectPool . managed vs non-managed This axis determines the connection creation aspect and the JTA behavior: A non-managed basic data source creates java.sql.Connection instances by using java.sql.Driver.connect() internally. A non-managed pooling data source creates java.sql.Connection instances using the passed org.apache.commons.pool2.ObjectPool object. A managed pooling data source wraps java.sql.Connection instances inside org.apache.commons.dbcp2.managed.ManagedConnection objects that ensure that javax.transaction.Transaction.enlistResource() is called if needed in the JTA context. But still the actual connection that is wrapped is obtained from any org.apache.commons.pool2.ObjectPool object that the pool is configured with. A managed basic data source frees you from configuring a dedicated org.apache.commons.pool2.ObjectPool . Instead, it is enough to configure existing, real, database-specific javax.sql.XADataSource objects. Bean properties are used to create an internal instance of org.apache.commons.pool2.impl.GenericObjectPool , which is then passed to an internal instance of a managed pooling data source ( org.apache.commons.dbcp2.managed.ManagedDataSource ). Note The only thing that DBCP2 cannot do is XA transaction recovery . DBCP2 correctly enlists XAResources in active JTA transactions, but it is not performing the recovery. This should be done separately and the configuration is usually specific to the chosen transaction manager implementation (such as Narayana ). 6.2.3. Pattern to use The recommended pattern is: Create or obtain a database-specific javax.sql.DataSource or javax.sql.XADataSource instance with database-specific configuration (URL, credentials, and so on) that can create connections/XA connections. Create or obtain a non database-specific javax.sql.DataSource instance (internally configured with the above, database-specific data source) with non database-specific configuration (connection pooling, transaction manager, and so on). Use javax.sql.DataSource to get an instance of java.sql.Connection and perform JDBC operations. Here is a canonical example: // Database-specific, non-pooling, non-enlisting javax.sql.XADataSource PGXADataSource postgresql = new org.postgresql.xa.PGXADataSource(); // Database-specific configuration postgresql.setUrl("jdbc:postgresql://localhost:5432/reportdb"); postgresql.setUser("fuse"); postgresql.setPassword("fuse"); postgresql.setCurrentSchema("report"); postgresql.setConnectTimeout(5); // ... // Non database-specific, pooling, enlisting javax.sql.DataSource BasicManagedDataSource pool = new org.apache.commons.dbcp2.managed.BasicManagedDataSource(); // Delegate to database-specific XADatasource pool.setXaDataSourceInstance(postgresql); // Delegate to JTA transaction manager pool.setTransactionManager(transactionManager); // Non database-specific configuration pool.setMinIdle(3); pool.setMaxTotal(10); pool.setValidationQuery("select schema_name, schema_owner from information_schema.schemata"); // ... // JDBC code: javax.sql.DataSource applicationDataSource = pool; try (Connection c = applicationDataSource.getConnection()) { try (Statement st = c.createStatement()) { try (ResultSet rs = st.executeQuery("select ...")) { // .... In a Fuse environment, there are many configuration options and there is no requirement to use DBCP2. 6.3. Configuring JDBC data sources As discussed in OSGi transaction architecture , some services must be registered in the OSGi service registry. Just as you can find ( lookup ) a transaction manager instance by using, for example, the javax.transaction.UserTransaction interface, you can do the same with JDBC data sources by using the javax.sql.DataSource interface. The requirements are: Database-specific data source that can communicate with the target database Generic data source where you can configure pooling and possibly transaction management (XA) In an OSGi environment, such as Fuse, data sources become accessible from applications if they are registered as OSGi services. Fundamentally, it is done as follows: org.osgi.framework.BundleContext.registerService(javax.sql.DataSource.class, dataSourceObject, properties); org.osgi.framework.BundleContext.registerService(javax.sql.XADataSource.class, xaDataSourceObject, properties); There are two methods for registering such services: Publishing data sources by using the jdbc:ds-create Karaf console command. This is the configuration method . Publishing data sources by using methods such as Blueprint, OSGi Declarative Services (SCR) or just a BundleContext.registerService() API call. This method requires a dedicated OSGi bundle that contains the code and/or metadata. This is the_deployment method_. 6.4. Using the OSGi JDBC service Chapter 125 of the OSGi Enterprise R6 specification defines a single interface in the org.osgi.service.jdbc package. This is how OSGi handles data sources: public interface DataSourceFactory { java.sql.Driver createDriver(Properties props); javax.sql.DataSource createDataSource(Properties props); javax.sql.ConnectionPoolDataSource createConnectionPoolDataSource(Properties props); javax.sql.XADataSource createXADataSource(Properties props); } As mentioned before, plain java.sql.Connection connections may be obtained directly from java.sql.Driver . Generic org.osgi.service.jdbc.DataSourceFactory The simplest implementation of org.osgi.service.jdbc.DataSourceFactory is org.ops4j.pax.jdbc.impl.DriverDataSourceFactory provided by mvn:org.ops4j.pax.jdbc/pax-jdbc/1.3.0 bundle. All it does is track bundles that may include the /META-INF/services/java.sql.Driver descriptor for the standard JavaTM 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. With the above commands, the javax.sql.DataSource service is still not registered, but you are one step closer. The above intermediary org.osgi.service.jdbc.DataSourceFactory services can be used to obtain: java.sql.Driver javax.sql.DataSource by passing properties: url , user and password to the createDataSource() method. You cannot obtain javax.sql.ConnectionPoolDataSource or javax.sql.XADataSource from the generic org.osgi.service.jdbc.DataSourceFactory created by a non database-specific pax-jdbc bundle. Note The mvn:org.postgresql/postgresql/42.2.5 bundle correctly implements the OSGi JDBC specification and registers an org.osgi.service.jdbc.DataSourceFactory instance with all methods that are implemented, including the ones that create XA and ConnectionPool data sources. Dedicated, database-specific org.osgi.service.jdbc.DataSourceFactory implementations There are additional bundles such as the following: mvn:org.ops4j.pax.jdbc/pax-jdbc-mysql/1.3.0 mvn:org.ops4j.pax.jdbc/pax-jdbc-db2/1.3.0 ... These bundles register database-specific org.osgi.service.jdbc.DataSourceFactory services that can return all kinds of factories , including javax.sql.ConnectionPoolDataSource and javax.sql.XADataSource . For example: 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 USD{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 : Install the OSGi JDBC service bundle and pax-jdbc-mysql bundle that registers intermediary org.osgi.service.jdbc.DataSourceFactory : Install the pax-jdbc bundle and the pax-jdbc-config bundle that tracks org.osgi.service.jdbc.DataSourceFactory services and org.ops4j.datasource factory PIDs : Create the factory configuration (assume a MySQL server is running): Check if pax-jdbc-config processed the configuration into the javax.sql.DataSource service: 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: 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: 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: 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 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: Here is an example of registering a MySQL-specific DataSourceFactory service: 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: As can see, the org.ops4j.datasource factory PID is created for you. However it is not automatically stored in USD{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: 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: pax-jdbc-pool-dbcp2 pax-jdbc-pool-narayana pax-jdbc-pool-transx Important This chapter presents exhaustive information about the internals of data source management. While information about the DBCP2 connection pool feature is provided, keep in mind that this connection pool provides proper JTA enlisting capabilities, but not XA Recovery . To ensure that XA recovery is in place, use the pax-jdbc-pool-transx or pax-jdbc-pool-narayana connection pool module. 6.7.1. Introduction to using JDBC connection pools examples showed how to register a database-specific data source factory . Because data source itself is a factory for connections, org.osgi.service.jdbc.DataSourceFactory may be treated as a meta factory that should be able to produce three kinds of data sources, plus, as a bonus, a java.sql.Driver ): javax.sql.DataSource javax.sql.ConnectionPoolDataSource javax.sql.XADataSource For example, pax-jdbc-mysql registers an org.ops4j.pax.jdbc.mysql.impl.MysqlDataSourceFactory that produces: javax.sql.DataSource com.mysql.jdbc.jdbc2.optional.MysqlDataSource javax.sql.ConnectionPoolDataSource com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource javax.sql.XADataSource com.mysql.jdbc.jdbc2.optional.MysqlXADataSource java.sql.Driver com.mysql.jdbc.Driver The PostgreSQL driver itself implements the OSGi JDBC service and produces: javax.sql.DataSource org.postgresql.jdbc2.optional.PoolingDataSource (if there are pool-related properties specified) or org.postgresql.jdbc2.optional.SimpleDataSource javax.sql.ConnectionPoolDataSource org.postgresql.jdbc2.optional.ConnectionPool javax.sql.XADataSource org.postgresql.xa.PGXADataSource java.sql.Driver org.postgresql.Driver As shown in the canonical DataSource example , any pooling , generic data source, if it is going to work in a JTA environment, needs a database specific data source to actually obtain (XA) connections. We already have the latter, and we need actual, generic, reliable connection pool. The canonical DataSource example shows how to configure a generic pool with a database-specific data source. The pax-jdbc-pool-* bundles work smoothly with the above described org.osgi.service.jdbc.DataSourceFactory services. Just as the OSGI Enterprise R6 JDBC specification provides the org.osgi.service.jdbc.DataSourceFactory standard interface, pax-jdbc-pool-common provides proprietary org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory interface: public interface PooledDataSourceFactory { javax.sql.DataSource create(org.osgi.service.jdbc.DataSourceFactory dsf, Properties config) } This interface is perfectly conformant with what this important note that was presented previously and is worth repeating: Important Even if the application uses XA transactions and/or connection pooling, the application interacts with javax.sql.DataSource and not the two other JDBC data source interfaces. This interface simply creates a pooling data source out of a database-specific, non-pooling datas ource. Or more precisely, it is a data source factory ( meta factory ) that turns a factory of database-specific data sources into a factory of pooling data sources. Note There is nothing that prevents an application from configuring pooling for a javax.sql.DataSource object by using an org.osgi.service.jdbc.DataSourceFactory service that already returns pooling for javax.sql.DataSource objects. The following table shows which bundles register pooled data source factories. In the table, instances of o.o.p.j.p represent org.ops4j.pax.jdbc.pool . Bundle PooledDataSourceFactory Pool Key pax-jdbc-pool-narayana o.o.p.j.p.narayana.impl.Dbcp(XA)PooledDataSourceFactory narayana pax-jdbc-pool-dbcp2 o.o.p.j.p.dbcp2.impl.Dbcp(XA)PooledDataSourceFactory dbcp2 pax-jdbc-pool-transx o.o.p.j.p.transx.impl.Transx(Xa)PooledDataSourceFactory transx The above bundles install only data source factories and not the data sources themselves. The application needs something that calls the javax.sql.DataSource create(org.osgi.service.jdbc.DataSourceFactory dsf, Properties config) method. 6.7.2. Using the dbcp2 connection pool module The section about generic data sources provides an example of how to use and configure the Apache Commons DBCP module . This section shows how to do this in the Fuse OSGi environment. Consider the Section 6.4.1, "PAX-JDBC configuration service" bundle. In addition to tracking the following: org.osgi.service.jdbc.DataSourceFactory services org.ops4j.datasource factory PIDs The bundle also tracks instances of org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory that are registered by one of the pax-jdbc-pool-* bundles. If the factory configuration contains the pool property, then the ultimate data source registered by the pax-jdbc-config bundle is the database-specific datas ource, but wrapped inside one of the following if pool=dbcp2 ): org.apache.commons.dbcp2.PoolingDataSource org.apache.commons.dbcp2.managed.ManagedDataSource This is consistent with the generic data source example . In addition to the pool property, and the boolean xa property, which selects a non-xa or an xa data source, the org.ops4j.datasource factory PID may contain prefixed properties: pool.* factory.* Where each property is used depends on which pax-jdbc-pool-* bundle isused. For DBCP2, it is: pool.* : bean properties of org.apache.commons.pool2.impl.GenericObjectPoolConfig (both xa and non-xa scenario) factory.* : bean properties of org.apache.commons.dbcp2.managed.PoolableManagedConnectionFactory (xa) or org.apache.commons.dbcp2.PoolableConnectionFactory (non-xa) 6.7.2.1. Configuration properties for BasicDataSource The following table lists the generic configuration properties for BasicDataSource. Parameter Default Description username The connection user name to be passed to our JDBC driver to establish a connection. password The connection password to be passed to our JDBC driver to establish a connection. url The connection URL to be passed to our JDBC driver to establish a connection. driverClassName The fully qualified Java class name of the JDBC driver to be used. initialSize 0 The initial number of connections that are created when the pool is started. maxTotal 8 The maximum number of active connections that can be allocated from this pool at the same time, or negative for no limit. maxIdle 8 The maximum number of connections that can remain idle in the pool, without extra ones being released, or negative for no limit. minIdle 0 The minimum number of connections that can remain idle in the pool, without extra ones being created, or zero to create none. maxWaitMillis indefinitely The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception, or -1 to wait indefinitely. validationQuery The SQL query that will be used to validate connections from this pool before returning them to the caller. If specified, this query MUST be an SQL SELECT statement that returns at least one row. If not specified, connections will be validation by calling the isValid() method. validationQueryTimeout no timeout The timeout in seconds before connection validation queries fail. If set to a positive value, this value is passed to the driver via the setQueryTimeout method of the Statement used to execute the validation query. testOnCreate false The indication of whether objects will be validated after creation. If the object fails to validate, the borrow attempt that triggered the object creation will fail. testOnBorrow true The indication of whether objects will be validated before being borrowed from the pool. If the object fails to validate, it will be dropped from the pool, and we will attempt to borrow another. testOnReturn false The indication of whether objects will be validated before being returned to the pool. testWhileIdle false The indication of whether objects will be validated by the idle object evictor (if any). If an object fails to validate, it will be dropped from the pool. timeBetweenEvictionRunsMillis -1 The number of milliseconds to sleep between runs of the idle object evictor thread. When non-positive, no idle object evictor thread will be run. numTestsPerEvictionRun 3 The number of objects to examine during each run of the idle object evictor thread (if any). minEvictableIdleTimeMillis 1000 * 60 * 30 The minimum amount of time an object may sit idle in the pool before it is eligible for eviction by the idle object evictor (if any). 6.7.2.2. Example of how to configure DBCP2 pool The following is a realistic example (except useSSL=false ) of a configuration of a DBCP2 pool ( org.ops4j.datasource-mysql factory PID ) that uses convenient syntax with jdbc. -prefixed properties: 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: For completeness, here is a full example with connection pool configuration added to the example . Again, this assumes that you are starting with a fresh Fuse installation. Install a JDBC driver: Install the jdbc , pax-jdbc-mysql and pax-jdbc-pool-dbcp2 features: Create the factory configuration : Check if pax-jdbc-config processed the configuration into the javax.sql.DataSource service: Use the data source: 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 (JavaTM 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 USDPQ_HOME . Section 6.8.1, "Manual deployment of data sources" Section 6.8.2, "Factory deployment of data sources" Section 6.8.3, "Mixed deployment of data sources" 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: As shown in the above listing, the Blueprint bundle exports the javax.sql.DataSource service, which is a generic, non database-specific, connection pool. The database-specific javax.sql.XADataSource bundle is not registered as an OSGi service, because Blueprint XML does not have an explicit <service ref="postgresql"> declaration. 6.8.2. Factory deployment of data sources Factory deployment of data sources uses the pax-jdbc-config bundle in a canonical way. This is a bit different from the method that was recommended in Fuse 6.x, which required specification of the pooling configuration as service properties. Here is the Blueprint XML example: <!-- A database-specific org.osgi.service.jdbc.DataSourceFactory that can create DataSource/XADataSource/ /ConnectionPoolDataSource/Driver using properties. It is registered by pax-jdbc-* or for example mvn:org.postgresql/postgresql/42.2.5 bundle natively. --> <reference id="dataSourceFactory" interface="org.osgi.service.jdbc.DataSourceFactory" filter="(osgi.jdbc.driver.class=org.postgresql.Driver)" /> <!-- Non database-specific org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory that can create pooled data sources using some org.osgi.service.jdbc.DataSourceFactory. dbcp2 pool is registered by pax-jdbc-pool-dbcp2 bundle. --> <reference id="pooledDataSourceFactory" interface="org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory" filter="(&amp;(pool=dbcp2)(xa=true))" /> <!-- Finally, use both factories to expose pooled, xa-aware data source. --> <bean id="pool" factory-ref="pooledDataSourceFactory" factory-method="create"> <argument ref="dataSourceFactory" /> <argument> <props> <!-- Properties needed by postgresql-specific org.osgi.service.jdbc.DataSourceFactory. Cannot prepend them with 'jdbc.' prefix as the DataSourceFactory is implemented directly by PostgreSQL driver, not by pax-jdbc-* bundle. --> <prop key="url" value="jdbc:postgresql://localhost:5432/reportdb" /> <prop key="user" value="fuse" /> <prop key="password" value="fuse" /> <prop key="currentSchema" value="report" /> <prop key="connectTimeout" value="5" /> <!-- Properties needed by dbcp2-specific org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory --> <prop key="pool.minIdle" value="2" /> <prop key="pool.maxTotal" value="10" /> <prop key="pool.blockWhenExhausted" value="true" /> <prop key="pool.maxWaitMillis" value="2000" /> <prop key="pool.testOnBorrow" value="true" /> <prop key="pool.testWhileIdle" value="false" /> <prop key="factory.validationQuery" value="select schema_name from information_schema.schemata" /> <prop key="factory.validationQueryTimeout" value="2" /> </props> </argument> </bean> <!-- Expose data source for use by application code (such as Camel, Spring, ...). --> <service interface="javax.sql.DataSource" ref="pool"> <service-properties> <entry key="osgi.jndi.service.name" value="jdbc/postgresql" /> </service-properties> </service> This example uses factory beans that create data sources by using data source factories. You do not need to explicitly reference the javax.transaction.TransactionManager service, as this is tracked internally by the XA-aware PooledDataSourceFactory . The following is the same example but in a Fuse/Karaf shell. Note To have the native org.osgi.service.jdbc.DataSourcFactory bundle registered, install mvn:org.osgi/org.osgi.service.jdbc/1.0.0 and then install a PostgreSQL driver. 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: 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 JavaTM persistence API From the perspective of transaction management, it is important to understand how data sources are used with the JavaTM Persistence API (JPA). This section does not describe the details of the JPA specification itself, nor the details about Hibernate, which is the most known JPA implementation. Instead, this section shows how to point JPA persistent units to data sources. 6.9.1. About data source references The META-INF/persistence.xml descriptor (see the JPA 2.1 specification, 8.2.1.5 jta-data-source, non-jta-data-source ) defines two kinds of data source references: <jta-data-source> - This is a JNDI reference to JTA-enabled data source to use with JTA transactions. <non-jta-data-source> - This is a JNDI reference to JTA-enabled data source to use outside of JTA transactions. This data source is usually also used in the initialization phase, for example, with the hibernate.hbm2ddl.auto property that configures Hibernate to auto-create database schema. These two data sources are not related to javax.sql.DataSource or javax.sql.XADataSource ! This is common misconception when developing JPA applications. Both JNDI names must refer to JNDI-bound javax.sql.DataSource services. 6.9.2. Referring to JNDI names When you register an OSGi service with the osgi.jndi.service.name property, it is bound in the OSGi JNDI service. In an OSGi runtime (such as Fuse/Karaf), JNDI is not a simple dictionary of name value pairs. Referring to objects by means of JNDI names in OSGi involves service lookups and other, more complex OSGi mechanisms, such as service hooks. In a fresh Fuse installation, the following listing shows how data sources are registered in JNDI: 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:

Commands:
// 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 ...")) { // .
org.osgi.framework.BundleContext.registerService(javax.sql.DataSource.class, dataSourceObject, properties); org.osgi.framework.BundleContext.registerService(javax.sql.XADataSource.class, xaDataSourceObject, properties);
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); }
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)
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)
public DataSource createDataSource(Properties props); public XADataSource createXADataSource(Properties props); public ConnectionPoolDataSource createConnectionPoolDataSource(Properties props);
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()> 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)
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
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:USD{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
karaf@root()> service:list javax.sql.DataSource [javax.sql.DataSource] ---------------------- dataSourceName = mysqlds felix.fileinstall.filename = file:USD{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)
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()> 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()> 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.13.0.fuse-7_13_0-00012-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:USD{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)
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: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()> 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
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>)
public interface PooledDataSourceFactory { javax.sql.DataSource create(org.osgi.service.jdbc.DataSourceFactory dsf, Properties config) }
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
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
karaf@root()> install -s mvn:mysql/mysql-connector-java/5.1.34 Bundle ID: 223
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)
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
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:USD{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)
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
<!-- 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>
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://USDPQ_HOME/databases/blueprints/postgresql-manual.xml Bundle ID: 226 karaf@root()> bundle:services -p 226 Bundle 226 provides: -------------------- objectClass = [javax.sql.DataSource] osgi.jndi.service.name = jdbc/postgresql osgi.service.blueprint.compname = pool service.bundleid = 226 service.id = 242 service.scope = bundle ----- objectClass = [org.osgi.service.blueprint.container.BlueprintContainer] osgi.blueprint.container.symbolicname = postgresql-manual.xml osgi.blueprint.container.version = 0.0.0 service.bundleid = 226 service.id = 243 service.scope = singleton karaf@root()> feature:install jdbc karaf@root()> jdbc:ds-list Name │ Product │ Version │ URL │ Status ────────────────┼────────────┼───────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────── jdbc/postgresql │ PostgreSQL │ 10.3 (Debian 10.3-1.pgdg90+1) │ jdbc:postgresql://localhost:5432/reportdb?prepareThreshold=5&preparedStatementCacheQueries=256&preparedStatementCacheSizeMiB=5&databaseMetadataCacheFields=65536&databaseMetadataCacheFieldsMiB=5&defaultRowFetchSize=0&binaryTransfer=true&readOnly=false&binaryTransferEnable=&binaryTransferDisable=&unknownLength=2147483647&logUnclosedConnections=false&disableColumnSanitiser=false&tcpKeepAlive=false&loginTimeout=0&connectTimeout=5&socketTimeout=0&cancelSignalTimeout=10&receiveBufferSize=-1&sendBufferSize=-1&ApplicationName=PostgreSQL JDBC Driver&jaasLogin=true&useSpnego=false&gsslib=auto&sspiServiceClass=POSTGRES&allowEncodingChanges=false&currentSchema=report&targetServerType=any&loadBalanceHosts=false&hostRecheckSeconds=10&preferQueryMode=extended&autosave=never&reWriteBatchedInserts=false │ OK karaf@root()> jdbc:query jdbc/postgresql 'select * from incident'; date │ summary │ name │ details │ id │ email ────────────────────┼────────────┼────────┼───────────────────────────────┼────┼───────────────── 2018-02-20 08:00:00 │ Incident 1 │ User 1 │ This is a report incident 001 │ 1 │ user1@redhat.com 2018-02-20 08:10:00 │ Incident 2 │ User 2 │ This is a report incident 002 │ 2 │ user2@redhat.com 2018-02-20 08:20:00 │ Incident 3 │ User 3 │ This is a report incident 003 │ 3 │ user3@redhat.com 2018-02-20 08:30:00 │ Incident 4 │ User 4 │ This is a report incident 004 │ 4 │ user4@redhat.com
<!-- A database-specific org.osgi.service.jdbc.DataSourceFactory that can create DataSource/XADataSource/ /ConnectionPoolDataSource/Driver using properties. It is registered by pax-jdbc-* or for example mvn:org.postgresql/postgresql/42.2.5 bundle natively. --> <reference id="dataSourceFactory" interface="org.osgi.service.jdbc.DataSourceFactory" filter="(osgi.jdbc.driver.class=org.postgresql.Driver)" /> <!-- Non database-specific org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory that can create pooled data sources using some org.osgi.service.jdbc.DataSourceFactory. dbcp2 pool is registered by pax-jdbc-pool-dbcp2 bundle. --> <reference id="pooledDataSourceFactory" interface="org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory" filter="(&amp;(pool=dbcp2)(xa=true))" /> <!-- Finally, use both factories to expose pooled, xa-aware data source. --> <bean id="pool" factory-ref="pooledDataSourceFactory" factory-method="create"> <argument ref="dataSourceFactory" /> <argument> <props> <!-- Properties needed by postgresql-specific org.osgi.service.jdbc.DataSourceFactory. Cannot prepend them with 'jdbc.' prefix as the DataSourceFactory is implemented directly by PostgreSQL driver, not by pax-jdbc-* bundle. --> <prop key="url" value="jdbc:postgresql://localhost:5432/reportdb" /> <prop key="user" value="fuse" /> <prop key="password" value="fuse" /> <prop key="currentSchema" value="report" /> <prop key="connectTimeout" value="5" /> <!-- Properties needed by dbcp2-specific org.ops4j.pax.jdbc.pool.common.PooledDataSourceFactory --> <prop key="pool.minIdle" value="2" /> <prop key="pool.maxTotal" value="10" /> <prop key="pool.blockWhenExhausted" value="true" /> <prop key="pool.maxWaitMillis" value="2000" /> <prop key="pool.testOnBorrow" value="true" /> <prop key="pool.testWhileIdle" value="false" /> <prop key="factory.validationQuery" value="select schema_name from information_schema.schemata" /> <prop key="factory.validationQueryTimeout" value="2" /> </props> </argument> </bean> <!-- Expose data source for use by application code (such as Camel, Spring, ...). --> <service interface="javax.sql.DataSource" ref="pool"> <service-properties> <entry key="osgi.jndi.service.name" value="jdbc/postgresql" /> </service-properties> </service>
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://USDPQ_HOME/databases/blueprints/postgresql-pax-jdbc-factory-dbcp2.xml Bundle ID: 233 karaf@root()> bundle:services -p 233 Bundle 233 provides: -------------------- objectClass = [javax.sql.DataSource] osgi.jndi.service.name = jdbc/postgresql osgi.service.blueprint.compname = pool service.bundleid = 233 service.id = 336 service.scope = bundle ----- objectClass = [org.osgi.service.blueprint.container.BlueprintContainer] osgi.blueprint.container.symbolicname = postgresql-pax-jdbc-factory-dbcp2.xml osgi.blueprint.container.version = 0.0.0 service.bundleid = 233 service.id = 337 service.scope = singleton karaf@root()> jdbc:ds-list Name │ Product │ Version │ URL │ Status ────────────────┼────────────┼───────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────── jdbc/postgresql │ PostgreSQL │ 10.3 (Debian 10.3-1.pgdg90+1) │ jdbc:postgresql://localhost:5432/reportdb?prepareThreshold=5&preparedStatementCacheQueries=256&preparedStatementCacheSizeMiB=5&databaseMetadataCacheFields=65536&databaseMetadataCacheFieldsMiB=5&defaultRowFetchSize=0&binaryTransfer=true&readOnly=false&binaryTransferEnable=&binaryTransferDisable=&unknownLength=2147483647&logUnclosedConnections=false&disableColumnSanitiser=false&tcpKeepAlive=false&loginTimeout=0&connectTimeout=5&socketTimeout=0&cancelSignalTimeout=10&receiveBufferSize=-1&sendBufferSize=-1&ApplicationName=PostgreSQL JDBC Driver&jaasLogin=true&useSpnego=false&gsslib=auto&sspiServiceClass=POSTGRES&allowEncodingChanges=false&currentSchema=report&targetServerType=any&loadBalanceHosts=false&hostRecheckSeconds=10&preferQueryMode=extended&autosave=never&reWriteBatchedInserts=false │ OK karaf@root()> jdbc:query jdbc/postgresql 'select * from incident'; date │ summary │ name │ details │ id │ email ────────────────────┼────────────┼────────┼───────────────────────────────┼────┼───────────────── 2018-02-20 08:00:00 │ Incident 1 │ User 1 │ This is a report incident 001 │ 1 │ user1@redhat.com 2018-02-20 08:10:00 │ Incident 2 │ User 2 │ This is a report incident 002 │ 2 │ user2@redhat.com 2018-02-20 08:20:00 │ Incident 3 │ User 3 │ This is a report incident 003 │ 3 │ user3@redhat.com 2018-02-20 08:30:00 │ Incident 4 │ User 4 │ This is a report incident 004 │ 4 │ user4@redhat.com
<!-- 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>
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://USDPQ_HOME/databases/blueprints/postgresql-pax-jdbc-discovery.xml Bundle ID: 233 karaf@root()> bundle:services -p 233 Bundle 233 provides: -------------------- factory.validationQuery = select schema_name from information_schema.schemata factory.validationQueryTimeout = 2 objectClass = [javax.sql.XADataSource] osgi.jndi.service.name = jdbc/postgresql osgi.service.blueprint.compname = postgresql pool = dbcp2 pool.blockWhenExhausted = true pool.maxTotal = 10 pool.maxWaitMillis = 2000 pool.minIdle = 2 pool.testOnBorrow = true pool.testWhileIdle = false service.bundleid = 233 service.id = 336 service.scope = bundle ----- objectClass = [org.osgi.service.blueprint.container.BlueprintContainer] osgi.blueprint.container.symbolicname = postgresql-pax-jdbc-discovery.xml osgi.blueprint.container.version = 0.0.0 service.bundleid = 233 service.id = 338 service.scope = singleton karaf@root()> service:list javax.sql.XADataSource [javax.sql.XADataSource] ------------------------ factory.validationQuery = select schema_name from information_schema.schemata factory.validationQueryTimeout = 2 osgi.jndi.service.name = jdbc/postgresql osgi.service.blueprint.compname = postgresql pool = dbcp2 pool.blockWhenExhausted = true pool.maxTotal = 10 pool.maxWaitMillis = 2000 pool.minIdle = 2 pool.testOnBorrow = true pool.testWhileIdle = false service.bundleid = 233 service.id = 336 service.scope = bundle Provided by : Bundle 233 Used by: OPS4J Pax JDBC Config (224) karaf@root()> service:list javax.sql.DataSource [javax.sql.DataSource] ---------------------- factory.validationQuery = select schema_name from information_schema.schemata factory.validationQueryTimeout = 2 osgi.jndi.service.name = jdbc/postgresql osgi.service.blueprint.compname = postgresql pax.jdbc.managed = true pax.jdbc.service.id.ref = 336 pool.blockWhenExhausted = true pool.maxTotal = 10 pool.maxWaitMillis = 2000 pool.minIdle = 2 pool.testOnBorrow = true pool.testWhileIdle = false service.bundleid = 224 service.id = 337 service.ranking = 1000 service.scope = singleton Provided by : OPS4J Pax JDBC Config (224) karaf@root()> jdbc:ds-list Name │ Product │ Version │ URL │ Status ────────────────┼────────────┼───────────────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────── jdbc/postgresql │ PostgreSQL │ 10.3 (Debian 10.3-1.pgdg90+1) │ jdbc:postgresql://localhost:5432/reportdb?prepareThreshold=5&preparedStatementCacheQueries=256&preparedStatementCacheSizeMiB=5&databaseMetadataCacheFields=65536&databaseMetadataCacheFieldsMiB=5&defaultRowFetchSize=0&binaryTransfer=true&readOnly=false&binaryTransferEnable=&binaryTransferDisable=&unknownLength=2147483647&logUnclosedConnections=false&disableColumnSanitiser=false&tcpKeepAlive=false&loginTimeout=0&connectTimeout=5&socketTimeout=0&cancelSignalTimeout=10&receiveBufferSize=-1&sendBufferSize=-1&ApplicationName=PostgreSQL JDBC Driver&jaasLogin=true&useSpnego=false&gsslib=auto&sspiServiceClass=POSTGRES&allowEncodingChanges=false&currentSchema=report&targetServerType=any&loadBalanceHosts=false&hostRecheckSeconds=10&preferQueryMode=extended&autosave=never&reWriteBatchedInserts=false │ OK jdbc/postgresql │ PostgreSQL │ 10.3 (Debian 10.3-1.pgdg90+1) │ jdbc:postgresql://localhost:5432/reportdb?prepareThreshold=5&preparedStatementCacheQueries=256&preparedStatementCacheSizeMiB=5&databaseMetadataCacheFields=65536&databaseMetadataCacheFieldsMiB=5&defaultRowFetchSize=0&binaryTransfer=true&readOnly=false&binaryTransferEnable=&binaryTransferDisable=&unknownLength=2147483647&logUnclosedConnections=false&disableColumnSanitiser=false&tcpKeepAlive=false&loginTimeout=0&connectTimeout=5&socketTimeout=0&cancelSignalTimeout=10&receiveBufferSize=-1&sendBufferSize=-1&ApplicationName=PostgreSQL JDBC Driver&jaasLogin=true&useSpnego=false&gsslib=auto&sspiServiceClass=POSTGRES&allowEncodingChanges=false&currentSchema=report&targetServerType=any&loadBalanceHosts=false&hostRecheckSeconds=10&preferQueryMode=extended&autosave=never&reWriteBatchedInserts=false │ OK karaf@root()> jdbc:query jdbc/postgresql 'select * from incident' date │ summary │ name │ details │ id │ email ────────────────────┼────────────┼────────┼───────────────────────────────┼────┼───────────────── 2018-02-20 08:00:00 │ Incident 1 │ User 1 │ This is a report incident 001 │ 1 │ user1@redhat.com 2018-02-20 08:10:00 │ Incident 2 │ User 2 │ This is a report incident 002 │ 2 │ user2@redhat.com 2018-02-20 08:20:00 │ Incident 3 │ User 3 │ This is a report incident 003 │ 3 │ user3@redhat.com 2018-02-20 08:30:00 │ Incident 4 │ User 4 │ This is a report incident 004 │ 4 │ user4@redhat.com
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
<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>
Persistence unit "pu-name" refers to a non OSGi service DataSource