sharding-jdbc整合springboot 实现读写分离

最新推荐文章于 2024-06-04 16:40:31 发布

原创最新推荐文章于 2024-06-04 16:40:31 发布 · 228 阅读

1 ·

CC 4.0 BY-SA版权

java 专栏收录该内容

45 篇文章

订阅专栏

本文详细介绍了如何在SpringBoot项目中整合Sharding-JDBC，配置数据源和分片策略，以便于实现数据库的读写分离和水平扩展。通过步骤演示了配置HikariCP数据源、设置主从库负载均衡以及MyBatis映射文件的调整，适合数据库优化和高可用场景。

sharding-jdbc整合springboot 实现读写分离

环境准备

引入相关依赖

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

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

sharding-jdbc 配置

server:
  port: 8754
spring:
  shardingsphere:
    props:
      sql:
        show: true # 打印日志
    datasource:
      # 数据源
      names: master,slave
      # 主库
      master:
        type: com.zaxxer.hikari.HikariDataSource
        password: 123456
        username: root
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.0.254:3316/test?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
      # 从库
      slave:
        type: com.zaxxer.hikari.HikariDataSource
        password: 123456
        username: root
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.0.254:3317/test?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
    masterslave:
      # 从库 负载均衡轮询算法
      load-balance-algorithm-type: round_robin
      name: master
      # 主库 数据源
      master-data-source-name: master
      # 从库数据源
      slave-data-source-names: slave
mybatis:
  configuration:
    map-underscore-to-camel-case: true
  mapper-locations: classpath:/mapper/*.xml
  type-aliases-package: com.example.project.entity

mapper

public interface TestUserMapper {


    @Insert("insert into test(username)values (#{username})")
    int save(TestUser user);

    @Select("select id,username from test order by id desc limit 1")
    TestUser get();
}

Service

@Service
public class TestUserService {

    @Autowired
    private TestUserMapper testUserMapper;

    public int saveUser(TestUser user) {
        return testUserMapper.save(user);
    }

    public TestUser getTestUser() {
            //特殊情况可以在mapper查询前加入 HintManager.getInstance().setMasterRouteOnly();
        // 查询主库 避免数据延迟
        // HintManager.getInstance().setMasterRouteOnly();
        return testUserMapper.get();
    }
}

Test

  @Autowired
    private TestUserService userService;

    @Test
    void test() {
        TestUser testUser = userService.getTestUser();
        System.out.println(testUser);

        testUser.setUsername("dsfsdkjafhklsah");
        userService.saveUser(testUser);

    }

日志输出

2021-05-06 14:36:35.641  INFO 11754 --- [           main] ShardingSphere-SQL                       : Actual SQL: slave ::: select id,username from test order by id desc limit 1
TestUser(id=1001526, username=dsfsdkjafhklsah)
2021-05-06 14:36:35.668  INFO 11754 --- [           main] ShardingSphere-SQL                       : Logic SQL: insert into test(username)values (?)
2021-05-06 14:36:35.668  INFO 11754 --- [           main] ShardingSphere-SQL                       : SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@4c91a008, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@6ef61cd0)
2021-05-06 14:36:35.668  INFO 11754 --- [           main] ShardingSphere-SQL                       : Actual SQL: master ::: insert into test(username)values (?)