SSM整合多数据源

Spring+SpringMVC+MyBaits 整合多数据源。数据库:MySQL+Mariadb。
SSM基本配置这里就不细说了,直接进入正题,进行多数据源的配置。

附源码地址:https://gitee.com/hiseico/mybatis-multi-source-isomerism

我的博客:https://blog.sitcat.cn/

引入Mysql及Maria数据库驱动包,以Maven为例

 <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.36</version>
  </dependency>
  
  <dependency>
     <groupId>org.mariadb.jdbc</groupId>
     <artifactId>mariadb-java-client</artifactId>
     <version>2.4.1</version>
   </dependency>

具体版本自己定。

jdbc.properties中配置多个数据源

jdbc.mysql.driver=com.mysql.jdbc.Driver
jdbc.mysql.url=jdbc:mysql://localhost:3306/clouddb01?useUnicode=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
jdbc.mysql.username=root
jdbc.mysql.password=root

jdbc.mariadb.driver=org.mariadb.jdbc.Driver
jdbc.mariadb.url=jdbc:mariadb://localhost:3307/clouddb02?useUnicode=true&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
jdbc.mariadb.username=root
jdbc.mariadb.password=root

在Spring的application.xml中为每一个数据库配置数据源、MyBatis相关、事务等

<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
       xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
    http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
    http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.0.xsd">

    <!-- 自动扫描 -->
    <context:component-scan base-package="cn.sitcat.service"/>

    <!-- 引入配置文件 -->
    <bean id="propertyConfigurer"
          class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list>
                <value>classpath:jdbc.properties</value>
            </list>
        </property>
    </bean>

    <!--第一步: 配置数据源 -->
    <!--mysql数据库 数据源-->
    <bean id="mysqlDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

        <property name="driverClassName" value="${jdbc.mysql.driver}"/>
        <property name="url" value="${jdbc.mysql.url}"/>
        <property name="username" value="${jdbc.mysql.username}"/>
        <property name="password" value="${jdbc.mysql.password}"/>
    </bean>
    <!--Maria数据库 数据源-->
    <bean id="MariaDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="${jdbc.mariadb.driver}"/>
        <property name="url" value="${jdbc.mariadb.url}"/>
        <property name="username" value="${jdbc.mariadb.username}"/>
        <property name="password" value="${jdbc.mariadb.password}"/>
    </bean>

    <!-- Mybatis SqlSessionFactory -->
    <!--mysql数据库 Mybatis SqlSessionFactory-->
    <bean id="sqlSessionFactory01" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="mysqlDataSource"/>
    </bean>
    <!--Maria数据库 Mybatis SqlSessionFactory-->
    <bean id="MariaSqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="MariaDataSource"/>
    </bean>

    <!-- 配置mybatis的代理接口开发 * 接口类名和映射文件必须同名 * 接口类和映射文件必须在同一个目 录下 * 接口的映射文件的namespace名称必须是接口的全限定名
      * 接口的方法名必须和映射的statement的id一致 -->
    <!--mysql数据库 mapperScanner-->
    <bean id="MysqlMapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="cn.sitcat.dao.mysql"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory01"/>
    </bean>
    <!--Maria数据库 mapperScanner-->
    <bean id="MariaMapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="cn.sitcat.dao.maria"/>
        <property name="sqlSessionFactoryBeanName" value="MariaSqlSessionFactory"/>
    </bean>

    <!-- 事务管理 -->
    <tx:annotation-driven transaction-manager="MysqlTransactionManager"/>
    <tx:annotation-driven transaction-manager="MariaTransactionManager"/>

    <!--mysql数据库-->
    <bean id="MysqlTransactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="mysqlDataSource"/>
        <qualifier value="mysqlDataSourceTx"/>
    </bean>
    <!--Maria数据库-->
    <bean id="MariaTransactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="mysqlDataSource"/>
        <qualifier value="MariaDataSourceTx"/>
    </bean>

    <!-- 配置事物增强,事物如何切入 -->
    <!--Mysql数据库的事务-->
    <tx:advice id="MysqlTxAdvice" transaction-manager="MysqlTransactionManager">
        <tx:attributes>
            <!-- 传播行为 -->
            <tx:method name="*" propagation="SUPPORTS"/>
            <tx:method name="save*" propagation="REQUIRED"/>
            <tx:method name="insert*" propagation="REQUIRED"/>
            <tx:method name="add*" propagation="REQUIRED"/>
            <tx:method name="create*" propagation="REQUIRED"/>
            <tx:method name="delete*" propagation="REQUIRED"/>
            <tx:method name="update*" propagation="REQUIRED"/>
            <tx:method name="batchUpdate*" propagation="REQUIRED"/>
            <tx:method name="find*" propagation="SUPPORTS" read-only="true"/>
            <tx:method name="select*" propagation="SUPPORTS" read-only="true"/>
            <tx:method name="get*" propagation="SUPPORTS" read-only="true"/>
        </tx:attributes>
    </tx:advice>

    <!--Maria数据库的事务-->
    <tx:advice id="MariaTxAdvice" transaction-manager="MariaTransactionManager">
        <tx:attributes>
            <!-- 传播行为 -->
            <tx:method name="*" propagation="SUPPORTS"/>
            <tx:method name="save*" propagation="REQUIRED"/>
            <tx:method name="insert*" propagation="REQUIRED"/>
            <tx:method name="add*" propagation="REQUIRED"/>
            <tx:method name="create*" propagation="REQUIRED"/>
            <tx:method name="delete*" propagation="REQUIRED"/>
            <tx:method name="update*" propagation="REQUIRED"/>
            <tx:method name="batchUpdate*" propagation="REQUIRED"/>
            <tx:method name="find*" propagation="SUPPORTS" read-only="true"/>
            <tx:method name="select*" propagation="SUPPORTS" read-only="true"/>
            <tx:method name="get*" propagation="SUPPORTS" read-only="true"/>
        </tx:attributes>
    </tx:advice>
</beans>

剩下的Mapper.xml、Serivce层等业务和单数据源的时候一样,直接编写对应表的Mapper即可。

注意事项

1.这里不推荐在application.xml中使用databaseIdProvider配置。如下:

<!-- 多数据库处理,设定vendor属性 -->
   <bean id="vendorProperties"
         class="org.springframework.beans.factory.config.PropertiesFactoryBean">
       <property name="properties">
           <props>
               <prop key="Oracle">oracle</prop> <!-- 配置数据库关键字,可在mapper的xml中使用 -->
               <prop key="MySQL">mysql</prop>
               <prop key="SQL Server">sqlserver</prop>
               <prop key="DB2">db2</prop>
           </props>
       </property>
   </bean>
   <bean id="databaseIdProvider" class="org.apache.ibatis.mapping.VendorDatabaseIdProvider">
       <property name="properties" ref="vendorProperties" />
   </bean>

这样配置的话,在mapper.xml中的语句上使用databaseId声明数据源会直接报错。

    <select id="selectUserList" databaseId="Mysql" resultType="cn.sitcat.entity.maria.User">
        SELECT * FROM user
    </select>

会报错invalid bound statement (not found),具体原因还未查明。(可能是我的配置不对)


2.每个数据库的mapper放到单独的一个包下,不要将多个数据库的mapper挡在同一个目录里。在Spring的配置文件application.xml中的MapperScannerConfigurer分别声明对应的basePackage。如下:

    <!--mysql数据库 mapperScanner-->
    <bean id="MysqlMapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="cn.sitcat.dao.mysql"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory01"/>
    </bean>
    <!--Maria数据库 mapperScanner-->
    <bean id="MariaMapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="cn.sitcat.dao.maria"/>
        <property name="sqlSessionFactoryBeanName" value="MariaSqlSessionFactory"/>
    </bean>

附:项目结构图

项目结构