springboot中配置多数据源mybatisPlus

1 背景

springboot配置mybatisPlus，mybatisPlus配置一主二从数据源。

数据源根据在切面方法上配置@DS注解选择对应的数据源，默认数据源为master。配置@DS("slave")注解，随机从slave_1和slave_1中选择从库数据源。

2 版本

• JDK：1.8
• mysql：5.6
• springboot：2.2.9.RELEASE
• mybatis-plus：3.2.0

3 配置

路径说明：

用途	路径
bean	com.sa.example.mybatis.ext.bean
mapper	com.sa.example.mybatis.ext.mapper；com.sa.example.mybatis.auto.mapper
xml	com.sa.example.mybatis.ext.mapper.xml；com.sa.example.mybatis.auto.mapper.xml

3.1 pom配置

3.1.1 父类pom

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.2.9.RELEASE</version>
    <relativePath/>
</parent>

3.1.2 pom依赖

<!-- ==========mybatisPlus依赖========== -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.30</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.23</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.2.0</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.2.0</version>
</dependency>
<!-- ==========springboot-web依赖========== -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- ==========其他依赖========== -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>
    <scope>provided</scope>
</dependency>

3.1.3 插件

保证可以在src的包里配置mapper的xml文件，否则需要再resource文件夹中配置mapper的xml。

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
    </plugins>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.*</include>
            </includes>
        </resource>
        <resource>
            <directory>src/main/resources</directory>
            <includes>
                <include>**/**</include>
            </includes>
        </resource>
    </resources>
</build>

3.2 application.yml配置

spring:
  # ==========mybatis-plus数据源配置==========
  datasource:
    dynamic:
      primary: master #设置默认的数据源或者数据源组,默认值即为master
      strict: false #设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候会抛出异常,不启动则使用默认数据源.
      datasource:
        master:
          url: jdbc:mysql://127.0.0.1:3306/test01?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=round&useAffectedRows=true
          username: root
          password: root
          driver-class-name: com.mysql.jdbc.Driver
          druid:
            initialSize: 100
            minIdle: 50
            maxActive: 200
            maxWait: 60000
            timeBetweenEvictionRunsMillis: 60000
            minEvictableIdleTimeMillis: 300000
            validationQuery: SELECT 'x'
            testWhileIdle: true
            testOnBorrow: false
            testOnReturn: false
            poolPreparedStatements: false
            maxPoolPreparedStatementPerConnectionSize: -1
            filters: stat
        slave_1:
          url: jdbc:mysql://127.0.0.1:3306/test02?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=round&useAffectedRows=true
          username: root
          password: root
          driver-class-name: com.mysql.jdbc.Driver
          druid:
            initialSize: 100
            minIdle: 50
            maxActive: 200
            maxWait: 60000
            timeBetweenEvictionRunsMillis: 60000
            minEvictableIdleTimeMillis: 300000
            validationQuery: SELECT 'x'
            testWhileIdle: true
            testOnBorrow: false
            testOnReturn: false
            poolPreparedStatements: false
            maxPoolPreparedStatementPerConnectionSize: -1
            filters: stat
        slave_2:
          url: jdbc:mysql://127.0.0.1:3306/test03?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=round&useAffectedRows=true
          username: root
          password: root
          driver-class-name: com.mysql.jdbc.Driver
          druid:
            initialSize: 100
            minIdle: 50
            maxActive: 200
            maxWait: 60000
            timeBetweenEvictionRunsMillis: 60000
            minEvictableIdleTimeMillis: 300000
            validationQuery: SELECT 'x'
            testWhileIdle: true
            testOnBorrow: false
            testOnReturn: false
            poolPreparedStatements: false
            maxPoolPreparedStatementPerConnectionSize: -1
            filters: stat

#==========整合mybatisPlus==========
mybatis-plus:
  type-aliases-package: com.lx.ms.db.mybatis.bean.ext
  # 配置mybatis的xml路径
  mapper-locations: classpath:com/sa/example/mybatis/ext/mapper/xml/*.xml,classpath:com/sa/example/mybatis/auto/mapper/xml/*.xml
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: true
    lazy-loading-enabled: false
    aggressive-lazy-loading: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

3.3 启动类配置

@SpringBootApplication(scanBasePackages = "com.sa.example")
// 配置xml的mapper路径
@MapperScan(basePackages = {"com.sa.example.mybatis.ext.mapper", "com.sa.example.mybatis.auto.mapper"})
public class MybatisApplication {
    public static void main(String[] args) {
        SpringApplication.run(MybatisApplication.class, args);
    }
}

4 使用

4.1 建表脚本

CREATE TABLE `c_m_phone` (
  `id` int(10) NOT NULL COMMENT 'id',
  `phone_first` varchar(20) DEFAULT NULL COMMENT '前缀号段',
  `phone_provence` varchar(20) DEFAULT NULL COMMENT '手机所在省份',
  `phone_city` varchar(20) DEFAULT NULL COMMENT '手机所在城市',
  `service` varchar(20) DEFAULT NULL COMMENT '服务商',
  PRIMARY KEY (`id`)
) COMMENT='电话区域表';

4.2 实体类

//包名：com.sa.example.mybatis.ext.bean
@Data
public class CMPhone {
    private Integer id;
    private String phoneFirst;
    private String phoneProvence;
    private String phoneCity;
    private String service;
}

4.3 mapper

// 包名：com.sa.example.mybatis.ext.mapper
public interface CMPhoneMapper {
    /**
     * 保存（配置主库）
     * @param model
     * @return
     */
    @DS("master")
    int save(CMPhone model);
    
    /**
     * 查询（配置从库）
     * @param model
     * @return
     */
    @DS("slave")
    List<CMPhone> findList(CMPhone model);
}

4.4 mapper映射

<!-- 包名：com.sa.example.mybatis.ext.mapper.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.sa.example.mybatis.ext.mapper.CMPhoneMapper">
    <resultMap id="BaseResultMap" type="com.sa.example.mybatis.ext.bean.CMPhone">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="phone_first" property="phoneFirst" jdbcType="VARCHAR"/>
        <result column="phone_provence" property="phoneProvence" jdbcType="VARCHAR"/>
        <result column="phone_city" property="phoneCity" jdbcType="VARCHAR"/>
        <result column="service" property="service" jdbcType="VARCHAR"/>
    </resultMap>
    <select id="findList" parameterType="com.sa.example.mybatis.ext.bean.CMPhone" resultMap="BaseResultMap">
        select id,phone_first,phone_provence,phone_city,service from c_m_phone
    </select>
    <insert id="save" parameterType="com.sa.example.mybatis.ext.bean.CMPhone">
        INSERT INTO `c_m_phone` (`id`, `phone_first`, `phone_provence`, `phone_city`, `service`) VALUES (
            #{id},#{phoneFirst},#{phoneProvence},#{phoneCity},#{service}
        )
    </insert>
</mapper>

4.5 使用

@RunWith(SpringRunner.class)
@SpringBootTest(classes = MybatisApplication.class)
public class MybatisTest {
    
    @Autowired
    private CMPhoneMapper cmPhoneMapper;
    
    @Test
    public void testMapper() {
        List<CMPhone> cmPhoneList = cmPhoneMapper.findList(new CMPhone());
        System.out.println(cmPhoneList);
    }
    
    @Test
    public void testSave(){
        CMPhone model = new CMPhone();
        model.setId(1000);
        model.setPhoneCity("PhoneCity test");
        model.setPhoneFirst("PhoneFirst test");
        model.setPhoneProvence("PhoneProvence test");
        model.setService("Service test");
        cmPhoneMapper.save(model);
    }
}