ssm+多数据源的配置oracle+mysql自由切换

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

fopi

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

阅读量449

点赞数 1

本文链接：https://blog.csdn.net/qq_41411592/article/details/106139567

版权

配置多数据源时只需要配置spring-mybatis.xml和config.properties文件，详细文件如下：

config.properties

#MASTER
postgresql.jdbc.driver=org.postgresql.Driver
postgresql.jdbc.url=jdbc:postgresql://localhost/xxxx
postgresql.jdbc.username=root
postgresql.jdbc.password=root

#SLAVE
mysql.jdbc.driver=com.mysql.jdbc.Driver
mysql.jdbc.url=jdbc:mysql://localhost:3306/xxx?characterEncoding=utf-8
mysql.jdbc.username=root
mysql.jdbc.password=root

#oracle
oracle.jdbc.driver=oracle.jdbc.driver.OracleDriver
#oracle.jdbc.url=jdbc:oracle:thin:@localhost:1521/orclzhly
#oracle.jdbc.url=jdbc:oracle:thin:@localhost:1521/orclzhly
#oracle.jdbc.url=jdbc:oracle:thin:@localhost:1521/orclzhly
oracle.jdbc.url=jdbc:oracle:thin:@localhost:1521/orclzhly
#oracle.jdbc.url=jdbc:oracle:thin:@localhost:1521/orcl
#oracle.jdbc.url=jdbc:oracle:thin:@localhost:1521/orcl
oracle.jdbc.username=root
oracle.jdbc.password=root


#druid
druid.initialSize=10  
druid.minIdle=10  
druid.maxActive=50  
druid.maxWait=60000  
druid.timeBetweenEvictionRunsMillis=60000
druid.minEvictableIdleTimeMillis=300000  
druid.testWhileIdle=true  
druid.testOnBorrow=false  
druid.testOnReturn=false  
druid.poolPreparedStatements=true  
druid.maxPoolPreparedStatementPerConnectionSize=20  
druid.filters=stat,wall,log4j
druid.validationQuery=SELECT 1 from dual
spring.datasource.connectionProperties=druid.stat.mergeSql=true;


#mongo
mongo.hostport=127.0.0.1:27017  
mongo.connectionsPerHost=8  
mongo.threadsAllowedToBlockForConnectionMultiplier=4  
mongo.connectTimeout=1000  
mongo.maxWaitTime=1500  
mongo.socketKeepAlive=true  
mongo.socketTimeout=1500  
mongo.databaseName=mongo_demo  


#\u540E\u53F0\u9875\u9762\u914D\u7F6E
//\u767B\u5F55\u56FE\u7247\u5730\u5740
loginImage=bj.jpg;

//logo\u56FE\u7247\u5730\u5740
logoImage=logo.jpg;
//\u9879\u76EE\u540D\u79F0
systemName=\u9752\u6D77\u6C34\u80A5\u4E00\u4F53\u5316\u667A\u80FD\u63A7\u5236\u7CFB\u7EDF;
//\u5F39\u6846\u989C\u8272
backgroudColor=#259f4c;

//\u5DE6\u4FA7\u680F
navbar=index-leftnav.html;
//\u5BFC\u822A\u680F
sidbar=index-top.html;

//\u65B0\u6DFB\u52A0\u7684\u5185\u5BB9
subject=subject_content.html



//\u6743\u9650\u56FE\u6807\u4FDD\u5B58\u5730\u5740
iconFilePath=D:\\01\u9752\u6D77\u519C\u4E1A\u7269\u8054\u7F51\u6C34\u80A5\u4E00\u4F53\u5316\u9879\u76EE\\04.\u7A0B\u5E8F\u4EE3\u7801\\rbt_sysframe\\src\\main\\webapp\\WEB-INF\\statics\\img

spring-mybatis.xml 配置文件

<?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-3.2.xsd
                           http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd">
    <!-- 加载配置文件 -->
    <bean id="configProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
        <property name="fileEncoding" value="UTF-8"/>
        <property name="locations">
            <list>
                <value>classpath:config/spring/config.properties</value>
            </list>
        </property>
    </bean>

    <bean id="propertyConfigurer" class="org.springframework.beans.factory.config.PreferencesPlaceholderConfigurer">
        <property name="properties" ref="configProperties"/>
    </bean>

    <!-- 数据库连接池 -->
    <bean id="dataSourcePostgresql" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <!-- 基本属性 url、user、password -->
        <property name="url" value="${oracle.jdbc.url}" />
        <property name="username" value="${oracle.jdbc.username}" />
        <property name="password" value="${oracle.jdbc.password}" />
        <property name="driverClassName" value="${oracle.jdbc.driver}" />
        <property name = "validationQuery" value = "${druid.validationQuery}" />
        <!-- 配置初始化大小、最小、最大 -->
        <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="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="dataSourceMysql" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
        <!-- 基本属性 url、user、password -->
        <property name="url" value="${mysql.jdbc.url}" />
        <property name="username" value="${mysql.jdbc.username}" />
        <property name="password" value="${mysql.jdbc.password}" />
        <property name="driverClassName" value="${mysql.jdbc.driver}" />
        <property name = "validationQuery" value = "${druid.validationQuery}" />

        <!-- 配置初始化大小、最小、最大 -->
        <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="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="false" />
        <property name="maxPoolPreparedStatementPerConnectionSize" value="${druid.maxPoolPreparedStatementPerConnectionSize}" />
        <!-- 配置监控统计拦截的filters -->
        <property name="filters" value="${druid.filters}" />
    </bean>

    <!--多数据源配置-->
    <bean id="dataSource" class="com.cn.reebotoo.util.MultipleDataSource">
        <property name="defaultTargetDataSource" ref="dataSourcePostgresql"/> <!--默认主库-->
        <property name="targetDataSources">
            <map>
                <entry key="master" value-ref="dataSourcePostgresql"/>
                <entry key="slave" value-ref="dataSourceMysql"/>
            </map>
        </property>
    </bean>

    <!-- mybatis文件配置，扫描所有mapper文件 -->
    <!-- 创建SqlSessionFactory，同时指定数据源-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!-- 注入数据库连接池 -->
        <property name="dataSource" ref="dataSource" />
        <!-- 配置MyBaties全局配置文件:mybatis-config.xml -->
        <property name="configLocation" value="classpath:config/mybatis/mybatis-config.xml" />
        <!-- 扫描sql配置文件:mapper需要的xml文件 -->
        <property name="mapperLocations" value="classpath:com/cn/xx/mapper/*.xml" />
    </bean>

    <!-- 配置扫描Dao接口包，动态实现Dao接口，注入到spring容器中 -->
    <bean class="tk.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
        <!-- 给出需要扫描Dao接口包 -->
        <property name="basePackage" value="com.cn.xx.mapper" />
        <!-- 注入sqlSessionFactory -->
        <property name="properties">
            <value>
                mappers=tk.mybatis.mapper.common.Mapper
            </value>
        </property>
    </bean>
    <!-- mybatis文件配置，扫描所有mapper文件 -->
    <!-- 创建SqlSessionFactory，同时指定数据源-->
    <bean id="sqlSessionFactory1" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!-- 注入数据库连接池 -->
        <property name="dataSource" ref="dataSource" />
        <!-- 配置MyBaties全局配置文件:mybatis-config.xml -->
        <property name="configLocation" value="classpath:config/mybatis/mybatis-config.xml" />
        <!-- 扫描sql配置文件:mapper需要的xml文件 -->
        <property name="mapperLocations" value="classpath:com/cn/xx/mapper/*.xml" />
    </bean>

    <!-- 配置扫描Dao接口包，动态实现Dao接口，注入到spring容器中 -->
    <bean class="tk.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory1" />
        <!-- 给出需要扫描Dao接口包 -->
        <property name="basePackage" value="com.cn.xx.mapper" />
        <!-- 注入sqlSessionFactory -->
        <property name="properties">
            <value>
                mappers=tk.mybatis.mapper.common.Mapper
            </value>
        </property>
    </bean>

    <!-- 事务 -->
    <bean id="postgresqlTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSourcePostgresql"/>
    </bean>
    <tx:annotation-driven transaction-manager="postgresqlTransactionManager"/>
    <!--&lt;!&ndash; 注解实现动态切换数据源 &ndash;&gt;
    <bean id="dataSourceAspect" class="com.cn.xx.aspect.DataSourceAspect" />
    <aop:config>
        <aop:aspect ref="dataSourceAspect">
           &lt;!&ndash; 拦截所有service方法,切面插入拦截的方法，获取注解&ndash;&gt;
            <aop:pointcut id="dataSourcePointcut" expression="execution(* mapper.*.*(..))" ></aop:pointcut>
            <aop:before pointcut-ref="dataSourcePointcut" method="intercept" />
        </aop:aspect>
    </aop:config>-->
    <bean id="mysqlTransactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSourceMysql" />
       </bean>
    <tx:annotation-driven transaction-manager="mysqlTransactionManager" />

</beans>

下面就是切换的类MultipleDataSource

public  class MultipleDataSource extends AbstractRoutingDataSource{
    public static final String DATA_SOURCE_MASTER= "master";
        public static final String DATA_SOURCE_SLAVE = "slave";

    private static ThreadLocal<String> threadLocalDatasource=new ThreadLocal<String>(){
        @Override
        protected  String initialValue() {
            return null;
        }
    };
    public static void setThreadLocalDatasource(String dsName){
        threadLocalDatasource.set(dsName);
    }
    protected  Object determineCurrentLookupKey() {
        return threadLocalDatasource.get();
    }


    public static void clearDataSource() {
        threadLocalDatasource.remove();
    }
}

最后要切换从库的mysql时只需要在当前的controller层中的具体方法上：

public void add（）{
MultipleDataSource.setThreadLocalDatasource(MultipleDataSource.DATA_SOURCE_SLAVE);

......核心代码区；

最后关掉当前当前线程