ssm多数据源配置

最新推荐文章于 2023-10-25 14:37:02 发布

一支孤独的98K

最新推荐文章于 2023-10-25 14:37:02 发布

阅读量297

点赞数

CC 4.0 BY-SA版权

文章标签：多数据源配置

本文链接：https://blog.youkuaiyun.com/weixin_41851719/article/details/95629772

博客主要介绍了多数据源配置相关内容，包括如何配置多个数据源，配置 DynamicDataSource，还提及了在业务层的具体写法，为多数据源配置提供了全面的操作指引。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.配置多个数据源

#msql
jdbc.validationQuery=SELECT 1  
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://192.168.1.109:3306/zknwportal?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
jdbc.username=root
jdbc.password=zknw
#jdbc.password=root
#jdbc.password=windows2012
jdbc.maxActive =20
jdbc.minIdle =5
jdbc.maxIdle=10
jdbc.maxWait =60000
jdbc.poolPreparedStatements = true
jdbc.maxPoolPreparedStatementPerConnectionSize =33
jdbc.testOnBorrow = false
jdbc.testOnReturn = false
jdbc.testWhileIdle = true
jdbc.timeBetweenEvictionRunsMillis =60000
jdbc.minEvictableIdleTimeMillis =25200000
jdbc.removeAbandoned = true
jdbc.removeAbandonedTimeout  =1800
jdbc.logAbandoned = true
jdbc.initialSize =0

#oracle
jdbc1.validationQuery=SELECT 1
jdbc1.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc1.url=jdbc:oracle:thin:@//10.38.1.13:1521/bfsorcl
jdbc1.username=bfsmh
jdbc1.password=bfsmh21
jdbc1.maxActive =20
jdbc1.minIdle =5
jdbc1.maxIdle=10
jdbc1.maxWait =60000
jdbc1.poolPreparedStatements = true
jdbc1.maxPoolPreparedStatementPerConnectionSize =33
jdbc1.testOnBorrow = false
jdbc1.testOnReturn = false
jdbc1.testWhileIdle = true
jdbc1.timeBetweenEvictionRunsMillis =60000
jdbc1.minEvictableIdleTimeMillis =25200000
jdbc1.removeAbandoned = true
jdbc1.removeAbandonedTimeout  =1800
jdbc1.logAbandoned = true
jdbc1.initialSize =0


#msql
jdbc2.validationQuery=SELECT 1  
jdbc2.driverClassName=com.mysql.jdbc.Driver
jdbc2.url=jdbc:mysql://192.168.1.109:3306/dcloud_yulin?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
jdbc2.username=root
jdbc2.password=zknw
jdbc2.maxActive =20
jdbc2.minIdle =5
jdbc2.maxIdle=10
jdbc2.maxWait =60000
jdbc2.poolPreparedStatements = true
jdbc2.maxPoolPreparedStatementPerConnectionSize =33
jdbc2.testOnBorrow = false
jdbc2.testOnReturn = false
jdbc2.testWhileIdle = true
jdbc2.timeBetweenEvictionRunsMillis =60000
jdbc2.minEvictableIdleTimeMillis =25200000
jdbc2.removeAbandoned = true
jdbc2.removeAbandonedTimeout  =1800
jdbc2.logAbandoned = true
jdbc2.initialSize =0

#sqlserver
jdbc3.validationQuery=SELECT 1  
jdbc3.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc3.url=jdbc:sqlserver://10.38.1.250:1433;databaseName=pd_spacs
jdbc3.username=sa
jdbc3.password=zknw0928
jdbc3.maxActive =20
jdbc3.minIdle =5
jdbc3.maxIdle=10
jdbc3.maxWait =60000
jdbc3.poolPreparedStatements = true
jdbc3.maxPoolPreparedStatementPerConnectionSize =33
jdbc3.testOnBorrow = false
jdbc3.testOnReturn = false
jdbc3.testWhileIdle = true
jdbc3.timeBetweenEvictionRunsMillis =60000
jdbc3.minEvictableIdleTimeMillis =25200000
jdbc3.removeAbandoned = true
jdbc3.removeAbandonedTimeout  =1800
jdbc3.logAbandoned = true
jdbc3.initialSize =0

#springMVC的路径
versionPath=/v1

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx"
	   xmlns:aop="http://www.springframework.org/schema/aop"
	   xsi:schemaLocation="
	http://www.springframework.org/schema/beans   
	http://www.springframework.org/schema/beans/spring-beans-4.2.xsd   
	http://www.springframework.org/schema/tx   
	http://www.springframework.org/schema/tx/spring-tx-4.2.xsd  
	http://www.springframework.org/schema/aop   
	http://www.springframework.org/schema/aop/spring-aop-4.2.xsd">
	<aop:aspectj-autoproxy proxy-target-class="true"/>
	<!--统一的dataSource-->
	<bean id="dynamicDataSource" class="com.zknw.config.DynamicDataSource">
		<property name="targetDataSources">
			<map key-type="java.lang.String">
				<!--通过不同的key决定用哪个dataSource-->
				<entry value-ref="dataSource" key="dataSource"></entry>
				<entry value-ref="oracleDataSource" key="oracleDataSource"></entry>
				<entry value-ref="inspectionDataSource" key="inspectionDataSource"></entry>
				<entry value-ref="sqlserverDataSource" key="sqlserverDataSource"></entry>
			</map>
		</property>
		<!--设置默认的dataSource-->
		<property name="defaultTargetDataSource" ref="dataSource">
		</property>
	</bean>
	<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate"
		  scope="prototype">
		<constructor-arg index="0" ref="sqlSessionFactory" />
	</bean>

	<!-- 配置数据源 使用的是Druid数据源 -->
	<bean name="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
		  init-method="init" destroy-method="close">
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
		<property name="driverClassName" value="${jdbc.driverClassName}" />
		<!-- 初始化连接大小 -->
		<property name="initialSize" value="${jdbc.initialSize}" />
		<!-- 连接池最大使用连接数量 -->
		<property name="maxActive" value="${jdbc.maxActive}" />

		<!-- 连接池最小空闲 -->
		<property name="minIdle" value="${jdbc.minIdle}" />
		<!-- 获取连接最大等待时间 -->
		<property name="maxWait" value="${jdbc.maxWait}" />
		<property name="poolPreparedStatements" value="true" />
		<property name="maxPoolPreparedStatementPerConnectionSize" value="${jdbc.maxPoolPreparedStatementPerConnectionSize}" />
		<!-- 用来检测有效sql -->
		<property name="validationQuery" value="${jdbc.validationQuery}" />
		<property name="testOnBorrow" value="${jdbc.testOnBorrow}" />
		<property name="testOnReturn" value="${jdbc.testOnReturn}" />
		<property name="testWhileIdle" value="${jdbc.testWhileIdle}" />
		<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
		<property name="timeBetweenEvictionRunsMillis" value="${jdbc.timeBetweenEvictionRunsMillis}" />
		<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
		<property name="minEvictableIdleTimeMillis" value="${jdbc.minEvictableIdleTimeMillis}" />
		<!-- 打开removeAbandoned功能 -->
		<property name="removeAbandoned" value="${jdbc.removeAbandoned}" />
		<!-- 1800秒，也就是30分钟 -->
		<property name="removeAbandonedTimeout" value="${jdbc.removeAbandonedTimeout}" />
		<!-- 关闭abanded连接时输出错误日志 -->
		<property name="logAbandoned" value="${jdbc.logAbandoned}" />
		<!-- 监控数据库 -->
		<!-- <property name="filters" value="mergeStat" /> -->
		<!-- 开启Druid的监控统计功能 -->
		<property name="filters" value="stat" />
	</bean>

	<bean name="oracleDataSource" class="com.alibaba.druid.pool.DruidDataSource"
		  init-method="init" destroy-method="close">
		<property name="url" value="${jdbc1.url}" />
		<property name="username" value="${jdbc1.username}" />
		<property name="password" value="${jdbc1.password}" />
		<property name="driverClassName" value="${jdbc1.driverClassName}" />
		<!-- 初始化连接大小 -->
		<property name="initialSize" value="${jdbc1.initialSize}" />
		<!-- 连接池最大使用连接数量 -->
		<property name="maxActive" value="${jdbc1.maxActive}" />

		<!-- 连接池最小空闲 -->
		<property name="minIdle" value="${jdbc1.minIdle}" />
		<!-- 获取连接最大等待时间 -->
		<property name="maxWait" value="${jdbc1.maxWait}" />
		<property name="poolPreparedStatements" value="true" />
		<property name="maxPoolPreparedStatementPerConnectionSize" value="${jdbc1.maxPoolPreparedStatementPerConnectionSize}" />
		<!-- 用来检测有效sql -->
		<property name="validationQuery" value="${jdbc1.validationQuery}" />
		<property name="testOnBorrow" value="${jdbc1.testOnBorrow}" />
		<property name="testOnReturn" value="${jdbc1.testOnReturn}" />
		<property name="testWhileIdle" value="${jdbc1.testWhileIdle}" />
		<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
		<property name="timeBetweenEvictionRunsMillis" value="${jdbc1.timeBetweenEvictionRunsMillis}" />
		<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
		<property name="minEvictableIdleTimeMillis" value="${jdbc1.minEvictableIdleTimeMillis}" />
		<!-- 打开removeAbandoned功能 -->
		<property name="removeAbandoned" value="${jdbc1.removeAbandoned}" />
		<!-- 1800秒，也就是30分钟 -->
		<property name="removeAbandonedTimeout" value="${jdbc1.removeAbandonedTimeout}" />
		<!-- 关闭abanded连接时输出错误日志 -->
		<property name="logAbandoned" value="${jdbc1.logAbandoned}" />
		<!-- 监控数据库 -->
		<!-- <property name="filters" value="mergeStat" /> -->
		<!-- 开启Druid的监控统计功能 -->
		<property name="filters" value="stat" />
	</bean>

	<bean name="inspectionDataSource" class="com.alibaba.druid.pool.DruidDataSource"
		  init-method="init" destroy-method="close">
		<property name="url" value="${jdbc2.url}" />
		<property name="username" value="${jdbc2.username}" />
		<property name="password" value="${jdbc2.password}" />
		<property name="driverClassName" value="${jdbc2.driverClassName}" />
		<!-- 初始化连接大小 -->
		<property name="initialSize" value="${jdbc2.initialSize}" />
		<!-- 连接池最大使用连接数量 -->
		<property name="maxActive" value="${jdbc2.maxActive}" />

		<!-- 连接池最小空闲 -->
		<property name="minIdle" value="${jdbc2.minIdle}" />
		<!-- 获取连接最大等待时间 -->
		<property name="maxWait" value="${jdbc2.maxWait}" />
		<property name="poolPreparedStatements" value="true" />
		<property name="maxPoolPreparedStatementPerConnectionSize" value="${jdbc2.maxPoolPreparedStatementPerConnectionSize}" />
		<!-- 用来检测有效sql -->
		<property name="validationQuery" value="${jdbc2.validationQuery}" />
		<property name="testOnBorrow" value="${jdbc2.testOnBorrow}" />
		<property name="testOnReturn" value="${jdbc2.testOnReturn}" />
		<property name="testWhileIdle" value="${jdbc2.testWhileIdle}" />
		<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
		<property name="timeBetweenEvictionRunsMillis" value="${jdbc2.timeBetweenEvictionRunsMillis}" />
		<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
		<property name="minEvictableIdleTimeMillis" value="${jdbc2.minEvictableIdleTimeMillis}" />
		<!-- 打开removeAbandoned功能 -->
		<property name="removeAbandoned" value="${jdbc2.removeAbandoned}" />
		<!-- 1800秒，也就是30分钟 -->
		<property name="removeAbandonedTimeout" value="${jdbc2.removeAbandonedTimeout}" />
		<!-- 关闭abanded连接时输出错误日志 -->
		<property name="logAbandoned" value="${jdbc2.logAbandoned}" />
		<!-- 监控数据库 -->
		<!-- <property name="filters" value="mergeStat" /> -->
		<!-- 开启Druid的监控统计功能 -->
		<property name="filters" value="stat" />
	</bean>

	<bean name="sqlserverDataSource" class="com.alibaba.druid.pool.DruidDataSource"
		  init-method="init" destroy-method="close">
		<property name="url" value="${jdbc3.url}" />
		<property name="username" value="${jdbc3.username}" />
		<property name="password" value="${jdbc3.password}" />
		<property name="driverClassName" value="${jdbc3.driverClassName}" />
		<!-- 初始化连接大小 -->
		<property name="initialSize" value="${jdbc3.initialSize}" />
		<!-- 连接池最大使用连接数量 -->
		<property name="maxActive" value="${jdbc3.maxActive}" />

		<!-- 连接池最小空闲 -->
		<property name="minIdle" value="${jdbc3.minIdle}" />
		<!-- 获取连接最大等待时间 -->
		<property name="maxWait" value="${jdbc3.maxWait}" />
		<property name="poolPreparedStatements" value="true" />
		<property name="maxPoolPreparedStatementPerConnectionSize" value="${jdbc3.maxPoolPreparedStatementPerConnectionSize}" />
		<!-- 用来检测有效sql -->
		<property name="validationQuery" value="${jdbc3.validationQuery}" />
		<property name="testOnBorrow" value="${jdbc3.testOnBorrow}" />
		<property name="testOnReturn" value="${jdbc3.testOnReturn}" />
		<property name="testWhileIdle" value="${jdbc3.testWhileIdle}" />
		<!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
		<property name="timeBetweenEvictionRunsMillis" value="${jdbc3.timeBetweenEvictionRunsMillis}" />
		<!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
		<property name="minEvictableIdleTimeMillis" value="${jdbc3.minEvictableIdleTimeMillis}" />
		<!-- 打开removeAbandoned功能 -->
		<property name="removeAbandoned" value="${jdbc3.removeAbandoned}" />
		<!-- 1800秒，也就是30分钟 -->
		<property name="removeAbandonedTimeout" value="${jdbc3.removeAbandonedTimeout}" />
		<!-- 关闭abanded连接时输出错误日志 -->
		<property name="logAbandoned" value="${jdbc3.logAbandoned}" />
		<!-- 监控数据库 -->
		<!-- <property name="filters" value="mergeStat" /> -->
		<!-- 开启Druid的监控统计功能 -->
		<property name="filters" value="stat" />
	</bean>

	<!-- myBatis文件 -->
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="configLocation" value="classpath:spring/mybatis-config.xml"></property>
		<property name="dataSource" ref="dynamicDataSource" />
		<!-- 自动扫描entity目录, 省掉Configuration.xml里的手工配置 -->
		<property name="mapperLocations" value="classpath:mybatis/**/*.xml" />
	</bean>

	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<property name="basePackage" value="com.zknw.**.dao" />
		<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
	</bean>
	<!--<bean id="dataSourceExchange" class="com.zknw.config.DataSourceExchange"/>-->

	<!-- 配置事务管理器 -->
	<bean id="transactionManager"
		  class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource" ref="dynamicDataSource" />
	</bean>

	<!-- 注解方式配置事物 -->
	<!-- <tx:annotation-driven transaction-manager="transactionManager" /> -->

	<!-- 拦截器方式配置事物 -->
	<tx:advice id="transactionAdvice" transaction-manager="transactionManager">
		<tx:attributes>
			<tx:method name="save*" propagation="REQUIRED" />
			<tx:method name="add*" propagation="REQUIRED" />
			<tx:method name="create*" propagation="REQUIRED" />
			<tx:method name="merge*" propagation="REQUIRED" />
			<tx:method name="insert*" propagation="REQUIRED" />
			<tx:method name="update*" propagation="REQUIRED" />
			<tx:method name="delete*" propagation="REQUIRED" />
			<tx:method name="put*" propagation="REQUIRED" />

			<tx:method name="get*" propagation="SUPPORTS" read-only="true" />
			<tx:method name="find*" propagation="SUPPORTS" read-only="true" />
			<tx:method name="select*" propagation="SUPPORTS" read-only="true" />
			<tx:method name="remove*" propagation="REQUIRED" />
			<tx:method name="get*" propagation="SUPPORTS" read-only="true" />
			<tx:method name="count*" propagation="SUPPORTS" read-only="true" />
			<tx:method name="list*" propagation="SUPPORTS" read-only="true" />
			<tx:method name="*" propagation="SUPPORTS" read-only="true" />

		</tx:attributes>
	</tx:advice>
	<!-- Spring aop事务管理 -->
	<aop:config>
		<aop:pointcut id="transactionPointcut"
					  expression="execution(* com.zknw.*..*Impl.*(..))" />


		<!-- 关键配置，切换数据源一定要比持久层代码更先执行（事务也算持久层代码） -->
		<aop:advisor pointcut-ref="transactionPointcut"
					 advice-ref="transactionAdvice" />
		<!--<aop:advisor advice-ref="dataSourceExchange" pointcut-ref="transactionPointcut" order="1"/>-->

	</aop:config>
</beans>

2.配置DynamicDataSource

package com.zknw.config;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return CustomerContextHolder.getCustomerType();
    }

}

package com.zknw.config;

import com.zknw.util.StringUtils;

public class CustomerContextHolder {
    public static final String DATA_SOURCE_MYSQL = "dataSource";
    public static final String DATA_SOURCE_ORACLE = "oracleDataSource";
    public static final String DATA_SOURCE_XDJMYSQL = "inspectionDataSource";
    public static final String DATA_SOURCE_SQLSERVER = "sqlserverDataSource";
    //用ThreadLocal来设置当前线程使用哪个dataSource
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();
    public static void setCustomerType(String customerType) {
        contextHolder.set(customerType);
    }
    public static String getCustomerType() {
        String dataSource = contextHolder.get();
        if (StringUtils.isEmpty(dataSource)) {
            return DATA_SOURCE_MYSQL;
        }else if (dataSource.equals(DATA_SOURCE_XDJMYSQL)){
            return DATA_SOURCE_XDJMYSQL;
        }else if (dataSource.equals(DATA_SOURCE_SQLSERVER)){
            return DATA_SOURCE_SQLSERVER;
        }else {
            return DATA_SOURCE_ORACLE;
        }
    }
    public static void clearCustomerType() {
        contextHolder.remove();
    }

}

3.在业务层的写法

package com.zknw.basicmanage.impl;

import com.zknw.basicmanage.api.ProduceService;
import com.zknw.basicmanage.dao.ProduceDao;
import com.zknw.basicmanage.model.NumbeDistribution;
import com.zknw.basicmanage.model.ProductionNumber;
import com.zknw.basicmanage.model.ProductionStaff;
import com.zknw.config.CustomerContextHolder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service("produceService")
public class ProduceServiceImpl implements ProduceService {

    @Autowired
    private ProduceDao produceDao;

    @Override
    public List<ProductionStaff> getPersonnelDetails() throws Exception {
        try {
            CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_SQLSERVER);
            List<ProductionStaff> productionStaffList = produceDao.getPersonnelDetails();
            CustomerContextHolder.setCustomerType(null);
            return productionStaffList;
        }catch (Exception e) {
            throw e;
        }
    }

    @Override
    public List<NumbeDistribution> getOutsideNum() throws Exception {
        try {
            CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_SQLSERVER);
            List<NumbeDistribution> numbeDistributions = produceDao.getOutsideNum();
            CustomerContextHolder.setCustomerType(null);
            return numbeDistributions;
        }catch (Exception e) {
            throw e;
        }
    }

    @Override
    public ProductionNumber getPeopleNum() throws Exception {
        try {
            CustomerContextHolder.setCustomerType(CustomerContextHolder.DATA_SOURCE_SQLSERVER);
            ProductionNumber numbeDistribution = new ProductionNumber();
            Integer numberPeople = produceDao.getNumberPeople();
            Integer outsideNumberPeople = produceDao.getOutsideNumberPeople();
            numbeDistribution.setTotalPeople(numberPeople);
            numbeDistribution.setOutsideNum(outsideNumberPeople);
            CustomerContextHolder.setCustomerType(null);
            return numbeDistribution;
        }catch (Exception e) {
            throw e;
        }
    }
}