shardingjdbc分库分表需要库、表提前手动创建,即只实现表的路由,不会自动创建表。
1. Sharding-JDBC是什么?
Sharding-JDBC提供标准化的数据分片、分布式事务和数据库治理功能,定位为轻量级Java框架,在Java的JDBC层提供的额外服务。 它使用客户端直连数据库,以jar包形式提供服务,无需额外部署和依赖,可理解为增强版的JDBC驱动,完全兼容JDBC和各种ORM框架。
适用于任何基于Java的ORM框架,如:JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
基于任何第三方的数据库连接池,如:DBCP, C3P0, BoneCP, Druid, HikariCP等。
支持任意实现JDBC规范的数据库。目前支持MySQL,Oracle,SQLServer和PostgreSQL。
参考:数据库分库分表(二)shardingjdbc配置文件_shardingsphere 配置文件-CSDN博客
1. 创建Maven项目,并配置 pom 依赖:
<?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>
<groupId>org.example</groupId>
<artifactId>apache-shardingjdbc</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.11.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<exclusions>
<!--排除默认的tomcat-jdbc-->
<exclusion>
<groupId>org.apache.tomcat</groupId>
<artifactId>tomcat-jdbc</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.9</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.2</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<!-- <version>2.1.3.RELEASE</version>-->
</plugin>
</plugins>
</build>
</project>
2.创建实体类
package com.sharding.entity;
import lombok.Data;
@Data
public class User {
private Long id ;
private Integer age ;
private String name;
private Long cid;
}
3. 创建 mapper 类
package com.sharding.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sharding.entity.User;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
4. 创建控制层
package com.sharding.controller;
import com.sharding.entity.User;
import com.sharding.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.Random;
@RestController
@RequestMapping("sharding")
public class UserController {
@Autowired
private UserMapper userMapper;
@RequestMapping("saveUser")
public int saveUser(){
User user = new User();
Random random = new Random();
int value = random.nextInt(20);
user.setAge(value);
long longValue = random.nextLong();
user.setCid(longValue);
user.setName("test"+value);
int result = userMapper.insert(user);
return result;
}
}
5. 创建启动类
package com.sharding;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan(basePackages = "com.sharding.mapper")
public class ApacheShardingJdbcApplication {
public static void main(String[] args) {
SpringApplication.run(ApacheShardingJdbcApplication.class,args);
}
}
6. 创建分库分表的配置文件
#数据库m1,m2
spring.shardingsphere.datasource.names=m1,m2
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://127.0.0.1:3306/userdb?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=zengjian
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://127.0.0.1:3306/userdb2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=zengjian
# 分库策略 根据id取模确定数据进哪个数据库
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=age
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=m$->{age % 2+1}
spring.shardingsphere.sharding.binding-tables=user
# user表进行分表
spring.shardingsphere.sharding.tables.user.actual-data-nodes=m$->{1..2}.user_$->{1..2}
# 分表字段id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
# 分表策略 根据id取模,确定数据最终落在那个表中
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression = user_$->{id % 2+1}
#分表列
spring.shardingsphere.sharding.tables.user.key-generator.column=id
#id数据生成算法
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true
7. 读写分离的配置文件
#shardingsphere 读写分离,master-slave,可以一主多从
spring.shardingsphere.datasource.names=ds-master,ds-slave0
#主库
spring.shardingsphere.datasource.ds-master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-master.jdbc-url=jdbc:mysql://112.125.26.63:3306/userdb_master?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds-master.username=root
spring.shardingsphere.datasource.ds-master.password=root
#从库0
spring.shardingsphere.datasource.ds-slave0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds-slave0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds-slave0.jdbc-url=jdbc:mysql://112.125.26.64:3306/userdb_slave?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.shardingsphere.datasource.ds-slave0.username=root
spring.shardingsphere.datasource.ds-slave0.password=root
#从库1
#spring.shardingsphere.datasource.ds-slave1.type=com.alibaba.druid.pool.DruidDataSource
#spring.shardingsphere.datasource.ds-slave1.type=com.zaxxer.hikari.HikariDataSource
#spring.shardingsphere.datasource.ds-slave1.driver-class-name=com.mysql.cj.jdbc.Driver
#spring.shardingsphere.datasource.ds-slave1.jdbc-url=jdbc:mysql://112.125.26.65:3306/shop_ds_slave1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
#spring.shardingsphere.datasource.ds-slave1.username=root
#spring.shardingsphere.datasource.ds-slave1.password=root
#读写分离主从规则设置,当有2个以上从库时,从库读采用轮询的负载均衡机制
spring.shardingsphere.masterslave.load-balance-algorithm-type=round_robin
spring.shardingsphere.masterslave.name=ms
spring.shardingsphere.masterslave.master-data-source-name=ds-master
spring.shardingsphere.masterslave.slave-data-source-names=ds-slave0
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true
8.创建启动类
package com.sharding;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan(basePackages = "com.sharding.mapper")
public class ApacheShardingJdbcApplication {
public static void main(String[] args) {
SpringApplication.run(ApacheShardingJdbcApplication.class,args);
}
}
9.创建对应数据库:userdb , userdb2 , 并分别执行以下脚本
-- ----------------------------
-- Table structure for user_1
-- ----------------------------
DROP TABLE IF EXISTS `user_1`;
CREATE TABLE `user_1` (
`id` bigint(20) NOT NULL,
`name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
`cid` bigint(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Table structure for user_2
-- ----------------------------
DROP TABLE IF EXISTS `user_2`;
CREATE TABLE `user_2` (
`id` bigint(20) NOT NULL,
`name` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
`cid` bigint(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;
10. 验证
浏览器请求: http://localhost:8080/sharding/saveUser
查看日志:可以看到sql 执行的逻辑过程如下
2021-11-13 23:54:20.056 INFO 186124 --- [nio-8080-exec-2] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=user, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=id, tableName=user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=0}), Condition(column=Column(name=age, tableName=user), operator=EQUAL, compareOperator=null, positionValueMap={}, positionIndexMap={0=1})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=user, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=18)], parametersIndex=4, logicSQL=INSERT INTO user ( id,
age,
name,
cid ) VALUES ( ?,
?,
?,
? )), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[id, age, name, cid], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@62520960, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@14c57902, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@7c661b99, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@6c580fa2])])
2021-11-13 23:54:20.056 INFO 186124 --- [nio-8080-exec-2] ShardingSphere-SQL : Actual SQL: m1 ::: INSERT INTO user_2 (id, age, name, cid) VALUES (?, ?, ?, ?) ::: [1459550199242993665, 14, test14, -2186172803516415750]