SpringBoot+mybatis+sharding-jdbc

317 阅读1分钟

首先创建二个数据库 没有多台服务器只能在本机上了,但是都是一样的,在ds0,ds1二个数据库都创建好t_order_1和t_order_2的表,表结构如下 四张表结构都是一样的。 然后创建一个SpringBoot项目,pom.xml文件如下:

<?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 https://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.8.RELEASE</version>
      <relativePath/> <!-- lookup parent from repository -->
  </parent>
  <groupId>com.example</groupId>
  <artifactId>sharding-jdbc</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <name>sharding-jdbc</name>
  <description>Demo project for Spring Boot</description>

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

  <dependencies>
      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-jdbc</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>
          <version>5.1.46</version>
      </dependency>
      <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-test</artifactId>
          <scope>test</scope>
          <exclusions>
              <exclusion>
                  <groupId>org.junit.vintage</groupId>
                  <artifactId>junit-vintage-engine</artifactId>
              </exclusion>
          </exclusions>
      </dependency>
      <dependency>
          <groupId>org.apache.shardingsphere</groupId>
          <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
          <version>4.0.0-RC2</version>
      </dependency>
  </dependencies>

  <build>
      <plugins>
          <plugin>
              <groupId>org.springframework.boot</groupId>
              <artifactId>spring-boot-maven-plugin</artifactId>
          </plugin>
      </plugins>
  </build>

创建实体类:

public class Order implements Serializable {

private int id;
private BigDecimal orderAmount;
private int orderStatus;
private int userId;

public int getId() {
    return id;
}

public void setId(int id) {
    this.id = id;
}

public BigDecimal getOrderAmount() {
    return orderAmount;
}

public void setOrderAmount(BigDecimal orderAmount) {
    this.orderAmount = orderAmount;
}

public int getOrderStatus() {
    return orderStatus;
}

public void setOrderStatus(int orderStatus) {
    this.orderStatus = orderStatus;
}

public int getUserId() {
    return userId;
}

public void setUserId(int userId) {
    this.userId = userId;
}
}

创建dao层:

@Repository
public interface OrderMapper {

Order getDataById(@Param("id") Long id,@Param("user_id") int user_id);

void saveData(Order order);

}

然后创建order.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.coco.dao.OrderMapper">

    <select id="getDataById" resultType="com.coco.entity.Order">
        select id,order_amount,order_status,user_id from t_order where id = #{id} and user_id = #{user_id}
    </select>

    <insert id="saveData" parameterType="com.coco.entity.Order">
        insert into t_order (id,order_amount,order_status,user_id) values (#{id},#{orderAmount},#{orderStatus},#{userId})
    </insert>
</mapper>

启动类:

@SpringBootApplication
@MapperScan("com.coco.dao")
public class ShardingJdbcApplication {

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

}

controller类:

@RestController
public class OrderController {


@Autowired
private OrderMapper orderMapper;

@RequestMapping("/test")
public String test() {
    Order order = new Order();
    order.setId(4);
    order.setUserId(20);
    order.setOrderAmount(BigDecimal.TEN);
    order.setOrderStatus(1);
    orderMapper.saveData(order);
    return "success";
}
}

接下来就是最重要的配置文件了

mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.type-aliases-package=com.coco.entity

spring.shardingsphere.datasource.names=ds0,ds1

spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds0
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root

spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds1
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=root


spring.shardingsphere.sharding.tables.t_order.actual-data-nodes=ds$->{0..1}.t_order_$->{1..2}
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_order.database-strategy.inline.algorithm-expression=ds$->{user_id % 2}
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression=t_order_$->{id % 2 + 1}

我们是根据user_id和2进行取模来选取数据库,根据id与2取模来选取表,比如这里user_id=20,那么20%2=0,所以会选择ds0数据库,id=4,那么4%2 + 1 = 1,所以会选择t_order_1这张表,启动项目,浏览器访问localhost:8080/test,然后刷新数据库,发现这条数据如期的保存到了指定路由的表中