springboot+mybatis-plus怎樣使用shardingsphere分庫分表

spring boot+mybatis-plus怎樣使用shardingsphere分庫分表，相信很多沒有經(jīng)驗(yàn)的人對(duì)此束手無策，為此本文總結(jié)了問題出現(xiàn)的原因和解決方法，通過這篇文章希望你能解決這個(gè)問題。

創(chuàng)新互聯(lián)是一家集網(wǎng)站建設(shè),錫林浩特企業(yè)網(wǎng)站建設(shè),錫林浩特品牌網(wǎng)站建設(shè),網(wǎng)站定制,錫林浩特網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營銷,網(wǎng)絡(luò)優(yōu)化,錫林浩特網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力。可充分滿足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長(zhǎng)自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。

一.關(guān)于

效果預(yù)覽：

ShardingSphere是一個(gè)數(shù)據(jù)庫中間件，可以實(shí)現(xiàn)分庫分表、讀寫分離等，詳見官網(wǎng)： https://shardingsphere.apache.org/

這里使用spring boot+mybatis-plus的方式來搭建demo。

二.項(xiàng)目搭建

使用vscode(java開發(fā)環(huán)境可以百度一下)新建一個(gè)spring boot項(xiàng)目，引入lombok、mybatis-plus等：

    <properties>
		<java.version>11</java.version>
		<sharding-sphere.version>4.1.1</sharding-sphere.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.1.3</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>MySQL</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<!--Mybatis-Plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1</version>
        </dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
		</dependency>
		<!-- for spring boot -->
		<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
			<version>${sharding-sphere.version}</version>
		</dependency>

		<!-- for spring namespace -->
		<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-spring-namespace</artifactId>
			<version>${sharding-sphere.version}</version>
		</dependency>
		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
		</dependency>
		<dependency>
            <groupId>org.antlr</groupId>
            <artifactId>antlr4-runtime</artifactId>
            <version>4.7.2</version>
        </dependency>

項(xiàng)目結(jié)構(gòu)：

application.yml配置：

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0: 
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/ds0?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: 123456
      ds1: 
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        url: jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: 123456
    sharding:
      tables:
        t_order: 
          actualDataNodes: ds${0..1}.t_order_${0..1}
          databaseStrategy: 
              inline:
                shardingColumn: user_id
                algorithmExpression: ds${user_id % 2}
          tableStrategy: 
            inline:
              shardingColumn: order_id
              algorithmExpression: t_order_${order_id % 2}
          keyGenerator:
            type: SNOWFLAKE
            column: order_id
        t_order_item:
          actualDataNodes: ds${0..1}.t_order_item_${0..1}
          databaseStrategy:
            inline:
              shardingColumn: user_id
              algorithmExpression: ds${user_id % 2}
          tableStrategy:
            inline:
              shardingColumn: order_id
              algorithmExpression: t_order_item_${order_id % 2}  
      binding-tables: t_order,t_order_item
      broadcast-tables: t_config
      defaultDataSourceName: ds0
      defaultTableStrategy:
        none:
      defaultKeyGenerator:
        type: SNOWFLAKE
        column: order_id
    props:
      sql.shwo: true

db：分別建ds0和ds1庫，并分別建t_order_0、t_order_1和t_order_item_0、t_order_item_1

# Host: localhost  (Version 5.7.17)
# Date: 2020-08-23 12:31:22
# Generator: MySQL-Front 6.0  (Build 2.29)


#
# Structure for table "t_order_0"
#

CREATE TABLE `t_order_0` (
  `order_id` bigint(11) NOT NULL DEFAULT '0',
  `user_id` bigint(1) DEFAULT NULL,
  `amount` decimal(18,2) DEFAULT NULL,
  `discount` decimal(10,2) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#
# Structure for table "t_order_item_0"
#

CREATE TABLE `t_order_item_0` (
  `order_id` bigint(11) NOT NULL DEFAULT '0',
  `product_id` bigint(11) DEFAULT NULL,
  `product_name` varchar(255) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `discount` decimal(10,2) DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

entity：

Order.java

@Data
@TableName("t_order")
public class Order extends Model<Order> {
    /**
     *
     */
    private static final long serialVersionUID = 1L;
    private Long orderId;
    private Long userId;
    private BigDecimal amount;
    private BigDecimal discount;
    private LocalDateTime createTime;

    public Order(){}

    public Order(Long orderId,Long userId,BigDecimal amount,BigDecimal discount,LocalDateTime createTime){
        this.orderId=orderId;
        this.userId=userId;
        this.amount=amount;
        this.discount=discount;
        this.createTime=createTime;
    }
}

OrderItem.java

@Data
@TableName("t_order_item")
public class OrderItem extends Model<OrderItem> {
    private Long orderId;
    private Long productId;
    private String productName;
    private BigDecimal price;
    private BigDecimal discount;
    @TableField("`count`")
    private int count;

    public OrderItem(){}

    public OrderItem(Long orderId,Long productId,String productName,BigDecimal price,BigDecimal discount,int count){
        this.orderId=orderId;
        this.productId=productId;
        this.productName=productName;
        this.price=price;
        this.discount=discount;
        this.count=count;
    }
}

mapper:
OrderMapper.java

public interface OrderMapper extends BaseMapper<Order> {
    
}

OrderItemMapper.java

public interface OrderItemMapper extends BaseMapper<OrderItem> {
    
}

service:

OrderService.java

public interface OrderService extends IService<Order> {
    boolean save(Order order,List<OrderItem> items);
}

OrderServiceImpl.java

@Service
public class OrderServiceImpl extends ServiceImpl<OrderMapper, Order> implements OrderService {
    @Autowired
    private OrderItemService orderItemService;

    @Transactional(rollbackFor = Exception.class)
    @Override
    public boolean save(Order order, List<OrderItem> items) {
        save(order);
        orderItemService.saveBatch(items);
        return true;
    }
    
}

OrderItemService.java

public interface OrderItemService extends IService<OrderItem> {
    
}

OrderItemServiceImpl.java

@Service
public class OrderItemServiceimpl extends ServiceImpl<OrderItemMapper,OrderItem> implements OrderItemService {
    
}

spring boot啟動(dòng)類：

@MapperScan("com.example.sharding.mapper")
@SpringBootApplication
public class ShardingDemoApplication {

	public static void main(String[] args) {
		SpringApplication.run(ShardingDemoApplication.class, args);
	}

	@Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // 設(shè)置請(qǐng)求的頁面大于最大頁后操作， true調(diào)回到首頁，false 繼續(xù)請(qǐng)求  默認(rèn)false
        // paginationInterceptor.setOverflow(false);
        // 設(shè)置最大單頁限制數(shù)量，默認(rèn) 500 條，-1 不受限制
        // paginationInterceptor.setLimit(500);
        // 開啟 count 的 join 優(yōu)化,只針對(duì)部分 left join
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }

}

三.測(cè)試效果

建一個(gè)測(cè)試類：OrderServiceImplTest.java  并編寫測(cè)試語句

package com.example.sharding.service.impl;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.ArrayList;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.sharding.entity.Order;
import com.example.sharding.entity.OrderItem;
import com.example.sharding.service.OrderItemService;
import com.example.sharding.service.OrderService;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
public class OrderServiceImplTest {
    @Autowired
    private OrderService orderService;
    @Autowired
    private OrderItemService orderItemService;

    @Test
    public void testSave(){
        var order1=new Order(1L,1L,new BigDecimal(22.5),new BigDecimal(1),LocalDateTime.now());
        var orderItemList1=new ArrayList<OrderItem>();
        orderItemList1.add(new OrderItem(1L,1L,"蘋果",new BigDecimal(6),new BigDecimal(1),2));
        orderItemList1.add(new OrderItem(1L,2L,"香蕉",new BigDecimal(3.5),new BigDecimal(1),3));

        var order2=new Order(2L,1L,new BigDecimal(799),new BigDecimal(1),LocalDateTime.now());
        var orderItemList2=new ArrayList<OrderItem>();
        orderItemList2.add(new OrderItem(2L,3L,"鞋子1",new BigDecimal(600),new BigDecimal(1),1));
        orderItemList2.add(new OrderItem(2L,4L,"襯衣1",new BigDecimal(199),new BigDecimal(1),1));

        var order3=new Order(3L,1L,new BigDecimal(399),new BigDecimal(1),LocalDateTime.now());
        var orderItemList3=new ArrayList<OrderItem>();
        orderItemList3.add(new OrderItem(3L,5L,"鞋子2",new BigDecimal(200),new BigDecimal(1),1));
        orderItemList3.add(new OrderItem(3L,6L,"襯衣2",new BigDecimal(199),new BigDecimal(1),1));

        var order4=new Order(4L,1L,new BigDecimal(499),new BigDecimal(1),LocalDateTime.now());
        var orderItemList4=new ArrayList<OrderItem>();
        orderItemList4.add(new OrderItem(4L,7L,"鞋子3",new BigDecimal(300),new BigDecimal(1),1));
        orderItemList4.add(new OrderItem(4L,8L,"襯衣3",new BigDecimal(199),new BigDecimal(1),1));

        var order5=new Order(5L,1L,new BigDecimal(899),new BigDecimal(1),LocalDateTime.now());
        var orderItemList5=new ArrayList<OrderItem>();
        orderItemList5.add(new OrderItem(5L,9L,"鞋子4",new BigDecimal(600),new BigDecimal(1),1));
        orderItemList5.add(new OrderItem(5L,10L,"襯衣4",new BigDecimal(299),new BigDecimal(1),1));
        assertTrue(orderService.save(order1,orderItemList1));
        assertTrue(orderService.save(order2,orderItemList2));
        assertTrue(orderService.save(order3,orderItemList3));
        assertTrue(orderService.save(order4,orderItemList4));
        assertTrue(orderService.save(order5,orderItemList5));
    }

    @Test
    public void testQuery(){
        var page=new Page<OrderItem>(1,4);
        var queryWrapper=new QueryWrapper<OrderItem>();
        queryWrapper.orderByAsc("order_id");
        
        var itemPage=orderItemService.page(page,queryWrapper);
        assertEquals(4, itemPage.getRecords().size());
        assertEquals("蘋果", itemPage.getRecords().get(0).getProductName());
    }
}

測(cè)試通過，看一下數(shù)據(jù)庫的數(shù)據(jù)：

看完上述內(nèi)容，你們掌握spring boot+mybatis-plus怎樣使用shardingsphere分庫分表的方法了嗎？如果還想學(xué)到更多技能或想了解更多相關(guān)內(nèi)容，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，感謝各位的閱讀！

名稱欄目：springboot+mybatis-plus怎樣使用shardingsphere分庫分表
鏈接URL：http://bm7419.com/article10/gihddo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供營銷型網(wǎng)站建設(shè)、手機(jī)網(wǎng)站建設(shè)、網(wǎng)站排名、定制開發(fā)、企業(yè)建站、做網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)