我就简单说下步骤吧!
一、数据库数据源配置表
我是将数据源的配置全部放在数据库中,这样就可以随时增删改查数据源了,这里表结构我是写了两种数据库的配置:“Mysql,Sqlserver”,数据库表截图如下:
二、初始化动态数据源管理类
package com.baofoo.admin.service.sys.data;
import java.beans.PropertyVetoException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.commons.lang.builder.ToStringBuilder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import com.baofoo.admin.dao.sys.data.IDynamicDataSourceDao;
import com.baofoo.admin.entity.sys.data.AdminDataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
* 初始化创建数据源
* @author zhour
*
*/
public class DynamicDataSourceManager {
Logger logger = LoggerFactory.getLogger(DynamicDataSourceManager.class);
@Autowired
private IDynamicDataSourceDao dynamicDataSourceDao;
//
private Map<Integer, ComboPooledDataSource> dataSourcePoolMap = new HashMap<Integer, ComboPooledDataSource>();
/**
* 初始化加载创建数据源连接池
*/
public void init() {
logger.info("-------------->开始初始化加载创建动态数据源...");
//获取所有数据源配置信息
List<AdminDataSource> dataSourceList = dynamicDataSourceDao.listAdminDataSource();
for(AdminDataSource adminDataSource : dataSourceList) {
//
createDataSourcePool(adminDataSource);
}
logger.info("-------------->初始化加载创建动态数据源完毕,加载数:"+dataSourceList.size());
}
/**
* 创建数据源连接池
* @param adminDataSource
*/
public void createDataSourcePool(AdminDataSource adminDataSource) {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
try {
comboPooledDataSource.setDriverClass(adminDataSource.getDriver_class());
comboPooledDataSource.setJdbcUrl(adminDataSource.getJdbc_url());
comboPooledDataSource.setUser(adminDataSource.getUser_name());
comboPooledDataSource.setPassword(adminDataSource.getPassword());
//
comboPooledDataSource.setInitialPoolSize(1);
comboPooledDataSource.setMinPoolSize(0);
comboPooledDataSource.setMaxPoolSize(5);
comboPooledDataSource.setAcquireIncrement(2);
comboPooledDataSource.setMaxIdleTime(10);
comboPooledDataSource.setMaxStatements(0);
} catch (PropertyVetoException e) {
e.printStackTrace();
}
this.dataSourcePoolMap.put(adminDataSource.getSource_id(), comboPooledDataSource);
}
/**
* 获取数据源
* @param sourceId
* @return
*/
public JdbcTemplate getDataSourcePoolBySourceID(int source_id) throws Exception {
//
ComboPooledDataSource comboPooledDataSource = this.dataSourcePoolMap.get(source_id);
if(comboPooledDataSource == null) {
logger.info(String.format("未找到[SourceID=%d]对应的数据源,则从数据库重新获取...", source_id));
//未获取到相应的数据源,则从数据库重新获取,来创建新的数据源连接池
AdminDataSource adminDataSource = dynamicDataSourceDao.getAdminDataSourceById(source_id);
if(adminDataSource == null) {
logger.info(String.format("从数据库重新获取,未找到[SourceID=%d]对应的数据源", source_id));
throw new Exception("未获取到匹配的动态数据源[ID="+source_id+"]");
} else if(adminDataSource.getSource_state() != 1) {
logger.info(String.format("[SourceID=%d]对应的数据源状态未开启", source_id));
throw new Exception("匹配的动态数据源状态未开启[ID="+source_id+"]");
} else {
//add
createDataSourcePool(adminDataSource);
//get
comboPooledDataSource = this.dataSourcePoolMap.get(source_id);
}
} else {
logger.info(String.format("已找到[SourceID=%d]对应的数据源!", source_id));
}
//
JdbcTemplate jdbcTempleDynamic = new JdbcTemplate(comboPooledDataSource);
//
return jdbcTempleDynamic;
}
/**
* 关闭所有数据源连接池
*/
public void close() {
Set<Integer> key = dataSourcePoolMap.keySet();
for (Iterator it = key.iterator(); it.hasNext();) {
ComboPooledDataSource comboPooledDataSource = dataSourcePoolMap.get(it.next());
try {
comboPooledDataSource.close();
} catch (Exception e) {
logger.error("关闭连接池异常:comboPooledDataSource="+ToStringBuilder.reflectionToString(comboPooledDataSource));
e.printStackTrace();
}
}
}
}
三、测试类
package com.baofoo.admin.test.dynamicData;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4Cla***unner;
import com.baofoo.admin.service.sys.data.DynamicDataSourceManager;
/**
* 测试动态数据源
* @author zhour
*
*/
@RunWith(SpringJUnit4Cla***unner.class)
@ContextConfiguration(locations = {"classpath*:app-context.xml"})
public class TestDynamicData {
@Autowired
private DynamicDataSourceManager dynamicDataSourceManager;
@Test
public void testData() throws Exception {
//获取数据源连接池
System.out.println("------------------->数据源1");
//
JdbcTemplate jdbcTemplatefwefewf = dynamicDataSourceManager.getDataSourcePoolBySourceID(1);
//
String sql = "SELECT * FROM BAOFOO_ADMIN.admin_login_user ORDER BY user_id DESC LIMIT 1";
List<Map<String, Object>> retList2 = jdbcTemplatefwefewf.queryForList(sql);
for(Map<String, Object> entityMap : retList2) {
System.out.println("-------查询结果:"+entityMap);
}
System.out.println("------------------->数据源2");
JdbcTemplate jdbcTemplatefwefwef = dynamicDataSourceManager.getDataSourcePoolBySourceID(2);
//
List<Map<String, Object>> retList3 = jdbcTemplatefwefwef.queryForList(sql);
for(Map<String, Object> entityMap : retList3) {
System.out.println("-------查询结果:"+entityMap);
}
}
}
总结:这种方式省去了在spring配置文件中配置数据源的麻烦,尤其是用户在页面查询数据时,可以设置他自由切换数据源来查看数据内容,比较方便
转载于:https://blog.51cto.com/peterz2011/1347045