环境：SpringMVC + Spring + JdbcTemplate、MySQL5.6.12
jdbc.properties：
#主数据库
jdbcM.url=jdbc:mysql://192.168.1.1:3306/test?useUnicode=true&characterEncoding=UTF-8
jdbcM.username=root
jdbcM.password=123456
#从数据库
jdbcS.url=jdbc:mysql://192.168.1.2:3306/test?useUnicode=true&characterEncoding=UTF-8
jdbcS.username=root
jdbcS.password=123456
spring-context.xml：
<!-- 定义数据源Bean -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
<!-- 连接数据库的驱动 -->
<property name="driverClass" value="${jdbc.driverClass}" />
<!-- 当连接池中的连接耗尽的时候c3p0一次同时获取的连接数 -->
<property name="acquireIncrement" value="${c3p0.acquireIncrement}" />
<!-- 初始化连接池时连接的数量 -->
<property name="initialPoolSize" value="${c3p0.initialPoolSize}" />
<!-- 连接池中保留的最小连接数 -->
<property name="minPoolSize" value="${c3p0.minPoolSize}" />
<!-- 连接池中保留的最大连接数 -->
<property name="maxPoolSize" value="${c3p0.maxPoolSize}" />
<!-- 控制数据源内加载的PreparedStatements数量 -->
<property name="maxStatements" value="${c3p0.maxStatements}" />
<!-- maxStatementsPerConnection定义了连接池内单个连接所拥有的最大缓存statements数。Default: 0 -->
<!-- <property name="maxStatementsPerConnection" value="${c3p0.maxStatementsPerConnection}"/> -->
<!-- 定义连接最大空闲时间,多长时间内连接未使用则连接被丢弃 -->
<property name="maxIdleTime" value="${c3p0.maxIdleTime}" />
<!-- 多长时间检查所有连接池中的空闲连接 -->
<property name="idleConnectionTestPeriod" value="${c3p0.idleConnectionTestPeriod}" />
<!-- 定义在从数据库获取新连接失败后重复尝试的次数 -->
<property name="acquireRetryAttempts" value="${c3p0.acquireRetryAttempts}" />
<!-- 获取连接失败后该数据源将申明已断开并永久关闭 -->
<property name="breakAfterAcquireFailure" value="${c3p0.breakAfterAcquireFailure}" />
<!-- 每个connection提交的时候是否校验其有效性 -->
<property name="testConnectionOnCheckout" value="${c3p0.testConnectionOnCheckout}" />
</bean>
<!-- 主数据库 -->
<bean id="dataSourceM" parent="dataSource">
<property name="jdbcUrl" value="${jdbcM.url}" />
<property name="user" value="${jdbcM.username}" />
<property name="password" value="${jdbcM.password}" />
</bean>
<!-- 从数据库 -->
<bean id="dataSourceS" parent="dataSource">
<property name="jdbcUrl" value="${jdbcS.url}" />
<property name="user" value="${jdbcS.username}" />
<property name="password" value="${jdbcS.password}" />
</bean>
<!-- 启用CGliB --><!-- 如果有配置SpringMVC.xml文件，因为父子容器的原因这个配置也要加入到SpringMVC.xml>
<aop:aspectj-autoproxy/>
<!--动态数据源、主从库选择-->
<bean id="dynamicDataSource" class="com.su.util.DynamicDataSource">
<property name="master" ref="dataSourceM"/>
<property name="slaves">
<list>
<ref bean="dataSourceS"/>
</list>
</property>
</bean>
<!-- JDBC配置 -->
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource">
<ref bean="dynamicDataSource" />
</property>
</bean>
<!-- JDBC配置 -->
<bean id="namedParameterJdbcTemplate"
class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
<constructor-arg ref="dynamicDataSource" />
</bean>
<!-- 配置事务管理器 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource">
<ref bean="dataSourceM" />
</property>
</bean>
自定义类：DataSourceAspect：
public class DynamicDataSource extends AbstractRoutingDataSource {
private DataSource master;
private List<DataSource> slaves;
private AtomicLong slaveCount = new AtomicLong();
private int slaveSize = 0;
private Map<Object, Object> dataSources = new HashMap<Object, Object>();
private static final ThreadLocal<LinkedList<String>> TARGET_DATA_SOURCE = new ThreadLocal<LinkedList<String>>() {
@Override
protected LinkedList<String> initialValue() {
return new LinkedList<String>();
}
};
private static final String DEFAULT = "master";// 主数据库的Key
private static final String SLAVE = "slave";// 从数据库的Key
/**
* 初始化
*/
@Override
public void afterPropertiesSet() {
if (null == master) {
throw new IllegalArgumentException("主数据库加载失败！");
}
dataSources.put(DEFAULT, master);
if (slaves != null && !slaves.isEmpty()) {
for (int i = 0; i < slaves.size(); i++) {
dataSources.put(SLAVE + (i + 1), slaves.get(i));
}
}
slaveSize = slaves.size();
this.setDefaultTargetDataSource(master);
this.setTargetDataSources(dataSources);
super.afterPropertiesSet();
}
/**
* 选择使用主库，并把选择放到当前ThreadLocal的栈顶
* */
public static void useMaster() {
LinkedList<String> m = TARGET_DATA_SOURCE.get();
m.offerFirst(DEFAULT);
}
/**
* 选择使用从库，并把选择放到当前ThreadLocal的栈顶
* */
public static void useSlave() {
LinkedList<String> m = TARGET_DATA_SOURCE.get();
m.offerFirst(SLAVE);
}
/**
* 重置当前栈
* */
public static void reset() {
LinkedList<String> m = TARGET_DATA_SOURCE.get();
if (!m.isEmpty()) {
m.poll();
}
}
/**
* 如果是选择使用从库，且从库的数量大于1，则通过取模来控制从库的负载 <br/>
* 计算结果返回AbstractRoutingDataSource
*/
@Override
protected Object determineCurrentLookupKey() {
LinkedList<String> m = TARGET_DATA_SOURCE.get();
String key = m.peekFirst() == null ? "" : m.peekFirst();
if (key == null) {
return null;
}
if (DEFAULT.equals(key)) {
return key;
} else if (SLAVE.equals(key)) {
if (slaveSize > 1) {
long c = slaveCount.incrementAndGet();
c = c % slaveSize;
return SLAVE + (c + 1);
} else {
return SLAVE + "1";
}
}
return null;
}
public DataSource getMaster() {
return master;
}
public void setMaster(DataSource master) {
this.master = master;
}
public List<DataSource> getSlaves() {
return slaves;
}
public void setSlaves(List<DataSource> slaves) {
this.slaves = slaves;
}
}
自定义注解 DataSourceChange：
@Inherited
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSourceChange {
boolean slave() default false;
}
Spring AOP切面 DataSourceAspect：
该类一定要注册，@Component注解式或者在spring配置文件配置，选一种就可以了
<!--切面注入spring自定义标签-->
<bean id="dsChangeAspect" class="com.su.util.DataSourceAspect"/>
@Aspect
@Component
public class DataSourceAspect {
@Pointcut("@annotation(com.su.util.DataSourceChange)")
public void changeDS() {
}
@Around(value = "changeDS()", argNames = "pjp")
public Object doAround(ProceedingJoinPoint pjp) throws Throwable {
Object retVal = null;
MethodSignature ms = (MethodSignature) pjp.getSignature();
Method method = ms.getMethod();
DataSourceChange annotation = method
.getAnnotation(DataSourceChange.class);
boolean selectedDataSource = false;
try {
if (annotation != null) {
selectedDataSource = true;
if (annotation.slave()) {
DynamicDataSource.useSlave();
} else {
DynamicDataSource.useMaster();
}
}
retVal = pjp.proceed();
} catch (Throwable e) {
throw e;
} finally {
if (selectedDataSource) {
DynamicDataSource.reset();
}
}
return retVal;
}
}
如果以上都配置正确，有{@DataSourceChange}注解的方法，调用时会切换到指定的数据源。
