sharding-jdbc教程看这⼀篇就够了
Sharding-JDBC是ShardingSphere的第⼀个产品，也是ShardingSphere的前⾝。它定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使⽤客户端直连数据库，以jar包形式提供服务，⽆需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。
适⽤于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使⽤JDBC。
⽀持任何第三⽅的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
⽀持任意实现JDBC规范的数据库。⽬前⽀持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。
上⾯是官⽹对于sharding-jdbc的解释和介绍，其实说的直⽩⼀点，就是包含了分库分表功能的JDBC，因此我们可以直接把sharding-jdbc当做普通的jdbc来进⾏使⽤。1、环境构建
1、创建⼀个springboot项⽬
2、导⼊如下依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="/POM/4.0.0" xmlns:xsi="/2001/XMLSchema-instance"
xsi:schemaLocation="/POM/4.0.0 /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.3.2.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.ityml</groupId>
<artifactId>shardingsphere_demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>shardingsphere_demo</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&batis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.23</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</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>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<configuration>
<skipTests>true</skipTests>
</configuration>
</plugin>
</plugins>
</build>
</project>
2、sharding-jdbc实现⽔平分表
1、创建sharding_sphere数据库
2、在数据库中创建两张表，orders_1和orders_2
3、分⽚规则：如果订单编号是偶数添加到orders_1，如果是奇数添加到orders_2
4、创建实体类
package com.ityml.shardingsphere_demo.bean;
public class Orders {
private Integer id;
private Integer orderType;
private Integer customerId;
private Double amount;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getOrderType() {
return orderType;
}
public void setOrderType(Integer orderType) {
}
public Integer getCustomerId() {
return customerId;
}
public void setCustomerId(Integer customerId) {
this.customerId = customerId;
}
public Double getAmount() {
return amount;
}
public void setAmount(Double amount) {
this.amount = amount;
}
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", orderType=" + orderType +
", customerId=" + customerId +
", amount=" + amount +
'}';
}
}
5、创建mapper类
package com.ityml.shardingsphere_demo.mapper;
java的jdbc连接数据库import com.ityml.shardingsphere_demo.bean.Orders;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;
@Repository
@Mapper
public interface OrdersMapper {
@Insert("insert into orders(id,order_type,customer_id,amount) values(#{id},#{orderType},#{customerId},#{amount})") public void insert(Orders orders);
@Select("select * from orders where id = #{id}")
@Results({
@Result(property = "id",column = "id"),
@Result(property = "orderType",column = "order_type"),
@Result(property = "customerId",column = "customer_id"),
@Result(property = "amount",column = "amount")
})
public Orders selectOne(Integer id);
}
6、创建配置⽂件
#整合mybatis
#配置数据源的名称
spring.shardingsphere.datasource.names=ds1
#配置数据源的具体内容，
spring.shardingsphere.pe=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.sql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://192.168.85.111:3306/sharding_sphere?serverTimezone=UTC
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456
#指定orders表的分布情况，配置表在哪个数据库中，表名称是什么
spring.shardingsphere.ders.ders_$->{1..2}
#指定orders表⾥主键id⽣成策略
spring.shardingsphere.lumn=id
spring.shardingsphere.pe=SNOWFLAKE
#指定分⽚策略。根据id的奇偶性来判断插⼊到哪个表
spring.shardingsphere.ders.table-strategy.inline.sharding-column=id
spring.shardingsphere.ders.table-strategy.inline.algorithm-expression=orders_${id%2+1}
#打开sql输出⽇志
spring.shardingsphere.props.sql.show=true
7、创建测试类
package com.ityml.shardingsphere_demo;
import com.ityml.shardingsphere_demo.bean.Orders;
import com.ityml.shardingsphere_demo.mapper.OrdersMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.st.context.SpringBootTest;
@SpringBootTest
class ShardingsphereDemoApplicationTests {
@Autowired
private OrdersMapper ordersMapper;
@Test
public void addOrders(){
for (int i = 1; i <=10 ; i++) {
Orders orders = new Orders();
orders.setId(i);
orders.setCustomerId(i);
orders.setOrderType(i);
orders.setAmount(1000.0*i);
ordersMapper.insert(orders);
}
}
@Test
public void queryOrders(){
Orders orders = ordersMapper.selectOne(1);
System.out.println(orders);
}
}
3、sharding-jdbc实现⽔平分库
1、在不同的数据节点node01,node02上创建不同名称的数据库：sharding_sphere_1,sharding_sphere_2
2、在两个数据库上创建相同的表orders_1,orders_2
3、分⽚规则，按照customer_id的奇偶性来进⾏分库，然后按照id的奇偶性进⾏分表
4、修改配置⽂件
# 配置不同的数据源
spring.shardingsphere.datasource.names=ds1,ds2
#配置ds1数据源的基本信息
spring.shardingsphere.pe=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.sql.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://192.168.85.111:3306/sharding_sphere_1?serverTimezone=UTC spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123456
#配置ds2数据源的基本信息
spring.shardingsphere.pe=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds2.sql.jdbc.Driver
spring.shardingsphere.datasource.ds2.url=jdbc:mysql://192.168.85.112:3306/sharding_sphere_2?serverTimezone=UTC spring.shardingsphere.datasource.ds2.username=root
spring.shardingsphere.datasource.ds2.password=123456
#指定数据库的分布情况
spring.shardingsphere.ders.actual-data-nodes=ds$->{1..2}.orders_$->{1..2}
#指定orders表的主键⽣成策略
spring.shardingsphere.lumn=id
spring.shardingsphere.pe=SNOWFLAKE
#指定表分⽚策略，根据id的奇偶性来添加到不同的表中
spring.shardingsphere.ders.table-strategy.inline.sharding-column=id
spring.shardingsphere.ders.table-strategy.inline.algorithm-expression=orders_$->{id%2+1}
#指定库分⽚策略，根据customer_id的奇偶性来添加到不同的库中
spring.shardingsphere.ders.database-strategy.inline.sharding-column=customer_id
spring.shardingsphere.ders.database-strategy.inline.algorithm-expression=ds$->{customer_id%2+1}
#打开sql输出⽇志
spring.shardingsphere.props.sql.show=true
5、修改mapper类
package com.ityml.shardingsphere_demo.mapper;
import com.ityml.shardingsphere_demo.bean.Orders;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;
@Repository
@Mapper
public interface OrdersMapper {
@Insert("insert into orders(id,order_type,customer_id,amount) values(#{id},#{orderType},#{customerId},#{amount})") public void insert(Orders orders);
@Select("select * from orders where id = #{id}")
@Results({
@Result(property = "id",column = "id"),
@Result(property = "orderType",column = "order_type"),
@Result(property = "customerId",column = "customer_id"),
@Result(property = "amount",column = "amount")
})
public Orders selectOne(Integer id);
@Select("select * from orders where id = #{id} and customer_id=#{customerId}")
@Results({
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论