spring动态创建数据源、动态切换数据源
1、创建动态数据源RoutingDataSource
package com.yhbc.datasourceT;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SessionFactory;
import org.springframework.beans.BeansException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.stereotype.Component;
import com.alibaba.druid.pool.DruidDataSource;
//@Component 不能有这个注解 ，xml的bean比他晚生成
public class RoutingDataSource extends AbstractRoutingDataSource {
private Map dataSources = new HashMap<>();
//@Autowired
//@Qualifier("dataSource")//根据xml的id写
private DruidDataSource defaultTargetDataSource ;//主数据源注入
JdbcTemplate jdbcTemplate;
protected SessionFactory sessionFactory;
@Override
protected DataSource determineTargetDataSource() {
// 根据数据库选择方案，拿到要访问的数据库
String dataSourceName = (String) determineCurrentLookupKey();
if(RoutingDataSourceContext.MASTER_DATASOURCE.equals(dataSourceName)) {
// 访问默认主库
return defaultTargetDataSource;
}
// 根据数据库名字，从已创建的数据库中获取要访问的数据库
return (DataSource) this.dataSources.get(dataSourceName);
}
@Override
protected Object determineCurrentLookupKey() {
System.out.println("---determineCurrentLookupKey-----主数据源=="+defaultTargetDataSource);
String dbName = RoutingDataSourceContext.getDataSourceRoutingKey();
if (StringUtils.isEmpty(dbName)) {
return RoutingDataSourceContext.getMainKey();
}
if (!dataSources.containsKey(dbName)){
createAndSaveDataSource(dbName);
}
return dbName;
}
private synchronized void createAndSaveDataSource(String dbName) {
DruidDataSource dataSource = createDruidDataSource(dbName);
dataSources.put(dbName, dataSource);
super.setTargetDataSources(dataSources);
afterPropertiesSet();
}
/**
* 根据配置创建DruidDataSource
* @param fanDataSource
* @return
*/
public DruidDataSource createDruidDataSource(String dbName ) {
if(dataSources.containsKey(dbName)){
return (DruidDataSource) dataSources.get(dbName);
}
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setName(dbName);
dataSource.setUrl("jdbc:mysql://192.168.1.13:3306/"+dbName+"?useUnicode=true&allowMultiQueries=true&useSSL=false");
dataSource.setUsername("root");
dataSource.setPassword("root");
dataSource.setInitialSize(2);
// 从池中取得链接时做健康检查，该做法十分保守
dataSource.setTestOnBorrow(true);
// 如果连接空闲超过1小时就断开
dataSource.setMinEvictableIdleTimeMillis(1 * 60000 * 60);
// 每十分钟验证一下连接
dataSource.setTimeBetweenEvictionRunsMillis(600000);
// 运行ilde链接测试线程，剔除不可用的链接
dataSource.setTestWhileIdle(true);
dataSource.setMaxWait(-1);
return dataSource;
}
/*public RoutingDataSource() {
//创建主库
// createAndSaveDataSource(RoutingDataSourceContext.getMainKey());
dataSources.put(RoutingDataSourceContext.MASTER_DATASOURCE,defaultTargetDataSource );
System.out.println("构造动态数据源，加入主数据源=="+defaultTargetDataSource);
DruidDataSource dataSource = createDruidDataSource();
jdbcTemplate = new JdbcTemplate();
jdbcTemplate.setDataSource(dataSource);
System.out.println("===========testQy========="+testQy());
}*/
/**
* 通过jdbc从数据库中查找数据源配置
* @param name
* @return
*/
private int testQy() {
String sql = "select id from box_function where url='1-supplyCodeList.htm' ";
// RowMapper<FanDataSource> rowMapper = new BeanPropertyRowMapper<>(FanDataSource.class);
int id = jdbcTemplate.queryForInt(sql);
return id;
}
public void setDefaultTargetDataSource(DruidDataSource defaultTargetDataSource) {
System.out.println("---setDefaultTargetDataSource-----主数据源=="+defaultTargetDataSource);
this.defaultTargetDataSource = defaultTargetDataSource;
}
}
2、创建线程切换类RoutingDataSourceContext，用来切换数据库的名称
package com.yhbc.datasourceT;
public class RoutingDataSourceContext {
static final ThreadLocal<String> threadLocalDataSourceKey = new ThreadLocal<>();
public static final String MASTER_DATASOURCE="defaultDataSource";
/**
* 获取主数据库的key
* @return
*/
public static String getMainKey() {
return MASTER_DATASOURCE;
}
/**
* 获取数据库key
* @return
*/
public static String getDataSourceRoutingKey() {
String key = threadLocalDataSourceKey.get();
return key == null ? getMainKey() : key;
}
/**
* 设置数据库的key
* @param key
*/
public static void setThreadLocalDataSourceKey(String key) {
threadLocalDataSourceKey.set(key);
}
}
3、关键的一步，数据源关联session
仔细观察哦，routingDataSource在bean中，赋予sessionFactory
<beans:bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<beans:property name="driverClassName" value="${silverbox.driver}" />
<beans:property name="url" value="${silverbox.url}" />
<beans:property name="username" value="${silverbox.username}" />
<beans:property name="password" value="${silverbox.password}" />
<beans:property name="maxActive" value="${silverbox.maxActive}" />
<beans:property name="validationQuery" value="${silverbox.testSql}"/>
<beans:property name="testWhileIdle" value="true"/>
<beans:property name="initialSize" value="${silverbox.initialSize}"/>
<beans:property name="maxWait" value="${silverbox.maxWait}"/>
</beans:bean>
<beans:bean id="routingDataSource" class="com.yhbc.datasourceT.RoutingDataSource">
<beans:property name="targetDataSources">
<beans:map key-type="java.lang.String">
<beans:entry key="defaultDataSource" value-ref="dataSource" />
</beans:map>
</beans:property>
<beans:property name="defaultTargetDataSource" ref="dataSource" ></beans:property>
</beans:bean>
<beans:bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<!-- <beans:property name="dataSource" ref="dataSource" /> -->
<beans:property name="dataSource" ref="routingDataSource" />
<beans:property name="packagesToScan">
<beans:list>
<beans:value>com.yhbc.entity</beans:value>
</beans:list>
</beans:property>
<beans:property name="hibernateProperties">
<beans:value>
hibernate.dialect= org.hibernate.dialect.MySQLDialect
hibernate.show_sql=false
hibernate.format_sql=false
hibernate.query.substitutions=true 1, false 0
hibernate.jdbc.batch_size=20
hibernate.cache.use_query_cache=false
hibernate.cache.use_second_level_cache=true
hibernate.cache.region.factory_class=org.hibernate.cache.ehcache.EhCacheRegionFactory
hibernate.search.default.directory_provider=filesystem
hibernate.search.default.indexBase=d:\\index
</beans:value>
</beans:property>
</beans:bean>
4、测试，接下来在control中切换调用：
@RequestMapping(value = { "/hybd-findfunction.htm" }, produces = "text/html;charset=UTF-8")
@ResponseBody
public String findfunction(String functionUsername,String dbtype) {
JSONObject jo=new JSONObject();
log.info("-----------------------------/hybd-add.htm");
try {
//silverbox_backup
/*Dbs.setDbType("backup");
dynamicDataSource.determineTargetDataSource();*/
if(dbtype!=null){
//切换数据源，dbtype是数据库名称哦，这里的所有数据源默认都是相同的host，只是库的区别
RoutingDataSourceContext.setThreadLocalDataSourceKey(dbtype);
}
System.out.println("sessionFactory==="+sessionFactory.toString());
List<?> list=this.hYBDService.findFunction(functionUsername);
jo.put("msg", "添加成功");
jo.put("list", list);
jo.put("success", true);
} catch (Exception e) {
jo.put("success", false);
e.printStackTrace();
log.error("error---hybd-add.htm-----:"+Utils.log4jDetail(e));
}
return jo.toJSONString();
}
亲测通过哦，如果有用请多多支持哦
