SpringBoot和Mybatis配置多數(shù)據(jù)源連接多個(gè)數(shù)據(jù)庫(kù)

目前業(yè)界操作數(shù)據(jù)庫(kù)的框架一般是 Mybatis昭躺，但在很多業(yè)務(wù)場(chǎng)景下近弟，我們需要在一個(gè)工程里配置多個(gè)數(shù)據(jù)源來(lái)實(shí)現(xiàn)業(yè)務(wù)邏輯壳鹤。在SpringBoot中也可以實(shí)現(xiàn)多數(shù)據(jù)源并配合Mybatis框架編寫xml文件來(lái)執(zhí)行SQL京髓。在SpringBoot中，配置多數(shù)據(jù)源的方式十分便捷或粮，

下面開(kāi)始上代碼：

• 在pom.xml文件中需要添加一些依賴

<!-- Spring Boot Mybatis 依賴 -->
<dependency>
  <groupId>org.mybatis.spring.boot</groupId>
  <artifactId>mybatis-spring-boot-starter</artifactId>
  <version>1.2.0</version>
</dependency>

<!-- MySQL 連接驅(qū)動(dòng)依賴 -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.39</version>
</dependency>

<!-- Druid 數(shù)據(jù)連接池依賴 -->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.0.18</version>
</dependency>

• application.properties 配置兩個(gè)數(shù)據(jù)源配置

# master 數(shù)據(jù)源配置
master.datasource.url=jdbc:mysql://localhost:3306/springbootdb?useUnicode=true&characterEncoding=utf8
master.datasource.username=root
master.datasource.password=321
master.datasource.driverClassName=com.mysql.jdbc.Driver

# second 數(shù)據(jù)源配置
second.datasource.url=jdbc:mysql://localhost:3306/springbootdb_second?useUnicode=true&characterEncoding=utf8
second.datasource.username=root
second.datasource.password=321
second.datasource.driverClassName=com.mysql.jdbc.Driver

• 數(shù)據(jù)源配置

多數(shù)據(jù)源配置的時(shí)候注意导饲，必須要有一個(gè)主數(shù)據(jù)源捞高，即 MasterDataSourceConfig 配置

• @Primary 標(biāo)志這個(gè) Bean 如果在多個(gè)同類 Bean 候選時(shí)氯材，該 Bean 優(yōu)先被考慮∠醺冢「多數(shù)據(jù)源配置的時(shí)候注意氢哮，必須要有一個(gè)主數(shù)據(jù)源，用 @Primary 標(biāo)志該 Bean
• @MapperScan 掃描 Mapper 接口并容器管理型檀，包路徑精確到 master冗尤，為了和下面 cluster 數(shù)據(jù)源做到精確區(qū)分
• @Value 獲取全局配置文件 application.properties 的 kv 配置,并自動(dòng)裝配sqlSessionFactoryRef 表示定義了 key ，表示一個(gè)唯一 SqlSessionFactory 實(shí)例

MasterDataSourceConfig的代碼：

@Configuration
// 掃描 Mapper 接口并容器管理
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
 
    // 精確到 master 目錄胀溺，以便跟其他數(shù)據(jù)源隔離
    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();
    }
}

第二個(gè)數(shù)據(jù)源SecondDataSourceConfig的配置如下:

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

    // 精確到 cluster 目錄裂七，以便跟其他數(shù)據(jù)源隔離
    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();
    }
}

到此，不同的數(shù)據(jù)源配置就已經(jīng)完成仓坞，剩下的只需要將將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的內(nèi)容：

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);
    }

}

創(chuàng)建數(shù)據(jù)庫(kù)和表SQL：

CREATE DATABASE springbootdb;

CREATE DATABASE springbootdb_second;

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用戶編號(hào)',
  `user_name` varchar(25) DEFAULT NULL COMMENT '用戶名稱',
  `description` varchar(25) DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

CREATE TABLE `school` (
  `id` bigint(20) NOT NULL,
  `school_name` varchar(64) DEFAULT NULL COMMENT '學(xué)校名',
  `school_describe` varchar(128) DEFAULT NULL COMMENT '學(xué)校描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into `springbootdb`.`user` ( `user_name`, `description`) values ( 'shuai', 'so handsome');

insert into `springbootdb_second`.`school` ( `id`, `school_name`, `school_describe`) values ( '1', '清華', '自強(qiáng)不息徙瓶，厚德載物');

創(chuàng)建好數(shù)據(jù)后，整個(gè)工程的機(jī)構(gòu)如下：

啟動(dòng)程序嫉称，在瀏覽器輸入：http://localhost:8080/api/v1/test/getUser?id=1即可返回結(jié)果侦镇。

工程結(jié)構(gòu).png

github地址：Spring Boot 教程、技術(shù)棧织阅、示例代碼

歡迎關(guān)注公眾號(hào)：java之旅