多資料源在項目中很多的應用,比如同步另一個資料庫的資料等運用。
方法一:使用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>