java - 如何在fuse osgi环境中通过sql server的验证查询
问题描述
如何在 Fuse Osgi 环境中将验证查询、testonborrow、validationQueryTimeout 属性传递给 sqlserver 数据源?我们正在使用 osgi 服务来公开 Microsoft SQLServer 数据源。现在我们将 datasourcename、user、password、portNumber 和 serverName 作为属性传递给实例化 SQLServerDataSource。我们如何通过验证查询、testonborrow、testWhileIdle、testOnBorrow 等类似于 Apache commons dbcp 的属性?我们看到了连接问题,我们通过将验证查询等传递给 commons dbcp 来处理它,并希望对 SQLServer 数据源做同样的事情。感谢任何帮助?这是我们如何实例化 SQLServer 数据源并公开为 OSGI 服务的示例。
<bean id="abcd" class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
<property name="databaseName" value="datasourcename" />
<property name="user" value="user" />
<property name="password" value="password" />
<property name="portNumber" value="portNumber" />
<property name="serverName" value="serverName" />
</bean>
<!-- Opening Datasource as osgi service -->
<service interface="javax.sql.DataSource" ref="abcd">
<service-properties>
<entry key="osgi.jndi.service.name" value="jdbc/abcd" />
</service-properties>
</service>
解决方案
这是使用您配置的非 XA 数据源的规范示例com.microsoft.sqlserver.jdbc.SQLServerDataSource
:
<?xml version="1.0"?>
<blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.osgi.org/xmlns/blueprint/v1.0.0 https://www.osgi.org/xmlns/blueprint/v1.0.0/blueprint.xsd">
<!--
This Blueprint XML shows the _deployment_ method for data source configuration.
With this method, it is not required to use pax-jdbc bundles at all. Instead both database-specific
and generic data sources are declared as Blueprint beans.
We need:
- SQL Server driver bundle
- mvn:org.apache.commons/commons-pool2/2.5.0 bundle
- mvn:org.apache.commons/commons-dbcp2/2.1.1 bundle
This Blueprint XML can be directly dropped to ${karaf.home}/deploy or archived in typical /OSGI-INF/blueprint
directory and installed as normal bundle.
-->
<!--
Database-specific, non-pooling, non-enlisting javax.sql.XADataSource
-->
<bean id="sqlServerDS" class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
<property name="databaseName" value="datasourcename" />
<property name="user" value="user" />
<property name="password" value="password" />
<property name="portNumber" value="portNumber" />
<property name="serverName" value="serverName" />
</bean>
<!--
Non database-specific, generic, pooling, non-XA factory for javax.sql.DataSource instances
-->
<bean id="dataSourceConnectionFactory" class="org.apache.commons.dbcp2.DataSourceConnectionFactory">
<!-- pass database-specific javax.sql.DataSource -->
<argument ref="sqlServerDS" />
</bean>
<!--
Non database-specific factory for org.apache.commons.dbcp2.PoolableConnection objects
-->
<bean id="poolableConnectionFactory" class="org.apache.commons.dbcp2.PoolableConnectionFactory">
<argument index="0" ref="dataSourceConnectionFactory" />
<!-- JMX Name - not needed -->
<argument index="1">
<null />
</argument>
<property name="maxConnLifetimeMillis" value="30000" />
<!-- set your validation query here: -->
<property name="validationQuery" value="select schema_name, schema_owner from information_schema.schemata" />
<!-- in seconds -->
<property name="validationQueryTimeout" value="2" />
</bean>
<!--
Pooling configuration
-->
<bean id="poolConfig" class="org.apache.commons.pool2.impl.GenericObjectPoolConfig">
<property name="minIdle" value="2" />
<property name="maxTotal" value="10" />
<property name="testOnBorrow" value="true" />
</bean>
<!--
Non database-specific pool of objects provided by PoolableConnectionFactory
-->
<bean id="pool" class="org.apache.commons.pool2.impl.GenericObjectPool">
<argument index="0" ref="poolableConnectionFactory" />
<argument index="1" ref="poolConfig" />
</bean>
<!--
And findally non database-specific, generic, pooling javax.sql.DataSource
-->
<bean id="dataSource" class="org.apache.commons.dbcp2.PoolingDataSource">
<argument ref="pool" />
</bean>
<!--
Expose datasource to use by application code (like Camel, Spring, ...)
-->
<service interface="javax.sql.DataSource" ref="dataSource">
<service-properties>
<entry key="osgi.jndi.service.name" value="jdbc/ds" />
</service-properties>
</service>
</blueprint>
我不确定是否com.microsoft.sqlserver.jdbc.SQLServerDataSource
实现javax.sql.XADataSource
- 在这种情况下它会更容易,因为你可以使用org.apache.commons.dbcp2.managed.BasicManagedDataSource
它并使用你需要的所有东西来配置它,如下所示:
<bean id="pool" class="org.apache.commons.dbcp2.managed.BasicManagedDataSource">
<property name="xaDataSourceInstance" ref="sqlServerDS" />
<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>
但在这种情况下,sqlServerDS
应该是javax.sql.XADataSource
.
编辑:我看到这里描述的问题仍然存在:https ://issues.apache.org/jira/browse/ARIES-960
就个人而言,最好创建一个这样的对象(请原谅我使用 PostgreSQL):
package com.example;
import javax.sql.DataSource;
public class DataSourceFactory {
public DataSource create() {
// Database-specific, non-pooling, non-enlisting javax.sql.XADataSource
org.postgresql.ds.PGSimpleDataSource postgresqlDs = new org.postgresql.ds.PGSimpleDataSource();
postgresqlDs.setUrl("jdbc:postgresql://localhost:5432/db");
postgresqlDs.setUser("user");
postgresqlDs.setPassword("password");
postgresqlDs.setCurrentSchema("schema");
postgresqlDs.setConnectTimeout(5);
// Non database-specific, generic, pooling, non-XA factory for javax.sql.DataSource instances
org.apache.commons.dbcp2.DataSourceConnectionFactory dataSourceConnectionFactory
= new org.apache.commons.dbcp2.DataSourceConnectionFactory(postgresqlDs);
// Non database-specific factory for org.apache.commons.dbcp2.PoolableConnection objects
org.apache.commons.dbcp2.PoolableConnectionFactory poolableConnectionFactory
= new org.apache.commons.dbcp2.PoolableConnectionFactory(dataSourceConnectionFactory, null);
poolableConnectionFactory.setMaxConnLifetimeMillis(30000);
poolableConnectionFactory.setValidationQuery("select schema_name, schema_owner from information_schema.schemata");
poolableConnectionFactory.setValidationQueryTimeout(2);
// Pooling configuration
org.apache.commons.pool2.impl.GenericObjectPoolConfig poolConfig
= new org.apache.commons.pool2.impl.GenericObjectPoolConfig();
poolConfig.setMinIdle(2);
poolConfig.setMaxTotal(10);
poolConfig.setTestOnBorrow(true);
// Non database-specific pool of objects provided by PoolableConnectionFactory
org.apache.commons.pool2.impl.GenericObjectPool<org.apache.commons.dbcp2.PoolableConnection> pool
= new org.apache.commons.pool2.impl.GenericObjectPool<>(poolableConnectionFactory, poolConfig);
// And finally non database-specific, generic, pooling javax.sql.DataSource
org.apache.commons.dbcp2.PoolingDataSource<org.apache.commons.dbcp2.PoolableConnection> dataSource
= new org.apache.commons.dbcp2.PoolingDataSource<>(pool);
return dataSource;
}
}
而使用更简单的蓝图是这样的:
<bean id="factory" class="com.example.DataSourceFactory">
<!-- You can configure properties for your factory here -->
</bean>
<bean id="dataSource" factory-ref="factory" factory-method="create" />
<service interface="javax.sql.DataSource" ref="dataSource">
<service-properties>
<entry key="osgi.jndi.service.name" value="jdbc/ds" />
</service-properties>
</service>
推荐阅读
- php - 教义 ORM:未定义的偏移量 2
- 32bit-64bit - RISC-V 32/64 位兼容性问题
- dynamic - 基于度量的数据查找
- machine-learning - 如何将一对向量提供给分类器以对相似/不相似进行分类
- powershell - 尝试编写 PS 脚本将数据从多个 .csv 转储到单个 csv
- powershell - 为什么变量不会更新?
- tsqlt - 如何避免 tSQLt 在 raiserror 之后中止调用的过程?
- mysql - MySQL 错误 1032 “在表中找不到记录”
- flexbox - 如何在flexlayoutgap中使整体卡的间隙均匀
- c# - MSTest v2 有序测试