多数据源在项目中很多的应用,比如同步另一个数据库的数据等运用。
方法一:使用spring JdbcTemplate 和druid连接池进行操作
DynamicDBDao封装JdbcTemplate常用操作,自己可以进行扩展。当然也可以使用apche-common的dbutil进行操作。
import java.util.List;
import java.util.Map;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.lang3.ArrayUtils;
import org.springframework.jdbc.core.JdbcTemplate;
public class DynamicDBDao {
private JdbcTemplate jdbcTemplate;
public DynamicDBDao() {
}
public DynamicDBDao(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
/**
*
* @title: getJdbcTemplate
* @description:不滿足方便获取操作
* @return
* @return: JdbcTemplate
*/
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void initJdbcTemplate(BasicDataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public List<Map<String, Object>> queryList(String sql, Object... param) {
List<Map<String, Object>> list;
if (ArrayUtils.isEmpty(param)) {
list = jdbcTemplate.queryForList(sql);
} else {
list = jdbcTemplate.queryForList(sql, param);
}
return list;
}
public <T> List<T> queryList(String sql, Class<T> clazz, Object... param) {
List<T> list;
if (ArrayUtils.isEmpty(param)) {
list = jdbcTemplate.queryForList(sql, clazz);
} else {
list = jdbcTemplate.queryForList(sql, clazz, param);
}
return list;
}
}
DynamicDBUtils 多数据源操作工具
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.lang3.StringEscapeUtils;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import cn.jeeweb.core.database.dynamic.dao.DynamicDBDao;
import cn.jeeweb.core.utils.SpringContextHolder;
import cn.jeeweb.modules.sys.entity.DataSource;
import cn.jeeweb.modules.sys.service.IDataSourceService;
/**
* @description: 多数据源操作工具 List<Map<String, Object>> list =
* DynamicDBUtils.getDynamicDBDao("neiwangbaogong") .queryList(
* "SELECT * from t_s_type");
*/
public class DynamicDBUtils {
private static IDataSourceService dataSourceService = SpringContextHolder.getBean(IDataSourceService.class);
public static DynamicDBDao getDynamicDBDao(String dbKey) {
DynamicDBDao dynamicDBDao = new DynamicDBDao();
/**使用mybatis-plus条件构造器,请自行建立controller、entity、service、mapper,实现增删改查。**/
if (dataSource == null) {
return null;
}
dynamicDBDao.initJdbcTemplate(getDataSource(dataSource));
//以下实现了dbcp连接池和druid连接池,如喜欢druid请自行更换为 getDruidDateSouce(dataSource)
return dynamicDBDao;
}
//使用dbcp连接池来进行操作。优化spring jdbcTemplate.
private static BasicDataSource getDataSource(DataSource dataSourceEntity) {
BasicDataSource dataSource = new BasicDataSource();
String driverClassName = dataSourceEntity.getDriverClass();
String url = StringEscapeUtils.unescapeHtml4(dataSourceEntity.getUrl());
String dbUser = dataSourceEntity.getDbUser();
String dbPassword = dataSourceEntity.getDbPassword();
dataSource.setDriverClassName(driverClassName);
dataSource.setUrl(url);
dataSource.setUsername(dbUser);
dataSource.setPassword(dbPassword);
return dataSource;
}
//使用druid连接池来进行操作
private static DruidDataSource getDruidDateSouce(DataSource dataSource){
DruidDataSource druidDataSource=new DruidDataSource();
String driverClassName = dataSource.getDriverClass();
String url = StringEscapeUtils.unescapeHtml4(dataSource.getUrl());
String dbUser = dataSource.getDbUser();
String dbPassword = dataSource.getDbPassword();
druidDataSource.setDriverClassName(driverClassName);
druidDataSource.setUrl(url);
druidDataSource.setUsername(dbUser);
druidDataSource.setPassword(dbPassword);
return druidDataSource;
}
}
方法二:使用 spring的AbstractRoutingDataSource接口类
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
/**
* @Description: 使用AbstractRoutingDataSource实现多数据源
* @Author: wbb
* @CreateDate: 2018/2/12 11:06
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return DataSourceContextHolder.getDBType();
}
}
/**
* @Description: 多数据源
* @Author: wbb
* @CreateDate: 2018/2/11 18:00
*/
public class DataSourceContextHolder {
private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
public static void setDBType(String dbType) {
contextHolder.set(dbType);
}
public static String getDBType() {
return ((String) contextHolder.get());
}
public static void clearDBType() {
contextHolder.remove();
}
}
<bean id="mysqldataSource" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">
<!-- 基本属性 url、user、password -->
<property name="url" value="${connection.url}" />
<property name="username" value="${connection.username}" />
<property name="password" value="${connection.password}" />
<property name="dbType" value = "${connection.dbType}" />
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="${druid.initialSize}" />
<property name="minIdle" value="${druid.minIdle}" />
<property name="maxActive" value="${druid.maxActive}" />
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="${druid.maxWait}" />
<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="${druid.timeBetweenEvictionRunsMillis}" />
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="${druid.minEvictableIdleTimeMillis}" />
<property name="validationQuery" value="${druid.validationQuery}" />
<property name="testWhileIdle" value="${druid.testWhileIdle}" />
<property name="testOnBorrow" value="${druid.testOnBorrow}" />
<property name="testOnReturn" value="${druid.testOnReturn}" />
<!-- 打开PSCache,并且指定每个连接上PSCache的大小 如果用Oracle,则把poolPreparedStatements配置为true,mysql可以配置为false。 -->
<property name="poolPreparedStatements" value="${druid.poolPreparedStatements}" />
<property name="maxPoolPreparedStatementPerConnectionSize"
value="${druid.maxPoolPreparedStatementPerConnectionSize}" />
<!-- 配置监控统计拦截的filters -->
<property name="filters" value="${druid.filters}" />
</bean>
<!--sqlserver数据源-->
<bean id="sqlserverdataSource" class="com.alibaba.druid.pool.DruidDataSource"
init-method="init" destroy-method="close">
<!-- 基本属性 url、user、password -->
<property name="url" value="${sqlserver.connection.url}" />
<property name="username" value="${sqlserver.connection.username}" />
<property name="password" value="${sqlserver.connection.password}" />
<property name="dbType" value = "${sqlserver.connection.dbType}" />
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="${druid.initialSize}" />
<property name="minIdle" value="${druid.minIdle}" />
<property name="maxActive" value="${druid.maxActive}" />
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="${druid.maxWait}" />
<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="${druid.timeBetweenEvictionRunsMillis}" />
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="${druid.minEvictableIdleTimeMillis}" />
<property name="validationQuery" value="${druid.validationQuery}" />
<property name="testWhileIdle" value="${druid.testWhileIdle}" />
<property name="testOnBorrow" value="${druid.testOnBorrow}" />
<property name="testOnReturn" value="${druid.testOnReturn}" />
<!-- 打开PSCache,并且指定每个连接上PSCache的大小 如果用Oracle,则把poolPreparedStatements配置为true,mysql可以配置为false。 -->
<property name="poolPreparedStatements" value="${druid.poolPreparedStatements}" />
<property name="maxPoolPreparedStatementPerConnectionSize"
value="${druid.maxPoolPreparedStatementPerConnectionSize}" />
<!-- 配置监控统计拦截的filters -->
<property name="filters" value="${druid.filters}" />
</bean>
<bean id="sqlSessionFactory" class="com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean">
<!-- 配置数据源 -->
<property name="dataSource" ref="dataSource"/>
<!-- 自动扫描 Xml 文件位置 -->
<property name="mapperLocations" value="classpath:/mappings/**/*.xml"/>
<!-- 配置 Mybatis 配置文件(可无) -->
<property name="configLocation" value="classpath:mybatis-config.xml"/>
<property name="configurationProperties">
<props>
<prop key="dbType">${connection.dbType}</prop>
</props>
</property>
<!-- 配置包别名 -->
<property name="typeAliasesPackage" value="cn.jeeweb.modules.*.entity"/>
<!-- 以上配置和传统 Mybatis 一致 -->
<!-- 插件配置 -->
<property name="plugins">
<array>
<!-- 分页插件配置, 参考文档分页插件部分!! -->
<!-- 如需要开启其他插件,可配置于此 -->
</array>
</property>
<!-- MP 全局配置注入 -->
<property name="globalConfig" ref="globalConfig"/>
</bean>
<bean id="dataSource" class="cn.jeeweb.core.database.dynamic.DynamicDataSource">
<property name="targetDataSources">
<map key-type="java.lang.String">
<entry value-ref="mysqldataSource" key="mysqldataSource"></entry>
<entry value-ref="sqlserverdataSource" key="sqlserverdataSource"></entry>
</map>
</property>
<!-- 默认使用dataSourceSig的数据源 -->
<property name="defaultTargetDataSource" ref="mysqldataSource"></property>
</bean>