shardingsphere读写分离

乡村校长

已于 2023-11-28 11:27:59 修改

阅读量104

点赞数

文章标签： java spring boot

于 2023-07-29 11:09:59 首次发布

本文链接：https://blog.csdn.net/qq_19427279/article/details/131992611

版权

文章介绍了如何在项目中添加ShardingSphere和Druid的依赖来实现数据库的读写分离，配置了Spring的ShardingSphere数据源，包括主库和从库的Druid数据源设置，并展示了因分组查询引发的MyBatis错误及解决方案。

摘要由CSDN通过智能技术生成

#增加jar包
<!-- 读写分离 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>

# Spring配置
spring:
  shardingsphere:
    datasource:
      names: master,slave0
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3309/xxxx?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
        username: root  
        password: 123456
        initial-size: 10
        min-idle: 10
        maxActive: 100
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        filter: stat,wall,log4j2
        connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
      slave0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.01:3310/xxxx?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
        username: root  
        password: 123456
        filters: stat,wall,log4j2
        connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
    props:
      sql:
        show: true
    masterslave:
      load-balance-algorithm-type: round_robin
      name: ms
      master-data-source-name: master
      slave-data-source-names: slave0

MyBatis报错： Caused by: java.lang.IllegalStateException: Can not find owner from table 报错前SQL语句
select * from (
    select * from test a
)  b GROUP BY b.id
修改后前SQL语句，注：一定要注意分组后的列
select * from (
    select * from test a
)  b GROUP BY id