shading-jdbc 4.1.1 tk.mybatis pagehelper 1.3.x spring boot 2.x 是一个很常用的组合，但在使用过程中可能会遇到一些小问题，记录于此：
一、pom依赖
主要有以下几个：
代码语言：javascript复制 1 <properties>
2 <java.version>1.8</java.version>
3 <sharding-sphere.version>4.1.1</sharding-sphere.version>
4 <tk.mybatis.version>2.1.5</tk.mybatis.version>
5 <mybatis.starter.version>2.1.3</mybatis.starter.version>
6 <pagehelper.version>1.3.0</pagehelper.version>
7 </properties>
8
9 <dependencyManagement>
10 <dependencies>
11 <!-- spring-boot-->
12 <dependency>
13 <groupId>org.apache.shardingsphere</groupId>
14 <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
15 <version>${sharding-sphere.version}</version>
16 </dependency>
17
18 <!--tk.mybatis-->
19 <dependency>
20 <groupId>tk.mybatis</groupId>
21 <artifactId>mapper-spring-boot-starter</artifactId>
22 <version>${tk.mybatis.version}</version>
23 </dependency>
24
25 <dependency>
26 <groupId>org.mybatis.spring.boot</groupId>
27 <artifactId>mybatis-spring-boot-starter</artifactId>
28 <version>${mybatis.starter.version}</version>
29 </dependency>
30
31 <!--pagehelper-->
32 <dependency>
33 <groupId>com.github.pagehelper</groupId>
34 <artifactId>pagehelper-spring-boot-starter</artifactId>
35 <version>${pagehelper.version}</version>
36 </dependency>
37
38 </dependencies>
39 </dependencyManagement>二、application.properties
代码语言：javascript复制 1 #数据源
2 spring.shardingsphere.datasource.names=ds0
3 spring.shardingsphere.sharding.default-data-source-name=ds0
4 spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
5 spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
6 spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://127.0.0.1:3306/testdb?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
7 spring.shardingsphere.datasource.ds0.username=root
8 spring.shardingsphere.datasource.ds0.password=***
9 #分表
10 spring.shardingsphere.sharding.tables.t_order_logic.actual-data-nodes=ds0.t_order_$->{0..1}
11 spring.shardingsphere.sharding.tables.t_order_logic.table-strategy.inline.sharding-column=order_id
12 spring.shardingsphere.sharding.tables.t_order_logic.table-strategy.inline.algorithm-expression=t_order_$->{order_id % 2}
13 spring.shardingsphere.props.sql.show=true
14 #精确路由
15 spring.shardingsphere.sharding.tables.t_order_logic_0.actual-data-nodes=ds0.t_order_0
16 spring.shardingsphere.sharding.tables.t_order_logic_1.actual-data-nodes=ds0.t_order_1
17 #mybatis
18 mybatis.mapper-locations=classpath*:mapper/*.xml注：
* 这里只演示了单库分表的最小配置
* 注意里面有一个“精确路由”的配置，有时候我们明确知道数据就在某个具体分表上，但是sql的where条件中又不包含sharding-key，就可以参考上述配置，当查询t_order_logic_0时，直接路由到ds0.t_order_0这张表
t_order_0/t_order_1表结构如下：
代码语言：javascript复制 1 CREATE TABLE `t_order_0` (
2 `order_id` bigint(20) unsigned NOT NULL,
3 `order_name` varchar(255) NOT NULL,
4 `order_date` varchar(255) NOT NULL,
5 PRIMARY KEY (`order_id`)
6 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
7
8 CREATE TABLE `t_order_1` (
9 `order_id` bigint(20) unsigned NOT NULL,
10 `order_name` varchar(255) NOT NULL,
11 `order_date` varchar(255) NOT NULL,
12 PRIMARY KEY (`order_id`)
13 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;三、mybatis自动生成tk.mybatis风格的代码
3.1 先配置plugin
代码语言：javascript复制<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.7</version>
<configuration>
<configurationFile>src/main/resources/generator/generatorConfig.xml</configurationFile>
<overwrite>true</overwrite>
<verbose>true</verbose>
</configuration>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper</artifactId>
<version>4.1.5</version>
</dependency>
</dependencies>
</plugin>3.2 再配置generatorConfig.xml
代码语言：javascript复制 1 <?xml version="1.0" encoding="UTF-8"?>
2
3 <!DOCTYPE generatorConfiguration
4 PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
5 "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
6
7 <generatorConfiguration>
8
9 <context id="Mysql" targetRuntime="MyBatis3" defaultModelType="flat">
10 <property name="beginningDelimiter" value="`"/>
11 <property name="endingDelimiter" value="`"/>
12
13 <plugin type="tk.mybatis.mapper.generator.MapperPlugin">
14 <property name="mappers" value="tk.mybatis.mapper.common.Mapper"/>
15 <property name="caseSensitive" value="true"/>
16 <property name="useActualColumnNames" value="true"/>
17 <property name="beginningDelimiter" value="`"/>
18 <property name="endingDelimiter" value="`"/>
19 </plugin>
20
21
22 <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
23 connectionURL="jdbc:mysql://127.0.0.1:3306/testdb"
24 userId="root"
25 password="***"/>
26
27 <javaModelGenerator
28 targetPackage="test.entity"
29 targetProject="src/test/java"/>
30
31 <sqlMapGenerator targetPackage="mapper" targetProject="src/test/resources"/>
32
33 <javaClientGenerator targetPackage="test.mapper"
34 targetProject="src/test/java"
35 type="XMLMAPPER">
36 <property name="enableSubPackages" value="true"/>
37 </javaClientGenerator>
38
39 <!-- 注：生成的OrderEntity，实际对应的表名为t_order_0，并非逻辑表t_order_logic-->
40 <!-- 解决办法有2个：-->
41 <!-- 1. 生成后，手动修改@Table(name = "`t_order_0`")为@Table(name = "`t_order_logic`")-->
42 <!-- 2. 直接在db中创建一个t_order_logic的表，仅用于生成代码-->
43 <table tableName="t_order_0" domainObjectName="OrderEntity"
44 enableCountByExample="false" enableUpdateByExample="false"
45 enableDeleteByExample="false" enableSelectByExample="false"
46 selectByExampleQueryId="false">
47 <generatedKey column="order_id" sqlStatement="MYSQL" identity="true"/>
48 </table>
49
50 </context>
51 </generatorConfiguration>四、分布式id的问题
sharding-jdbc内置了snowflake算法，但是集成tk.mybatis生成记录后，并不能马上返回自动生成的id值，如下图：
从输出的sql语句上看，sharding-jdbc改写了sql语句，附加了order_id字段，并用snowflake算法生成了新id，但是insert成功后，entity的orderId仍为null。
tips: 要开启sharding-jdbc的snowflake功能，需要修改下面2点
1. application.properties中必须指定snowflake
代码语言：javascript复制1 spring.shardingsphere.sharding.tables.t_order_logic.key-generator.column=order_id
2 spring.shardingsphere.sharding.tables.t_order_logic.key-generator.type=SNOWFLAKE2. 表结构上的自增主键id，需要把entity类的自动生成主键注释掉
如果insert成功后，要拿到新的id值，建议id字段在insert前就手动赋值，参考下面的做法，直接调用内置的snowflake生成器：
创建一个IdService服务：
代码语言：javascript复制 1 package com.cnblogs.yjmyzz.sharding.jdbc.demo.service;
2
3 import com.cnblogs.yjmyzz.sharding.jdbc.demo.utils.NetworkUtils;
4 import org.apache.shardingsphere.core.strategy.keygen.SnowflakeShardingKeyGenerator;
5 import org.springframework.beans.factory.InitializingBean;
6 import org.springframework.stereotype.Service;
7
8 import java.math.BigInteger;
9 import java.util.Properties;
10 import java.util.concurrent.atomic.AtomicInteger;
11
12 @Service("idService")
13 public class IdService implements InitializingBean {
14 private AtomicInteger serverIndex = new AtomicInteger(0);
15
16 private static final SnowflakeShardingKeyGenerator keyGenerator = new SnowflakeShardingKeyGenerator();
17
18 public long nextId() {
19 return (Long) keyGenerator.generateKey();
20 }
21
22 @Override
23 public void afterPropertiesSet() throws Exception {
24 BigInteger ipAddrBigInt = NetworkUtils.ipAddressToBigInt(NetworkUtils.getLocalHostAddress());
25 //假设服务器最多512台
26 BigInteger base = new BigInteger("512");
27 serverIndex.set(ipAddrBigInt.mod(base).intValue());
28 synchronized (this) {
29 Properties prop = keyGenerator.getProperties();
30 prop.setProperty("worker.id", serverIndex.toString());
31 keyGenerator.setProperties(prop);
32 }
33 }
34 }注：snowflake算法要求设置1个唯一的worker.id，防止多个服务器生成相同的id，这里我们取服务器的ip地址，转换成数字，再对集群节点数取模，保证整个集群内唯一。
这里有一个小工具类，用于取服务器ip地址：
代码语言：javascript复制 1 package com.cnblogs.yjmyzz.sharding.jdbc.demo.utils;
2
3
4 import java.math.BigInteger;
5 import java.net.InetAddress;
6 import java.net.NetworkInterface;
7 import java.net.UnknownHostException;
8 import java.util.Enumeration;
9
10 public final class NetworkUtils {
11 public NetworkUtils() {
12 }
13
14 public static String getLocalHostAddress() {
15 InetAddress addr = null;
16
17 try {
18 addr = getLocalHostLANAddress();
19 } catch (UnknownHostException var2) {
20 return "";
21 }
22
23 return addr != null ? addr.getHostAddress() : "";
24 }
25
26 public static InetAddress getLocalHostLANAddress() throws UnknownHostException {
27 try {
28 InetAddress candidateAddress = null;
29 Enumeration ifaces = NetworkInterface.getNetworkInterfaces();
30
31 while (ifaces.hasMoreElements()) {
32 NetworkInterface iface = (NetworkInterface) ifaces.nextElement();
33 Enumeration inetAddrs = iface.getInetAddresses();
34
35 while (inetAddrs.hasMoreElements()) {
36 InetAddress inetAddr = (InetAddress) inetAddrs.nextElement();
37 if (!inetAddr.isLoopbackAddress()) {
38 if (inetAddr.isSiteLocalAddress()) {
39 return inetAddr;
40 }
41
42 if (candidateAddress == null) {
43 candidateAddress = inetAddr;
44 }
45 }
46 }
47 }
48
49 if (candidateAddress != null) {
50 return candidateAddress;
51 } else {
52 InetAddress jdkSuppliedAddress = InetAddress.getLocalHost();
53 if (jdkSuppliedAddress == null) {
54 throw new UnknownHostException("The JDK InetAddress.getLocalHost() method unexpectedly returned null.");
55 } else {
56 return jdkSuppliedAddress;
57 }
58 }
59 } catch (Exception var5) {
60 UnknownHostException unknownHostException = new UnknownHostException("Failed to determine LAN address: " var5);
61 unknownHostException.initCause(var5);
62 throw unknownHostException;
63 }
64 }
65
66 public static BigInteger ipAddressToBigInt(String ipInString) {
67 ipInString = ipInString.replace(" ", "");
68 byte[] bytes;
69 if (ipInString.contains(":")) {
70 bytes = ipv6ToBytes(ipInString);
71 } else {
72 bytes = ipv4ToBytes(ipInString);
73 }
74
75 return new BigInteger(bytes);
76 }
77
78 public static String bigIntToIpAddress(BigInteger ipInBigInt) {
79 byte[] bytes = ipInBigInt.toByteArray();
80 byte[] unsignedBytes = bytes;
81
82 try {
83 String ip = InetAddress.getByAddress(unsignedBytes).toString();
84 return ip.substring(ip.indexOf(47) 1).trim();
85 } catch (UnknownHostException var4) {
86 throw new RuntimeException(var4);
87 }
88 }
89
90 private static byte[] ipv6ToBytes(String ipv6) {
91 byte[] ret = new byte[17];
92 ret[0] = 0;
93 int ib = 16;
94 boolean comFlag = false;
95 if (ipv6.startsWith(":")) {
96 ipv6 = ipv6.substring(1);
97 }
98
99 String[] groups = ipv6.split(":");
100
101 for (int ig = groups.length - 1; ig > -1; --ig) {
102 if (groups[ig].contains(".")) {
103 byte[] temp = ipv4ToBytes(groups[ig]);
104 ret[ib--] = temp[4];
105 ret[ib--] = temp[3];
106 ret[ib--] = temp[2];
107 ret[ib--] = temp[1];
108 comFlag = true;
109 } else {
110 int temp;
111 if ("".equals(groups[ig])) {
112 for (temp = 9 - (groups.length (comFlag ? 1 : 0)); temp-- > 0; ret[ib--] = 0) {
113 ret[ib--] = 0;
114 }
115 } else {
116 temp = Integer.parseInt(groups[ig], 16);
117 ret[ib--] = (byte) temp;
118 ret[ib--] = (byte) (temp >> 8);
119 }
120 }
121 }
122
123 return ret;
124 }
125
126 private static byte[] ipv4ToBytes(String ipv4) {
127 byte[] ret = new byte[5];
128 ret[0] = 0;
129 int position1 = ipv4.indexOf(".");
130 int position2 = ipv4.indexOf(".", position1 1);
131 int position3 = ipv4.indexOf(".", position2 1);
132 ret[1] = (byte) Integer.parseInt(ipv4.substring(0, position1));
133 ret[2] = (byte) Integer.parseInt(ipv4.substring(position1 1, position2));
134 ret[3] = (byte) Integer.parseInt(ipv4.substring(position2 1, position3));
135 ret[4] = (byte) Integer.parseInt(ipv4.substring(position3 1));
136 return ret;
137 }
138
139 public static boolean ipCheck(String tip, String[][] myRange) {
140 boolean flag = false;
141 BigInteger tbig = ipAddressToBigInt(tip);
142 int rangeLength = myRange.length;
143
144 for (int i = 0; i < rangeLength; i) {
145 for (int j = 0; j < myRange[i].length; j) {
146 BigInteger sbig = ipAddressToBigInt(myRange[i][j]);
147 j;
148 BigInteger ebig = ipAddressToBigInt(myRange[i][j]);
149 if (tbig.compareTo(sbig) == 0) {
150 flag = true;
151 break;
152 }
153
154 if (tbig.compareTo(sbig) == 1 && tbig.compareTo(ebig) == -1) {
155 flag = true;
156 break;
157 }
158 }
159 }
160
161 return flag;
162 }
163
164 class IpRange {
165 private String[][] ipRange;
166
167 public IpRange(String[][] ip) {
168 this.ipRange = ip;
169 }
170
171 public String getIpAt(int row, int column) {
172 return this.ipRange[row][column];
173 }
174 }
175 }有了IdService后，在insert前，调用nextId()生成新id，直接赋值给entity即可。
五、MapperScan包名问题
MapperScan有2个，1个是mybatis自带的，1个是tk.mybatis的，集成tk.mybatis时，注意要使用tk.mybatis的MapperScan
六、PageHelper的count问题
PageHelper为了算总记录条数，会改写原始sql，做1次count，比如：
代码语言：javascript复制select order_id, order_name, order_date from t_order_logic_0 where order_date='2020-09-06';会首先被改写为：
代码语言：javascript复制select count(0) from t_order_logic_0 where order_date='2020-09-06';然后再由sharding-jdbc改写成:(精确路由)
代码语言：javascript复制select count(0) from t_order_0 where order_date='2020-09-06';对于简单语句，这样没什么问题。但是如果原始语句上，有一些聚合函数或group by，比如下面这样：
如上图，加了group by 后，下面的语句
代码语言：javascript复制SELECT count(0) FROM (SELECT order_id FROM t_order_logic WHERE order_date = ? GROUP BY order_id) table_countsharding-jdbc并不能正确解析为t_order_0，仍然还是t_order_logic
代码语言：javascript复制SELECT count(0) FROM (SELECT order_id FROM t_order_logic WHERE order_date = ? GROUP BY order_id) table_count ::: [2020-09-06]解决办法：pagehelper对于count语句，允许用户自定义，只要在原来的语句id，加上“_COUNT”
1 <select id="selectList2" resultMap="BaseResultMap">
2 select order_id from t_order_logic${tableIndex} where order_date=#{orderDate} group by order_id
3 </select>
4
5 <select id="selectList2_COUNT" resultType="long">
6 select count(1) from t_order_logic${tableIndex} where order_date=#{orderDate} group by order_id
7 </select>再运行一下：
现在就正常了。
示例代码已经上传到github，地址：https://github.com/yjmyzz/shardingjdbc-mybatis-pagehelper-springboot-demo
