崛起于SpringBoot2.X + 多数据源主从分离(68)

原创
2019/12/23 11:01
阅读数 606

《SpringBoot2.X心法总纲》 

上一篇博客已经讲述了Aop的使用,接下来会结合Aop配置两个数据源的主从分离,数据库score和point02

目录结构

数据库表

CREATE TABLE `t_employee` (
  `employee_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工ID',
  `name` varchar(64) NOT NULL COMMENT '员工名称',
  `cert_num` varchar(64) NOT NULL COMMENT '身份证号',
  PRIMARY KEY (`employee_id`),
  KEY `cert_num` (`cert_num`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=100083 DEFAULT CHARSET=utf8

1、pom依赖

<!--web-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--lombok-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>
<!--jdbc-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--mysql-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<!--mybatis-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>
<!--druid-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.10</version>
</dependency>
<!--aop-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-aop</artifactId>
</dependency>

2、配置aop

根据规则,查询走从库,更新、添加等走主库

@Aspect
@Component
@Slf4j
public class DataSourceAOP {

    @Before("(@annotation(com.jd.jdshop.config.druid.Master)" +
            "&& !@annotation(com.jd.jdshop.config.druid.Slave)" +
            "||  execution(* com.jd.jdshop.service..*.update*(..))" +
            "||  execution(* com.jd.jdshop.service..*.save*(..))" +
            "||  execution(* com.jd.jdshop.service..*.insert*(..))" +
            "||  execution(* com.jd.jdshop.service..*.delete*(..))" +
            ")")
    public void setWriteDataSourceType() {
        DynamicDataSource.master();
        log.info("dataSource切换到:master");
    }

    @Before("(@annotation(com.jd.jdshop.config.druid.Slave)" +
            "&& !@annotation(com.jd.jdshop.config.druid.Master)" +
            "||  execution(* com.jd.jdshop.service..*.query*(..))" +
            "||  execution(* com.jd.jdshop.service..*.get*(..))" +
            ")")
    public void setReadDataSourceType() {
        DynamicDataSource.slave();
        log.info("dataSource切换到:slave");
    }

   @After("(" +
           "execution(* com.jd.jdshop.service..*.query*(..))" +
           "|| execution(* com.jd.jdshop.service..*.get*(..))" +
           "|| execution(* com.jd.jdshop.service..*.update*(..))" +
           "|| execution(* com.jd.jdshop.service..*.insert*(..))" +
           "|| execution(* com.jd.jdshop.service..*.save*(..))" +
           "|| execution(* com.jd.jdshop.service..*.delete*(..))" +
           "|| @annotation(com.jd.jdshop.config.druid.Slave)" +
           "|| @annotation(com.jd.jdshop.config.druid.Master)" +
           ")")
    public void clean() {
        DynamicDataSource.cleanAll();
        log.info("======dataSource cleanAll======");
    }
}

3、配置文件

server:
  port: 8085

datasource:
  business:
    url: jdbc:mysql://localhost:3306/score?useUnicode=true&characterEncoding=utf-8
    username: admin
    password: admin
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
  #readonly
  business_slave:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://localhost:3307/score?useUnicode=true&characterEncoding=utf-8
    username: admin
    password: admin
    driver-class-name: com.mysql.cj.jdbc.Driver
  point02:
    url: jdbc:mysql://localhost:3306/point2?useUnicode=true&characterEncoding=utf-8
    username: admin
    password: admin
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    #readonly
  point02_slave:
    type: com.alibaba.druid.pool.DruidDataSource
    url: jdbc:mysql://localhost/point2?useUnicode=true&characterEncoding=utf-8
    username: admin
    password: admin
    driver-class-name: com.mysql.cj.jdbc.Driver

spring:
  #druid config
  datasource:
    druid:
      initial-size: 5
      min-idle: 5
      max-active: 20
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 300000
      validation-query: SELECT 1
      test-on-borrow: false
      test-on-return: false
      filter:
        stat:
          log-slow-sql: true

4、主从库注解

/**
 * 切主库
 */
public @interface Master {
}
/**
 * 切从库
 */
public @interface Slave {
}

5、切换数据库配置

@Configuration
@EnableTransactionManagement
@Slf4j
public class Ms1Config {

    @Value("${datasource.business.url}")
    private String master_url;
    @Value("${datasource.business.username}")
    private String master_username;
    @Value("${datasource.business.password}")
    private String master_password;
    @Value("${datasource.business.driver-class-name}")
    private String master_DriverClassName;
    @Value("${datasource.business_slave.url}")
    private String slave_url;
    @Value("${datasource.business_slave.username}")
    private String slave_username;
    @Value("${datasource.business_slave.password}")
    private String slave_password;
    @Value("${datasource.business_slave.driver-class-name}")
    private String slave_DriverClassName;

    public Ms1Config(){
        log.info("####################  DataBaseConfiguration");
    }

    private DataSource master() {
        log.info("注入 Master druid!!!");
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(master_url);
        datasource.setDriverClassName(master_DriverClassName);
        datasource.setUsername(master_username);
        datasource.setPassword(master_password);
        return datasource;
    }

    private DataSource slave() {
        log.info("Slave druid!!!");
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(slave_url);
        datasource.setDriverClassName(slave_DriverClassName);
        datasource.setUsername(slave_username);
        datasource.setPassword(slave_password);
        return datasource;
    }

    @Bean(name = "dynamicDataSource")
    public DynamicDataSource dynamicDataSource() {
        DataSource master = master();
        DataSource slave = slave();
        Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
        targetDataSources.put(DynamicDataSource.DatabaseType.Master, master);
        targetDataSources.put(DynamicDataSource.DatabaseType.Slave, slave);

        DynamicDataSource dataSource = new DynamicDataSource();
        // 该方法是AbstractRoutingDataSource的方法
        dataSource.setTargetDataSources(targetDataSources);
        dataSource.setDefaultTargetDataSource(master);
        return dataSource;
    }
}
@Configuration
@EnableTransactionManagement
@Slf4j
public class Ms2Config {

    @Value("${datasource.point02.url}")
    private String master_url;
    @Value("${datasource.point02.username}")
    private String master_username;
    @Value("${datasource.point02.password}")
    private String master_password;
    @Value("${datasource.point02.driver-class-name}")
    private String master_DriverClassName;
    @Value("${datasource.point02_slave.url}")
    private String slave_url;
    @Value("${datasource.point02_slave.username}")
    private String slave_username;
    @Value("${datasource.point02_slave.password}")
    private String slave_password;
    @Value("${datasource.point02_slave.driver-class-name}")
    private String slave_DriverClassName;

    public Ms2Config(){
        log.info("####################  DataBaseConfiguration");
    }

    private DataSource master() {
        log.info("注入 Master druid!!!");
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(master_url);
        datasource.setDriverClassName(master_DriverClassName);
        datasource.setUsername(master_username);
        datasource.setPassword(master_password);
        return datasource;
    }

    private DataSource slave() {
        log.info("Slave druid!!!");
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(slave_url);
        datasource.setDriverClassName(slave_DriverClassName);
        datasource.setUsername(slave_username);
        datasource.setPassword(slave_password);
        return datasource;
    }

    @Bean(name = "dynamicDataSource2")
    public DynamicDataSource dynamicDataSource() {
        DataSource master = master();
        DataSource slave = slave();
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DynamicDataSource.DatabaseType.Master, master);
        targetDataSources.put(DynamicDataSource.DatabaseType.Slave, slave);

        DynamicDataSource dataSource = new DynamicDataSource();
        // 该方法是AbstractRoutingDataSource的方法
        dataSource.setTargetDataSources(targetDataSources);
        dataSource.setDefaultTargetDataSource(master);
        return dataSource;
    }
}
public class DynamicDataSource extends AbstractRoutingDataSource {
    private static final ThreadLocal<DatabaseType> contextHolder = new ThreadLocal<DatabaseType>();

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

    public enum DatabaseType {
        Master, Slave
    }

    public static void master() {
        contextHolder.set(DatabaseType.Master);
    }


    public static void slave() {
        contextHolder.set(DatabaseType.Slave);
    }

    public static void setDatabaseType(DatabaseType type) {
        contextHolder.set(type);
    }

    public static DatabaseType getType() {
        return contextHolder.get();
    }

    public static void cleanAll(){
        contextHolder.remove();
    }
}
@Slf4j
@Configuration
public class DruidConfiguration {

    @Value("${druidConfig.druidName}")
    private String druidName;

    @Value("${druidConfig.druidPwd}")
    private String druidPwd;
    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");
        reg.addInitParameter("loginUsername", druidName);
        reg.addInitParameter("loginPassword", druidPwd);
        return reg;
    }

    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
        filterRegistrationBean.setFilter(new WebStatFilter());
        filterRegistrationBean.addUrlPatterns("/*");
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        filterRegistrationBean.addInitParameter("profileEnable", "true");
        return filterRegistrationBean;
    }
}
@Configuration
@MapperScan(basePackages = "com.jd.jdshop.mapper.score", sqlSessionTemplateRef  = "businessSqlSessionTemplate")
public class DbScoreConfig {

    @Bean(name = "businessSqlSessionFactory")
    public SqlSessionFactory managerSqlSessionFactory(@Qualifier("dynamicDataSource") DynamicDataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setTypeAliasesPackage("com.jd.jdshop.entity");
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/score/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "businessTransactionManager")
    public DataSourceTransactionManager managerTransactionManager(@Qualifier("dynamicDataSource") DynamicDataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "businessSqlSessionTemplate")
    public SqlSessionTemplate managerSqlSessionTemplate(@Qualifier("businessSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}
@Configuration
@MapperScan(basePackages = "com.jd.jdshop.mapper.point02", sqlSessionTemplateRef  = "businessSqlSessionTemplate2")
public class DbPoint02Config {

    @Bean(name = "businessSqlSessionFactory2")
    public SqlSessionFactory managerSqlSessionFactory(@Qualifier("dynamicDataSource2") DynamicDataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setTypeAliasesPackage("com.jd.jdshop.entity");
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/point02/*.xml"));
        return bean.getObject();
    }

    @Bean(name = "businessTransactionManager2")
    public DataSourceTransactionManager managerTransactionManager(@Qualifier("dynamicDataSource2") DynamicDataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean(name = "businessSqlSessionTemplate2")
    public SqlSessionTemplate managerSqlSessionTemplate(@Qualifier("businessSqlSessionFactory2") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

}

6、mapper层

point02文件下的mapper

public interface EmployeeMapper_point02 {

    @Select({
            "select name,cert_num from t_employee where employee_id = #{id}"
    })
    @Results({
            @Result(column = "employee_id",property = "id",jdbcType = JdbcType.INTEGER),
            @Result(column = "name",property = "name",jdbcType = JdbcType.VARCHAR),
            @Result(column = "cert_num",property = "certNum",jdbcType = JdbcType.VARCHAR)
    })
    EmployeeEntity getEmployeeById(@Param("id") int id);
}

score文件下的mapper

public interface EmployeeMapper {
    @Select({
            "select employee_id,name,cert_num from t_employee where employee_id = #{id}"
    })
    @Results({
            @Result(column = "employee_id",property = "id",jdbcType = JdbcType.INTEGER),
            @Result(column = "name",property = "name",jdbcType = JdbcType.VARCHAR),
            @Result(column = "cert_num",property = "certNum",jdbcType = JdbcType.VARCHAR)
    })
    EmployeeEntity getEmployeeById(@Param("id") int id);

    @Insert(
            "insert into t_employee(employee_id,name,cert_num) VALUES (#{id},#{name},#{certNum}) "
    )
    int insertEmployee(EmployeeEntity employeeEntity);
}

7、service层

public interface EmployeeService {

    EmployeeEntity getEmployeeById(int id);

    void insertEmployee();

    EmployeeEntity getPoint02ById(int id);
}
@Service
public class EmployeeServiceImpl implements EmployeeService {

    @Autowired
    EmployeeMapper employeeMapper;

    @Autowired
    EmployeeMapper_point02 employeeMapper_point02;

    @Override
    public EmployeeEntity getEmployeeById(int id) {
        return employeeMapper.getEmployeeById(id);
    }

    @Override
    public void insertEmployee() {
        EmployeeEntity employeeEntity = new EmployeeEntity();
        employeeEntity.setCertNum("2323242442");
        employeeEntity.setId(100+ new Random().nextInt(1000));
        employeeEntity.setName("ceshi");
        employeeMapper.insertEmployee(employeeEntity);
    }

    @Override
    public EmployeeEntity getPoint02ById(int id) {
        return employeeMapper_point02.getEmployeeById(id);
    }
}

8、实体类

@Data
@ToString
@AllArgsConstructor
@NoArgsConstructor
public class EmployeeEntity {
    private int id;
    private String name;
    private String certNum;
}

9、controller层

@RestController
@Slf4j
public class EmployeeController {

    @Autowired
    EmployeeService employeeService;

    @GetMapping(value = "/test1")
    public String test1(int id, int type){
        EmployeeEntity employeeEntity = employeeService.getEmployeeById(id);
        if (type == 1){
            employeeService.insertEmployee();
        }
        log.info(employeeEntity.toString());
        return "success";
    }

    @Autowired
    EmployeeMapper_point02 employeeMapper_point02;

    @GetMapping(value = "/test3")
    public String test3(int id){
        EmployeeEntity employeeEntity = employeeService.getPoint02ById(id);
        System.out.println(employeeEntity.toString());
        return "success";
    }

    @GetMapping(value = "/test2")
    public String test2(int id){
        EmployeeEntity employeeEntity = employeeMapper_point02.getEmployeeById(id);
        System.out.println(employeeEntity.toString());

        return "success";
    }
}

10、测试

访问:http://localhost:8085/test1?id=121&type=2

如图:

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部