SpringBoot+Mybatis实现数据库读写分离

application.yml配置文件配置数据源：

spring.datasource.master.jdbc-url: jdbc:mysql://127.0.0.1:3306/write?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT
spring.datasource.master.username: root
spring.datasource.master.password: root
spring.datasource.master.driver-class-name: com.mysql.cj.jdbc.Driver


spring.datasource.slave1.jdbc-url: jdbc:mysql://127.0.0.1:3306/read?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT
spring.datasource.slave1.username: root
spring.datasource.slave1.password: root
spring.datasource.slave1.driver-class-name: com.mysql.cj.jdbc.Driver


spring.datasource.slave2.jdbc-url: jdbc:mysql://127.0.0.1:3306/read1?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT
spring.datasource.slave2.username: root
spring.datasource.slave2.password: root
spring.datasource.slave2.driver-class-name: com.mysql.cj.jdbc.Driver

配置Dept.java实体对象：

/**
 * 部门表
 */
@TableName("sys_dept")
public class Dept implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * 主键id
     */
    @TableField("DEPT_ID")
    private Long deptId;
    /**
     * 父部门id
     */
    @TableField("PID")
    private Long pid;
    /**
     * 父级ids
     */
    @TableField("PIDS")
    private String pids;
    /**
     * 简称
     */
    @TableField("SIMPLE_NAME")
    private String simpleName;
    /**
     * 全称
     */
    @TableField("FULL_NAME")
    private String fullName;
    /**
     * 描述
     */
    @TableField("DESCRIPTION")
    private String description;
    /**
     * 版本（乐观锁保留字段）
     */
    @TableField("VERSION")
    private Integer version;
    /**
     * 排序
     */
    @TableField("SORT")
    private Integer sort;
    /**
     * 创建时间
     */
    @TableField(value = "CREATE_TIME", fill = FieldFill.INSERT)
    private Date createTime;
    /**
     * 修改时间
     */
    @TableField(value = "UPDATE_TIME", fill = FieldFill.UPDATE)
    private Date updateTime;
    /**
     * 创建人
     */
    @TableField(value = "CREATE_USER", fill = FieldFill.INSERT)
    private Long createUser;
    /**
     * 修改人
     */
    @TableField(value = "UPDATE_USER", fill = FieldFill.UPDATE)
    private Long updateUser;


    public Long getDeptId() {
        return deptId;
    }

    public void setDeptId(Long deptId) {
        this.deptId = deptId;
    }

    public Long getPid() {
        return pid;
    }

    public void setPid(Long pid) {
        this.pid = pid;
    }

    public String getPids() {
        return pids;
    }

    public void setPids(String pids) {
        this.pids = pids;
    }

    public String getSimpleName() {
        return simpleName;
    }

    public void setSimpleName(String simpleName) {
        this.simpleName = simpleName;
    }

    public String getFullName() {
        return fullName;
    }

    public void setFullName(String fullName) {
        this.fullName = fullName;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getVersion() {
        return version;
    }

    public void setVersion(Integer version) {
        this.version = version;
    }

    public Integer getSort() {
        return sort;
    }

    public void setSort(Integer sort) {
        this.sort = sort;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }

    public Long getCreateUser() {
        return createUser;
    }

    public void setCreateUser(Long createUser) {
        this.createUser = createUser;
    }

    public Long getUpdateUser() {
        return updateUser;
    }

    public void setUpdateUser(Long updateUser) {
        this.updateUser = updateUser;
    }

    @Override
    public String toString() {
        return "Dept{" +
        ", deptId=" + deptId +
        ", pid=" + pid +
        ", pids=" + pids +
        ", simpleName=" + simpleName +
        ", fullName=" + fullName +
        ", description=" + description +
        ", version=" + version +
        ", sort=" + sort +
        ", createTime=" + createTime +
        ", updateTime=" + updateTime +
        ", createUser=" + createUser +
        ", updateUser=" + updateUser +
        "}";
    }
}

DeptMapper.java类：

public interface DeptMapper extends BaseMapper<Dept> {

    /**
     * 获取ztree的节点列表
     */
    List<ZTreeNode> tree();

    /**
     * 获取所有部门列表
     */
    Page<Map<String, Object>> list(@Param("page") Page page, @Param("condition") String condition, @Param("deptId") String deptId);

    /**
     * 获取所有部门树列表
     */
    List<TreeviewNode> treeviewNodes();

    List<Dept> selectDeptList(@Param("description") String description);
}

DeptMapper.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="cn.stylefeng.guns.modular.system.mapper.DeptMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="cn.stylefeng.guns.modular.system.entity.Dept">
        <id column="DEPT_ID" property="deptId"/>
        <result column="PID" property="pid"/>
        <result column="PIDS" property="pids"/>
        <result column="SIMPLE_NAME" property="simpleName"/>
        <result column="FULL_NAME" property="fullName"/>
        <result column="DESCRIPTION" property="description"/>
        <result column="VERSION" property="version"/>
        <result column="SORT" property="sort"/>
        <result column="CREATE_TIME" property="createTime"/>
        <result column="UPDATE_TIME" property="updateTime"/>
        <result column="CREATE_USER" property="createUser"/>
        <result column="UPDATE_USER" property="updateUser"/>
    </resultMap>
    
    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        DEPT_ID AS deptId, PID AS pid, PIDS AS pids, SIMPLE_NAME AS simpleName, FULL_NAME AS fullName, DESCRIPTION AS description, VERSION AS version, SORT AS sort, CREATE_TIME AS createTime, UPDATE_TIME AS updateTime, CREATE_USER AS createUser, UPDATE_USER AS updateUser
    </sql>

    <insert id="insert" parameterType="cn.stylefeng.guns.modular.system.entity.Dept">
        insert into sys_dept(DEPT_ID,PID,PIDS,SIMPLE_NAME,FULL_NAME,DESCRIPTION
        ,VERSION,SORT,CREATE_TIME,UPDATE_TIME,CREATE_USER,UPDATE_USER)
        values (#{deptId,jdbcType=BIGINT},#{pid,jdbcType=BIGINT},#{pids,jdbcType=VARCHAR},
        #{simpleName,jdbcType=VARCHAR},#{fullName,jdbcType=VARCHAR},#{description,jdbcType=VARCHAR},
        #{version,jdbcType=BIGINT},#{sort,jdbcType=BIGINT},#{createTime,jdbcType=TIMESTAMP},
        #{updateTime,jdbcType=TIMESTAMP},#{createUser,jdbcType=BIGINT},#{updateUser,jdbcType=BIGINT}
        )
    </insert>
    
    <select id="tree" resultType="cn.stylefeng.guns.core.common.node.ZTreeNode">
		select DEPT_ID AS id, PID as pId, SIMPLE_NAME as name,
		(
		CASE
		WHEN (PID = 0 OR PID IS NULL) THEN
		'true'
		ELSE
		'false'
		END
		) as open from sys_dept
	</select>

    <select id="list" resultType="map">
        select
        <include refid="Base_Column_List"/>
        from sys_dept where 1 = 1
        <if test="condition != null and condition != ''">
            and SIMPLE_NAME like CONCAT('%',#{condition},'%') or FULL_NAME like CONCAT('%',#{condition},'%')
        </if>
        <if test="deptId != null and deptId != ''">
            and (DEPT_ID = #{deptId} or DEPT_ID in ( select DEPT_ID from sys_dept where PIDS like CONCAT('%[', #{deptId}, ']%') ))
        </if>
        order by SORT ASC
    </select>

    <select id="selectDeptList" parameterType="java.lang.String" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from sys_dept
        where DESCRIPTION = #{description,jdbcType=VARCHAR}
    </select>

    <select id="treeviewNodes" resultType="cn.stylefeng.guns.core.common.node.TreeviewNode">
        select DEPT_ID AS tags, PID as parentId, SIMPLE_NAME as text from sys_dept
    </select>

</mapper>

配置SpringAOP切面：

/**
 * @author chenbin
 * @ClassName DataSourceAop
 * @Description TODO
 * @date 2019/6/23 11:58
 * @Vsersion
 */
@Aspect
@Component
public class DataSourceAop {


    /**
     * 配置只读切入点
     */
    @Pointcut("!@annotation(cn.stylefeng.guns.annotation.Master)" +
              "&& (execution(* cn.stylefeng.guns.service..*.select*(..)))" +
              "|| (execution(* cn.stylefeng.guns.service..*.get*(..)))")
    public void readPointcut() {
    }

    @Pointcut("@annotation(cn.stylefeng.guns.annotation.Master)" +
              "|| (execution(* cn.stylefeng.guns.service..*.insert*(..)))" +
              "|| (execution(* cn.stylefeng.guns.service..*.add*(..)))" +
              "|| (execution(* cn.stylefeng.guns.service..*.update*(..)))" +
              "|| (execution(* cn.stylefeng.guns.service..*.edit*(..)))" +
              "|| (execution(* cn.stylefeng.guns.service..*.delete*(..)))" +
              "|| (execution(* cn.stylefeng.guns.service..*.remove*(..)))")
    public void writePointcut() {
    }

    @Before("readPointcut()")
    public void read(JoinPoint point) throws Exception{
        String currentClassName = point.getTarget().getClass().getName();//根据切点获取当前调用的类名
        String methodName = point.getSignature().getName();//根据切点获取当前调用的类方法
        Object[] args = point.getArgs();//根据切点获取当前类方法的参数
        System.out.println("开始执行:"+currentClassName+"."+methodName+"()方法...");
        Class reflexClassName = Class.forName(currentClassName);//根据反射获取当前调用类的实例
        Method[] methods = reflexClassName.getMethods();//获取该实例的所有方法
        DBContextHolder.slave();
    }

    @Before("writePointcut()")
    public void write(JoinPoint point) {
        String className = point.getTarget().getClass().getName();
        String methodName = point.getSignature().getName();
        System.out.println("开始执行:"+className+"."+methodName+"()方法...");
        DBContextHolder.master();
    }
}

配置数据库路由：

/**
 * @author chenbin
 * @ClassName DBContextHolder
 * @Description TODO
 * @date 2019/6/23 11:58
 * @Vsersion
 */
public class DBContextHolder {

    private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();

    private static final AtomicInteger counter = new AtomicInteger(-1);

    public static void set(DBTypeEnum dbType) {
        contextHolder.set(dbType);
    }

    public static DBTypeEnum get() {
        return contextHolder.get();
    }

    public static void master() {
        set(DBTypeEnum.MASTER);
        System.out.println("切换到master数据库。。。");
    }

    public static void slave() {
        //轮询
        int index = counter.getAndIncrement() % 2;
        if (counter.get()>9999) {
            counter.set(-1);
        }
        if (index == 0) {
            set(DBTypeEnum.SLAVE1);
            System.out.println("切换到slave1数据库。。。");
        } else {
            set(DBTypeEnum.SLAVE2);
            System.out.println("切换到slave2数据库。。。");
        }
    }
}

配置数据库路由获取MyRoutingDataSource.java：

/**
 * @author chenbin
 * @ClassName MyRoutingDataSource
 * @Description TODO
 * @date 2019/6/23 11:59
 * @Vsersion
 */
public class MyRoutingDataSource extends AbstractRoutingDataSource {


    @Override
    protected Object determineCurrentLookupKey() {
        return DBContextHolder.get();
    }
}

配置数据源DataSourceConfig.java

/**
 * @author chenbin
 * @ClassName DataSourceConfig
 * @Description TODO
 * @date 2019/6/23 11:59
 * @Vsersion
 */
@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        System.out.println("创建mater数据库连接...");
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.slave1")
    public DataSource slave1DataSource() {
        System.out.println("创建slave1数据库连接...");
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.slave2")
    public DataSource slave2DataSource() {
        System.out.println("创建slave2数据库连接...");
        return DataSourceBuilder.create().build();
    }

    @Bean
    public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                          @Qualifier("slave1DataSource") DataSource slave1DataSource,
                                          @Qualifier("slave2DataSource") DataSource slave2DataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
        targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);
        targetDataSources.put(DBTypeEnum.SLAVE2, slave2DataSource);
        MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
        myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
        myRoutingDataSource.setTargetDataSources(targetDataSources);
        return myRoutingDataSource;
    }
}

配置Mybatis与数据库交互：

/**
 * @author chenbin
 * @ClassName MyBatisConfig
 * @Description TODO
 * @date 2019/6/23 12:00
 * @Vsersion
 */
@EnableTransactionManagement
@Configuration
public class MyBatisConfig {

    @Resource(name = "myRoutingDataSource")
    private DataSource myRoutingDataSource;

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
        sqlSessionFactoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public PlatformTransactionManager platformTransactionManager() {
        return new DataSourceTransactionManager(myRoutingDataSource);
    }
}

配置数据库节点枚举类：

public enum DBTypeEnum {

    MASTER,SLAVE1,SLAVE2
}

配置实体对应的service类：

/**
 * @author chenbin
 * @ClassName DeptService
 * @Description TODO
 * @date 2019/6/23 12:06
 * @Vsersion
 */
public interface DeptService {


    public int insert(Dept dept);

    public int save(Dept dept);

    public List<Dept> selectAll();


}

配置实体对应的service实现类：

/**
 * @author chenbin
 * @ClassName DeptServiceImpl
 * @Description TODO
 * @date 2019/6/23 12:06
 * @Vsersion
 */
@Service
public class DeptServiceImpl implements DeptService {

    @Autowired
    private DeptMapper deptMapper;

    @Transactional
    @Override
    public int insert(Dept dept) {
        System.out.println("入参为：" + dept.toString());
        return deptMapper.insert(dept);
    }

    @Master
    @Override
    public int save(Dept dept) {
        return deptMapper.insert(dept);
    }

    @Override
    public List<Dept> selectAll() {
        return deptMapper.selectDeptList("001");
    }
}

测试类：

public class DeptTest extends BaseJunit {

    @Resource
    DeptMapper deptMapper;
    @Autowired
    private DeptService deptService;

    
    @Test
    public void testWrite() {
        Dept dept = new Dept();
        dept.setDeptId(61L);
        dept.setPid(60L);
        dept.setPids("[0],[24],");
        dept.setSimpleName("政府事业本部");
        dept.setFullName("开发七部");
        dept.setCreateTime(new Date());
        dept.setUpdateTime(new Date());
        deptService.insert(dept);
    }

    @Test
    public void testRead() {
        List<Dept> list = deptService.selectAll();
        System.out.println(JSON.toJSONString(list));
    }

}

以上就是springboot+Mybatis实现数据读写分离。