[Java知识库]SpringBoot实现读写分离

SpringBoot实现读写分离

数据库读写分离配置：linux环境数据库读写分离

gitee地址：SpringBoot实现读写分离

1.目录结构

2.maven依赖

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

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</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>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.8</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.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

3.application.xml

spring:
  datasource:
    master:
      jdbc-url: jdbc:mysql://192.168.153.130:3306/liqq?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false
      username: root
      password: root
      driver-class-name: com.mysql.cj.jdbc.Driver
    slave:
      jdbc-url: jdbc:mysql://192.168.153.128:3306/liqq?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false
      username: liqq   # 只读账户
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver

4.annotation注解

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * 用于标记一些特殊的方法使用到写库
 */
@Target({ElementType.TYPE,ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface Master {
}

5.配置切面（用于更换数据源）

import com.qq.bean.DBContextHolder;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;

/**
 * @author 黔程似景
 * @description 在执行service层前，根据切点选择对应的数据源
 * @date 2021/11/25 22:35
 **/
@Aspect
@Component
public class DataSourceAop {

    /**
     * 查询切点
     */
    @Pointcut("(execution(* com.qq.service..*.select*(..)) " +
            "|| execution(* com.qq.service..*.get*(..))) " +
            "|| execution(* com.qq.service..*.query*(..)) " +
            "|| execution(* com.qq.service..*.find*(..)))")
    public void readPointcut() {

    }

    /**
     * 增删改切点:用master注解标注的，或者为修改数据库的方法
     */
    @Pointcut(
            "@annotation(com.qq.annotation.Master) " +
                    "|| execution(* com.qq.service..*.add*(..)) " +
                    "|| execution(* com.qq.service..*.save*(..)) " +
                    "|| execution(* com.qq.service..*.insert*(..)) " +
                    "|| execution(* com.qq.service..*.update*(..)) " +
                    "|| execution(* com.qq.service..*.delete*(..)) " +
                    "|| execution(* com.qq.service..*.remove*(..))"
    )
    public void writePointcut() {

    }

    @Before("readPointcut()")
    public void read() {
        DBContextHolder.slave();
    }

    @Before("writePointcut()")
    public void write() {
        DBContextHolder.master();
    }

}

6.数据源切换

/**
 * @author 黔程似景
 * @description TODO
 * @date 2021/11/25 22:33
 **/
public class DBContextHolder {

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

    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() {
        set(DBTypeEnum.SLAVE);
        System.out.println("切换到slave");
    }
    
    public enum  DBTypeEnum {
        MASTER, SLAVE
    }

}

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/**
 * @author 黔程似景
 * @description 告知选择的数据源
 * @date 2021/11/25 22:34
 **/
public class MyRoutingDataSource extends AbstractRoutingDataSource {

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

import com.qq.bean.MyRoutingDataSource;
import com.qq.enums.DBTypeEnum;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**
 * @author 黔程似景
 * @description TODO
 * @date 2021/11/25 22:29
 **/
@Configuration
public class DataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 将多数据源加载到数据库路由
     * @param masterDataSource 写数据源
     * @param slaveDataSource 读数据源
     * @return
     */
    @Bean
    public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                          @Qualifier("slaveDataSource") DataSource slaveDataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
        targetDataSources.put(DBTypeEnum.SLAVE, slaveDataSource);
        MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
        myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
        myRoutingDataSource.setTargetDataSources(targetDataSources);
        return myRoutingDataSource;
    }
}

7.mybatis配置

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

import javax.annotation.Resource;
import javax.sql.DataSource;

/**
 * @author 黔程似景
 * @description TODO
 * @date 2021/11/25 22:30
 **/
@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);
    }
}

8.entity

import lombok.Data;

/**
 * @author 黔程似景
 * @description TODO
 * @date 2021/11/25 22:38
 **/
@Data
public class User {

    private int id;

    private String name;
}

9.mapper

import com.qq.entity.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author 黔程似景
 * @description TODO
 * @date 2021/11/25 22:42
 **/
@Repository
public interface UserMapper {

    /**
     * 添加用户
     * @param user 用户
     * @return 影响行数
     */
    @Insert("insert into `user` value(#{id} , name)")
    public int insert(User user);
    
    /**
     * 添加查询用户信息
     * @param user 条件
     * @return 符合条件的用户
     */
    @Select("select * from `user`")
    public List<User> selectByUser(User user);
    
}

10.service

import com.qq.annotation.Master;
import com.qq.entity.User;
import com.qq.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @author 黔程似景
 * @description TODO
 * @date 2021/11/25 22:37
 **/
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Transactional
    @Override
    public int insert(User user) {
        return userMapper.insert(user);
    }

    @Master
    @Override
    public int save(User user) {
        return userMapper.insert(user);
    }

    @Override
    public List<User> selectAll() {
        return userMapper.selectByUser(new User());
    }

    @Master
    @Override
    public String getToken(String appId) {
        //  有些读操作必须读主数据库
        //  比如，获取微信access_token，因为高峰时期主从同步可能延迟
        //  这种情况下就必须强制从主数据读
        return null;
    }

}
interface UserService {

    public int insert(User user);

    public int save(User user);

    public List<User> selectAll();

    public String getToken(String appId);
}

11.启动类

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.qq.mapper")
public class WriteAndReadApplication {
    public static void main(String[] args) {
        SpringApplication.run(WriteAndReadApplication.class, args);
    }
}

12.测试类

import com.qq.entity.User;
import com.qq.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;

/**
 * @author 黔程似景
 * @description TODO
 * @date 2021/11/28 13:17
 **/
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserServiceTest {

    @Autowired
    private UserService userService;

    @Test
    public void testSelectAll(){
        userService.selectAll().forEach(System.out::println);
    }
    
    @Test
    public void testInsert(){
        int i = userService.insert(new User(3, "张三"));
        System.out.println(i>0 ? "添加成功" : "添加失败");
    }

}

13.测试结果

## 14.参考：

1.springboot实现读写分离(基于Mybatis，mysql)

2.SpringBoot+MyBatis+MySQL读写分离