开发项目用到数据库 db2 某个功能需要从SQL server数据库取数据 对比
在此把代码整理 发上来 不然 都忘了
一、配置文件
注意: 数据库连接池 看项目不同 做对应调整即可
<!-- 启动AspectJ支持 只对扫描过的bean有效-->
<aop:aspectj-autoproxy proxy-target-class="true" />
<!-- base dataSource -->
<bean name="baseDataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="initialSize" value="${jdbc.initialSize}"/>
<property name="minIdle" value="${jdbc.minIdle}"/>
<property name="maxIdle" value="${jdbc.maxIdle}"/>
<property name="maxActive" value="${jdbc.maxActive}"/>
<property name="maxWait" value="${jdbc.maxWait}"/>
<property name="defaultAutoCommit" value="${jdbc.defaultAutoCommit}"/>
<property name="removeAbandoned" value="${jdbc.removeAbandoned}"/>
<property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}"/>
<property name="testWhileIdle" value="${jdbc.testWhileIdle}"/>
<property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}"/>
<property name="numTestsPerEvictionRun" value="${jdbc.numTestsPerEvictionRun}"/>
<property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}"/>
</bean>
<!-- 主库 DB2 -->
<bean name="master-dataSource" parent="baseDataSource">
<property name="driverClassName" value="${jdbc.db2.driver}"/>
<property name="url" value="${jdbc.db2.url}" />
<property name="username" value="${jdbc.db2.username}" />
<property name="password" value="${jdbc.db2.password}" />
</bean>
<!-- 从库 SQL Server -->
<bean name="slave-dataSource" parent="baseDataSource">
<property name="driverClassName" value="${jdbc.sqlserver.driver}"/>
<property name="url" value="${jdbc.sqlserver.url}" />
<property name="username" value="${jdbc.sqlserver.username}" />
<property name="password" value="${jdbc.sqlserver.password}" />
</bean>
<!--主从库选择 -->
<bean id="dynamicDataSource" class="com.icss.barcode.monitoring.common.datasource.DynamicDataSource">
<property name="master" ref="master-dataSource" />
<property name="slaves" ref="slave-dataSource"/>
</bean>
<!-- 定义事务 -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dynamicDataSource" />
</bean>
<!-- 配置 Annotation 驱动,扫描@Transactional注解的类定义事务 -->
<tx:annotation-driven transaction-manager="transactionManager" proxy-target-class="true"/>
配置文件设置
#============================================================================
# 通用配置
#============================================================================
jdbc.initialSize=5
jdbc.minIdle=5
jdbc.maxIdle=20
jdbc.maxActive=100
jdbc.maxWait=100000
jdbc.defaultAutoCommit=false
jdbc.removeAbandoned=true
jdbc.removeAbandonedTimeout=600
jdbc.testWhileIdle=true
jdbc.timeBetweenEvictionRunsMillis=60000
jdbc.numTestsPerEvictionRun=20
jdbc.minEvictableIdleTimeMillis=300000
#============================================================================
# DB2
#============================================================================
jdbc.db2.driver=com.ibm.db2.jcc.DB2Driver
##本地
jdbc.db2.url=jdbc:db2://localhost:50000/barcode
jdbc.db2.username=db2inst1
jdbc.db2.password=db2admin
#============================================================================
# MS SQL Server
#============================================================================
jdbc.sqlserver.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.sqlserver.url=jdbc:sqlserver://127.0.0.1:1401
jdbc.sqlserver.username=sa
jdbc.sqlserver.password=gptrbrt5683RR
数据库安装:
注解 实现类
package com.icss.barcode.monitoring.common.datasource;
import com.icss.barcode.monitoring.common.annotation.DataSourceChange;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
/**
* aop 注解 选择数据库
* @author Haiming
* @date 2020/8/7 11:40 AM
*/
@Aspect
@Component
public class DataSourceAspect {
private static final Logger LOGGER = LoggerFactory.getLogger(DataSourceAspect.class);
@Around("@annotation(dataSourceChange)")
public Object doAround(ProceedingJoinPoint pjp, DataSourceChange dataSourceChange) {
Object retVal = null;
boolean selectedDataSource = false;
try {
if (null != dataSourceChange) {
selectedDataSource = true;
if (dataSourceChange.slave()) {
DynamicDataSource.useSlave();
} else {
DynamicDataSource.useMaster();
}
}
retVal = pjp.proceed();
} catch (Throwable e) {
LOGGER.warn("数据源切换错误", e);
throw new RuntimeException("数据源切换错误", e);
} finally {
if (selectedDataSource) {
DynamicDataSource.reset();
}
}
return retVal;
}
}
package com.icss.barcode.monitoring.common.datasource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.Map;
/**
* 配置主从数据源后,根据选择,返回对应的数据源
* https://blog.csdn.net/itdragons/article/details/52578892
* @author Haiming
* @date 2020/8/7 11:01 AM
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
private static final Logger LOGGER = LoggerFactory.getLogger(DynamicDataSource.class);
// 主库,只允许有一个
private DataSource master;
private DataSource slaves;
private static final String DEFAULT = "master";
private static final String SLAVE = "slave";
private Map<Object, Object> dataSources = new HashMap<Object, Object>();
private static final ThreadLocal<LinkedList<String>> datasourceHolder = new ThreadLocal<LinkedList<String>>() {
@Override
protected LinkedList<String> initialValue() {
return new LinkedList<String>();
}
};
/**
* 初始化
*/
@Override
public void afterPropertiesSet() {
if (null == master) {
throw new IllegalArgumentException("Property 'master' is required");
}
dataSources.put(DEFAULT, master);
dataSources.put(SLAVE, slaves);
this.setDefaultTargetDataSource(master);
this.setTargetDataSources(dataSources);
super.afterPropertiesSet();
}
/**
* 选择使用主库,并把选择放到当前ThreadLocal的栈顶
*/
public static void useMaster() {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("use datasource :" + datasourceHolder.get());
}
LinkedList<String> m = datasourceHolder.get();
m.offerFirst(DEFAULT);
}
/**
* 选择使用从库,并把选择放到当前ThreadLocal的栈顶
*/
public static void useSlave() {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("use datasource :" + datasourceHolder.get());
}
LinkedList<String> m = datasourceHolder.get();
m.offerFirst(SLAVE);
}
/**
* 重置当前栈
*/
public static void reset() {
LinkedList<String> m = datasourceHolder.get();
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("reset datasource {}", m);
}
if (m.size() > 0) {
m.poll();
}
}
/**
* 如果是选择使用从库
* 计算结果返回AbstractRoutingDataSource
*/
@Override
protected Object determineCurrentLookupKey() {
LinkedList<String> m = datasourceHolder.get();
String key = m.peekFirst() == null ? DEFAULT : m.peekFirst();
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("currenty datasource :" + key);
}
if (null != key) {
if (DEFAULT.equals(key)) {
return key;
} else if (SLAVE.equals(key)) {
return SLAVE;
}
return null;
} else {
return null;
}
}
public DataSource getMaster() {
return master;
}
public void setMaster(DataSource master) {
this.master = master;
}
public DataSource getSlaves() {
return slaves;
}
public void setSlaves(DataSource slaves) {
this.slaves = slaves;
}
}
package com.icss.barcode.monitoring.common.annotation;
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;
/**
* 切换数据库注解
* @author haiming
*/
@Inherited
@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface DataSourceChange {
boolean slave() default false;
}
实际使用 添加注解即可: @DataSourceChange(slave = true)
/**
* 查询S_BILL_BASE信息
*
* @param bbId S_BILL_BASEID
* @return S_BILL_BASE信息
*/
@Override
@DataSourceChange(slave = true)
public SBillBase selectSBillBaseById(Long bbId){
return sBillBaseMapper.selectSBillBaseById(bbId);
}
@Override
@DataSourceChange(slave = true)
public List<SBillBase> selectSBillBaseBybbContactNo(List<String> list) {
return sBillBaseMapper.selectSBillBaseBybbContactNo(list);
}
/**
* 查询S_BILL_BASE列表
*
* @param sBillBase S_BILL_BASE信息
* @return S_BILL_BASE集合
*/
@Override
@DataSourceChange(slave = true)
public List<SBillBase> selectSBillBaseList(SBillBase sBillBase){
return sBillBaseMapper.selectSBillBaseList(sBillBase);
}
如有问题 欢迎留言
码云主页:https://gitee.com/TomYule
GitHub: https://github.com/TomYule
如果对您有帮助 欢迎点赞 收藏 转发