coding……
但行好事 莫问前程

Spring Boot + Mybatis + Druid实现多数据源管理

本文通过使用Druid数据库连接池,完成了多数据源的配置和操作。在讲述之前,希望大家可以了解一下Druid的相关概念和强大。首先讲一下,Druid远远不止是一个数据库连接池,本文所讲的使用Druid配置多数据源也仅仅是Druid的一个简单的应用。墙裂建议一下去看一下Druid Github官方文档

1. 项目结构

|   pom.xml
|   springboot-08-mybatis-druid.iml
|
+---src
|   +---main
|   |   +---java
|   |   |   \---com
|   |   |       \---zhuoli
|   |   |           \---service
|   |   |               \---springboot
|   |   |                   \---mybatis
|   |   |                       \---druid
|   |   |                           |   SpringBootMybatisDruidApplicationContext.java
|   |   |                           |
|   |   |                           +---common
|   |   |                           |   +---enums
|   |   |                           |   |       DataStatusEnum.java
|   |   |                           |   |
|   |   |                           |   \---request
|   |   |                           |           CreateUserRequest.java
|   |   |                           |
|   |   |                           +---controller
|   |   |                           |       HealthCheckController.java
|   |   |                           |       UserController.java
|   |   |                           |
|   |   |                           +---repository
|   |   |                           |   +---conf
|   |   |                           |   |       MasterDataSourceConfig.java
|   |   |                           |   |       SlaveDataSourceConfig.java
|   |   |                           |   |
|   |   |                           |   +---mapper
|   |   |                           |   |   +---master
|   |   |                           |   |   |       MasterUserMapper.java
|   |   |                           |   |   |
|   |   |                           |   |   \---slave
|   |   |                           |   |           SlaveUserMapper.java
|   |   |                           |   |
|   |   |                           |   +---model
|   |   |                           |   |   +---master
|   |   |                           |   |   |       MasterUser.java
|   |   |                           |   |   |       MasterUserExample.java
|   |   |                           |   |   |
|   |   |                           |   |   \---slave
|   |   |                           |   |           SlaveUser.java
|   |   |                           |   |           SlaveUserExample.java
|   |   |                           |   |
|   |   |                           |   \---service
|   |   |                           |       |   UserRepository.java
|   |   |                           |       |
|   |   |                           |       \---impl
|   |   |                           |               UserRepositoryImpl.java
|   |   |                           |
|   |   |                           \---service
|   |   |                               |   UserControllerService.java
|   |   |                               |
|   |   |                               \---impl
|   |   |                                       UserControllerServiceImpl.java
|   |   |
|   |   \---resources
|   |       |   application.properties
|   |       |
|   |       +---autogen
|   |       |       generatorConfig_master.xml
|   |       |       generatorConfig_slave.xml
|   |       |
|   |       \---base
|   |           \---com
|   |               \---zhuoli
|   |                   \---service
|   |                       \---springboot
|   |                           \---mybatis
|   |                               \---druid
|   |                                   \---repository
|   |                                       \---mapper
|   |                                           +---master
|   |                                           |       MasterUserMapper.xml
|   |                                           |
|   |                                           \---slave
|   |                                                   SlaveUserMapper.xml
|   |
|   \---test
|       \---java

MasterDataSourceConfig.java、SlaveDataSourceConfig.java分别用来配置主从数据源

generatorConfig_master.xml、generatorConfig_slave.xml分别用来给主从数据源Mybatis Generator逆向工程生成代码

2. pom.xml

<?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>

    <groupId>com.zhuoli.service</groupId>
    <artifactId>springboot-08-mybatis-druid</artifactId>
    <version>1.0-SNAPSHOT</version>

    <!-- Spring Boot 启动父依赖 -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.3.RELEASE</version>
    </parent>

    <build>
        <plugins>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.5</version>
                <!--如果不配置configuration节点,配置文件名字必须为generatorConfig.xml-->
                <configuration>
                    <!--可以自定义generatorConfig文件名-->
                    <configurationFile>src/main/resources/autogen/generatorConfig_master.xml</configurationFile>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <dependencies>
        <!-- Exclude Spring Boot's Default Logging -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.2</version>
            <scope>provided</scope>
        </dependency>

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

    </dependencies>

</project>

由于存在两个数据源的数据要通过Mybatis Generator逆向工程生成,我没找到一个合适的方法,同时为两个数据源做Mybatis Generator配置。所以我在项目中写了两个generator_config.xml文件,分两次执行Mybatis Generator生成代码(第二次生成时,注意修改pom.xml文件中Mybatis Generator配置文件名generator_***.xml)。

3. application.properties配置

## master 数据源配置
master.datasource.url=jdbc:mysql://115.47.149.48:3306/demo_master?useUnicode=true&characterEncoding=utf8&useSSL=true
master.datasource.username=zhuoli
master.datasource.password=zhuoli
master.datasource.driverClassName=com.mysql.jdbc.Driver

## slave 数据源配置
slave.datasource.url=jdbc:mysql://115.47.149.48:3306/demo_slave?useUnicode=true&characterEncoding=utf8&useSSL=true
slave.datasource.username=zhuoli
slave.datasource.password=zhuoli
slave.datasource.driverClassName=com.mysql.jdbc.Driver

4. 主从数据源配置

@Configuration
@MapperScan(basePackages = "com.zhuoli.service.springboot.mybatis.druid.repository.mapper.master", sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
    @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("classpath:base/com/zhuoli/service/springboot/mybatis/druid/repository/mapper/master/*.xml"));
        /*主库设置sql控制台打印*/
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setLogImpl(StdOutImpl.class);
        sessionFactory.setConfiguration(configuration);
        return sessionFactory.getObject();
    }
}

 

@Configuration
@MapperScan(basePackages = "com.zhuoli.service.springboot.mybatis.druid.repository.mapper.slave", sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class SlaveDataSourceConfig {
    @Value("${slave.datasource.url}")
    private String url;

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

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

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

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

    @Bean(name = "slaveTransactionManager")
    public DataSourceTransactionManager slaveTransactionManager() {
        return new DataSourceTransactionManager(slaveDataSource());
    }

    @Bean(name = "slaveSqlSessionFactory")
    public SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource slaveDataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(slaveDataSource);
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:base/com/zhuoli/service/springboot/mybatis/druid/repository/mapper/slave/*.xml"));
        /*从库设置sql控制台打印*/
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        configuration.setLogImpl(StdOutImpl.class);
        sessionFactory.setConfiguration(configuration);
        return sessionFactory.getObject();
    }
}

Spring Autowired,默认是根据类型Type来自动注入的。但有些特殊情况,对同一个接口,可能会有几种不同的实现类,这时候Spring容器就无法识别使用哪个Bean注入。这时候就要使用@Primary,@Primary标志这个Bean如果在多个同类Bean候选时,该Bean优先被考虑。多数据源配置的时候注意,必须要有一个主数据源,用@Primary标志该Bean

5. Repository操作主从数据源

@Repository
@AllArgsConstructor
public class UserRepositoryImpl implements UserRepository {

    private MasterUserMapper masterUserMapper;

    private SlaveUserMapper slaveUserMapper;

    @Override
    public void createUser(String userName, String description) {
        MasterUser masterUser = new MasterUser();

        /*主数据库插入*/
        masterUser.setUserName(userName);
        masterUser.setDescription(description);
        masterUser.setIsDeleted(DataStatusEnum.EXIST.getCode());
        masterUserMapper.insertSelective(masterUser);

        /*从数据库插入*/
        SlaveUser slaveUser = new SlaveUser();
        slaveUser.setUserName(userName);
        slaveUser.setDescription(description);
        slaveUser.setIsDeleted(DataStatusEnum.EXIST.getCode());
        slaveUserMapper.insertSelective(slaveUser);
    }
}

6. Service层

@Service
@AllArgsConstructor
public class UserControllerServiceImpl implements UserControllerService {
    private UserRepository userRepository;

    @Override
    public void createUser(CreateUserRequest createUserRequest) {
        userRepository.createUser(createUserRequest.getName(), createUserRequest.getDescription());
    }
}

7. Controller

@AllArgsConstructor
@RestController
@RequestMapping(value = "/user")
public class UserController {
    private UserControllerService userControllerService;

    @RequestMapping(value = "/create", method = RequestMethod.POST)
    public ResponseEntity createBalanceReminding(@Valid @RequestBody CreateUserRequest createUserRequest) {
        userControllerService.createUser(createUserRequest);
        return ResponseEntity.status(HttpStatus.OK).body("success");
    }
}

注意controller参数使用的是@RequestBody接收的,测试时,request要使用json传输请求,具体请查看

8. 测试

使用postman进行接口测试,由于controller方法参数使用的是@RequestBody接收的,所以测试时,请求头要设置为raw & json,request如下:

{
	"name": "zhuoli4",
	"description": "zhuoli4 is a lele"
}

9. 控制台输出

SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2d265cbc] was not registered for synchronization because synchronization is not active
2018-08-15 21:45:28.474  INFO 70740 --- [nio-8080-exec-1] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
JDBC Connection [com.mysql.jdbc.JDBC4Connection@55143f28] will not be managed by Spring
==>  Preparing: insert into master_user ( user_name, description, is_deleted ) values ( ?, ?, ? ) 
==> Parameters: zhuoli4(String), zhuoli4 is a lele(String), 0(Integer)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2d265cbc]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1aac9624] was not registered for synchronization because synchronization is not active
2018-08-15 21:45:29.094  INFO 70740 --- [nio-8080-exec-1] com.alibaba.druid.pool.DruidDataSource   : {dataSource-2} inited
JDBC Connection [com.mysql.jdbc.JDBC4Connection@66123670] will not be managed by Spring
==>  Preparing: insert into slave_user ( user_name, description, is_deleted ) values ( ?, ?, ? ) 
==> Parameters: zhuoli4(String), zhuoli4 is a lele(String), 0(Integer)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1aac9624]

说明服务端进行了主从数据库的插入,所数据源配置是生效的。但是也可以看到,示例代码中是没有做事务控制的,在实际开发中一般不会这么做。对于单源数据库,事务控制可以很简单,直接使用Spring的@transactional就可以完成,但是多数据源下,注解是不生效的,下篇文章我会介绍一下多数据源下,事务控制的几种可行方案。

示例代码:码云 – 卓立 – Spring Boot + Mybatis + Druid多源数据库控制

 

赞(0) 打赏
Zhuoli's Blog » Spring Boot + Mybatis + Druid实现多数据源管理
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

zhuoli's blog

联系我关于我

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏