spring整合sharding-jdbc实现分库分表

1.创建两个库，每个库创建两个分表t_order_1,t_order_2

DROP TABLE IF EXISTS `t_order_1`;

CREATE TABLE `t_order_1` (

  `id` int(10) NOT NULL AUTO_INCREMENT,

  `amount` int(255) NOT NULL,

  `name` varchar(10) NOT NULL,

  `user_id` int(10) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2.引入依赖

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

<project xmlns="http://maven.apache.org/POM/4.0.0"

         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <parent>

        <groupId>org.springframework.boot</groupId>

        <artifactId>spring-boot-starter-parent</artifactId>

        <version>2.1.5.RELEASE</version>

        <relativePath />

    </parent>

    <groupId>org.example</groupId>

    <artifactId>spring-shardingjdbc</artifactId>

    <version>1.0-SNAPSHOT</version>

    <properties>

        <java.version>1.8</java.version>

    </properties>

    <dependencies>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-data-jpa</artifactId>

        </dependency>

        <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.0</version>

        </dependency>

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

            <scope>runtime</scope>

        </dependency>

        <dependency>

            <groupId>org.springframework.boot</groupId>

            <artifactId>spring-boot-starter-test</artifactId>

            <scope>test</scope>

        </dependency>

        <dependency>

        <groupId>org.apache.shardingsphere</groupId>

        <artifactId>sharding-jdbc-spring-namespace</artifactId>

        <version>4.0.0-RC2</version>

        </dependency>

    </dependencies>

</project>

3.创建sharding-jdbc的配置文件sharding-jdbc.xml

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

<beans xmlns="http://www.springframework.org/schema/beans"

       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

       xmlns:context="http://www.springframework.org/schema/context"

       xmlns:tx="http://www.springframework.org/schema/tx"

       xmlns:sharding="http://shardingsphere.apache.org/schema/shardingsphere/sharding"

       xmlns:master-slave="http://shardingsphere.apache.org/schema/shardingsphere/masterslave"

       xmlns:bean="http://www.springframework.org/schema/util"

       xsi:schemaLocation="http://www.springframework.org/schema/beans

                        http://www.springframework.org/schema/beans/spring-beans.xsd

                        http://shardingsphere.apache.org/schema/shardingsphere/sharding

                        http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd

                        http://shardingsphere.apache.org/schema/shardingsphere/masterslave

                        http://shardingsphere.apache.org/schema/shardingsphere/masterslave/master-slave.xsd

                        http://www.springframework.org/schema/context

                        http://www.springframework.org/schema/context/spring-context.xsd

                        http://www.springframework.org/schema/tx

                        http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/util https://www.springframework.org/schema/util/spring-util.xsd">

    <bean id="ds0" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">

        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />

        <property name="username" value="root" />

        <property name="password" value="root123456" />

        <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/sharding?serverTimezone=Asia/Shanghai&useSSL=false"/>

    </bean>

    <bean id="ds1" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">

        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />

        <property name="username" value="root" />

        <property name="password" value="root123456" />

        <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1:3306/shard?serverTimezone=Asia/Shanghai&useSSL=false"/>

    </bean>

    <sharding:data-source id="sharding-data-source">

        <sharding:sharding-rule data-source-names="ds0,ds1">

            <sharding:table-rules>

                <sharding:table-rule logic-table="t_order" actual-data-nodes="ds$->{0..1}.t_order_$->{1..2}"

                    database-strategy-ref="databaseStrategy" table-strategy-ref="tableStrategy"

                />

                <!-- 如果多个表需要分库，继续在此配置 -->

            </sharding:table-rules>

        </sharding:sharding-rule>

    </sharding:data-source>

    <sharding:inline-strategy id="databaseStrategy" sharding-column="user_id"

                              algorithm-expression="ds$->{user_id%2}"/>

    <sharding:inline-strategy id="tableStrategy" sharding-column="id"

                              algorithm-expression="t_order_$->{id%2+1}"/>

    <bean class="org.mybatis.spring.SqlSessionFactoryBean">

        <property name="dataSource" ref="sharding-data-source"/>

    </bean>

</beans>

4.编写代码及测试类

package com.sharding.pojo.vo;

public class Order {

    private int id;

    private int amount;

    private String name;

    private int userId;

    public int getId() {

        return id;

    }

    public void setId(int id) {

        this.id = id;

    }

    public int getAmount() {

        return amount;

    }

    public void setAmount(int amount) {

        this.amount = amount;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public int getUserId() {

        return userId;

    }

    public void setUserId(int userId) {

        this.userId = userId;

    }

    @Override

    public String toString() {

        return "Order{" +

                "id=" + id +

                ", amount=" + amount +

                ", name='" + name + '\'' +

                ", userId=" + userId +

                '}';

    }

}

package com.sharding.mapper;

import com.sharding.pojo.vo.Order;

import org.apache.ibatis.annotations.Insert;

import org.apache.ibatis.annotations.Param;

import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface OrderMapper {

    @Insert("insert into t_order values(#{item.id},#{item.amount},#{item.name},#{item.userId})")

    public void insert(@Param("item") Order order);

    @Select("select " +

            " id,amount,name,user_id as 'userId' from t_order where name=#{name}")

    public List<Order> getOrderByName(String name);

}

package com.sharding;

import org.mybatis.spring.annotation.MapperScan;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

import org.springframework.context.annotation.ImportResource;

@SpringBootApplication

@ImportResource("classpath*:sharding-jdbc.xml")

@MapperScan("com.sharding.mapper")

public class ShardingTest {

    public static void main(String[] args) {

        SpringApplication.run(ShardingTest.class);

    }

}

package com.sharding.test;

import com.sharding.mapper.OrderMapper;

import com.sharding.pojo.vo.Order;

import org.junit.Test;

import org.junit.runner.RunWith;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.boot.test.context.SpringBootTest;

import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

@RunWith(SpringRunner.class)

@SpringBootTest

public class AppTest {

    @Autowired

    private OrderMapper orderMapper;

    @Test

    public void testInsert(){

      Order order = new Order();

      order.setId(10);

      order.setUserId(20);

      order.setAmount(200);

      order.setName("Test");

      orderMapper.insert(order);

    }

    @Test

    public void testQuery(){

        List<Order> test = orderMapper.getOrderByName("Test");

        System.out.println("查询结果：" + test);

    }

}

巴特西

spring整合sharding-jdbc实现分库分表

最新文章

热门文章