6.4. 使用 OSGi JDBC 服务

Enterprise R6 规范的第 125 章在 org.osgi.service.jdbc 软件包中定义一个接口。这是OSOS 如何处理数据源:

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);
}

如前文所述,普通的 java.sql.Connection 连接可以直接从 java.sql.Driver 获取。

Generic org.osgi.service.jdbc.DataSourceFactory

org.osgi.service.jdbc.DataSourceFactory 最简单的实施是 org.ops4j.pax.jdbc.impl.DriverDataSourceFactory by mvn:org.ops4j.pax.pax.jdbc/pax-jdbc/1.3.0 bundle。它会跟踪包含标准 Java™ ServiceLoader 实用程序的 /META-INF/services/java.sql.Driver 描述符的捆绑包。如果您安装任何标准 JDBC 驱动程序,pax-jdbc 捆绑包注册一个可用于(而非直接)的 DataSourceFactory,以便通过 java.sql.Driver.connect() 调用来获取连接。

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)

使用以上命令时,javax.sql.DataSource 服务仍然没有注册,但您更接近一步。以上中介 org.osgi.service.jdbc.DataSourceFactory 服务可用于获取:

  • java.sql.Driver
  • javax.sql.DataSource 通过传递属性: url用户和密码 createDataSource() 方法。

您无法从普通的 org.osgi.jdbc.DataSource 获取 javax.sql.ConnectionPoolDataSourcejavax.sql.XADataSource Factory,由一个非数据库特定的 pax-jdbc 捆绑包创建。

注意

mvn:org.postgresql/postgresql/42.2.5 捆绑包可以正确地实现 OSGi JDBC 规格,并注册一个 org.osgi.service.jdbc.DataSourceFactory 实例,并附带所有实施的方法,包括创建 XA 和 ConnectionPool 数据源。

专用、特定于数据库的 org.osgi.service.jdbc.DataSource factory 实施

还有其他捆绑包,比如以下所示:

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

这些捆绑包注册特定于数据库的 org.osgi.service.jdbc.DataSource factory 服务,这些服务可以返回所有 工厂,包括 javax.sql.ConnectionPoolDataSourcejavax.sql.XADataSource。例如:

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

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

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

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

6.4.1. PAX- JDBC 配置服务

使用 pax-jdbc (或 pax-jdbc-mysql,pax-jdbc-oracle, …​)捆绑包时,您可以具有 org.osgi.service.jdbc.DataSourceFactory 服务,注册可用于获取给定数据库的数据源(请参阅 第 6.2.1 节 “特定于数据库及通用数据源”)。但是您还没有实际数据源。

mvn:org.ops4j.pax.jdbc/pax-jdbc-config/1.3.0 捆绑包提供了一个可执行以下操作的托管服务工厂:

  • 跟踪 org.osgi.service.jdbc.DataSource factory OSGi 服务,以调用其方法:

    public DataSource createDataSource(Properties props);
    public XADataSource createXADataSource(Properties props);
    public ConnectionPoolDataSource createConnectionPoolDataSource(Properties props);
  • 跟踪 org.ops4j.datasource factory PID,以收集以上方法需要的属性。如果您使用 Configuration Admin 服务可用的任何方法创建 工厂配置,例如,创建一个 ${karaf.etc}/org.ops4j.datasource-mysql.cfg 文件,您可以执行最后一步公开实际的数据库特定数据源。

以下是从全新的 Fuse 安装开始的详细 规范 步骤指南。

注意

您可以明确安装捆绑包而不是功能,以精确显示需要哪些捆绑包。为方便起见,PAX JDBC 项目为几个数据库产品和配置方法提供了功能。

  1. 使用 /META-INF/services/java.sql.Driver 安装 JDBC 驱动程序:

    karaf@root()> install -s mvn:mysql/mysql-connector-java/5.1.34
    Bundle ID: 223
  2. 安装 OSGi JDBC 服务捆绑包和 pax-jdbc-mysql 捆绑包,该捆绑包注册 中介org.osgi.service.jdbc.DataSourceFactory:

    karaf@root()> install -s mvn:org.osgi/org.osgi.service.jdbc/1.0.0
    Bundle ID: 224
    karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-mysql/1.3.0
    Bundle ID: 225
    
    karaf@root()> service:list org.osgi.service.jdbc.DataSourceFactory
    [org.osgi.service.jdbc.DataSourceFactory]
    -----------------------------------------
     osgi.jdbc.driver.class = com.mysql.jdbc.Driver
     osgi.jdbc.driver.name = mysql
     service.bundleid = 225
     service.id = 242
     service.scope = singleton
    Provided by :
     OPS4J Pax JDBC MySQL Driver Adapter (225)
  3. 安装 pax-jdbc 捆绑包和 pax-jdbc-config 捆绑包,该捆绑包跟踪 org.osgi.service.jdbc.DataSourceFactory 服务和org.ops4j.datasource factory PIDs:

    karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc/1.3.0
    Bundle ID: 226
    karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-pool-common/1.3.0
    Bundle ID: 227
    karaf@root()> install -s mvn:org.ops4j.pax.jdbc/pax-jdbc-config/1.3.0
    Bundle ID: 228
    
    karaf@root()> bundle:services -p org.ops4j.pax.jdbc.config
    
    OPS4J Pax JDBC Config (228) provides:
    -------------------------------------
    objectClass = [org.osgi.service.cm.ManagedServiceFactory]
    service.bundleid = 228
    service.id = 245
    service.pid = org.ops4j.datasource
    service.scope = singleton
  4. 创建 factory 配置 (假设 MySQL 服务器正在运行):

    karaf@root()> config:edit --factory --alias mysql org.ops4j.datasource
    karaf@root()> config:property-set osgi.jdbc.driver.name mysql
    karaf@root()> config:property-set dataSourceName mysqlds
    karaf@root()> config:property-set url jdbc:mysql://localhost:3306/reportdb
    karaf@root()> config:property-set user fuse
    karaf@root()> config:property-set password fuse
    karaf@root()> config:update
    
    karaf@root()> config:list '(service.factoryPid=org.ops4j.datasource)'
    ----------------------------------------------------------------
    Pid:            org.ops4j.datasource.a7941498-9b62-4ed7-94f3-8c7ac9365313
    FactoryPid:     org.ops4j.datasource
    BundleLocation: ?
    Properties:
       dataSourceName = mysqlds
       felix.fileinstall.filename = file:${karaf.etc}/org.ops4j.datasource-mysql.cfg
       osgi.jdbc.driver.name = mysql
       password = fuse
       service.factoryPid = org.ops4j.datasource
       service.pid = org.ops4j.datasource.a7941498-9b62-4ed7-94f3-8c7ac9365313
       url = jdbc:mysql://localhost:3306/reportdb
       user = fuse
  5. 检查 pax-jdbc-config 是否处理配置到 javax.sql.DataSource 服务:

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

您现在有一个特定于数据库的(还没有池)数据源。您已经将其注入了需要的位置。例如,您可以使用 Karaf 命令来查询数据库:

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

在上例中,您可以看到 MySQL 警告。这不是一个问题。可能不提供任何属性(而不仅限于 OSGi JDBC 特定属性):

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

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

6.4.2. 处理的属性摘要

管理 工厂 PID 配置的属性传递到相关的 org.osgi.service.jdbc.DataSource factory 实施。

通用

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

  • url
  • user
  • password

DB2

org.ops4j.pax.jdbc.db2.impl.DB2DataSource factory 属性包含这些实施类的所有 bean 属性:

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

PostgreSQL

Nnative org.postgresql.osgi.PGDataSource factory 属性包括 org.postgresql.PGProperty 中指定的所有属性。

HSQLDB

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

  • url
  • user
  • password
  • databaseName
  • 所有 bean 属性

    • org.hsqldb.jdbc.JDBCDataSource
    • org.hsqldb.jdbc.pool.JDBCPooledDataSource
    • org.hsqldb.jdbc.pool.JDBCXADataSource

SQL 服务器和 Sybase

org.ops4j.pax.jdbc.jtds.impl.JTDSDataSource factory 属性包含 net.sourceforge.jtds.jdbcx.JtdsDataSource 的所有属性。

SQL Server

org.ops4j.pax.jdbc.mssql.impl.MSSQLDataSource factory 属性:

  • url
  • user
  • password
  • databaseName
  • serverName
  • portNumber
  • 所有 bean 属性

    • 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
  • 所有 bean 属性

    • 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
  • 所有 bean 属性

    • oracle.jdbc.pool.OracleDataSource
    • oracle.jdbc.pool.OracleConnectionPoolDataSource
    • oracle.jdbc.xa.client.OracleXADataSource

SQLite

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

  • url
  • databaseName
  • org.sqlite.SQLiteDataSource的所有 bean 属性

6.4.3. pax-jdb-config 捆绑包如何处理属性

pax-jdbc-config 捆绑包处理前缀为 jdbc. 的属性。所有这些属性将删除此前缀,其余名称将随即传递。

下面是一下例子,从全新的 Fuse 安装开始:

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

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

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

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

pax-jdbc-config 捆绑包需要以下属性:

  • osgi.jdbc.driver.name
  • dataSourceName
  • dataSourceType

定位并调用相关的 org.osgi.service.jdbc.DataSourceFactory 方法。带有 jdbc. 前缀的属性传递(在删除前缀后),例如 org.osgi.service.jdbc.DataSource factory.createDataSource(properties)。但是,这些属性会在没有删除前缀的情况下添加,例如 javax.sql.DataSource OSGi 服务的属性。