摘要: Spring JDBC_多数据源和事务的配置
Spring JDBC_多数据源和事务的配置
直接帖代码：
maven项目，以下是pom：
<dependencies>
<dependency>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jdbc</artifactId>
<version>8.0.14</version>
</dependency>
<!--spring context -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.1.1.RELEASE</version>
</dependency>
<!--spring core -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.1.1.RELEASE</version>
</dependency>
<!--spring bean -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>4.1.1.RELEASE</version>
</dependency>
<!--spring aop -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>4.1.1.RELEASE</version>
</dependency>
<!--spring jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.1.1.RELEASE</version>
</dependency>
<!-- spring tx -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.1.1.RELEASE</version>
</dependency>
<!-- spring test -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>4.1.1.RELEASE</version>
</dependency>
<!-- spring-batch-core -->
<dependency>
<groupId>org.springframework.batch</groupId>
<artifactId>spring-batch-core</artifactId>
<version>3.0.2.RELEASE</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.10</version>
<scope>test</scope>
</dependency>
<!--aspectj -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjrt</artifactId>
<version>1.8.2</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.8.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
</dependencies>主要说一下我的思路：
首先配置多个数据源，继承spring 的AbstractRoutingDataSource 实现管理多数据源
然后考虑怎么实现动态选择多数据源，使用ThreadLocal，在线程中传递参数，通过对应的数据源的key动态的选择多数据源
以上就是基本思路，但还要考虑如何管理事务。这里考虑使用@Transactional标签管理事务，当你配置好事务，并且在代码中动态选择数据源时，发现根本不好使，原因就是你开启了事务，早在你动态选择数据源之前，开启的事务就已经连接了数据库，等到执行具体的sql语句时，选择的数据源总是事务中连接的数据库，下面是DataSourceUtils.java中的一段代码，通过该代码，你可以看到具体是如何选择数据库连接的：
public static Connection doGetConnection(DataSource dataSource) throws SQLException {
Assert.notNull(dataSource, "No DataSource specified");
ConnectionHolder conHolder = (ConnectionHolder) TransactionSynchronizationManager.getResource(dataSource);
if (conHolder != null && (conHolder.hasConnection() || conHolder.isSynchronizedWithTransaction())) {
conHolder.requested();
if (!conHolder.hasConnection()) {
logger.debug("Fetching resumed JDBC Connection from DataSource");
conHolder.setConnection(dataSource.getConnection());
}
return conHolder.getConnection();
}
// Else we either got no holder or an empty thread-bound holder here.
logger.debug("Fetching JDBC Connection from DataSource");
Connection con = dataSource.getConnection();
if (TransactionSynchronizationManager.isSynchronizationActive()) {
logger.debug("Registering transaction synchronization for JDBC Connection");
// Use same Connection for further JDBC actions within the transaction.
// Thread-bound object will get removed by synchronization at transaction completion.
ConnectionHolder holderToUse = conHolder;
if (holderToUse == null) {
holderToUse = new ConnectionHolder(con);
}
else {
holderToUse.setConnection(con);
}
holderToUse.requested();
TransactionSynchronizationManager.registerSynchronization(
new ConnectionSynchronization(holderToUse, dataSource));
holderToUse.setSynchronizedWithTransaction(true);
if (holderToUse != conHolder) {
TransactionSynchronizationManager.bindResource(dataSource, holderToUse);
}
}
return con;
}好了，思路就又来了，既然在开启事务的时候就选择了数据源，那么必须要在事务开启之前选择数据源：很简单的做法，其实就是在开启事务的方法上加上切面，实现动态选择数据源。
下面是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:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
<aop:aspectj-autoproxy />
<!-- 定义包的扫描规则 -->
<context:component-scan base-package="com.lyx">
<context:include-filter type="annotation"
expression="org.springframework.stereotype.Service" />
<context:include-filter type="annotation"
expression="org.springframework.stereotype.Repository" />
<context:include-filter type="annotation"
expression="org.springframework.stereotype.Component" />
</context:component-scan>
<!--tomcat jdbc pool数据源配置 -->
<bean id="localDataSource" class="org.apache.tomcat.jdbc.pool.DataSource"
destroy-method="close">
<property name="poolProperties">
<bean class="org.apache.tomcat.jdbc.pool.PoolProperties">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://10.255.9.79:3306/local_database" />
<property name="username" value="root" />
<property name="password" value="034039" />
</bean>
</property>
</bean>
<!--tomcat jdbc pool数据源配置 -->
<bean id="threadDataSource" class="org.apache.tomcat.jdbc.pool.DataSource"
destroy-method="close">
<property name="poolProperties">
<bean class="org.apache.tomcat.jdbc.pool.PoolProperties">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://10.255.9.79:3306/thread_database" />
<property name="username" value="root" />
<property name="password" value="034039" />
</bean>
</property>
</bean>
<!--tomcat jdbc pool数据源配置 -->
<bean id="remoteDataSource" class="org.apache.tomcat.jdbc.pool.DataSource"
destroy-method="close">
<property name="poolProperties">
<bean class="org.apache.tomcat.jdbc.pool.PoolProperties">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://10.255.9.79:3306/remote_database" />
<property name="username" value="root" />
<property name="password" value="034039" />
</bean>
</property>
</bean>
<!--使用枚举类型作为key -->
<bean id="dataSource" class="com.lyx.DynamicDataSource">
<property name="targetDataSources">
<map key-type="com.lyx.DataSourceLookupKey">
<entry key="REMOTE_DATASOURCE" value-ref="remoteDataSource" />
<entry key="THREAD_DATASOURCE" value-ref="threadDataSource" />
<entry key="LOCAL_DATASOURCE" value-ref="localDataSource" />
</map>
</property>
<property name="defaultTargetDataSource" ref="localDataSource" />
</bean>
<!-- spring的事务管理器 -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<!-- 动态选择数据源的切面 -->
<bean id = "determineDataSourceAspect" class="com.lyx.DetermineDataSourceAspect"/>
<!-- 切面的配置 -->
<aop:config>
<aop:aspect id="determineDataSourceAspect" ref="determineDataSourceAspect">
<aop:pointcut id="dataSourcePoint"
expression="@annotation(com.lyx.RequireDataSource)" />
<aop:before method="determineDataSource" pointcut-ref="dataSourcePoint" />
</aop:aspect>
</aop:config>
<tx:annotation-driven transaction-manager="transactionManager" />
</beans>看到这里，你可能会问，为什么没有使用aspect的风格来配置切面，实现aop，答案就是那种方式不好用，@Transactional代理比我配置的注解的代理要提前一步，导致动态选择数据源的切面发挥不了作用。而这种方式就可以很好的工作。。
相关的类：
DataSourceLookupKey.java
package com.lyx;
/**
*
* @author Lenovo
*/
public enum DataSourceLookupKey {
LOCAL_DATASOURCE("本地数据库"), REMOTE_DATASOURCE("远程数据库"), THREAD_DATASOURCE(
"THREAD数据库");
private String value;
private DataSourceLookupKey(String value) {
this.value = value;
}
@Override
public String toString() {
return this.value;
}
}DbContextHolder.java
package com.lyx;
/**
*
* @author Lenovo
*/
public class DbContextHolder {
// 利用ThreadLocal解决线程安全问题
private static final ThreadLocal<DataSourceLookupKey> contextHolder = new ThreadLocal<DataSourceLookupKey>();
// 设置数据源
public static void setDbType(DataSourceLookupKey dbType) {
contextHolder.set(dbType);
}
// 获取数据源
public static DataSourceLookupKey getDbType() {
return (DataSourceLookupKey) contextHolder.get();
}
// 清空
public static void clearDbType() {
contextHolder.remove();
}
}DynamicDataSource.java
package com.lyx;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected DataSourceLookupKey determineCurrentLookupKey() {
// TODO Auto-generated method stub
if (!(DbContextHolder.getDbType() == null)) {
System.out.println("====>" + DbContextHolder.getDbType());
} else {
System.out.println("====>没有初始化数据库上下文环境");
}
return DbContextHolder.getDbType();
}
}DetermineDataSourceAspect.java
package com.lyx;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.reflect.MethodSignature;
public class DetermineDataSourceAspect {
public void determineDataSource(JoinPoint point) {
System.out
.println("determine datasource begin>>>>>>>>>>>>>>>>>>>>>>>>>");
RequireDataSource datasource = ((MethodSignature) point.getSignature())
.getMethod().getAnnotation(RequireDataSource.class);
System.out.println("====>" + datasource.name());
DbContextHolder.setDbType(datasource.name());
System.out.println("determine datasource end>>>>>>>>>>>>>>>>>>>>>>>>>");
}
}RequireDataSource.java
package com.lyx;
import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Target({ ElementType.METHOD, ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface RequireDataSource {
/**
* the name of datasource
*
* @return
*/
DataSourceLookupKey name();
}PersonDao.java
package com.lyx;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
@Repository
public class PersonDao {
private JdbcTemplate jdbcTemplate;
@Autowired
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
/**
* 查询记录的个数
*
* @return
*/
public long count() {
long rowCount = this.jdbcTemplate.queryForObject(
"select count(*) from people", Long.class);
return rowCount;
}
/**
* 查询同名的人的个数
*
* @param name
* @return
*/
public long countPeopleByName(String name) {
long countOfActorsNamedJoe = this.jdbcTemplate.queryForObject(
"select count(*) from people where first_name = ?", Long.class,
name);
return countOfActorsNamedJoe;
}
/**
* 根据Id查询姓名
*
* @param id
* @return
*/
public String queryNameById(int id) {
String lastName = this.jdbcTemplate.queryForObject(
"select last_name from t_actor where person_id = ?",
new Object[] { id }, String.class);
return lastName;
}
/**
* 根据Id查找实体
*
* @param id
* @return
*/
public People findPeopleById(int id) {
People people = this.jdbcTemplate.queryForObject(
"select first_name, last_name from people where person_id = ?",
new Object[] { id }, new RowMapper<People>() {
public People mapRow(ResultSet rs, int rowNum)
throws SQLException {
People p = new People();
p.setFirstName(rs.getString("first_name"));
p.setLastName(rs.getString("last_name"));
return p;
}
});
return people;
}
/**
* 分页查询people
*
* @param pageNo
* @param pageSize
* @return
*/
public List<People> findPeopleList(int pageNo, int pageSize) {
int start = (pageNo - 1) * pageSize;
List<People> peoples = this.jdbcTemplate.query(
"select first_name, last_name from people limit ?,?",
new Object[] { start, pageSize }, new RowMapper<People>() {
public People mapRow(ResultSet rs, int rowNum)
throws SQLException {
People p = new People();
p.setFirstName(rs.getString("first_name"));
p.setLastName(rs.getString("last_name"));
return p;
}
});
return peoples;
}
/**
* 分页查询
*
* @param pageNo
* @param pageSize
* @return
*/
public List<People> findPeopleList0(int pageNo, int pageSize) {
int start = (pageNo - 1) * pageSize;
List<People> peoples = this.jdbcTemplate.query(
"select first_name, last_name from people limit ?,?",
new Object[] { start, pageSize }, new PeopleRowMapper());
return peoples;
}
private static class PeopleRowMapper implements RowMapper<People> {
public People mapRow(ResultSet rs, int rowNum) throws SQLException {
// TODO Auto-generated method stub
People p = new People();
p.setFirstName(rs.getString("first_name"));
p.setLastName(rs.getString("last_name"));
return p;
}
}
/**
* 插入实体
*
* @param people
*/
public void addPeople(People people) {
this.jdbcTemplate.update(
"insert into people (first_name, last_name) values (?, ?)",
people.getFirstName(), people.getLastName());
}
/**
* 更新实体
*
* @param id
*/
public void updatePeopleName(int id, String name) {
this.jdbcTemplate
.update("update people set last_name = ? where person_id = ?",
name, id);
}
/**
* 删除实体
*
* @param id
*/
public void deletePeople(int id) {
this.jdbcTemplate.update("delete from people where person_id = ?", id);
}
/**
* 执行sql语句
*
* @param sql
* create table mytable (id integer, name varchar(100))
*/
public void execute(String sql) {
this.jdbcTemplate.execute(sql);
}
}PersonService.java
package com.lyx;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
public class PersonService {
@Autowired
private PersonDao personDao;
@RequireDataSource(name = DataSourceLookupKey.REMOTE_DATASOURCE)
@Transactional
public void addperson() {
System.out.println("-----addPerson begin-----");
System.out.println("====>" + DbContextHolder.getDbType());
People people = new People();
people.setFirstName("adasdfasdfwe");
people.setLastName("adadfeexcsdwadfsafd");
this.personDao.addPeople(people);
System.out.println("-----addPerson end-----");
System.out.println("====>" + DbContextHolder.getDbType());
}
}测试类：
AppMain.java
package com.lyx;
import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.datasource.ConnectionHolder;
import org.springframework.jdbc.datasource.DataSourceUtils.ConnectionSynchronization;
import org.springframework.transaction.support.TransactionSynchronizationManager;
import org.springframework.util.Assert;
public class AppMain {
public static void main(String[] args) {
@SuppressWarnings("resource")
ApplicationContext context = new ClassPathXmlApplicationContext(
new String[] { "classpath:spring-dao.xml" });
PersonService personService = (PersonService) context
.getBean("personService");
personService.addperson();
}
}结果：
determine datasource begin>>>>>>>>>>>>>>>>>>>>>>>>>
====>远程数据库
determine datasource end>>>>>>>>>>>>>>>>>>>>>>>>>
====>远程数据库
-----addPerson begin-----
====>远程数据库
-----addPerson end-----
====>远程数据库
如果你要动态选择数据库，就在该方法上加上注解@RequireDataSource，name值为数据库的枚举值。。
============================END============================
© 著作权归作者所有
