Spring，JOTM，Mybatis多数据源动态配置和使用

鞠泰平

2023-12-01

最近项目中用到了多数据源，同时或者不同场景下操作不同的数据库，故而总结此篇文章。

JOTM (Java Open Transaction Manager)是由ObjectWeb协会开发的功能完整的且资源开放的独立的事务管理器。完全分布式事务支持.如果数据层、业务层、表示层运行在不同的 JVM 上，则有可能有一个全程的事务跨度这些JVM，事务的内容在 RMI/JRMP 和 RMI/IIOP 上传播。并且Spring对JOTM提供了较好的支持。

1.配置基础数据源（默认连接的数据库为常用数据库）：jdbc.properties:

#oracle
portal.jdbc.driverClass=oracle.jdbc.driver.OracleDriver
portal.jdbc.jdbcUrl=jdbc:oracle:thin:@1.1.1.1:1521:orapub
portal.jdbc.user=testuser
portal.jdbc.password=123456

#JOTM分布式数据源连接池
#最小连接数
jdbc.minPoolSize=0
#最大连接数
jdbc.maxPoolSize=200
#PoolKeeper检测时间间隔
jdbc.sleepTime=120
#连接生命周期（上次访问时间-当前时间）
jdbc.lifeTime=120
#超过最大连接之后的调用getConnection的等待时间
jdbc.deadLockMaxWait=120
#超过最大连接之后的调用getConnection等待，在等待中重试的时间间隔
jdbc.deadLockRetryWait=100

2.数据源、事务配置文件spring.xml

<bean id="jotm" class="com.utils.jotmutil.JotmFactoryBean">
<property name="defaultTimeout" value="500000"/>
</bean>
<bean id="myJtaManager"
class="org.springframework.transaction.jta.JtaTransactionManager">
<property name="userTransaction">
<ref local="jotm"/>
</property>
</bean>

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.hoo.**.mapper" />
<property name="sqlSessionTemplateBeanName" value="sqlSessionTemplate"/>
</bean>

<bean id="sqlSessionTemplate" class="com.utils.jotmutil.CustomSqlSessionTemplate">
<constructor-arg ref="portalSqlSessionFactory" />
<property name="targetSqlSessionFactorys">
<map> </map>
</property>
</bean>


<bean id="portal" class="org.enhydra.jdbc.pool.StandardXAPoolDataSource" destroy-method="shutdown">
<property name="dataSource">
<bean class="org.enhydra.jdbc.standard.StandardXADataSource" destroy-method="shutdown">
<property name="transactionManager" ref="jotm"/>
<property name="driverName" value="${portal.jdbc.driverClass}"/>
<property name="url" value="${portal.jdbc.jdbcUrl}"/>
</bean>
</property>
<property name="user" value="${portal.jdbc.user}"/>
<property name="password" value="${portal.jdbc.password}"/>
<property name="minSize" value="${jdbc.minPoolSize}"/>
<property name="maxSize" value="${jdbc.maxPoolSize}"/>
<property name="sleepTime" value="${jdbc.sleepTime}"/>
<property name="lifeTime" value="${jdbc.lifeTime}"/>
<property name="deadLockMaxWait" value="${jdbc.deadLockMaxWait}"/>
<property name="deadLockRetryWait" value="${jdbc.deadLockRetryWait}"/>
</bean>

<bean id="portalSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="portal"/>
<property name="mapperLocations" value="classpath*:com/hoo/**/*.xml" />
</bean>

<bean id="jdbcTemplate"
class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" >
<ref bean="portal"/>
</property>
</bean>

<bean id="applicationEventListener" class="com.common.core.DynamicCreateDataSourceBean">
<property name="jdbcTemplate" ref="jdbcTemplate" ></property>
<property name="sqlSessionTemplate" ref="sqlSessionTemplate" ></property>

</bean >

<aop:config>
<aop:pointcut id="serviceOperation" expression="execution(* com.service.*.*(..))"/>
<aop:advisor pointcut-ref="serviceOperation" advice-ref="txAdvice"/>
</aop:config>

<tx:advice id="txAdvice" transaction-manager="myJtaManager">
<tx:attributes>
<tx:method name="del*" propagation="REQUIRED" read-only="false"
rollback-for="java.lang.Exception" no-rollback-for="java.lang.RuntimeException" />
<tx:method name="insert*" propagation="REQUIRED" read-only="false"
rollback-for="java.lang.Exception" />
<tx:method name="update*" propagation="REQUIRED" read-only="false"
rollback-for="java.lang.Exception" />
<tx:method name="add*" propagation="REQUIRED" read-only="false"
rollback-for="java.lang.Exception" />
<tx:method name="modify*" propagation="REQUIRED" read-only="false"
rollback-for="java.lang.Exception" />
<tx:method name="correction*" propagation="REQUIRED" read-only="false"
rollback-for="java.lang.Exception" />

<tx:method name="find*" propagation="SUPPORTS" />
<tx:method name="query*" propagation="SUPPORTS" />
<tx:method name="get*" propagation="SUPPORTS" />
<tx:method name="select*" propagation="SUPPORTS" />
<tx:method name="list*" propagation="SUPPORTS" />
<tx:method name="*" propagation="SUPPORTS" />
</tx:attributes>
</tx:advice>

3.DynamicCreateDataSourceBean 动态注入数据源

public class DynamicCreateDataSourceBean implements ApplicationContextAware,
ApplicationListener<ApplicationEvent> {

private static final String DBFILE_PATH = "/jdbc.properties";

private ConfigurableApplicationContext app;

private JdbcTemplate jdbcTemplate;

private CustomSqlSessionTemplate sqlSessionTemplate;

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}

public void setSqlSessionTemplate(CustomSqlSessionTemplate sqlSessionTemplate) {
this.sqlSessionTemplate = sqlSessionTemplate;
}

@Override
public void setApplicationContext(ApplicationContext app)
throws BeansException {
this.app = (ConfigurableApplicationContext)app;
}

@Override
public void onApplicationEvent(ApplicationEvent event) {
// 如果是容器刷新事件OR Start Event
if (event instanceof ContextRefreshedEvent) {
try {
regDynamicBean();
} catch (IOException e) {
e.printStackTrace();
}
// System.out.println(event.getClass().getSimpleName()+" 事件已发生！");
}

}

private void regDynamicBean() throws IOException {
// 解析属性文件，得到数据源Map
Map<String, DbConfig> mapCustom = parsePropertiesFile();
// 把数据源bean注册到容器中
addSqlSessionFactorySourceToApp(mapCustom);
}

/**
* 功能说明：GET ALL SM_STATIONS FROM DB1 多个数据源配置账号相关存在数据库中，此方法获取多数据源配置
* 本次用id_company来标识不同数据源
* @return
* @throws IOException
*/
@SuppressWarnings("rawtypes")
private Map<String, DbConfig> parsePropertiesFile()
throws IOException {

String sql = "SELECT ID_COMPANY,DRIVERCLASS,JDBCURL,USERNAME,PASSWORD FROM CRS_DB_CONFIG WHERE IS_DEL=0 AND IS_ENABLED=10091020";

List list = jdbcTemplate.queryForList(sql);
Iterator iterator = list.iterator();
Map<String, DbConfig> mds = new HashMap<String, DbConfig>();
while (iterator.hasNext()) {
Map map4station = (Map) iterator.next();
DbConfig dsi = new DbConfig();
dsi.setIdCompany((String)map4station.get("ID_COMPANY"));
dsi.setDriverclass((String)map4station.get("DRIVERCLASS"));
dsi.setJdbcurl((String)map4station.get("JDBCURL"));
dsi.setUsername((String)map4station.get("USERNAME"));
dsi.setPassword((String)map4station.get("PASSWORD"));
mds.put(dsi.getIdCompany(), dsi);
}
return mds;
}

private void addSqlSessionFactorySourceToApp(Map<String, DbConfig> mapCustom){
DefaultListableBeanFactory acf = (DefaultListableBeanFactory) app.getAutowireCapableBeanFactory();
PropertyUtil propertyUtil = new PropertyUtil();
String minSize = propertyUtil.getProperty(DBFILE_PATH, "jdbc.minPoolSize");
String maxSize = propertyUtil.getProperty(DBFILE_PATH, "jdbc.maxPoolSize");
String sleepTime = propertyUtil.getProperty(DBFILE_PATH, "jdbc.sleepTime");
String lifeTime = propertyUtil.getProperty(DBFILE_PATH, "jdbc.lifeTime");
String deadLockMaxWait = propertyUtil.getProperty(DBFILE_PATH, "jdbc.deadLockMaxWait");
String deadLockRetryWait = propertyUtil.getProperty(DBFILE_PATH, "jdbc.deadLockRetryWait");

String DATASOURCE_BEAN_CLASS = "org.enhydra.jdbc.pool.StandardXAPoolDataSource";

BeanDefinitionBuilder bdb;
BeanDefinitionBuilder bdb_child;
BeanDefinitionBuilder bdb_ssf;

Iterator<String> iter = mapCustom.keySet().iterator();

Map<Object, SqlSessionFactory> targetSqlSessionFactorys = new LinkedHashMap<Object, SqlSessionFactory>();

// 将默认SqlSessionFactory放入 targetSqlSessionFactorys map中
targetSqlSessionFactorys.put("portal", (SqlSessionFactory)app.getBean("portalSqlSessionFactory"));

while (iter.hasNext()) {
// bean ID
String beanKey = iter.next();
// 创建连接池bean
bdb = BeanDefinitionBuilder.rootBeanDefinition(DATASOURCE_BEAN_CLASS);
bdb.getBeanDefinition().setAttribute("id", beanKey);
bdb.getBeanDefinition().setAttribute("destroy-method", "shutdown");
//创建数据源
bdb_child = BeanDefinitionBuilder.rootBeanDefinition("org.enhydra.jdbc.standard.StandardXADataSource");
bdb_child.getBeanDefinition().setAttribute("destroy-method", "shutdown");
bdb_child.addPropertyValue("transactionManager", app.getBean("jotm"));
bdb_child.addPropertyValue("driverName", mapCustom.get(beanKey).getDriverclass());
bdb_child.addPropertyValue("url", mapCustom.get(beanKey).getJdbcurl());
acf.registerBeanDefinition("ds_child" + beanKey, bdb_child.getBeanDefinition());

bdb.addPropertyValue("dataSource", app.getBean("ds_child" + beanKey));

bdb.addPropertyValue("user", mapCustom.get(beanKey).getUsername());
bdb.addPropertyValue("password", mapCustom.get(beanKey).getPassword());
bdb.addPropertyValue("minSize", Integer.parseInt(minSize));
bdb.addPropertyValue("maxSize", Integer.parseInt(maxSize));
bdb.addPropertyValue("sleepTime", Integer.parseInt(sleepTime));
bdb.addPropertyValue("lifeTime", Integer.parseInt(lifeTime));
bdb.addPropertyValue("deadLockMaxWait", Integer.parseInt(deadLockMaxWait));
bdb.addPropertyValue("deadLockRetryWait", Integer.parseInt(deadLockRetryWait));
// 注册连接池bean
acf.registerBeanDefinition("ds" + beanKey, bdb.getBeanDefinition());

//创建SqlSessionFactory
bdb_ssf = BeanDefinitionBuilder.rootBeanDefinition("org.mybatis.spring.SqlSessionFactoryBean");
bdb_ssf.getBeanDefinition().setAttribute("id", "sqlSessionFactory" + beanKey);
bdb_ssf.addPropertyValue("dataSource", bdb.getBeanDefinition());
bdb_ssf.addPropertyValue("mapperLocations", "classpath*:com/**/*.xml");
//注册SqlSessionFactory
acf.registerBeanDefinition("sqlSessionFactory" + beanKey, bdb_ssf.getBeanDefinition());
targetSqlSessionFactorys.put(beanKey, (SqlSessionFactory)app.getBean("sqlSessionFactory" + beanKey));
}
sqlSessionTemplate.setTargetSqlSessionFactorys(targetSqlSessionFactorys);
sqlSessionTemplate.setDefaultTargetSqlSessionFactory((SqlSessionFactory)app.getBean("portalSqlSessionFactory"));
}
}

4.CustomSqlSessionTemplate

/**
* <b>function:</b> 继承SqlSessionTemplate 重写相关方法
*/
public class CustomSqlSessionTemplate extends SqlSessionTemplate {

private final SqlSessionFactory sqlSessionFactory;
private final ExecutorType executorType;
private final SqlSession sqlSessionProxy;
private final PersistenceExceptionTranslator exceptionTranslator;

private Map<Object, SqlSessionFactory> targetSqlSessionFactorys;
private SqlSessionFactory defaultTargetSqlSessionFactory;

public void setTargetSqlSessionFactorys(Map<Object, SqlSessionFactory> targetSqlSessionFactorys) {
this.targetSqlSessionFactorys = targetSqlSessionFactorys;
}

public void setDefaultTargetSqlSessionFactory(SqlSessionFactory defaultTargetSqlSessionFactory) {
this.defaultTargetSqlSessionFactory = defaultTargetSqlSessionFactory;
}

public CustomSqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
this(sqlSessionFactory, sqlSessionFactory.getConfiguration().getDefaultExecutorType());
}

public CustomSqlSessionTemplate(SqlSessionFactory sqlSessionFactory, ExecutorType executorType) {
this(sqlSessionFactory, executorType, new MyBatisExceptionTranslator(sqlSessionFactory.getConfiguration()
.getEnvironment().getDataSource(), true));
}

public CustomSqlSessionTemplate(SqlSessionFactory sqlSessionFactory, ExecutorType executorType,
PersistenceExceptionTranslator exceptionTranslator) {

super(sqlSessionFactory, executorType, exceptionTranslator);

this.sqlSessionFactory = sqlSessionFactory;
this.executorType = executorType;
this.exceptionTranslator = exceptionTranslator;

this.sqlSessionProxy = (SqlSession) newProxyInstance(
SqlSessionFactory.class.getClassLoader(),
new Class[] { SqlSession.class },
new SqlSessionInterceptor());

this.defaultTargetSqlSessionFactory = sqlSessionFactory;
}

@Override
public SqlSessionFactory getSqlSessionFactory() {

SqlSessionFactory targetSqlSessionFactory = targetSqlSessionFactorys.get(CustomerContextHolder.getContextType());
if (targetSqlSessionFactory != null) {
return targetSqlSessionFactory;
} else if (defaultTargetSqlSessionFactory != null) {
return defaultTargetSqlSessionFactory;
} else {
Assert.notNull(targetSqlSessionFactorys, "Property 'targetSqlSessionFactorys' or 'defaultTargetSqlSessionFactory' are required");
Assert.notNull(defaultTargetSqlSessionFactory, "Property 'defaultTargetSqlSessionFactory' or 'targetSqlSessionFactorys' are required");
}
return this.sqlSessionFactory;
}

@Override
public Configuration getConfiguration() {
return this.getSqlSessionFactory().getConfiguration();
}

public ExecutorType getExecutorType() {
return this.executorType;
}

public PersistenceExceptionTranslator getPersistenceExceptionTranslator() {
return this.exceptionTranslator;
}

/**
* {@inheritDoc}
*/
public <T> T selectOne(String statement) {
return this.sqlSessionProxy.<T> selectOne(statement);
}

/**
* {@inheritDoc}
*/
public <T> T selectOne(String statement, Object parameter) {
return this.sqlSessionProxy.<T> selectOne(statement, parameter);
}

/**
* {@inheritDoc}
*/
public <K, V> Map<K, V> selectMap(String statement, String mapKey) {
return this.sqlSessionProxy.<K, V> selectMap(statement, mapKey);
}

/**
* {@inheritDoc}
*/
public <K, V> Map<K, V> selectMap(String statement, Object parameter, String mapKey) {
return this.sqlSessionProxy.<K, V> selectMap(statement, parameter, mapKey);
}

/**
* {@inheritDoc}
*/
public <K, V> Map<K, V> selectMap(String statement, Object parameter, String mapKey, RowBounds rowBounds) {
return this.sqlSessionProxy.<K, V> selectMap(statement, parameter, mapKey, rowBounds);
}

/**
* {@inheritDoc}
*/
public <E> List<E> selectList(String statement) {
return this.sqlSessionProxy.<E> selectList(statement);
}

/**
* {@inheritDoc}
*/
public <E> List<E> selectList(String statement, Object parameter) {
return this.sqlSessionProxy.<E> selectList(statement, parameter);
}

/**
* {@inheritDoc}
*/
public <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds) {
return this.sqlSessionProxy.<E> selectList(statement, parameter, rowBounds);
}

/**
* {@inheritDoc}
*/
public void select(String statement, ResultHandler handler) {
this.sqlSessionProxy.select(statement, handler);
}

/**
* {@inheritDoc}
*/
public void select(String statement, Object parameter, ResultHandler handler) {
this.sqlSessionProxy.select(statement, parameter, handler);
}

/**
* {@inheritDoc}
*/
public void select(String statement, Object parameter, RowBounds rowBounds, ResultHandler handler) {
this.sqlSessionProxy.select(statement, parameter, rowBounds, handler);
}

/**
* {@inheritDoc}
*/
public int insert(String statement) {
return this.sqlSessionProxy.insert(statement);
}

/**
* {@inheritDoc}
*/
public int insert(String statement, Object parameter) {
return this.sqlSessionProxy.insert(statement, parameter);
}

/**
* {@inheritDoc}
*/
public int update(String statement) {
return this.sqlSessionProxy.update(statement);
}

/**
* {@inheritDoc}
*/
public int update(String statement, Object parameter) {
return this.sqlSessionProxy.update(statement, parameter);
}

/**
* {@inheritDoc}
*/
public int delete(String statement) {
return this.sqlSessionProxy.delete(statement);
}

/**
* {@inheritDoc}
*/
public int delete(String statement, Object parameter) {
return this.sqlSessionProxy.delete(statement, parameter);
}

/**
* {@inheritDoc}
*/
public <T> T getMapper(Class<T> type) {
return getConfiguration().getMapper(type, this);
}

/**
* {@inheritDoc}
*/
public void commit() {
throw new UnsupportedOperationException("Manual commit is not allowed over a Spring managed SqlSession");
}

/**
* {@inheritDoc}
*/
public void commit(boolean force) {
throw new UnsupportedOperationException("Manual commit is not allowed over a Spring managed SqlSession");
}

/**
* {@inheritDoc}
*/
public void rollback() {
throw new UnsupportedOperationException("Manual rollback is not allowed over a Spring managed SqlSession");
}

/**
* {@inheritDoc}
*/
public void rollback(boolean force) {
throw new UnsupportedOperationException("Manual rollback is not allowed over a Spring managed SqlSession");
}

/**
* {@inheritDoc}
*/
public void close() {
throw new UnsupportedOperationException("Manual close is not allowed over a Spring managed SqlSession");
}

/**
* {@inheritDoc}
*/
public void clearCache() {
this.sqlSessionProxy.clearCache();
}

/**
* {@inheritDoc}
*/
public Connection getConnection() {
return this.sqlSessionProxy.getConnection();
}

/**
* {@inheritDoc}
* @since 1.0.2
*/
public List<BatchResult> flushStatements() {
return this.sqlSessionProxy.flushStatements();
}

/**
* Proxy needed to route MyBatis method calls to the proper SqlSession got from Spring's Transaction Manager It also
* unwraps exceptions thrown by {@code Method#invoke(Object, Object...)} to pass a {@code PersistenceException} to
* the {@code PersistenceExceptionTranslator}.
*/
private class SqlSessionInterceptor implements InvocationHandler {
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
final SqlSession sqlSession = getSqlSession(
CustomSqlSessionTemplate.this.getSqlSessionFactory(),
CustomSqlSessionTemplate.this.executorType,
CustomSqlSessionTemplate.this.exceptionTranslator);
try {
Object result = method.invoke(sqlSession, args);
if (!isSqlSessionTransactional(sqlSession, CustomSqlSessionTemplate.this.getSqlSessionFactory())) {
// force commit even on non-dirty sessions because some databases require
// a commit/rollback before calling close()
sqlSession.commit(true);
}
return result;
} catch (Throwable t) {
Throwable unwrapped = unwrapThrowable(t);
if (CustomSqlSessionTemplate.this.exceptionTranslator != null && unwrapped instanceof PersistenceException) {
Throwable translated = CustomSqlSessionTemplate.this.exceptionTranslator
.translateExceptionIfPossible((PersistenceException) unwrapped);
if (translated != null) {
unwrapped = translated;
}
}
throw unwrapped;
} finally {
closeSqlSession(sqlSession, CustomSqlSessionTemplate.this.getSqlSessionFactory());
}
}
}

}

5.CustomerContextHolder线程中存放数据源ID也就是id_company

/**
* <b>function:</b> 多数据源
*/
public abstract class CustomerContextHolder {

public final static String SESSION_FACTORY_PORTAL1 = "portal1";

private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

public static void setContextType(String contextType) {
contextHolder.set(contextType);
}

public static String getContextType() {
return contextHolder.get();
}

public static void clearContextType() {
contextHolder.remove();
}
}

使用数据库之前先将id_company放到线程中，等提交事务获取数据库连接时，会从线程中根据id_company拿到想要的链接。

Spring，JOTM，Mybatis多数据源动态配置和使用

1.配置基础数据源（默认连接的数据库为常用数据库）：jdbc.properties:

2.数据源、事务配置文件spring.xml

3.DynamicCreateDataSourceBean 动态注入数据源

4.CustomSqlSessionTemplate

5.CustomerContextHolder线程中存放数据源ID也就是id_company

相关阅读

相关文章

相关问答

相关文档