SpringBoot整合Mybatis和ShardingJDBC实现读写分离

1. 首先需要搭建两台已经配置好的主从复制的的数据库服务器，本次演示的两台数据库分别为192.168.9.174(主)和192.168.9.184(从)

2. 安装MySQL数据库和搭建MySQL主从复制结构参见

   CentOS下安装MySQL5.7(图文)

   ContOS下搭建MySQL主从复制

3. 准备数据表

   DROP TABLE IF EXISTS `t_user`;
   CREATE TABLE `t_user` (
     `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
     `username` varchar(50) DEFAULT NULL,
     `password` varchar(50) DEFAULT NULL,
     `birthday` datetime DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4;
   

4. 新建一个SpringBoot工程springboot-mybatis-shardingjdbc

5. 导入相关的maven依赖坐标

   <dependencies>
       <dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-web</artifactId>
       </dependency>
       <dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-configuration-processor</artifactId>
           <optional>true</optional>
       </dependency>
       <dependency>
           <groupId>org.mybatis.spring.boot</groupId>
           <artifactId>mybatis-spring-boot-starter</artifactId>
           <version>2.1.0</version>
       </dependency>
       <!--shardingjdbc整合包-->
       <dependency>
           <groupId>io.shardingsphere</groupId>
           <artifactId>sharding-jdbc</artifactId>
           <version>3.0.0.M3</version>
       </dependency>
       <dependency>
           <groupId>com.alibaba</groupId>
           <artifactId>druid</artifactId>
           <version>1.1.10</version>
       </dependency>
       <dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
           <scope>runtime</scope>
       </dependency>
       <dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-test</artifactId>
           <scope>test</scope>
       </dependency>
       <dependency>
           <groupId>org.mybatis.generator</groupId>
           <artifactId>mybatis-generator-core</artifactId>
           <version>1.3.5</version>
           <scope>provided</scope>
       </dependency>
   </dependencies>
   

6. 添加mybatis-generator插件，用于自动生成数据库对应的代码

   <plugin>
       <groupId>org.mybatis.generator</groupId>
       <artifactId>mybatis-generator-maven-plugin</artifactId>
       <version>1.3.2</version>
       <configuration>
           <configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
           <verbose>true</verbose>
           <overwrite>true</overwrite>
       </configuration>
       <executions>
           <execution>
               <id>Generate MyBatis Artifacts</id>
               <goals>
                   <goal>generate</goal>
               </goals>
           </execution>
       </executions>
       <dependencies>
           <dependency>
               <groupId>org.mybatis.generator</groupId>
               <artifactId>mybatis-generator-core</artifactId>
               <version>1.3.2</version>
           </dependency>
       </dependencies>
   </plugin>
   

7. 在resources\mybatis下创建Mybatis配置文件config.xml

   <?xml version="1.0" encoding="UTF-8" ?>
   <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
   <configuration>
       <settings>
           <!--配置命名规则-->
           <setting name="mapUnderscoreToCamelCase" value="true" />
       </settings>
       <typeAliases>
           <typeAlias alias="Integer" type="java.lang.Integer" />
           <typeAlias alias="Long" type="java.lang.Long" />
           <typeAlias alias="HashMap" type="java.util.HashMap" />
           <typeAlias alias="LinkedHashMap" type="java.util.LinkedHashMap" />
           <typeAlias alias="ArrayList" type="java.util.ArrayList" />
           <typeAlias alias="LinkedList" type="java.util.LinkedList" />
       </typeAliases>
   </configuration>
   

8. 在resources下创建mybatis-generator配置文件generatorConfig.xml

   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE generatorConfiguration
           PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
           "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
   <generatorConfiguration>
       <!--本机数据库驱动jar包存放目录-->
       <classPathEntry    location="C:\Users\Administrator\.m2\repository\mysql\mysql-connector-java\5.1.42\mysql-connector-java-5.1.42.jar"/>
       <context id="DB2Tables"    targetRuntime="MyBatis3">
           <commentGenerator>
               <property name="suppressDate" value="true"/>
               <property name="suppressAllComments" value="true"/>
           </commentGenerator>
           <!--数据库驱动，数据库地址及表名，账号，密码-->
           <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://192.168.9.174:3306/test"    userId="root" password="root">
           </jdbcConnection>
           <javaTypeResolver>
               <property name="forceBigDecimals" value="false"/>
           </javaTypeResolver>
           <!--生成Model类的包名及存放位置-->
           <javaModelGenerator targetPackage="com.kangswx.springbootmybatisplusshardingjdbc.domain" targetProject="src/main/java">
               <property name="enableSubPackages" value="true"/>
               <property name="trimStrings" value="true"/>
           </javaModelGenerator>
           <!--生成映射文件的包名及存放位置-->
           <sqlMapGenerator targetPackage="com.kangswx.springbootmybatisplusshardingjdbc.configuration.mapper.mapping" targetProject="src/main/java">
               <property name="enableSubPackages" value="true"/>
           </sqlMapGenerator>
           <!--生成Dao类的包名及存放位置-->
           <javaClientGenerator type="XMLMAPPER" targetPackage="com.kangswx.springbootmybatisplusshardingjdbc.configuration.mapper" targetProject="src/main/java">
               <property name="enableSubPackages" value="true"/>
           </javaClientGenerator>
           <!--生成对应表及类名,domainObjectName是设置实体类的名字的-->
           <table tableName="t_user" domainObjectName="User" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"></table>
       </context>
   </generatorConfiguration>
   

9. 修改applicationContext.xml为applicationContext.yml并添加下面的配置项

   server:
     port: 8080
   
   spring:
     datasource:
       type: com.alibaba.druid.pool.DruidDataSource
     application:
       name: springboot-mybatisplus-shardingjdbc
   
   sharding.jdbc:
     data-sources:
       ds_master:
         driver-class-name: com.mysql.cj.jdbc.Driver
         url: jdbc:mysql://192.168.9.174:3306/test?useSSL=false  #主库
         username: root
         password: root
       ds_slave_0:
         driver-class-name: com.mysql.cj.jdbc.Driver
         url: jdbc:mysql://192.168.9.184:3306/test?useSSL=false  #从库
         username: root
         password: root
     master-slave-rule:
       name: ds_ms
       master-data-source-name: ds_master
       slave-data-source-names: ds_slave_0
       load-balance-algorithm-type: round_robin
       props:
         sql.show: true
   mybatis:
     config-location: classpath:mybatis/config.xml
     mapper-locations:
       - classpath:mapping/*.xml
   

10. 增ShardingJDBC的配置类ShardingMasterSlaveConfig

    package com.kangswx.springbootmybatisplusshardingjdbc.configuration.shardingjdbc;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import io.shardingsphere.core.api.config.MasterSlaveRuleConfiguration;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    
    import java.util.HashMap;
    import java.util.Map;
    
    @ConfigurationProperties(prefix ="sharding.jdbc")
    public class ShardingMasterSlaveConfig {
    
        private Map<String, DruidDataSource> dataSources = new HashMap<>();
    
        private MasterSlaveRuleConfiguration masterSlaveRule;
    
        public Map<String, DruidDataSource> getDataSources() {
            return dataSources;
        }
    
        public void setDataSources(Map<String, DruidDataSource> dataSources) {
            this.dataSources = dataSources;
        }
    
        public MasterSlaveRuleConfiguration getMasterSlaveRule() {
            return masterSlaveRule;
        }
    
        public void setMasterSlaveRule(MasterSlaveRuleConfiguration masterSlaveRule) {
            this.masterSlaveRule = masterSlaveRule;
        }
    }
    

11. 新增ShardingJDBC的配置类ShardingDataSourceConfig

    package com.kangswx.springbootmybatisplusshardingjdbc.configuration.shardingjdbc;
    
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.google.common.collect.Maps;
    import io.shardingsphere.core.api.MasterSlaveDataSourceFactory;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
    import org.springframework.boot.context.properties.EnableConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    import javax.sql.DataSource;
    import java.sql.SQLException;
    import java.util.HashMap;
    import java.util.Map;
    import java.util.Properties;
    
    @Configuration
    @EnableConfigurationProperties(ShardingMasterSlaveConfig.class)
    @ConditionalOnProperty({"sharding.jdbc.data-sources.ds_master.url", "sharding.jdbc.master-slave-rule.master-data-source-name"})
    public class ShardingDataSourceConfig {
    
        private Logger logger = LoggerFactory.getLogger(ShardingDataSourceConfig.class);
    
        @Autowired(required = false)
        private ShardingMasterSlaveConfig shardingMasterSlaveConfig;
    
        @Bean("dataSource")
        public DataSource masterSlaveDataSource() throws SQLException {
            shardingMasterSlaveConfig.getDataSources().forEach((k, v) -> configDataSource(v));
            Map<String, DataSource> dataSourceMap = Maps.newHashMap();
            dataSourceMap.putAll(shardingMasterSlaveConfig.getDataSources());
            DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource(dataSourceMap, shardingMasterSlaveConfig.getMasterSlaveRule(),  new HashMap<>(), new Properties());
            logger.info("masterSlaveDataSource config complete");
            return dataSource;
        }
    
        private void configDataSource(DruidDataSource druidDataSource) {
            druidDataSource.setMaxActive(20);
            druidDataSource.setInitialSize(1);
            druidDataSource.setMaxWait(60000);
            druidDataSource.setMinIdle(1);
            druidDataSource.setTimeBetweenEvictionRunsMillis(60000);
            druidDataSource.setMinEvictableIdleTimeMillis(300000);
            druidDataSource.setValidationQuery("select 'x'");
            druidDataSource.setTestWhileIdle(true);
            druidDataSource.setTestOnBorrow(false);
            druidDataSource.setTestOnReturn(false);
            druidDataSource.setPoolPreparedStatements(true);
            druidDataSource.setMaxOpenPreparedStatements(20);
            druidDataSource.setUseGlobalDataSourceStat(true);
            try {
                druidDataSource.setFilters("stat,wall,slf4j");
            } catch (SQLException e) {
                logger.error("druid configuration initialization filter", e);
            }
        }
    }
    

12. 利用mybatis-generator自动生成数据库对应的Mapper，mapping和实体类，对于不知道mybatis-generator的可以参考SpringBoot整合Mybatis和Mybatis-generator实现代码自动生成

13. 实体类代码

    package com.kangswx.springbootmybatisplusshardingjdbc.domain;
    
    import java.util.Date;
    
    public class User {
        private Integer id;
    
        private String username;
    
        private String password;
    
        private Date birthday;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username == null ? null : username.trim();
        }
    
        public String getPassword() {
            return password;
        }
    
        public void setPassword(String password) {
            this.password = password == null ? null : password.trim();
        }
    
        public Date getBirthday() {
            return birthday;
        }
    
        public void setBirthday(Date birthday) {
            this.birthday = birthday;
        }
    }
    

14. Mapper代码

    package com.kangswx.springbootmybatisplusshardingjdbc.mapper;
    
    import com.kangswx.springbootmybatisplusshardingjdbc.domain.User;
    import org.apache.ibatis.annotations.Mapper;
    import org.springframework.stereotype.Repository;
    
    @Repository
    @Mapper
    public interface UserMapper {
    
        int deleteByPrimaryKey(Integer id);
    
        int insert(User record);
    
        int insertSelective(User record);
    
        User selectByPrimaryKey(Integer id);
    
        int updateByPrimaryKeySelective(User record);
    
        int updateByPrimaryKey(User record);
    
    }
    

15. mapping代码

    <?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.kangswx.springbootmybatisplusshardingjdbc.mapper.UserMapper" >
    
      <resultMap id="BaseResultMap" type="com.kangswx.springbootmybatisplusshardingjdbc.domain.User" >
        <id column="id" property="id" jdbcType="INTEGER" />
        <result column="username" property="username" jdbcType="VARCHAR" />
        <result column="password" property="password" jdbcType="VARCHAR" />
        <result column="birthday" property="birthday" jdbcType="TIMESTAMP" />
      </resultMap>
    
      <sql id="Base_Column_List" >
        id, username, password, birthday
      </sql>
    
      <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
        select 
        <include refid="Base_Column_List" />
        from t_user
        where id = #{id,jdbcType=INTEGER}
      </select>
    
      <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
        delete from t_user
        where id = #{id,jdbcType=INTEGER}
      </delete>
    
      <insert id="insert" parameterType="com.kangswx.springbootmybatisplusshardingjdbc.domain.User" >
        insert into t_user (id, username, password, 
          birthday)
        values (#{id,jdbcType=INTEGER}, #{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, 
          #{birthday,jdbcType=TIMESTAMP})
      </insert>
    
      <insert id="insertSelective" parameterType="com.kangswx.springbootmybatisplusshardingjdbc.domain.User" >
        insert into t_user
        <trim prefix="(" suffix=")" suffixOverrides="," >
          <if test="id != null" >
            id,
          </if>
          <if test="username != null" >
            username,
          </if>
          <if test="password != null" >
            password,
          </if>
          <if test="birthday != null" >
            birthday,
          </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides="," >
          <if test="id != null" >
            #{id,jdbcType=INTEGER},
          </if>
          <if test="username != null" >
            #{username,jdbcType=VARCHAR},
          </if>
          <if test="password != null" >
            #{password,jdbcType=VARCHAR},
          </if>
          <if test="birthday != null" >
            #{birthday,jdbcType=TIMESTAMP},
          </if>
        </trim>
      </insert>
    
      <update id="updateByPrimaryKeySelective" parameterType="com.kangswx.springbootmybatisplusshardingjdbc.domain.User" >
        update t_user
        <set >
          <if test="username != null" >
            username = #{username,jdbcType=VARCHAR},
          </if>
          <if test="password != null" >
            password = #{password,jdbcType=VARCHAR},
          </if>
          <if test="birthday != null" >
            birthday = #{birthday,jdbcType=TIMESTAMP},
          </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
      </update>
    
      <update id="updateByPrimaryKey" parameterType="com.kangswx.springbootmybatisplusshardingjdbc.domain.User" >
        update t_user
        set username = #{username,jdbcType=VARCHAR},
          password = #{password,jdbcType=VARCHAR},
          birthday = #{birthday,jdbcType=TIMESTAMP}
        where id = #{id,jdbcType=INTEGER}
      </update>
    
    </mapper>
    

16. 新增UserService接口

    package com.kangswx.springbootmybatisplusshardingjdbc.service;
    
    import com.kangswx.springbootmybatisplusshardingjdbc.domain.User;
    
    public interface UserService {
    
        int addUser(User user);
    
        User findById(Integer id);
    }
    

17. 新增UserService的实现类

    package com.kangswx.springbootmybatisplusshardingjdbc.service.impl;
    
    import com.kangswx.springbootmybatisplusshardingjdbc.domain.User;
    import com.kangswx.springbootmybatisplusshardingjdbc.mapper.UserMapper;
    import com.kangswx.springbootmybatisplusshardingjdbc.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    
    @Service
    public class UserServiceImpl implements UserService {
    
        @Autowired
        private UserMapper userMapper;
    
    
        @Override
        public int addUser(User user) {
            return userMapper.insert(user);
        }
    
        @Override
        public User findById(Integer id) {
            return userMapper.selectByPrimaryKey(id);
        }
    }
    

18. 在SpringBoot的启动类上面需要添加下面的注解

    @MapperScan("com.kangswx.springbootmybatisplusshardingjdbc.mapper")
    

19. 编写UserServiceImpl的测试类UserServiceImplTest

    package com.kangswx.springbootmybatisplusshardingjdbc.service.impl;
    
    import com.kangswx.springbootmybatisplusshardingjdbc.domain.User;
    import com.kangswx.springbootmybatisplusshardingjdbc.service.UserService;
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.test.context.junit4.SpringRunner;
    
    import java.text.SimpleDateFormat;
    import java.util.Date;
    
    @RunWith(SpringRunner.class)
    @SpringBootTest
    public class UserServiceImplTest {
    
        @Autowired
        private UserService userService;
    
        /**
         * 读写分离写入测试
         */
        @Test
        public void addUser() {
            User user = new User();
            user.setUsername("张三--+++");
            user.setPassword("1234569090");
            Date date = new Date();
            user.setBirthday(date);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM--dd HH:mm:ss");
            System.out.println(sdf.format(date));
            int ret = userService.addUser(user);
            System.out.println("ret: "+ret);
        }
    
        /**
         * 读写分离读取测试
         */
        @Test
        public void findById() {
            User user = userService.findById(20);
            System.out.println(user.getUsername() + "==" + user.getPassword());
        }
    }
    

20. 测试需要注意的点，因为之前两台服务器已经搭建为主从结构了，所以在读取和写入的时候不知道是从哪台数据库上面进行的，可以再找一台单独的服务器模拟从数据库，在写入的时候发现数据成功插入到了主从两台数据库服务器，配置从库为单独的服务器，在查询的时候找一条在主库中没有的数据进行查询，如果数据能成功查询，则说明读和写的操作发生在不同的服务器上面；至此读写分离成功实现。

21. 上面所有的代码可参见 SpringBoot整合Mybatis和ShardingJDBC实现读写分离