通过AOP实现Spring+ibatis 多数据源配置
Spring实现多数据源动态切换主要是基于继承AbstractRoutingDataSource类, 通过AOP切点动态设置并获取数据源的。在运行时, 根据某种key值来动态切换到真正的DataSource上, 代码入侵性较低。
1. 创建类继承AbstractRoutingDataSource类,获取spring动态数据源切换能力;
工程目录:
定义多个数据源的路由相关key值, 在此我简单定义两个:datasource1 和 datasource2。为了方便添加自定义逻辑, 重写数据源选择方法determineCurrentLookupKey()。
package com.zhujq.learn.dynamic;
import org.apache.log4j.Logger;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* 继承spring AbstractRoutingDataSource类实现动态数据源切换
* @author jinQiang.zhu
*
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
public static final String MY_DATASOURCE_ONE = "datasource1";//和spring动态数据源配置中的key一致
public static final String MY_DATASOURCE_TWO = "datasource2";//和spring动态数据源配置中的key一致
@Override
protected Object determineCurrentLookupKey() {
String dbType = DatabaseContextHolder.getDBType();
String className = super.getClass().getCanonicalName();
log.info("determineCurrentLookupKey() - dbType=" + dbType + ", className=" + className);
return dbType;
}
private Logger log = Logger.getLogger(this.getClass());
}
2. 定义一个DatabaseContextHolder, 用于提供当前数据源的设置, 获取及清理能力
package com.zhujq.learn.dynamic;
import org.apache.log4j.Logger;
/**
* 数据动态管理类,动态设置,获取, 清理当前实际数据源。
* @author jinQiang.zhu
*
*/
public class DatabaseContextHolder {
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
public static void setDBType(String dbType) {
log.info("setDBType() - " + dbType);
contextHolder.set(dbType);
}
public static String getDBType() {
return contextHolder.get();
}
public static void clearDBType() {
contextHolder.remove();
}
private static Logger log = Logger.getLogger(DatabaseContextHolder.class);
}
3. 设置AOP切面, 使得DAO层方法执行时,触发AOP切点动态设置当前数据源;
我分别为两个DAO UserLevelTemplateDAOImpl和MktMessageMysqlDAOImpl设置不同的切点, 自定义数据源选取的规则, 代码如下:
package com.zhujq.learn.aop;
import org.apache.log4j.Logger;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.AfterReturning;
import org.aspectj.lang.annotation.AfterThrowing;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
import com.zhujq.learn.dynamic.DatabaseContextHolder;
import com.zhujq.learn.dynamic.DynamicDataSource;
@Component
@Aspect
public class UserLevelDataSourceAdvice {
@Pointcut("execution(* com.zhujq.learn.mysql.level.dao.impl.*.*(..))")
public void anyMethod() {}
@Before(value = "execution(* com.zhujq.learn.mysql.level.dao.impl.*.*(..))")
public void doBefore(JoinPoint jp) {
//全限定类名中包含“.level.”的, 如className=com.zhujq.learn.mysql.level.dao.impl.UserLevelTemplateDAOImpl
String className = jp.getTarget().getClass().getCanonicalName();
int index = className.indexOf(".level.");
log.info("doBefore() - className=" + className + ", index=" + index);
if (index > 0) {
DatabaseContextHolder.setDBType(DynamicDataSource.MY_DATASOURCE_ONE);
} else {
DatabaseContextHolder.setDBType(DynamicDataSource.MY_DATASOURCE_ONE);//未匹配时根据实际情况选择数据源
}
}
@AfterReturning(value = "anyMethod()", returning = "result")
public void doAfter(JoinPoint jp, Object result) {
String className = jp.getTarget().getClass().getCanonicalName();
log.info("doAfter() - className=" + className);
DatabaseContextHolder.clearDBType();
}
/**
* 异常通知
*
* @param jp
* @param e
*/
@AfterThrowing(value = "execution(* com.cn21.edrive.mysql.persistence.oracle.user.dao.impl.*.*(..))", throwing = "e")
public void doThrow(JoinPoint jp, Throwable e) {
String className = jp.getTarget().getClass().getCanonicalName();
log.warn("doThrow() - className=" + className, e);
DatabaseContextHolder.clearDBType();
}
/**
* 日志服务
*/
private Logger log = Logger.getLogger(this.getClass());
}
package com.zhujq.learn.aop;
import org.apache.log4j.Logger;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.AfterReturning;
import org.aspectj.lang.annotation.AfterThrowing;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
import com.zhujq.learn.dynamic.DatabaseContextHolder;
import com.zhujq.learn.dynamic.DynamicDataSource;
@Component
@Aspect
public class UserMessageDataSourceAdvice {
@Pointcut("execution(* com.zhujq.learn.mysql.message.dao.impl.*.*(..))")
public void anyMethod() {}
@Before(value = "execution(* com.zhujq.learn.mysql.message.dao.impl.*.*(..))")
public void doBefore(JoinPoint jp) {
String className = jp.getTarget().getClass().getCanonicalName();
//全限定类名中包含“.level.”的, 如className=com.zhujq.learn.mysql.level.dao.impl.UserLevelTemplateDAOImpl
int index = className.indexOf(".message.");
log.info("doBefore() - className=" + className + ", index=" + index);
if (index > 0) {
DatabaseContextHolder.setDBType(DynamicDataSource.MY_DATASOURCE_TWO);
} else {
DatabaseContextHolder.setDBType(DynamicDataSource.MY_DATASOURCE_ONE);
}
}
@AfterReturning(value = "anyMethod()", returning = "result")
public void doAfter(JoinPoint jp, Object result) {
String className = jp.getTarget().getClass().getCanonicalName();
log.info("doAfter() - className=" + className);
DatabaseContextHolder.clearDBType();
}
/**
* 异常通知
*
* @param jp
* @param e
*/
@AfterThrowing(value = "execution(* com.cn21.edrive.mysql.persistence.oracle.user.dao.impl.*.*(..))", throwing = "e")
public void doThrow(JoinPoint jp, Throwable e) {
String className = jp.getTarget().getClass().getCanonicalName();
log.warn("doThrow() - className=" + className, e);
DatabaseContextHolder.clearDBType();
}
/**
* 日志服务
*/
private Logger log = Logger.getLogger(this.getClass());
}
4. Spring配置:
<?xml version="1.0" encoding="UTF-8" ?>
<beans
xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd"
default-autowire="byName">
<context:component-scan base-package="com.zhujq.learn" />
<aop:config proxy-target-class="false"/>
<aop:aspectj-autoproxy />
<!-- 导入数据源1 和 数据源2 等等 -->
<import resource="classpath:datasource1.xml" />
<import resource="classpath:datasource2.xml" />
<bean id="dynamicDataSource" class="com.zhujq.learn.dynamic.DynamicDataSource">
<property name="targetDataSources">
<map>
<entry value-ref="myDataSource1" key="datasource1"></entry>
<entry value-ref="myDataSource2" key="datasource2"></entry>
</map>
</property>
<property name="defaultTargetDataSource" ref="myDataSource1" />
</bean>
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="configLocations">
<list>
<value>classpath:com/zhujq/learn/mysql/level/config/app_sqlMap_config.xml</value>
<value>classpath:com/zhujq/learn/mysql/message/config/app_sqlMap_config.xml</value>
</list>
</property>
<property name="dataSource" ref="dynamicDataSource" />
</bean>
</beans>
两个数据源的配置datasource1.xml 及datasource2.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd">
<bean id="myDataSource1" class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<!--驱动包 -->
<property name="driverClass" value="com.mysql.jdbc.Driver" />
<!--连接路径 -->
<property name="jdbcUrl" value="数据源1 的数据库host,port, database构成的url" />
<property name="user" value="用户账号" />
<property name="password" value="密码" />
<!--初始化时获取三个连接,取值应在minPoolSize与maxPoolSize之间。Default: 3 -->
<property name="minPoolSize" value="5" />
<!--连接池中保留的最大连接数。Default: 15 -->
<property name="maxPoolSize" value="100" />
<!--初始化时启动的连接数 -->
<property name="initialPoolSize" value="5" />
<!--连接的最大闲时间,单位为秒 -->
<property name="maxIdleTime" value="30" />
<!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
<property name="acquireIncrement" value="1" />
<!--定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 -->
<property name="acquireRetryAttempts" value="30" />
<!--两次连接中间隔时间,单位毫秒。Default: 1000 -->
<property name="acquireRetryDelay" value="1000" />
<!--如果设为true那么在取得连接的同时将校验连接的有效性。Default: false -->
<property name="testConnectionOnCheckin" value="false" />
<!--每隔多少秒测试闲连接 -->
<property name="idleConnectionTestPeriod" value="10" />
<!--当连接池用完时客户端调用getConnection()后等待获取新连接的时间,超时后将抛出 SQLException,如设为0则无限期等待。单位毫秒。Default: 0 -->
<property name="checkoutTimeout" value="30000" />
<!--连接关闭时默认将所有未提交的操作回滚。Default: false -->
<property name="autoCommitOnClose" value="true" />
<!--定义所有连接测试都执行的测试语句。在使用连接测试的情况下这个一显著提高测试速度-->
<property name="preferredTestQuery" value ="select 1;" />
</bean>
</beans>
<?xml version="1.0" encoding="UTF-8" ?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd">
<bean id="myDataSource2" class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<!--驱动包 -->
<property name="driverClass" value="com.mysql.jdbc.Driver" />
<!--连接路径数据源2 -->
<property name="jdbcUrl" value="jdbc:mysql://10.10.xxx.xxx:xxx/edrivegs1" />
<property name="user" value="用户" />
<property name="password" value="密码" />
<!--初始化时获取三个连接,取值应在minPoolSize与maxPoolSize之间。Default: 3 -->
<property name="minPoolSize" value="5" />
<!--连接池中保留的最大连接数。Default: 15 -->
<property name="maxPoolSize" value="100" />
<!--初始化时启动的连接数 -->
<property name="initialPoolSize" value="5" />
<!--连接的最大闲时间,单位为秒 -->
<property name="maxIdleTime" value="30" />
<!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
<property name="acquireIncrement" value="1" />
<!--定义在从数据库获取新连接失败后重复尝试的次数。Default: 30 -->
<property name="acquireRetryAttempts" value="30" />
<!--两次连接中间隔时间,单位毫秒。Default: 1000 -->
<property name="acquireRetryDelay" value="1000" />
<!--如果设为true那么在取得连接的同时将校验连接的有效性。Default: false -->
<property name="testConnectionOnCheckin" value="false" />
<!--每隔多少秒测试闲连接 -->
<property name="idleConnectionTestPeriod" value="10" />
<!--当连接池用完时客户端调用getConnection()后等待获取新连接的时间,超时后将抛出 SQLException,如设为0则无限期等待。单位毫秒。Default: 0 -->
<property name="checkoutTimeout" value="30000" />
<!--连接关闭时默认将所有未提交的操作回滚。Default: false -->
<property name="autoCommitOnClose" value="true" />
<!--定义所有连接测试都执行的测试语句。在使用连接测试的情况下这个一显著提高测试速度-->
<property name="preferredTestQuery" value ="select 1;" />
</bean>
</beans>
5. 在设置好了AOP及Spring动态数据源切换相关类之后, 整合iBATIS, 生成相关的DAO
定义全局基础DAO接口(选择添加)
package com.zhujq.learn.mysql;
/**
* 基本DAO接口, 用于定义所有DAO均需要的基本方法
* @author jinQiang.zhu
*
* @param <T>
* @param <PK>
*/
public interface BaseDAO<T, PK> {
public T selectByPrimaryKey(T entity);
public int updateByPrimaryKey(T entity);
}
定义全局基础DAO实现, 整合iBATIS必须继承
package com.zhujq.learn.mysql;
import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
/**
* 基本DAO接口实现类, 继承org.springframework.orm.ibatis.support.SqlMapClientDaoSupport 完成spring和iBATIS的整合,
* Spring 提供SqlMapClientDaoSupport对象,我们的DAO可以继承这个类,通过它所提供的SqlMapClientTemplate对象来操纵数据库
*
* @author jinQiang.zhu
*
*/
public class BaseDAOImpl<T, PK> extends SqlMapClientDaoSupport implements BaseDAO<T, PK> {
@Override
public T selectByPrimaryKey(T entity) {
// TODO Auto-generated method stub
return null;
}
@Override
public int updateByPrimaryKey(T entity) {
// TODO Auto-generated method stub
return 0;
}
}
(1) UserLevelTemplateDAOImpl相关model实体类,DAO接口,DAO实现,sqlmap映射文件等。
package com.zhujq.learn.mysql.level.dao.impl;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.zhujq.learn.mysql.BaseDAOImpl;
import com.zhujq.learn.mysql.level.dao.UserLevelTemplateDAO;
import com.zhujq.learn.mysql.level.dao.model.UserLevelTemplate;
@Repository
public class UserLevelTemplateDAOImpl extends BaseDAOImpl<UserLevelTemplate, Long> implements UserLevelTemplateDAO {
private Logger log = Logger.getLogger(this.getClass());
/**
* 继承SqlMapClientDaoSupport,要求我们注入SqlMapClient对象
*/
@Resource(name = "sqlMapClient")
private SqlMapClient sqlMapClient;
@PostConstruct
public void injectSqlMapClient() {
super.setSqlMapClient(sqlMapClient);
}
@Override
public UserLevelTemplate insert(UserLevelTemplate userLevelTemplate) {
log.debug("insert() - userLevelTemplate=" + userLevelTemplate);
if (userLevelTemplate == null|| userLevelTemplate.getUserLevelTemplateId()== null) {
log.warn("insert() - userLevelTemplate=" + userLevelTemplate + ", invalid arguments. ");
return null;
}
String code = UserLevelTemplate.class.getName() + ".insertSelective";
getSqlMapClientTemplate().insert(code, userLevelTemplate);
return userLevelTemplate;
}
}
DAO接口:
package com.zhujq.learn.mysql.level.dao;
import com.zhujq.learn.mysql.level.dao.model.UserLevelTemplate;
public interface UserLevelTemplateDAO {
public UserLevelTemplate insert(UserLevelTemplate userLevelTemplate);
}
sqlmap映射文件集中配置app_sqlMap_config.xml:user_level_template_SqlMap.xml 实际的表映射文件,实际开发中app_sqlMap_config.xml可配置多个表映射文件。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd" >
<sqlMapConfig >
<settings useStatementNamespaces="true" cacheModelsEnabled="false" enhancementEnabled="true" lazyLoadingEnabled="true" errorTracingEnabled="true" maxSessions="200" maxTransactions="50" maxRequests="500" />
<sqlMap resource="com/zhujq/learn/mysql/level/dao/sqlmap/user_level_template_SqlMap.xml" />
</sqlMapConfig>
(2) MktMessageMysqlDAOImpl相关model实体类,DAO接口,DAO实现,sqlmap映射文件等同UserLevelTemplateDAOImpl类似配置。
6. 测试插入UserLevelTemplate表, 日志显示动态选择数据源1 datasource1:
测试插入MktMessage表, 日志显示动态选择数据源2 datasource2: