SpringBoot+Mybatis配置多数据源

发表于

开发企业级应用程序时常遇到要使用多个服务器上数据库的情况，此时需要配置多个数据源。查阅相关资料，遇到不少坑，亲测如下：

项目目录结构

multi_dataSource_dic

关键文件

pom文件配置：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.kodgames</groupId>
    <artifactId>MultiDataSource</artifactId>
    <packaging>jar</packaging>
    <version>1.0-SNAPSHOT</version>
    <name>MultiDataSource</name>
    <description>test Spring boot multi Datasource</description>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <jarName>MultiDataSource</jarName>
    </properties>
    <dependencies>
        <!-- Spring Boot web依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.6.1</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.6.1</version>
        </dependency>
        <!--MySQL JDBC驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- 数据库连接池 -->
        <!-- https://mvnrepository.com/artifact/com.zaxxer/HikariCP -->
        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>2.6.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>*.*</exclude>
                    </excludes>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
	
</project>
application.properties配置文件
1
2
3
4
5
6
7
8
9
10
11
12
13
server.port=8095
# Primary DataSource configuration
datasource.primary.jdbcUrl=jdbc:mysql://172.16.2.163:8091/club4agency?useUnicode=true&characterEncoding=utf8&useSSL=false
datasource.primary.username=****
datasource.primary.password=******
datasource.primary.driver-class-name=com.mysql.jdbc.Driver
# Secondary DataSource configuration
datasource.secondary.jdbcUrl=jdbc:mysql://127.0.0.1/mahjongforagency?useUnicode=true&characterEncoding=utf8&useSSL=false
datasource.secondary.username=****
datasource.secondary.password=******
datasource.secondary.driver-class-name=com.mysql.jdbc.Driver
ApplicationConfiguration配置类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
package com.test.multi.component.config;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
/**
 * Created by Bean on 2017/10/9.
 */
@Configuration
@MapperScan(value = "com.test.multi.component.dao.dao1", sqlSessionFactoryRef = "primarySqlSessionFactory")
public class ApplicationConfiguration {
    @Bean(name = "primaryDatasource")
    @Primary
    @ConfigurationProperties(prefix = "datasource.primary")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }
    @Bean(name = "primarySqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory1(@Qualifier("primaryDatasource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:ClubDataDaoMapper.xml"));
        return bean.getObject();
    }
}
同样的，ApplicationConfiguration2配置类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
package com.test.multi.component.config;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
/**
 * Created by Bean on 2017/10/9.
 */
@Configuration
@MapperScan(value = "com.test.multi.component.dao.dao2", sqlSessionFactoryRef = "secondSqlSessionFactory")
public class ApplicationConfiguration2 {
    @Bean(name = "secondaryDataSource")
    @ConfigurationProperties(prefix = "datasource.secondary")
    public DataSource secondDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }
    @Bean(name = "secondSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory2(@Qualifier("secondaryDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:GmDataDaoMapper.xml"));
        return bean.getObject();
    }
}
  • type(HikariDataSource.class)指定数据源，设置数据库连接池，若无，则数据库默认时间（通常为8小时）无访问会导致连接断开。指定数据源后，配置文件只认jdbcUrl，即datasource.primary.jdbcUrl而不是datasource.primary.url。
  • ApplicationConfiguration与ApplicationConfiguration2配置类，@MapperScan扫描的域要区分开（不同的包），两个dao文件ClubDataDao与GmDataDao分别操作不同的数据源。
  • ApplicationConfiguration与ApplicationConfiguration2配置类，sqlSessionFactoryRef参数声明mybatis sqlsessionfactory来源。
  • @Primary仅注解到单个数据源，作为默认数据源，用作操作频率最高的数据源上（也常用来区分主从数据库，这个与本文无关）。

其他文件

ClubDataDao
1
2
3
4
5
6
7
8
9
10
package com.test.multi.component.dao.dao1;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface ClubDataDao {
    List<Integer> queryClubManagerIdByAgencyId(int agencyID);
}
GmDataDao
1
2
3
4
5
6
7
8
9
package com.test.multi.component.dao.dao2;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface GmDataDao {
    Integer queryGroupIdByAgencyId(int agencyID);
}
`
ClubDataDaoMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
<?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.test.multi.component.dao.dao1.ClubDataDao">
    <select id="queryClubManagerIdByAgencyId" resultType="Integer" parameterType="Integer">
        select
        DISTINCT club_manager_id
        from
        club4agency.agency_club_info
        where
        club_agency_id=#{agencyID};
    </select>
</mapper>
GmDataDaoMapper.xml
1
2
3
4
5
6
7
8
9
10
11
<?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.test.multi.component.dao.dao2.GmDataDao">
    <select id="queryGroupIdByAgencyId" resultType="int">
        select
        groupId
        from
        account
        where agencyID = #{agencyID};
    </select>
</mapper>
CrossOriginFilter
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package com.test.multi.component.filter;
import javax.servlet.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
/**
 * Created by Bean on 2017/10/9.
 */
public class CrossOriginFilter implements Filter {
    @Override
    public void destroy() {
        // TODO Auto-generated method stub
    }
    @Override
    public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)
            throws IOException, ServletException {
        HttpServletRequest httpServletRequest = (HttpServletRequest) request;
        HttpServletResponse httpServletResponse = (HttpServletResponse) response;
        httpServletResponse.setHeader("Access-Control-Allow-Origin", "*");
        chain.doFilter(httpServletRequest, httpServletResponse);
    }
    @Override
    public void init(FilterConfig arg0) throws ServletException {
        // TODO Auto-generated method stub
    }
}
Swagger2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
package com.test.multi.component.doc;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import springfox.documentation.builders.ApiInfoBuilder;
import springfox.documentation.builders.PathSelectors;
import springfox.documentation.builders.RequestHandlerSelectors;
import springfox.documentation.service.ApiInfo;
import springfox.documentation.service.Contact;
import springfox.documentation.spi.DocumentationType;
import springfox.documentation.spring.web.plugins.Docket;
import springfox.documentation.swagger2.annotations.EnableSwagger2;
/**
 * Created by Bean on 2017/10/9.
 */
@Configuration
@EnableSwagger2
public class Swagger2 {
    public static Contact contact = new Contact("MonkeyBean", "http://hostip:8095/swagger-ui.html", "monkeybean_zhang@163.com");
    @Bean
    public Docket createRestApi() {
        return new Docket(DocumentationType.SWAGGER_2)
                .apiInfo(apiInfo())
                .select()
                .apis(RequestHandlerSelectors.basePackage("com.test.multi"))
                .paths(PathSelectors.any())
                .build();
    }
    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("Spring Boot中使用Swagger2构建RESTful APIs")
                .description("多数据源测试")
                .termsOfServiceUrl("https://www.google.com/")
                .contact(contact)
                .version("1.0")
                .build();
    }
}

测试类TestController

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
package com.test.multi.controller;
import com.test.multi.component.dao.dao1.ClubDataDao;
import com.test.multi.component.dao.dao2.GmDataDao;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import java.util.HashMap;
import java.util.List;
/**
 * Created by Bean on 2017/10/9.
 */
@RequestMapping(path = "test")
@RestController
public class TestController {
    @Autowired
    private ClubDataDao clubDataDao;
    @Autowired
    private GmDataDao gmDataDao;
    /**
     * 测试多数据源
     */
    @ApiOperation(value = "testMultiDataSource")
    @RequestMapping(path = "multi", method = RequestMethod.GET)
    public HashMap<String, Object> testMulti(@RequestParam(value = "agentId") Integer agentId) {
        HashMap<String, Object> result = new HashMap<>();
        List<Integer> managerIdList = clubDataDao.queryClubManagerIdByAgencyId(agentId);
        result.put("managerIdList", managerIdList);
        Integer groupId = gmDataDao.queryGroupIdByAgencyId(agentId);
        result.put("groupId", groupId);
        return result;
    }
}
使用swagger2测试如下：

multi_dataSource_swagger2

总结

本例关键为配置类的@Primary(同一接口可能有多个实现类，声明默认情况下采取一种)注解，当前声明在数据源的bean上，告知Spring默认注入哪个。若不加，则报错:

1
Parameter 0 of method environmentMvcEndpoint in org.springframework.boot.actuate.autoconfigure.EndpointWebMvcManagementContextConfiguration required a single bean, but 2 were found

其他方案：若不加@Primary, 在启动类上声明排除数据源配置，效果相同

1
2
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class, MybatisAutoConfiguration.class})
public class MainApplication {}