专栏首页菩提树下的杨过shading-jdbc 4.1.1 + tk.mybatis + pagehelper 1.3.x +spring boot 2.x 使用注意事项

shading-jdbc 4.1.1 + tk.mybatis + pagehelper 1.3.x +spring boot 2.x 使用注意事项

shading-jdbc 4.1.1 + tk.mybatis + pagehelper 1.3.x + spring boot 2.x 是一个很常用的组合,但在使用过程中可能会遇到一些小问题,记录于此:

一、pom依赖

主要有以下几个:

 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

 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表结构如下:

 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

<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

 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

1 spring.shardingsphere.sharding.tables.t_order_logic.key-generator.column=order_id
2 spring.shardingsphere.sharding.tables.t_order_logic.key-generator.type=SNOWFLAKE

2. 表结构上的自增主键id,需要把entity类的自动生成主键注释掉

如果insert成功后,要拿到新的id值,建议id字段在insert前就手动赋值,参考下面的做法,直接调用内置的snowflake生成器:

创建一个IdService服务:

 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地址:

  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,比如:

select order_id, order_name, order_date from t_order_logic_0 where order_date='2020-09-06';

会首先被改写为:

select count(0) from t_order_logic_0 where order_date='2020-09-06';

然后再由sharding-jdbc改写成:(精确路由)

select count(0) from t_order_0 where order_date='2020-09-06';

对于简单语句,这样没什么问题。但是如果原始语句上,有一些聚合函数或group by,比如下面这样: 

如上图,加了group by 后,下面的语句

SELECT count(0) FROM (SELECT order_id FROM t_order_logic WHERE order_date = ? GROUP BY order_id) table_count

sharding-jdbc并不能正确解析为t_order_0,仍然还是t_order_logic

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 纯手工搭建JSF开发环境(JSF2.2+maven+weblogic 12c/jboss EAP 6.1+)

    JSF 2.X因为种种原因(我个人觉得主要是因为推出太晚),再加上EJB2之前的设计过于复杂,引起很多开发人员对官方解决方案的反感,即使EJB3后来做了大量改进...

    菩提树下的杨过
  • tomcat 8.5.9.0 解决catalina.out过大的问题

    先吐嘈一下tomcat这个项目,日志切割这么常见的功能,tomcat这种知名开源项目默认居然不开启,生产环境跑不了几天,磁盘就满了,而且很多网上流传的方法,比如...

    菩提树下的杨过
  • MSDN官方的ASP.Net异步页面的经典示例代码

    示例1.演示异步获取一个网址的内容,处理后显示在OutPut这一Label上 using System; using System.Web; using S...

    菩提树下的杨过
  • 1 小时 SQL 极速入门(三)

    今天我们讲一些在做报表和复杂计算时非常实用的分析函数。由于各个数据库函数的实现不太一样,本文基于 Oracle 12c 。

    Lenis
  • 1 小时 SQL 极速入门(一)

    先解释下 CRUD 是什么。CRUD 就是我们常说的增删改查(Create,Retrieve,Update,Delete)

    Lenis
  • Django实战-生鲜电商-订单评论

    经过几轮的视图类的编写,整个电商项目的逻辑有清晰一点了么?对于事务逻辑的数据处理,分为查询和创建。相对于本项目来说,创建的操作大多数在用户下单到支付成功这一过程...

    小团子
  • 分库分表之第二篇

    使用Sharding-JDBC完成对订单表的水平分表,通过快速入门程序的开发,快速体验Sharding-JDBC的使用。人工创建两张表,t_order_1和t_...

    海仔
  • 1 SpringBoot 使用sharding jdbc进行分库分表

    分库分表在数据量大的系统中比较常用,解决方案有Cobar,TDDL等,这次主要是拿当当网开源的Sharding-JDBC来做个小例子。 它的github地址...

    天涯泪小武
  • Mysql order by 优化

    本节描述MySQL何时可以使用索引来满足ORDER BY子句,当不能使用索引时使用filesort,以及优化器中有关ORDER BY的执行计划信息。

    XING辋
  • 批量删除指定user和transaction type对应order的report

    版权声明:署名,允许他人基于本文进行创作,且必须基于与原先许可协议相同的许可协议分发本文 (Creative Commons)

    Jerry Wang

扫码关注云+社区

领取腾讯云代金券