ShardingSphere-JDBC 4.0.0-RC1 MyBatis 单库分表
pom文件
增加shardingsphere引用
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.0.0-RC1</version>
</dependency>
完整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>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.4.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>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-web</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.32</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.8</version>
</dependency>
</dependencies>
</project>
代码
entity类
package com.example.demo.entity;
public class TestTb implements Serializable {
private Long id;
private String name;
private String inscode;
private String haha;
/// set get ...
}
DAO类
package com.example.demo.dao;
@Mapper
public interface TestTbDAO {
int insert(TestTb testTb);
List<TestTb> select();
}
Service类
package com.example.demo.service;
public interface TestTbService {
int insert(TestTb testTb);
List<TestTb> select();
}
ServiceImpl类
package com.example.demo.service;
@Service
public class TestTbServiceImpl implements TestTbService {
@Autowired
TestTbDAO testTbDAO;
@Override
public int insert(TestTb testTb) {
return testTbDAO.insert(testTb);
}
@Override
public List<TestTb> select() {
return testTbDAO.select();
}
}
Controller类
package com.example.demo.controller;
@RestController
public class TestController {
@Autowired
private TestTbService testTbService;
@GetMapping("/getlist")
public String getUsers() {
return JSON.toJSONString(testTbService.select());
}
@GetMapping("/add")
public String add() {
int num = (int) (Math.random() * 900) + 100;
TestTb info = new TestTb();
info.setHaha("测测测" + num);
info.setInscode("123456");
info.setName("名字" + num);
testTbService.insert(info);
return "OK";
}
}
mapper文件
<?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.example.demo.dao.TestTbDAO">
<resultMap id="BaseResultMap" type="com.example.demo.entity.TestTb">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="name" jdbcType="VARCHAR" property="name"/>
<result column="inscode" jdbcType="VARCHAR" property="inscode"/>
<result column="haha" jdbcType="VARCHAR" property="haha"/>
</resultMap>
<insert id="insert" parameterType="com.example.demo.entity.TestTb"
useGeneratedKeys="true" keyProperty="id">
insert into test_tb
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="name != null">name,</if>
<if test="inscode != null">inscode,</if>
<if test="haha != null">haha,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="name != null">#{name,jdbcType=VARCHAR},</if>
<if test="inscode != null">#{inscode,jdbcType=VARCHAR},</if>
<if test="haha != null">#{haha,jdbcType=VARCHAR},</if>
</trim>
</insert>
<select id="select" resultMap="BaseResultMap">
select * from test_tb
</select>
</mapper>
application.yml文件
mybatis:
configuration:
map-underscore-to-camel-case: true
mapper-locations: classpath*:mapper/*.xml
typeAliasesPackage: com.example.demo.entity
spring:
main:
allow-bean-definition-overriding: true
shardingsphere:
datasource: ## 数据库配置
names: master
master:
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://192.168.12.33/test?relaxAutoCommit=true&zeroDateTimeBehavior=convertToNull&&allowMultiQueries=true&serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=UTF-8
username: root
password: 123123
props:
sql:
show: true
sharding:
tables:
test_tb: # test_tb的分片配置
actual-data-nodes: master.test_tb_$->{0..2} # 拼接表名
key-generator:
column: id
type: SNOWFLAKE
table-strategy:
inline:
algorithm-expression: test_tb_$->{id % 3} # 分片规则
sharding-column: id
运行效果
插入数据
运行项目执行访问几次接口/add
打印日志
2020-12-17 10:10:12.334 INFO 9272 --- [nio-8080-exec-7] ShardingSphere-SQL : Rule Type: sharding
2020-12-17 10:10:12.335 INFO 9272 --- [nio-8080-exec-7] ShardingSphere-SQL : Logic SQL: insert into test_tb
( name,
inscode,
haha )
values ( ?,
?,
? )
2020-12-17 10:10:12.335 INFO 9272 --- [nio-8080-exec-7] ShardingSphere-SQL : SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=test_tb, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=test_tb, quoteCharacter=NONE, schemaNameLength=0), SQLToken(startIndex=29)], parametersIndex=3, logicSQL=insert into test_tb
( name,
inscode,
haha )
values ( ?,
?,
? )), deleteStatement=false, updateTableAlias={}, updateColumnValues={}, whereStartIndex=0, whereStopIndex=0, whereParameterStartIndex=0, whereParameterEndIndex=0), columnNames=[name, inscode, haha], values=[InsertValue(columnValues=[org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@7112dec3, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@1af2f22b, org.apache.shardingsphere.core.parse.old.parser.expression.SQLPlaceholderExpression@52e34230])])
2020-12-17 10:10:12.335 INFO 9272 --- [nio-8080-exec-7] ShardingSphere-SQL : Actual SQL: master ::: insert into test_tb_2
(name, inscode, haha, id) VALUES (?, ?, ?, ?) ::: [名字160, 123123, 测测测160, 546272076718145537]
Logic SQL 打印记录程序写的SQL,使用的逻辑表
Actual SQL 打印记录经过ShardingSphere转换修改之后的SQL 定位到的真实表
执行SQL查询,查询表中数据
查询数据
请求getlist接口
1.查询所有数据
查询语句为
select * from test_tb
打印日志:
2020-12-17 10:16:26 JRebel: Reloading SQL maps
2020-12-17 10:16:26.353 INFO 9272 --- [nio-8080-exec-1] ShardingSphere-SQL : Rule Type: sharding
2020-12-17 10:16:26.353 INFO 9272 --- [nio-8080-exec-1] ShardingSphere-SQL : Logic SQL: select * from test_tb
2020-12-17 10:16:26.353 INFO 9272 --- [nio-8080-exec-1] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=test_tb, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=test_tb, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=0, logicSQL=select * from test_tb)), containStar=true, firstSelectItemStartIndex=7, selectListStopIndex=7, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2020-12-17 10:16:26.353 INFO 9272 --- [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL: master ::: select * from test_tb_0
2020-12-17 10:16:26.353 INFO 9272 --- [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL: master ::: select * from test_tb_1
2020-12-17 10:16:26.353 INFO 9272 --- [nio-8080-exec-1] ShardingSphere-SQL : Actual SQL: master ::: select * from test_tb_2
没有使用任何条件的查询语句,查询所有的分表,将结果进行拼接到一起进行返回
2.根据ID查询数据
修改查询sql为
select * from test_tb where id=546272010158735361
打印日志:
2020-12-17 10:18:55 JRebel: Reloading SQL maps
2020-12-17 10:18:55.649 INFO 9272 --- [nio-8080-exec-4] ShardingSphere-SQL : Rule Type: sharding
2020-12-17 10:18:55.649 INFO 9272 --- [nio-8080-exec-4] ShardingSphere-SQL : Logic SQL: select * from test_tb where id=546272010158735361
2020-12-17 10:18:55.649 INFO 9272 --- [nio-8080-exec-4] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=test_tb, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=id, tableName=test_tb), operator=EQUAL, compareOperator==, positionValueMap={0=546272010158735361}, positionIndexMap={})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=test_tb, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=0, logicSQL=select * from test_tb where id=546272010158735361)), containStar=true, firstSelectItemStartIndex=7, selectListStopIndex=7, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2020-12-17 10:18:55.649 INFO 9272 --- [nio-8080-exec-4] ShardingSphere-SQL : Actual SQL: master ::: select * from test_tb_0 where id=546272010158735361
根据查询条件ID 定位到查询test_tb_0表进行查询,返回查询数据
3.根据NAME查询数据,非分表字段
修改sql为
select * from test_tb where name like '%名字%'
打印日志:
2020-12-17 10:18:55 JRebel: Reloading SQL maps
2020-12-17 10:18:55.649 INFO 9272 --- [nio-8080-exec-4] ShardingSphere-SQL : Rule Type: sharding
2020-12-17 10:18:55.649 INFO 9272 --- [nio-8080-exec-4] ShardingSphere-SQL : Logic SQL: select * from test_tb where id=546272010158735361
2020-12-17 10:18:55.649 INFO 9272 --- [nio-8080-exec-4] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=test_tb, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=id, tableName=test_tb), operator=EQUAL, compareOperator==, positionValueMap={0=546272010158735361}, positionIndexMap={})])])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=test_tb, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=0, logicSQL=select * from test_tb where id=546272010158735361)), containStar=true, firstSelectItemStartIndex=7, selectListStopIndex=7, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2020-12-17 10:18:55.649 INFO 9272 --- [nio-8080-exec-4] ShardingSphere-SQL : Actual SQL: master ::: select * from test_tb_0 where id=546272010158735361
2020-12-17 10:21:28 JRebel: Reloading SQL maps
2020-12-17 10:21:28.468 INFO 9272 --- [nio-8080-exec-8] ShardingSphere-SQL : Rule Type: sharding
2020-12-17 10:21:28.468 INFO 9272 --- [nio-8080-exec-8] ShardingSphere-SQL : Logic SQL: select * from test_tb where name like '%名字%'
2020-12-17 10:21:28.468 INFO 9272 --- [nio-8080-exec-8] ShardingSphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=test_tb, alias=Optional.absent())]), routeConditions=Conditions(orCondition=OrCondition(andConditions=[])), encryptConditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(tableName=test_tb, quoteCharacter=NONE, schemaNameLength=0)], parametersIndex=0, logicSQL=select * from test_tb where name like '%名字%')), containStar=true, firstSelectItemStartIndex=7, selectListStopIndex=7, groupByLastIndex=0, items=[StarSelectItem(owner=Optional.absent())], groupByItems=[], orderByItems=[], limit=null, subqueryStatement=null, subqueryStatements=[], subqueryConditions=[])
2020-12-17 10:21:28.469 INFO 9272 --- [nio-8080-exec-8] ShardingSphere-SQL : Actual SQL: master ::: select * from test_tb_0 where name like '%名字%'
2020-12-17 10:21:28.469 INFO 9272 --- [nio-8080-exec-8] ShardingSphere-SQL : Actual SQL: master ::: select * from test_tb_1 where name like '%名字%'
2020-12-17 10:21:28.469 INFO 9272 --- [nio-8080-exec-8] ShardingSphere-SQL : Actual SQL: master ::: select * from test_tb_2 where name like '%名字%'
根据NAME字段无法定位到查询的表,则执行所有表查询,将查询结果进行拼接返回