首先创建二个数据库 没有多台服务器只能在本机上了,但是都是一样的,在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,然后刷新数据库,发现这条数据如期的保存到了指定路由的表中