spring boot和Mybatis配置多个mysql数据源

spring boot和Mybatis配置多个mysql数据源

在很多场景下，我们的项目都是一个数据源；但有些业务场景下需要配置多个数据源，下面我们就介绍spring boot下配置两个mysql数据源：

• 在pom文件下添加依赖

<!-- Druid 数据连接池依赖 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.10</version>
</dependency>

• application.properties配置两个数据源配置

# master 数据源配置
master.datasource.driverClassName=com.mysql.jdbc.Driver
master.datasource.url=jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=utf8
master.datasource.username=root
master.datasource.password=123456

# second 数据源配置
second.datasource.driverClassName=com.mysql.jdbc.Driver
second.datasource.url=jdbc:mysql://localhost:3306/db_second?useUnicode=true&characterEncoding=utf8
second.datasource.username=root
second.datasource.password=123456

• Configuration数据源

多数据源配置的时候注意，必须要有一个主数据源，即 MasterDataSourceConfig 配置
@Primary 标志这个 Bean 如果在多个同类 Bean 候选时，该 Bean 优先被考虑。「多数据源配置的时候注意，必须要有一个主数据源，用 @Primary 标志该 Bean
@MapperScan 扫描 Mapper 接口并容器管理，包路径精确到 master，为了和下面 cluster 数据源做到精确区分
@Value 获取全局配置文件 application.properties 的 kv 配置,并自动装配sqlSessionFactoryRef 表示定义了 key ，表示一个唯一 SqlSessionFactory 实例

MasterDataSourceConfig的代码：

@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
 
    // 精确到 master 目录，以便跟其他数据源隔离
    static final String PACKAGE = "org.spring.springboot.dao.master";
    static final String MAPPER_LOCATION = "classpath:mapper/master/*.xml";
 
    @Value("${master.datasource.url}")
    private String url;
 
    @Value("${master.datasource.username}")
    private String user;
 
    @Value("${master.datasource.password}")
    private String password;
 
    @Value("${master.datasource.driverClassName}")
    private String driverClass;
 
    @Bean(name = "masterDataSource")
    @Primary
    public DataSource masterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }
 
    @Bean(name = "masterTransactionManager")
    @Primary
    public DataSourceTransactionManager masterTransactionManager() {
        return new DataSourceTransactionManager(masterDataSource());
    }
 
    @Bean(name = "masterSqlSessionFactory")
    @Primary
    public SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource masterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(masterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(MasterDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

SecondDataSourceConfig的配置如下:

@Configuration
// 扫描 Mapper 接口并容器管理
@MapperScan(basePackages = SecondDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "secondSqlSessionFactory")
public class SecondDataSourceConfig {

    // 精确到 cluster 目录，以便跟其他数据源隔离
    static final String PACKAGE = "com.springboot.dao.second";
    static final String MAPPER_LOCATION = "classpath:mapper/second/*.xml";

    @Value("${second.datasource.url}")
    private String url;

    @Value("${second.datasource.username}")
    private String user;

    @Value("${second.datasource.password}")
    private String password;

    @Value("${second.datasource.driverClassName}")
    private String driverClass;

    @Bean(name = "secondDataSource")
    public DataSource clusterDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        return dataSource;
    }

    @Bean(name = "secondTransactionManager")
    public DataSourceTransactionManager clusterTransactionManager() {
        return new DataSourceTransactionManager(clusterDataSource());
    }

    @Bean(name = "secondSqlSessionFactory")
    public SqlSessionFactory clusterSqlSessionFactory(@Qualifier("secondDataSource") DataSource clusterDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(clusterDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(SecondDataSourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

将Mybatis的xml文件和DAO层的接口写好，并在Service层注入，直接使用就行。

Service层的代码：
@Service
public class UserServiceImpl implements UserService {

@Autowired
private UserDao userDao;
@Autowired
private SchoolDao schoolDao;

public UserVo getUser(Long id) {
    UserVo userVo = userDao.findById(id);
    SchoolVo schoolVo = schoolDao.findByName("清华");
    userVo.setSchoolVo(schoolVo);
    return userVo;
}

Mybatis的xml文件UserDao.xml和SchoolDao.xml的内容：

UserDao.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.springboot.dao.master.UserDao">
    <resultMap id="BaseResultMap" type="com.springboot.vo.UserVo">
        <result column="id" property="id" />
        <result column="user_name" property="userName" />
    </resultMap>

    <sql id="Base_Column_List">
        id, user_name
    </sql>

    <select id="findById" resultMap="BaseResultMap" parameterType="java.lang.Long">
        select
            <include refid="Base_Column_List" />
        from
            user
        where
            id = #{id}
    </select>
</mapper>

SchoolDao.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.springboot.dao.second.SchoolDao">
    <resultMap id="BaseResultMap" type="com.springboot.vo.SchoolVo">
        <result column="id" property="id" />
        <result column="school_name" property="schoolName" />
        <result column="school_describe" property="schoolDescribe" />
    </resultMap>

    <sql id="Base_Column_List">
        id, school_name, school_describe
    </sql>

    <select id="findByName" resultMap="BaseResultMap" parameterType="java.lang.String">
        select
            <include refid="Base_Column_List" />
        from
            school
        where
            school_name = #{schoolName}
    </select>

</mapper>

TestController

@Controller
@RequestMapping(value = "/api/v1/test")
public class TestController {

    @Autowired
    private UserService userService;

    @RequestMapping(value = "/getUser")
    @ResponseBody
    public UserVo getUser(@RequestParam(value = "id") Long id) {
        return userService.getUser(id);
    }

}