四、springboot搭建mysql主从数据库读写分离

1、搭建简单的ssm框架。继承springboot -parent3.0.5，项目结构

common模块公有，provider是主要的提供者服务，consumer暂时不管

    <dependencies>
        <dependency>
            <groupId>org.bamboo</groupId>
            <artifactId>common</artifactId>
            <version>1.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>


        <!-- 测试环境 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </dependency>


         <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>3.0.2</version>
        </dependency>

        <!-- 数据库相关配置启动器 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
          <!-- 驱动类-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.28</version>
        </dependency>
        <!-- druid启动器的依赖  -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-3-starter</artifactId>
            <version>1.2.18</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.28</version>
        </dependency>

       

2、yaml文件配置,数据库 ip 需要改成自己的

server:
  port: 8800

spring:
  application:
    name: provider-app
  master:
    datasource:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://ip:3308/studb
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver
  slave:
    datasource:
      type: com.alibaba.druid.pool.DruidDataSource
      url: jdbc:mysql://ip:3307/studb
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver

3、连接池需要特殊处理才能工作

在resource下新建两层文件夹META-INF/spring（注意是两层）

新建文件org.springframework.boot.autoconfigure.AutoConfiguration.imports（整个都是文件名）

文件内容如下

com.alibaba.druid.spring.boot3.autoconfigure.DruidDataSourceAutoConfigure

可解决德鲁伊连接池适配springboot3的问题

4、测试代码：

public class DbUtil {
    public static String master="master";
    public static String slave="slave";
 
    private static ThreadLocal<String> threadLocal=new ThreadLocal();
 
 
    public static void setDb(String db){
        threadLocal.set(db);
    }
 
    public static String getDb(){
        return threadLocal.get();
    }
 
}

import com.alibaba.druid.pool.DruidDataSource;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.boot.context.properties.ConfigurationProperties;
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.sql.DataSource;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DruidConfig {
 
    public final static String MAPPER_XML_PATH = "classpath:org/bamboo/mapper/*.xml";
 
    @ConfigurationProperties(prefix = "spring.master.datasource")
    @Bean(name = "masterDataSource")
    public DataSource masterDataSource() {
        return new DruidDataSource();
    }
 
 
    @Bean
    public PlatformTransactionManager txManager(DataSource dynamicDataSource) {
        return new DataSourceTransactionManager(dynamicDataSource);
    }
 
 
    @ConfigurationProperties(prefix = "spring.slave.datasource")
    @Bean
    public DataSource slaveDataSource(){
        DruidDataSource druidDataSource = new DruidDataSource();
        return  druidDataSource;
    }
 
 
    @Bean
    public DynamicDataSource dynamicDataSource(){
        DynamicDataSource dynamicDataSource=new DynamicDataSource();
        Map<Object,Object> map=new HashMap<>();
        map.put(DbUtil.master,masterDataSource());
        map.put(DbUtil.slave,slaveDataSource());
        dynamicDataSource.setDefaultTargetDataSource(masterDataSource());
        dynamicDataSource.setTargetDataSources(map);
        return dynamicDataSource;
    }
 
    @Bean
    public SqlSessionFactoryBean sqlSessionFactoryBean(DataSource dynamicDataSource) throws IOException {
        SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(dynamicDataSource);
        sqlSessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_XML_PATH));
        sqlSessionFactory.setTypeAliasesPackage("org.bamboo.pojo");
        org.apache.ibatis.session.Configuration conf = new org.apache.ibatis.session.Configuration();
        conf.setMapUnderscoreToCamelCase(true);
        sqlSessionFactory.setConfiguration(conf);
        return sqlSessionFactory;
    }
 
    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactoryBean sqlSessionFactoryBean) throws Exception {
        SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactoryBean.getObject());
        return sqlSessionTemplate;
    }
}

import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;

@Slf4j
public class DynamicDataSource extends AbstractRoutingDataSource {
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        log.info("当前使用数据库：{}",DbUtil.getDb());
        return DbUtil.getDb();
    }
}

import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.bamboo.compont.DbUtil;
import org.springframework.stereotype.Component;

import java.lang.reflect.Method;

@Aspect
@Component
public class DatabaseAOP {
    @Pointcut(value = "execution(* org.bamboo.mapper..*.*(..))")
    public void pointCut() {
 
    }
 
    @Before("pointCut()")
    public void before(JoinPoint joinPoint) {
        MethodSignature methodSignature = (MethodSignature) joinPoint.getSignature();
        Method method = methodSignature.getMethod();
        boolean isExist = method.isAnnotationPresent(MasterDataSource.class);
        if (!isExist) {
            DbUtil.setDb(DbUtil.slave);
            return;
        }
        DbUtil.setDb(DbUtil.master);
    }
}

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

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface MasterDataSource {
    String value() default "";
}

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student implements Serializable {
    private static final long serialVersionUID  = 1L;
    private String id;
    private String name;
}

import org.bamboo.apo.MasterDataSource;
import org.bamboo.pojo.Student;

import java.util.List;

public interface StudentMapper  {
    List<Student> queryAll();
    @MasterDataSource
    Student getStudentById(String param);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace = 接口的全限定符 -->
<mapper namespace="org.bamboo.mapper.StudentMapper">

    <select id="queryAll" resultType="org.bamboo.pojo.Student">
        select * from student
    </select>
    <select id="getStudentById" resultType="org.bamboo.pojo.Student">
        select * from student where id=#{id}
    </select>
</mapper>

@SpringBootTest
public class SpringBootMybatisPlusTest {

    @Autowired
    private StudentMapper studentMapper;

    @Test
    public void test(){

     
        List<Student> students = studentMapper.queryAll();
        Student student = studentMapper.getStudentById("1");
        System.out.println("students = " + students);
        System.out.println(student);
    }
}

5、运行结果

可以看到，查询单条数据使用的是注解master