概念
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。
项目结构
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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<artifactId>demo</artifactId>
<groupId>com.alex</groupId>
<version>0.0.1-SNAPSHOT</version>
</parent>
<groupId>com.alex</groupId>
<artifactId>sharding-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sharding-jdbc</name>
<description>Demo project for Spring Boot</description>
<packaging>jar</packaging>
<properties>
<java.version>1.8</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.3.7.RELEASE</spring-boot.version>
<lombok.version>1.18.4</lombok.version>
<hutool.version>4.1.1</hutool.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</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.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<!-- Druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.21</version>
</dependency>
<!--jdbc-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- mybatis-plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>${lombok.version}</version>
</dependency>
<!-- hutool -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>${hutool.version}</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-core</artifactId>
<version>3.5.1</version>
<scope>compile</scope>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>2.3.7.RELEASE</version>
<configuration>
<mainClass>com.alex.shardingjdbc.ShardingJdbcApplication</mainClass>
</configuration>
<executions>
<execution>
<id>repackage</id>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
yml文件配置
单库分表的写法:
server:
port: 8089
servlet:
context-path: /sharding-jdbc
spring:
application:
name: sharding-jdbc
main:
# 一个实体类对应三张表,覆盖,不然启动报错
allow-bean-definition-overriding: true
# Sharding-JDBC的配置
shardingsphere:
datasource:
# 数据源(逻辑名字)
names: m1
# 配置数据源
m1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/alex_demo?characterEncoding=utf8&useSSL=true&createDatabaseIfNotExist=true&serverTimezone=GMT&nullNamePatternMatchesAll=true
username: root
password: yimaisc@2019
# 分片的配置
sharding:
# 表的分片策略
tables:
# 逻辑表的名称
user:
# 数据节点配置,采用Groovy表达式
actual-data-nodes: m1.user_$->{0..1}
# 配置策略
table-strategy:
# 精确匹配
inline:
sharding-column: cid
algorithm-expression: user_$->{cid % 2}
# 主键生成策略
key-generator:
# 主键
column: cid
# 雪花算法
type: SNOWFLAKE
props:
sql:
# 日志显示具体的SQL
show: true
logging:
level:
com.wang.test.demo: DEBUG
mybatis-plus:
mapper-locations: classpath*:/mapper/*.xml
type-aliases-package: com.alex.shardingjdbc.entity
configuration:
#在映射实体或者属性时,将数据库中表名和字段名中的下划线去掉,按照驼峰命名法映射 address_book ---> addressBook
map-underscore-to-camel-case: true
分库分表的写法:
server:
port: 8089
servlet:
context-path: /sharding-jdbc
spring:
application:
name: sharding-jdbc
main:
# 一个实体类对应三张表,覆盖,不然启动报错
allow-bean-definition-overriding: true
# Sharding-JDBC的配置
shardingsphere:
datasource:
# 数据源(逻辑名字)
names: m0,m1
# 配置数据源
m0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/alex_demo?characterEncoding=utf8&useSSL=true&createDatabaseIfNotExist=true&serverTimezone=GMT&nullNamePatternMatchesAll=true
username: root
password: yimaisc@2019
m1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/activiti?characterEncoding=utf8&useSSL=true&createDatabaseIfNotExist=true&serverTimezone=GMT&nullNamePatternMatchesAll=true
username: root
password: yimaisc@2019
# 分片的配置
sharding:
# 表的分片策略
tables:
# 逻辑表的名称
user:
# 数据节点配置,采用Groovy表达式
actual-data-nodes: m$->{0..1}.user_$->{0..1} #根据表名定义,超了就要创建对应的表,不然则会报错
# 配置策略
table-strategy:
# 精确匹配
inline:
sharding-column: cid
algorithm-expression: user_$->{cid % 2}
# 主键生成策略
key-generator:
# 主键
column: cid
# 雪花算法
type: SNOWFLAKE
props:
sql:
# 日志显示具体的SQL
show: true
logging:
level:
com.wang.test.demo: DEBUG
mybatis-plus:
mapper-locations: classpath*:/mapper/*.xml
type-aliases-package: com.alex.shardingjdbc.entity
configuration:
#在映射实体或者属性时,将数据库中表名和字段名中的下划线去掉,按照驼峰命名法映射 address_book ---> addressBook
map-underscore-to-camel-case: true
业务代码
package com.alex.shardingjdbc.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* 分页配置类
*/
@Configuration
public class MybatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
package com.alex.shardingjdbc.entity;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.Data;
import java.io.Serializable;
@Data
public class User implements Serializable {
private static final long serialVersionUID = 337361630075002456L;
@TableId
private Long cid;
private String name;
private String gender;
private String data;
public User(String name, String gender, String data) {
this.name = name;
this.gender = gender;
this.data = data;
}
}
package com.alex.shardingjdbc.mapper;
import com.alex.shardingjdbc.entity.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserMapper extends BaseMapper<User> {
List<User> getList();
}
package com.alex.shardingjdbc.service;
import com.alex.shardingjdbc.entity.User;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import java.util.List;
public interface UserService extends IService<User> {
void add();
IPage<User> selectListPage();
List<User> getList();
}
package com.alex.shardingjdbc.service.impl;
import com.alex.shardingjdbc.entity.User;
import com.alex.shardingjdbc.mapper.UserMapper;
import com.alex.shardingjdbc.service.UserService;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public void add() {
for (int i = 1; i < 10; i++) {
User test = new User("王" + i, "男", "数据" + i);
userMapper.insert(test);
}
}
@Override
public IPage<User> selectListPage() {
IPage<User> page = new Page(1, 5);
IPage<User> userIPage = userMapper.selectPage(page, new QueryWrapper<User>().orderByAsc("name"));
return userIPage;
}
@Override
public List<User> getList() {
return this.baseMapper.getList();
}
}
package com.alex.shardingjdbc.controller;
import com.alex.shardingjdbc.entity.User;
import com.alex.shardingjdbc.service.UserService;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("/test")
public class UserController {
@Autowired
private UserService userService;
/**
* 插入
*
* @return
*/
@GetMapping("/insertTest")
public String insertTest() {
userService.add();
return "插入完成";
}
/**
* 查询单个
*
* @return
*/
@GetMapping("/selectTest")
public User selectTest() {
User user = this.userService.getById(798130859839324160L);
return user;
}
/**
* 查询所有
*
* @return
*/
@GetMapping("/selectListTest")
public List<User> selectListTest() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.orderByAsc("name");
List<User> list = this.userService.list(wrapper);
return list;
}
/**
* 分页查询
* 逻辑:会向所有的表中去进行一遍分页查询,第一个表数据不够就会加上另一个表分页拿到的值,但是如果加了排序的话,则会混合取值
*
* @return
*/
@GetMapping("/selectListPage")
public IPage<User> selectListPage() {
return this.userService.selectListPage();
}
/**
* 非分片属性查询:同时查询两张表的数据
*
* @return
*/
@GetMapping("/selectListByGender")
public List<User> selectListByGender() {
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("gender", "女");
List<User> list = this.userService.list(wrapper);
return list;
}
/**
* 自定义sql查询
* 备注:不需要指定具体表,只需要按照定义的规则指定表的前缀即可,如select * from user则会去查询user_1和user_2的数据
*
* @return
*/
@GetMapping("/getList")
public List<User> getList() {
List<User> list = this.userService.getList();
return list;
}
}
<?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.alex.shardingjdbc.mapper.UserMapper">
<select id="getList" resultType="com.alex.shardingjdbc.entity.User">
select * from user
</select>
</mapper>