@springBoot 集成mysql 主从与mybatis，实现读写分离

配置文件

spring:
  application:
    name: order-service
  datasource:
    # 主库配置
    master:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://192.168.88.128:3306/emall?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
      username: root
      password: "#Wa123456!"
      type: com.zaxxer.hikari.HikariDataSource
      hikari:
        pool-name: Master-HikariCP
        maximum-pool-size: 20
        minimum-idle: 5
        connection-timeout: 30000
        idle-timeout: 600000
        max-lifetime: 1800000

    # 从库配置
    slave:
      driver-class-name: com.mysql.cj.jdbc.Driver
      jdbc-url: jdbc:mysql://192.168.88.102:3306/emall?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
      username: root
      password: "#Wa123456!"
      type: com.zaxxer.hikari.HikariDataSource
      hikari:
        pool-name: Slave-HikariCP
        maximum-pool-size: 20
        minimum-idle: 5
        connection-timeout: 30000
        idle-timeout: 600000
        max-lifetime: 1800000

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.emall.order_service.model
  configuration:
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl


logging:
  level:
    com.emall.order_service.config: DEBUG
    io.github.resilience4j: DEBUG
    com.emall.order_service.mapper: DEBUG  # Mapper接口的日志级别
    com.emall.order_service.handler.DynamicDataSource: DEBUG  # 动态数据源日志
    org.mybatis: DEBUG
    java.sql.Connection: DEBUG
    java.sql.Statement: DEBUG
    java.sql.PreparedStatement: DEBUG
    java.sql.ResultSet: DEBUG

需要的jar 包

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-aop</artifactId>
		</dependency>
		<!-- 数据库连接相关jar包 -->
		<!-- Mybatis Spring Boot Starter -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>3.0.3</version>
		</dependency>

		<!-- MySQL驱动 -->
		<dependency>
			<groupId>com.mysql</groupId>
			<artifactId>mysql-connector-j</artifactId>
			<version>8.0.33</version>
			<scope>runtime</scope>
		</dependency>

		<!-- 数据源 -->
		<dependency>
			<groupId>com.zaxxer</groupId>
			<artifactId>HikariCP</artifactId>
		</dependency>

Mapper.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="com.emall.order_service.mapper.OrderMapper">

    <!-- 您的SQL语句 -->
    <select id="findAllUser">
        select * from users;
    </select>

    <insert id="saveUser" parameterType="com.emall.order_service.model.User">
        insert into users (username, password, name, email, phone, avatar, status, last_login, department_id, role_id, created_at, updated_at)
        values (#{username}, #{password}, #{name}, #{email}, #{phone}, #{avatar}, #{status}, #{lastLogin}, #{departmentId}, #{roleId}, #{createdAt}, #{updatedAt});
    </insert>

</mapper>

model 对象

public class User implements Serializable{
    private Integer id;
    private String username;
    private String password;
    private String name;
    private String email;
    private String phone;
    private String avatar;
    private Boolean status;
    private Date lastLogin;
    private Integer departmentId;
    private Integer roleId;
    private Date createdAt;
    private Date updatedAt;

    // 无参构造函数
    public User() {
    }

service

@Service
public class OrderService {
    @Autowired
    private OrderMapper orderMapper;
    public List<User> findAllUser(){
        return orderMapper.findAllUser();
    }

    public void saveUser(User user){
        orderMapper.saveUser(user);
    }

}

mapper 接口

public interface OrderMapper {
    List<User> findAllUser();

    void saveUser(User user);
}

居中的图片:
在这里插入图片描述

数据源枚举值

public enum DataSourceTypeEnum {
    MASTER,
    SLAVE
}

测试接口

    @GetMapping("/get-all-user")
    public List<User> getAllUser() {
        return orderService.findAllUser();
    }

    @GetMapping("/save-user")
    public void saveUser(@RequestParam("username") String username,@RequestParam("email") String email) {
        User user = new User();
        user.setName(username);
        user.setEmail(email);
        user.setPhone("12345678901");
        user.setUsername(username);
        user.setPassword("<PASSWORD>");
        user.setStatus(true);
        user.setCreatedAt(new java.util.Date());
        user.setUpdatedAt(new java.util.Date());
        user.setRoleId(1);
        user.setDepartmentId(1);
        user.setLastLogin(new java.util.Date());
        user.setAvatar("https://avatars.githubusercontent.com/u/10251080?s=200&v=4");
        user.setName("test");
        orderService.saveUser(user);
    }

动态数据源切换实例

package com.emall.order_service.handler;

import com.emall.order_service.enums.DataSourceTypeEnum;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicDataSource extends AbstractRoutingDataSource {

    private static final ThreadLocal<DataSourceTypeEnum> CONTEXT_HOLDER = new ThreadLocal<>();

    @Override
    protected Object determineCurrentLookupKey() {
        return getDataSource();
    }

    public static void setDataSource(DataSourceTypeEnum dataSource) {
        CONTEXT_HOLDER.set(dataSource);
    }

    public static DataSourceTypeEnum getDataSource() {
        return CONTEXT_HOLDER.get() == null ? DataSourceTypeEnum.MASTER : CONTEXT_HOLDER.get();
    }

    public static void clearDataSource() {
        CONTEXT_HOLDER.remove();
    }
}

动态数据源配置类

package com.emall.order_service.config;

import com.emall.order_service.enums.DataSourceTypeEnum;
import com.emall.order_service.handler.DynamicDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
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 org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
@MapperScan(basePackages = "com.emall.order_service.mapper", sqlSessionTemplateRef = "sqlTemplate")
public class DataSourceConfig {

    /**
     * 主数据源
     */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 从数据源
     */
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.slave")
    public DataSource slaveDataSource() {
        return DataSourceBuilder.create().build();
    }

    /**
     * 动态数据源
     */
    @Bean
    public DynamicDataSource dynamicDataSource() {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DataSourceTypeEnum.MASTER, masterDataSource());
        targetDataSources.put(DataSourceTypeEnum.SLAVE, slaveDataSource());

        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        dynamicDataSource.setTargetDataSources(targetDataSources);
        dynamicDataSource.setDefaultTargetDataSource(masterDataSource());
        return dynamicDataSource;
    }

    @Bean
    public SqlSessionFactory sessionFactory() throws Exception {
        SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dynamicDataSource());
        // 设置MyBatis配置
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources("classpath:mapper/*.xml"));
        return sessionFactory.getObject();
    }

    @Bean
    public SqlSessionTemplate sqlTemplate(@Qualifier("sessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean
    public DataSourceTransactionManager dataSourceTransactionManager() {
        return new DataSourceTransactionManager(dynamicDataSource());
    }
}

方法切面

package com.emall.order_service.aspect;

import com.emall.order_service.enums.DataSourceTypeEnum;
import com.emall.order_service.handler.DynamicDataSource;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;

@Aspect
@Component
@Order(1)
public class DataSourceAspect {

    private static final Logger log = LoggerFactory.getLogger(DataSourceAspect.class);

    @Pointcut("execution(* com.emall.order_service.mapper..*.select*(..)) " +
            "|| execution(* com.emall.order_service.mapper..*.get*(..)) " +
            "|| execution(* com.emall.order_service.mapper..*.find*(..)) " +
            "|| execution(* com.emall.order_service.mapper..*.query*(..))")
    public void readPointcut() {}

    @Pointcut("execution(* com.emall.order_service.mapper..*.insert*(..)) " +
            "|| execution(* com.emall.order_service.mapper..*.update*(..)) " +
            "|| execution(* com.emall.order_service.mapper..*.delete*(..)) " +
            "|| execution(* com.emall.order_service.mapper..*.save*(..)) " +
            "|| execution(* com.emall.order_service.mapper..*.add*(..)) " +
            "|| execution(* com.emall.order_service.mapper..*.modify*(..)) " +
            "|| execution(* com.emall.order_service.mapper..*.create*(..))")
    public void writePointcut() {}

    @Before("readPointcut()")
    public void read() {
        log.info("数据源切换到：slave");
        DynamicDataSource.setDataSource(DataSourceTypeEnum.SLAVE);
    }

    @Before("writePointcut()")
    public void write() {
        log.info("数据源切换到：master");
        DynamicDataSource.setDataSource(DataSourceTypeEnum.MASTER);
    }

    @After("readPointcut() || writePointcut()")
    public void after() {
        log.info("清除次执行源");
        DynamicDataSource.clearDataSource();
    }
}
# mysql
Logo
AI Agent技术社区

Agent 垂直技术社区，欢迎活跃、内容共建。

更多推荐

cover

2026AI Agent风口！零基础保姆级学习路线，手把手教你从入门到落地变现！

avatar AI Agent技术社区

GitHub 热榜项目：日榜（2026-07-27）

本期 GitHub 热门开源项目聚焦 AI 与基础设施领域，收录 17 个项目共新增 8,161 stars。主要趋势包括： AI 协同工具涌现 ego-lite 实现浏览器状态共享，支持 AI Agent 并行工作（+900⭐） Buzz 构建人机协同平台，支持代码审查与频道管理（+1,710⭐） 去中心化通信创新 BitChat 通过蓝牙 Mesh 提供离线加密聊天（+1,166⭐） 垂直领域

avatar AI Agent技术社区

AI Agent 30天速成｜Day6 笔记

状态(S)

avatar AI Agent技术社区