MyBatis多数据源的两种配置方式

MyBatis多数据源的两种配置方式包括XML配置和注解配置。

XML配置

步骤一：配置数据源

在mybatis-config.xml文件中配置多数据源的数据源信息，注意要配置各自的数据库连接信息以及数据库驱动信息。

<environments default="dev">
  <environment id="dev">
    <transactionManager type="JDBC"/>
    <dataSource type="POOLED">
      <property name="driver" value="com.mysql.jdbc.Driver"/>
      <property name="url" value="jdbc:mysql://localhost:3306/db1"/>
      <property name="username" value="root"/>
      <property name="password" value="root"/>
    </dataSource>
  </environment>
  <environment id="test">
    <transactionManager type="JDBC"/>
    <dataSource type="POOLED">
      <property name="driver" value="com.mysql.jdbc.Driver"/>
      <property name="url" value="jdbc:mysql://localhost:3306/db2"/>
      <property name="username" value="root"/>
      <property name="password" value="root"/>
    </dataSource>
  </environment>
</environments>

步骤二：配置mapper

创建对应的mapper接口和xml文件，如下所示：

public interface UserMapper1 {

    List<User> getAll();

    User getOne(Long id);

    void insert(User user);

    void update(User user);

    void delete(Long id);

}

<mapper namespace="com.example.mapper.UserMapper1">

    <select id="getAll" resultType="User">
        select * from user
    </select>

    <select id="getOne" parameterType="Long" resultType="User">
        select * from user where id = #{id}
    </select>

    <insert id="insert" parameterType="User">
        insert into user(id,name,age) values(#{id},#{name},#{age})
    </insert>

    <update id="update" parameterType="User">
        update user set name=#{name},age=#{age} where id=#{id}
    </update>

    <delete id="delete" parameterType="Long">
        delete from user where id=#{id}
    </delete>

</mapper>

步骤三：配置SqlSessionFactory

在Spring配置文件中根据不同的环境配置多个SqlSessionFactory对象。例如：

<bean id="sqlSessionFactory1" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="configLocation" value="classpath:mybatis-config.xml"/>
    <property name="dataSource" ref="dataSource1"/>
</bean>

<bean id="sqlSessionFactory2" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="configLocation" value="classpath:mybatis-config.xml"/>
    <property name="dataSource" ref="dataSource2"/>
</bean>

步骤四：配置MapperScannerConfigurer

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="com.example.mapper"/>
    <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory1"/>
</bean>

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="basePackage" value="com.example.mapper"/>
    <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory2"/>
</bean>

注解配置

步骤一：配置数据源

在application.yml或application.properties文件中配置数据源信息，如下所示：

# 默认数据源
spring.datasource.primary.url=jdbc:mysql://localhost:3306/db1
spring.datasource.primary.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.primary.username=root
spring.datasource.primary.password=root

# 第二个数据源
spring.datasource.secondary.url=jdbc:mysql://localhost:3306/db2
spring.datasource.secondary.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.secondary.username=root
spring.datasource.secondary.password=root

步骤二：配置Mapper

在Mapper接口的方法上增加@Mapper注解，并且在方法上增加@Select、@Insert等注解，如下所示：

@Mapper
public interface UserMapper1 {

    @Select("SELECT * FROM user")
    List<User> getAll();

    @Select("SELECT * FROM user WHERE id = #{id}")
    User getOne(@Param("id") Long id);

    @Insert("INSERT INTO user(id,name,age) VALUES(#{id},#{name},#{age})")
    void insert(User user);

    @Update("UPDATE user SET name=#{name},age=#{age} WHERE id=#{id}")
    void update(User user);

    @Delete("DELETE FROM user WHERE id=#{id}")
    void delete(Long id);

}

步骤三：配置SqlSessionFactory

在Spring Boot配置类中使用@Primary注解定义默认的SqlSessionFactory，如下所示：

@Configuration
@MapperScan(basePackages = "com.example.mapper", sqlSessionFactoryRef = "sqlSessionFactory")
public class DataSourceConfig {

    @Primary
    @Bean(name = "dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource.primary")
    public DataSource dataSource1() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "dataSource2")
    @ConfigurationProperties(prefix = "spring.datasource.secondary")
    public DataSource dataSource2() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource1") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        return sessionFactoryBean.getObject();
    }

    @Bean(name = "sqlSessionFactory2")
    public SqlSessionFactory sqlSessionFactory2(@Qualifier("dataSource2") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        return sessionFactoryBean.getObject();
    }

    @Bean(name = "transactionManager")
    public DataSourceTransactionManager DataSourceTransactionManager(@Qualifier("dataSource1") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}

步骤四：配置MapperScannerConfigurer

@Configuration
public class MybatisConfig {

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource1") DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        return sessionFactoryBean.getObject();
    }

    @Bean(name = "sqlSessionFactory2")
    public SqlSessionFactory sqlSessionFactory2(@Qualifier("dataSource2") DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
        sessionFactoryBean.setDataSource(dataSource);
        return sessionFactoryBean.getObject();
    }

    @Bean(name = "db1SqlSessionTemplate")
    @Primary
    public SqlSessionTemplate db1SqlSessionTemplate(
            @Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean(name = "db2SqlSessionTemplate")
    public SqlSessionTemplate db2SqlSessionTemplate(
            @Qualifier("sqlSessionFactory2") SqlSessionFactory sqlSessionFactory2) {
        return new SqlSessionTemplate(sqlSessionFactory2);
    }

}

至此，我们已经了解了MyBatis多数据源的两种配置方式。其中，第一种方式使用XML配置，相对比较繁琐，但是更加灵活，适用于使用基于XML的MyBatis的用户。第二种方式使用注解配置，相对简洁，且在Spring Boot项目中使用非常方便，是一种常用的配置方式。

下面我们看两个示例：

示例一

假设我们有两个数据源，db1和db2，下面我们分别对这两个数据源进行增删查改的操作。

• 从db1中查询所有用户

@Autowired
private UserMapper1 userMapper1;

public List<User> getAllFromDb1() {
    return userMapper1.getAll();
}

• 从db2中查询所有用户

@Autowired
@Qualifier("sqlSessionFactory2")
private SqlSessionFactory sqlSessionFactory2;

public List<User> getAllFromDb2() {
    SqlSession session = sqlSessionFactory2.openSession();
    UserMapper2 userMapper2 = session.getMapper(UserMapper2.class);
    return userMapper2.getAll();
}

示例二

假设我们有两个数据源，db1和db2，下面我们分别同时向这两个数据源增加一个用户。

@Autowired
@Qualifier("db1SqlSessionTemplate")
private SqlSessionTemplate db1SqlSessionTemplate;

@Autowired
@Qualifier("db2SqlSessionTemplate")
private SqlSessionTemplate db2SqlSessionTemplate;

public void addUsers() {
    User user1 = new User(1L, "admin", 18);
    User user2 = new User(2L, "admin2", 20);

    db1SqlSessionTemplate.insert("com.example.mapper.UserMapper1.insert", user1);
    db2SqlSessionTemplate.insert("com.example.mapper.UserMapper2.insert", user2);
}