devbox
寸_铁
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

使用Shardingsphere与mybatisPlus 在SpringBoot项目中实现读写分离_shardingsphere mybatisplus

作者：寸_铁 | 2024-06-25 18:05:13

赞

踩

shardingsphere mybatisplus

前言：

当云产品使用的用户越来越多时，会面临页面加载缓慢甚至系统假死的问题，不断会有客户投拆，造成客户流失。但初创企业又没有资金去搞分布式，购买更多的服务器，而且数据优化已达极限，这时我们就得考虑读写分离，分库分表的策略，来减少对同一数据库的访问的压力。

1、首先要使用MySQL搭建主从数据库

参照本人博客
https://blog.csdn.net/lovoo/article/details/115447598?spm=1001.2014.3001.5501

2、创建项目，如图：

在这里插入图片描述

3、配置gradle，引入jar包

buildscript {
    ext {
        springBootVersion = '2.2.4.RELEASE'
    }
    repositories {
        maven { url "http://maven.aliyun.com/nexus/content/groups/public" }
        mavenLocal()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

plugins {
    id 'java'
    id 'idea'
}

dependencies {
        implementation 'org.springframework.boot:spring-boot-starter-web'
    	implementation group: 'org.apache.shardingsphere', name: 'sharding-jdbc-spring-boot-starter', version: '4.1.1'

        compile group: 'org.springframework.session', name: 'spring-session-data-redis', version: '2.2.3.RELEASE'

        developmentOnly 'org.springframework.boot:spring-boot-devtools'

        //数据处理相关
        runtimeOnly 'mysql:mysql-connector-java'
        compile group: 'com.alibaba', name: 'druid-spring-boot-starter', version: '1.1.18'
        compile group: 'com.baomidou', name: 'mybatis-plus-boot-starter', version: '3.1.2'

        //工具类
        compile group: 'commons-lang', name: 'commons-lang', version: '2.5'
        compile group: 'org.apache.commons', name: 'commons-lang3', version: '3.4'
        compile group: 'commons-codec', name: 'commons-codec', version: '1.11'
        compile group: 'commons-beanutils', name: 'commons-beanutils', version: '1.9.2'

        //lombok
        compileOnly 'org.projectlombok:lombok'
        annotationProcessor 'org.springframework.boot:spring-boot-configuration-processor'
        annotationProcessor 'org.projectlombok:lombok'

        //测试
        testImplementation('org.springframework.boot:spring-boot-starter-test') {
            exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
        }
        

    }

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49

4、yml配置文件

#端口号
server:
  port: 8283
#数据库连接
spring:
  main:
    allow-bean-definition-overriding: true
  shardingsphere:
    datasource:
      names: master,slave
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver #数据库新的驱动，下面连接url一定要加区时
        url: jdbc:mysql://192.168.16.149:3301/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: root
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver #数据库新的驱动，下面连接url一定要加区时
        url: jdbc:mysql://192.168.16.149:3302/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
        username: root
        password: root
    props:
      sql:
         show: true  #打印sql
  config:
    # 配置数据分表
    sharding:
      master-slave-rules:
        db:
          master-data-source-name: master
          slave-data-source-names: slave
      tables:
        orders:
          actualDataNodes: master.orders_$->{0..1}
          databaseStrategy:
            inline:
              shardingColumn:  adddate
          tableStrategy:
              inline:
                shardingColumn: id
        orders_detail:
          actualDataNodes: master.orders_detail_$->{0..1}
          tableStrategy:
            inline:
              shardingColumn: orders_id
  devtools:
    restart:
      enabled: false

mybatis-plus:
  check-config-location: false
  type-aliases-package: com.wys.*.*.entity
  configuration:
    map-underscore-to-camel-case: true
    cache-enabled: false
    #日志输出
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      id-type: id_worker_str
      table-underline: true
      #驼峰下划线转换
      column-underline: true
      db-type: mysql
      logic-delete-value: 1 #默认值1
      logic-not-delete-value: 0 #默认值0
    #刷新mapper
    refresh-mapper: true

#日志
logging:
  config: classpath:logback.xml

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73

5、创建表

CREATE TABLE `orders_0` (
  `id` varchar(36) NOT NULL COMMENT '订单id',
  `parent_orders_uuid` varchar(36) DEFAULT NULL COMMENT '业务平台的订单id',
  `parent_orders_id` varchar(36) NOT NULL COMMENT '业务平台的订单编号',
  `order_origin` char(2) DEFAULT NULL COMMENT '订单来源1=PC',
  `order_type` char(2) DEFAULT NULL COMMENT '业务来源 1:A业务;2:B业务',
  `adddate` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表_0';

CREATE TABLE `orders_1` (
  `id` varchar(36) NOT NULL COMMENT '订单id',
  `parent_orders_uuid` varchar(36) DEFAULT NULL COMMENT '业务平台的订单id',
  `parent_orders_id` varchar(36) NOT NULL COMMENT '业务平台的订单编号',
  `order_origin` char(2) DEFAULT NULL COMMENT '订单来源1=PC',
  `order_type` char(2) DEFAULT NULL COMMENT '业务来源 1:A业务;2:B业务',
  `adddate` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表_1';

CREATE TABLE `orders_detail_0` (
  `id` varchar(36) NOT NULL COMMENT '主键',
  `orders_id` varchar(36) NOT NULL COMMENT '订单id',
  `goods_id` varchar(36) DEFAULT NULL COMMENT '商品id',
  `goods_name` varchar(100) DEFAULT NULL COMMENT '商品名称',
  `goods_kindname` varchar(300) DEFAULT NULL COMMENT '商品属性',
  PRIMARY KEY (`id`),
  KEY `index_orders_id` (`orders_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='子订单明细表_0';
CREATE TABLE `orders_detail_1` (
  `id` varchar(36) NOT NULL COMMENT '主键',
  `orders_id` varchar(36) NOT NULL COMMENT '订单id',
  `goods_id` varchar(36) DEFAULT NULL COMMENT '商品id',
  `goods_name` varchar(100) DEFAULT NULL COMMENT '商品名称',
  `goods_kindname` varchar(300) DEFAULT NULL COMMENT '商品属性',
  PRIMARY KEY (`id`),
  KEY `index_orders_id` (`orders_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='子订单明细表_1';

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38

6、创建mybatisplus配置文件

@EnableTransactionManagement
@Configuration
@MapperScan(basePackages = {"com.wys.*.*.mapper"})
public class MybatisPlusConfig {

    /**
     * mybatis-plus 分页插件
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }

    /**sql执行效率插件*/
    @Bean
//    @Profile({"dev","int","test"})//设置 dev int test环境开启
    public PerformanceInterceptor performanceInterceptor() {
        return new PerformanceInterceptor();
    }

}


public class BaseServiceImpl<M extends BaseMapper<T>, T> extends ServiceImpl<M, T> {
    public BaseServiceImpl() {
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27

7、日志文件

<?xml version="1.0" encoding="UTF-8"?>
<configuration>

    <property name="log.context.name" value="admin-shardingjdbc" />
    <property name="log.charset" value="UTF-8" />
    <property name="log.pattern" value="[%-5level] %date --%thread-- [%logger] %msg %n" />

    <contextName>${log.context.name}</contextName>

    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder charset="${log.charset}">
            <pattern>${log.pattern}</pattern>
        </encoder>
    </appender>
    <logger name="com.wys.mall.*.mapper" level="DEBUG" >
        <appender-ref ref="STDOUT" />
    </logger>
    <root>
        <level value="INFO" />
        <appender-ref ref="STDOUT" />
    </root>
</configuration>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

8、实体类

@Data
public class Orders {
    /**
     * 订单id
     */
    private String id;

    /**
     *  业务平台的订单id
     */
    private String parentOrdersUuid;
    /**
     * 业务平台的订单编号
     */
    private String parentOrdersId;
    /**
     * 订单来源
     */
    private String orderOrigin;
    /**
     * 订单类型
     */
    private String orderType;
    /**
     * 创建时间
     */
    private Date adddate;

}

@Data
public class OrdersDetail {
    /**
     * 订单明细id
     */
    private String id;

    /**
     * 订单id
     */
    private String ordersId;
    /**
     * 商品id
     */
    private String goodsId;
    /**
     * 商品名称
     */
    private String goodsName;
    /**
     * 商品属性
     */
    private String goodsKindname;
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54

9、创建service

public interface OrdersService extends IService<Orders> {

    /**
     * @param orders
     * @return
     */
    boolean saveOrders(Orders orders);
 }
 
public interface OrdersDetailService extends IService<OrdersDetail> {

    /**
     * @param ordersDetail
     * @return
     */
    boolean saveOrderDetail(OrdersDetail ordersDetail);
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

10、service实现类

@Service
@Slf4j
public class OrdersServiceImpl extends BaseServiceImpl<OrdersMapper, Orders> implements OrdersService {
    @Autowired
    private OrdersMapper ordersMapper;

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean saveOrders(Orders orders) {
        if (orders==null) {
            return false;
        }
        int result = ordersMapper.insertOrders(orders);
        return result == 1 ? true : false;
    }
    }

@Service
@Slf4j
public class OrdersDetailServiceImpl  extends BaseServiceImpl<OrdersDetailMapper, OrdersDetail> implements OrdersDetailService {

    @Autowired
    private OrdersDetailMapper ordersDetailMapper;

    @Override
    public boolean saveOrderDetail(OrdersDetail ordersDetail) {
        if (ordersDetail == null) {
            return false;
        }
        int result = ordersDetailMapper.insertDetail(ordersDetail);
        return result > 0;
    }
 }

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33

11、创建mapper

@Mapper
public interface OrdersMapper extends BaseMapper<Orders> {

    /**
     * @param orders
     * @return
     */
    int insertOrders(Orders orders);
}
@Mapper
public interface OrdersDetailMapper extends BaseMapper<OrdersDetail> {

    /**
     * @param ordersDetail
     * @return
     */
    int insertDetail(OrdersDetail ordersDetail);
 }

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

12、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.wys.mall.order.mapper.OrdersMapper">
    <resultMap id="OrdersResultMap" type="com.wys.mall.order.entity.Orders">
        <id column="id" property="id" jdbcType="VARCHAR"/>
        <result column="parent_orders_uuid" property="parentOrdersUuid" jdbcType="VARCHAR"/>
        <result column="parent_orders_id" property="parentOrdersId" jdbcType="VARCHAR"/>
        <result column="order_origin" property="orderOrigin" jdbcType="CHAR"/>
        <result column="order_type" property="orderType" jdbcType="CHAR"/>
        <result column="adddate" property="adddate" jdbcType="TIMESTAMP" />
    </resultMap>
    <sql id="Base_Column">
        id,parent_orders_uuid,parent_orders_id,order_origin,order_type,adddate
    </sql>
    <insert id="insertOrders" parameterType="com.wys.mall.order.entity.Orders">
        INSERT INTO orders(
          <include refid="Base_Column"/>
        )
        VALUES(#{id},#{parentOrdersUuid},#{parentOrdersId},#{orderOrigin},#{orderType},#{adddate})
    </insert>
    
</mapper>



<?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.wys.mall.order.mapper.OrdersDetailMapper">
    <resultMap id="OrdersDetailResultMap" type="com.wys.mall.order.entity.OrdersDetail">
        <result column="id" jdbcType="VARCHAR" property="id" />
        <result column="orders_id" jdbcType="VARCHAR" property="ordersId" />
        <result column="goods_id" jdbcType="VARCHAR" property="goodsId" />
        <result column="goods_name" jdbcType="VARCHAR" property="goodsName" />
        <result column="goods_kindname" jdbcType="VARCHAR" property="goodsKindname" />
    </resultMap>
    <sql id="Base_Column">
      id,orders_id,goods_id,goods_name,goods_kindname
    </sql>

    <insert id="insertDetail" parameterType="com.wys.mall.order.entity.OrdersDetail">
        INSERT INTO orders_detail(
            <include refid="Base_Column"/>
        )
        VALUES (#{id},#{ordersId},#{goodsId},#{goodsName},#{goodsKindname})
    </insert>

</mapper>

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47

13、创建测试文件

@SpringBootTest(classes = ShardingApplication.class)
public class OrdersDetailServiceImplTest {

    @Autowired
    private OrdersDetailService ordersDetailService;

    @Test
    public void saveOrderDetail() {
        OrdersDetail detail = new OrdersDetail();
        detail.setOrdersId("a11");
        detail.setGoodsId("g11");
        detail.setGoodsName("aa");
        ordersDetailService.saveOrderDetail(detail);
    }

    @Test
    public void getDetailByOrderId() {
    }

    @Test
    public void getDetailByName() {
    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/寸_铁/article/detail/756992
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号