当前位置:Gxlcms > 数据库问题 > Spring Boot整合Sharding-JDBC实现分库分表+读写分离io.shardingsphere(4)

Spring Boot整合Sharding-JDBC实现分库分表+读写分离io.shardingsphere(4)

时间:2021-07-01 10:21:17 帮助过:17人阅读

int(10) NOT NULL, `name` varchar(50) NOT NULL, `sex` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `t_user_1`; CREATE TABLE `t_user_1` ( `id` int(10) NOT NULL, `name` varchar(50) NOT NULL, `sex` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `t_user_2`; CREATE TABLE `t_user_2` ( `id` int(10) NOT NULL, `name` varchar(50) NOT NULL, `sex` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `t_user_3`; CREATE TABLE `t_user_3` ( `id` int(10) NOT NULL, `name` varchar(50) NOT NULL, `sex` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; USE `test2`; DROP TABLE IF EXISTS `t_user_0`; CREATE TABLE `t_user_0` ( `id` int(10) NOT NULL, `name` varchar(50) NOT NULL, `sex` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `t_user_1`; CREATE TABLE `t_user_1` ( `id` int(10) NOT NULL, `name` varchar(50) NOT NULL, `sex` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `t_user_2`; CREATE TABLE `t_user_2` ( `id` int(10) NOT NULL, `name` varchar(50) NOT NULL, `sex` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `t_user_3`; CREATE TABLE `t_user_3` ( `id` int(10) NOT NULL, `name` varchar(50) NOT NULL, `sex` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3、上代码

1、pom.xml配置引入maven依赖

  1. <dependency>
  2. <groupId>org.springframework.boot</groupId>
  3. <artifactId>spring-boot-starter-web</artifactId>
  4. </dependency>
  5. <!--springboot整合mybatis的依赖 -->
  6. <!-- https:<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter --></span>
  7. <dependency>
  8. <groupId>org.mybatis.spring.boot</groupId>
  9. <artifactId>mybatis-spring-boot-starter</artifactId>
  10. <version><span style="color: rgba(128, 0, 128, 1)">2.1</span>.<span style="color: rgba(128, 0, 128, 1)">4</span></version>
  11. </dependency>
  12. <!-- https:<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">mvnrepository.com/artifact/mysql/mysql-connector-java --></span>
  13. <dependency>
  14. <groupId>mysql</groupId>
  15. <artifactId>mysql-connector-java</artifactId>
  16. </dependency>
  17. <!-- 这里用的是sharding-jdbc-spring-boot-starter 需要注意的是,此时druid不能用spring-boot-starter版本的,需要用正常的包: -->
  18. <dependency>
  19. <groupId>com.alibaba</groupId>
  20. <artifactId>druid-spring-boot-starter</artifactId>
  21. <version><span style="color: rgba(128, 0, 128, 1)">1.1</span>.<span style="color: rgba(128, 0, 128, 1)">10</span></version>
  22. </dependency>
  23. <dependency>
  24. <groupId>io.shardingsphere</groupId>
  25. <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
  26. <version><span style="color: rgba(128, 0, 128, 1)">3.1</span>.<span style="color: rgba(128, 0, 128, 1)">0</span>.M1</version>
  27. </dependency>
  28. <dependency>
  29. <groupId>org.springframework.boot</groupId>
  30. <artifactId>spring-boot-starter-test</artifactId>
  31. <scope>test</scope>
  32. </dependency>
  33. <!-- https:<span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">mvnrepository.com/artifact/org.projectlombok/lombok --></span>
  34. <dependency>
  35. <groupId>org.projectlombok</groupId>
  36. <artifactId>lombok</artifactId>
  37. <scope>provided</scope>
  38. </dependency>

2、在application.yml中配置引用数据源及分库分表信息

  1. mybatis.config-location: classpath:META-INF/mybatis-<span style="color: rgba(0, 0, 0, 1)">config.xml
  2. spring:
  3. profiles:
  4. active: sharding</span>-tbl-<span style="color: rgba(0, 0, 0, 1)">ms
  5. main:
  6. allow</span>-bean-definition-overriding: <span style="color: rgba(0, 0, 255, 1)">true</span><span style="color: rgba(0, 0, 0, 1)">
  7. sharding:
  8. jdbc:
  9. ### 数据库
  10. dataSource:
  11. ### 数据库的别名
  12. names: ds</span>-master-<span style="color: rgba(128, 0, 128, 1)">0</span>,ds-master-<span style="color: rgba(128, 0, 128, 1)">1</span>,ds-master-<span style="color: rgba(128, 0, 128, 1)">0</span>-slave-<span style="color: rgba(128, 0, 128, 1)">0</span>,ds-master-<span style="color: rgba(128, 0, 128, 1)">0</span>-slave-<span style="color: rgba(128, 0, 128, 1)">1</span>,ds-master-<span style="color: rgba(128, 0, 128, 1)">1</span>-slave-<span style="color: rgba(128, 0, 128, 1)">0</span>,ds-master-<span style="color: rgba(128, 0, 128, 1)">1</span>-slave-<span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">
  13. # 主库1 ,master数据库
  14. ds</span>-master-<span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">:
  15. ### 数据源类别
  16. type: com.alibaba.druid.pool.DruidDataSource
  17. driverClassName: com.mysql.cj.jdbc.Driver
  18. url: jdbc:mysql:</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">192.168.8.162:3306/test1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8</span>
  19. <span style="color: rgba(0, 0, 0, 1)"> username: root
  20. password: root
  21. maxPoolSize: </span><span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)">
  22. ### 主库1从库1 ,slave数据库
  23. ds</span>-master-<span style="color: rgba(128, 0, 128, 1)">0</span>-slave-<span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">:
  24. ### 数据源类别
  25. type: com.alibaba.druid.pool.DruidDataSource
  26. driverClassName: com.mysql.cj.jdbc.Driver
  27. url: jdbc:mysql:</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">192.168.8.134:3306/test1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8</span>
  28. <span style="color: rgba(0, 0, 0, 1)"> username: root
  29. password: root
  30. maxPoolSize: </span><span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)">
  31. ### 主库1从库1 ,slave数据库
  32. ds</span>-master-<span style="color: rgba(128, 0, 128, 1)">0</span>-slave-<span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">:
  33. ### 数据源类别
  34. type: com.alibaba.druid.pool.DruidDataSource
  35. driverClassName: com.mysql.cj.jdbc.Driver
  36. url: jdbc:mysql:</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">192.168.8.176:3306/test1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8</span>
  37. <span style="color: rgba(0, 0, 0, 1)"> username: root
  38. password: root
  39. maxPoolSize: </span><span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)">
  40. # 主库2 ,master数据库
  41. ds</span>-master-<span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">:
  42. ### 数据源类别
  43. type: com.alibaba.druid.pool.DruidDataSource
  44. driverClassName: com.mysql.cj.jdbc.Driver
  45. url: jdbc:mysql:</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">192.168.8.162:3306/test2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8</span>
  46. <span style="color: rgba(0, 0, 0, 1)"> username: root
  47. password: root
  48. maxPoolSize: </span><span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)">
  49. ### 主库2从库1 ,slave数据库
  50. ds</span>-master-<span style="color: rgba(128, 0, 128, 1)">1</span>-slave-<span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">:
  51. ### 数据源类别
  52. type: com.alibaba.druid.pool.DruidDataSource
  53. driverClassName: com.mysql.cj.jdbc.Driver
  54. url: jdbc:mysql:</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">192.168.8.134:3306/test2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8</span>
  55. <span style="color: rgba(0, 0, 0, 1)"> username: root
  56. password: root
  57. maxPoolSize: </span><span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)">
  58. ### 主库2从库2 ,slave数据库
  59. ds</span>-master-<span style="color: rgba(128, 0, 128, 1)">1</span>-slave-<span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">:
  60. ### 数据源类别
  61. type: com.alibaba.druid.pool.DruidDataSource
  62. driverClassName: com.mysql.cj.jdbc.Driver
  63. url: jdbc:mysql:</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">192.168.8.176:3306/test2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8</span>
  64. <span style="color: rgba(0, 0, 0, 1)"> username: root
  65. password: root
  66. maxPoolSize: </span><span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)">
  67. props:
  68. sql:
  69. show: </span><span style="color: rgba(0, 0, 255, 1)">true</span><span style="color: rgba(0, 0, 0, 1)">
  70. config:
  71. masterslave: # 配置读写分离
  72. # 配置从库选择策略,提供轮询与随机,这里选择用轮询</span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">random 随机 </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">round-robin 轮询</span>
  73. load-balance-algorithm-type: round-<span style="color: rgba(0, 0, 0, 1)">robin
  74. name: datasource
  75. sharding:
  76. master</span>-slave-<span style="color: rgba(0, 0, 0, 1)">rules:
  77. ds_0:
  78. ###配置的是主库的数据库名,本案例为ds</span>-master-<span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">,其中ds_0为分区名。
  79. master</span>-data-source-name: ds-master-<span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">
  80. ###配置的是从库的数据库名,本案例为ds</span>-master-<span style="color: rgba(128, 0, 128, 1)">0</span>-slave-<span style="color: rgba(128, 0, 128, 1)">0</span>,ds-master-<span style="color: rgba(128, 0, 128, 1)">0</span>-slave-<span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">
  81. slave</span>-data-source-names: ds-master-<span style="color: rgba(128, 0, 128, 1)">0</span>-slave-<span style="color: rgba(128, 0, 128, 1)">0</span>,ds-master-<span style="color: rgba(128, 0, 128, 1)">0</span>-slave-<span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">
  82. ds_1:
  83. ###配置的是主库的数据库名,本案例为ds</span>-master-<span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">,其中ds_1为分区名。
  84. master</span>-data-source-name: ds-master-<span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">
  85. ###配置的是从库的数据库名,本案例为ds</span>-master-<span style="color: rgba(128, 0, 128, 1)">1</span>-slave-<span style="color: rgba(128, 0, 128, 1)">0</span>,ds-master-<span style="color: rgba(128, 0, 128, 1)">1</span>-slave-<span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">
  86. slave</span>-data-source-names: ds-master-<span style="color: rgba(128, 0, 128, 1)">1</span>-slave-<span style="color: rgba(128, 0, 128, 1)">0</span>,ds-master-<span style="color: rgba(128, 0, 128, 1)">1</span>-slave-<span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">
  87. tables:
  88. ###需要分表的表名
  89. t_user:
  90. ###配置的分表信息,真实的数据库信息。ds_0.t_user_$</span>->{<span style="color: rgba(128, 0, 128, 1)">0</span>…<span style="color: rgba(128, 0, 128, 1)">3</span><span style="color: rgba(0, 0, 0, 1)">},表示读取ds_0数据源的user_0、user_1、user_2、user_3。
  91. actual</span>-data-nodes: ds_$->{<span style="color: rgba(128, 0, 128, 1)">0</span>..<span style="color: rgba(128, 0, 128, 1)">1</span>}.t_user_$->{<span style="color: rgba(128, 0, 128, 1)">0</span>..<span style="color: rgba(128, 0, 128, 1)">3</span><span style="color: rgba(0, 0, 0, 1)">}
  92. database</span>-<span style="color: rgba(0, 0, 0, 1)">strategy:
  93. standard:
  94. ###是配置数据分库的策略的类,这里是自定义的类MyDBPreciseShardingAlgorithm
  95. precise</span>-algorithm-<span style="color: rgba(0, 0, 255, 1)">class</span>-<span style="color: rgba(0, 0, 0, 1)">name: com.demo.shardingjdbc.MyDBPreciseShardingAlgorithm
  96. ###配置的数据分表的字段,是根据id来分的
  97. sharding</span>-<span style="color: rgba(0, 0, 0, 1)">column: id
  98. table</span>-<span style="color: rgba(0, 0, 0, 1)">strategy:
  99. standard:
  100. ###是配置数据分表的策略的类,这里是自定义的类MyTablePreciseShardingAlgorithm
  101. precise</span>-algorithm-<span style="color: rgba(0, 0, 255, 1)">class</span>-<span style="color: rgba(0, 0, 0, 1)">name: com.demo.shardingjdbc.MyTablePreciseShardingAlgorithm
  102. ###配置的数据分表的字段,是根据id来分的
  103. sharding</span>-column: id

3、配置分库分表分片规则(结合application.yml)

       分库规则(结合pplication.yml中database-strategy)

  1. <span style="color: rgba(0, 0, 0, 1)">package com.demo.shardingjdbc;
  2. import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
  3. import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
  4. import java.util.Collection;
  5. </span><span style="color: rgba(0, 128, 0, 1)">/*</span><span style="color: rgba(0, 128, 0, 1)">*
  6. * 自定义分片算法
  7. *
  8. * @author hzy
  9. *
  10. </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
  11. <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">class</span> MyDBPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer><span style="color: rgba(0, 0, 0, 1)"> {
  12. @Override
  13. </span><span style="color: rgba(0, 0, 255, 1)">public</span> String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer><span style="color: rgba(0, 0, 0, 1)"> shardingValue) {
  14. </span><span style="color: rgba(0, 0, 255, 1)">for</span><span style="color: rgba(0, 0, 0, 1)"> (String tableName : availableTargetNames) {
  15. </span><span style="color: rgba(0, 0, 255, 1)">if</span> (tableName.endsWith(shardingValue.getValue() % <span style="color: rgba(128, 0, 128, 1)">2</span> + <span style="color: rgba(128, 0, 0, 1)">""</span><span style="color: rgba(0, 0, 0, 1)">)) {
  16. </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> tableName;
  17. }
  18. }
  19. </span><span style="color: rgba(0, 0, 255, 1)">throw</span> <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> IllegalArgumentException();
  20. }
  21. }</span>

     分表规则(结合pplication.yml中table-strategy)

  

  1. <span style="color: rgba(0, 0, 0, 1)">package com.demo.shardingjdbc;
  2. import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
  3. import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
  4. import java.util.Collection;
  5. </span><span style="color: rgba(0, 128, 0, 1)">/*</span><span style="color: rgba(0, 128, 0, 1)">*
  6. * 自定义分片算法
  7. *
  8. * @author hzy
  9. *
  10. </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
  11. <span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">class</span> MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer><span style="color: rgba(0, 0, 0, 1)"> {
  12. @Override
  13. </span><span style="color: rgba(0, 0, 255, 1)">public</span> String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer><span style="color: rgba(0, 0, 0, 1)"> shardingValue) {
  14. </span><span style="color: rgba(0, 0, 255, 1)">for</span><span style="color: rgba(0, 0, 0, 1)"> (String tableName : availableTargetNames) {
  15. </span><span style="color: rgba(0, 0, 255, 1)">if</span> (tableName.endsWith(shardingValue.getValue() % <span style="color: rgba(128, 0, 128, 1)">4</span> + <span style="color: rgba(128, 0, 0, 1)">""</span><span style="color: rgba(0, 0, 0, 1)">)) {
  16. </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> tableName;
  17. }
  18. }
  19. </span><span style="color: rgba(0, 0, 255, 1)">throw</span> <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> IllegalArgumentException();
  20. }
  21. }</span>

4、mybatis操作数据库配置

User.java

  1. <span style="color: rgba(0, 0, 0, 1)">package com.demo.shardingjdbc.entity;
  2. import java.io.Serializable;
  3. import lombok.Data;
  4. @Data
  5. </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)"> User implements Serializable {
  6. </span><span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span> final <span style="color: rgba(0, 0, 255, 1)">long</span> serialVersionUID = -<span style="color: rgba(128, 0, 128, 1)">1205226416664488559L</span><span style="color: rgba(0, 0, 0, 1)">;
  7. </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> Integer id;
  8. </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String name;
  9. </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String sex;
  10. }</span>

mapper层

  1. <span style="color: rgba(0, 0, 0, 1)">package com.demo.shardingjdbc.mapper;
  2. import org.apache.ibatis.annotations.Mapper;
  3. import com.demo.shardingjdbc.entity.User;
  4. import java.util.List;
  5. @Mapper
  6. </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">interface</span><span style="color: rgba(0, 0, 0, 1)"> UserMapper {
  7. Integer addUser(User user);
  8. List</span><User><span style="color: rgba(0, 0, 0, 1)"> list();
  9. }</span>

mybatis配置文件mybatis-config.xml

  1. <?xml version=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">1.0</span><span style="color: rgba(128, 0, 0, 1)">"</span> encoding=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">UTF-8</span><span style="color: rgba(128, 0, 0, 1)">"</span> ?>
  2. <!<span style="color: rgba(0, 0, 0, 1)">DOCTYPE configuration
  3. PUBLIC </span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">-//mybatis.org//DTD Config 3.0//EN</span><span style="color: rgba(128, 0, 0, 1)">"</span>
  4. <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">http://mybatis.org/dtd/mybatis-3-config.dtd</span><span style="color: rgba(128, 0, 0, 1)">"</span>>
  5. <configuration>
  6. <typeAliases>
  7. <package name=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">com.demo.shardingjdbc.entity</span><span style="color: rgba(128, 0, 0, 1)">"</span>/>
  8. </typeAliases>
  9. <mappers>
  10. <mapper resource=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">META-INF/mappers/User.xml</span><span style="color: rgba(128, 0, 0, 1)">"</span>/>
  11. </mappers>
  12. </configuration>

user.xml

  1. <?xml version=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">1.0</span><span style="color: rgba(128, 0, 0, 1)">"</span> encoding=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">UTF-8</span><span style="color: rgba(128, 0, 0, 1)">"</span> ?>
  2. <!DOCTYPE mapper PUBLIC <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">-//mybatis.org//DTD Mapper 3.0//EN</span><span style="color: rgba(128, 0, 0, 1)">"</span> <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">http://mybatis.org/dtd/mybatis-3-mapper.dtd</span><span style="color: rgba(128, 0, 0, 1)">"</span>>
  3. <mapper <span style="color: rgba(0, 0, 255, 1)">namespace</span>=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">com.demo.shardingjdbc.mapper.UserMapper</span><span style="color: rgba(128, 0, 0, 1)">"</span>>
  4. <resultMap id=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">baseResultMap</span><span style="color: rgba(128, 0, 0, 1)">"</span> type=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">com.demo.shardingjdbc.entity.User</span><span style="color: rgba(128, 0, 0, 1)">"</span>>
  5. <result column=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">id</span><span style="color: rgba(128, 0, 0, 1)">"</span> property=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">id</span><span style="color: rgba(128, 0, 0, 1)">"</span> jdbcType=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">INTEGER</span><span style="color: rgba(128, 0, 0, 1)">"</span> />
  6. <result column=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">name</span><span style="color: rgba(128, 0, 0, 1)">"</span> property=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">name</span><span style="color: rgba(128, 0, 0, 1)">"</span> jdbcType=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">VARCHAR</span><span style="color: rgba(128, 0, 0, 1)">"</span> />
  7. <result column=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">sex</span><span style="color: rgba(128, 0, 0, 1)">"</span> property=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">sex</span><span style="color: rgba(128, 0, 0, 1)">"</span> jdbcType=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">VARCHAR</span><span style="color: rgba(128, 0, 0, 1)">"</span> />
  8. </resultMap>
  9. <insert id=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">addUser</span><span style="color: rgba(128, 0, 0, 1)">"</span> parameterType=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">com.demo.shardingjdbc.entity.User</span><span style="color: rgba(128, 0, 0, 1)">"</span>><span style="color: rgba(0, 0, 0, 1)">
  10. INSERT INTO t_user (
  11. id, name, sex
  12. )
  13. VALUES (
  14. #{id,jdbcType</span>=<span style="color: rgba(0, 0, 0, 1)">INTEGER},
  15. #{name,jdbcType</span>=<span style="color: rgba(0, 0, 0, 1)">VARCHAR},
  16. #{sex,jdbcType</span>=<span style="color: rgba(0, 0, 0, 1)">VARCHAR}
  17. )
  18. </span></insert>
  19. <<span style="color: rgba(0, 0, 255, 1)">select</span> id=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">list</span><span style="color: rgba(128, 0, 0, 1)">"</span> resultMap=<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">baseResultMap</span><span style="color: rgba(128, 0, 0, 1)">"</span>><span style="color: rgba(0, 0, 0, 1)">
  20. SELECT u.</span>*<span style="color: rgba(0, 0, 0, 1)"> FROM t_user u order by u.id
  21. </span></<span style="color: rgba(0, 0, 255, 1)">select</span>>
  22. </mapper>

5、service层

  1. <span style="color: rgba(0, 0, 0, 1)">package com.demo.shardingjdbc.service.impl;
  2. import com.demo.shardingjdbc.entity.User;
  3. import com.demo.shardingjdbc.mapper.UserMapper;
  4. import com.demo.shardingjdbc.service.UserService;
  5. import org.springframework.beans.factory.annotation.Autowired;
  6. import org.springframework.stereotype.Service;
  7. import java.util.List;
  8. @Service
  9. </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)"> UserServiceImpl implements UserService {
  10. @Autowired
  11. </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> UserMapper userMapper;
  12. @Override
  13. </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> Integer addUser(User user) {
  14. </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 强制路由主库</span>
  15. <span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> userMapper.addUser(user);
  16. }
  17. @Override
  18. </span><span style="color: rgba(0, 0, 255, 1)">public</span> List<User><span style="color: rgba(0, 0, 0, 1)"> list() {
  19. </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> userMapper.list();
  20. }
  21. }</span>

6、controller层

  1. <span style="color: rgba(0, 0, 0, 1)">package com.demo.shardingjdbc.controller;
  2. import org.springframework.beans.factory.annotation.Autowired;
  3. import org.springframework.web.bind.annotation.GetMapping;
  4. import org.springframework.web.bind.annotation.RestController;
  5. import com.demo.shardingjdbc.entity.User;
  6. import com.demo.shardingjdbc.service.UserService;
  7. import lombok.</span><span style="color: rgba(0, 0, 255, 1)">extern</span><span style="color: rgba(0, 0, 0, 1)">.slf4j.Slf4j;
  8. @RestController
  9. @Slf4j
  10. </span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)"> UserController {
  11. @Autowired
  12. </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> UserService userService;
  13. @GetMapping(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">/users</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">)
  14. </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> Object list() {
  15. </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> userService.list();
  16. }
  17. @GetMapping(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">/add</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">)
  18. </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> Object add() {
  19. </span><span style="color: rgba(0, 0, 255, 1)">int</span> num=<span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">;
  20. </span><span style="color: rgba(0, 0, 255, 1)">for</span>(<span style="color: rgba(0, 0, 255, 1)">int</span> i=<span style="color: rgba(128, 0, 128, 1)">1</span>;i<=<span style="color: rgba(128, 0, 128, 1)">300</span>;i++<span style="color: rgba(0, 0, 0, 1)">) {
  21. User user </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> User();
  22. user.setId(i);
  23. user.setName(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">hzy</span><span style="color: rgba(128, 0, 0, 1)">"</span>+<span style="color: rgba(0, 0, 0, 1)">(i));
  24. String sex</span>=(i%<span style="color: rgba(128, 0, 128, 1)">2</span>==<span style="color: rgba(128, 0, 128, 1)">0</span>)? <span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">男</span><span style="color: rgba(128, 0, 0, 1)">"</span>:<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">女</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(0, 0, 0, 1)">;
  25. user.setSex(sex);
  26. </span><span style="color: rgba(0, 0, 255, 1)">int</span> resutl=<span style="color: rgba(0, 0, 0, 1)"> userService.addUser(user);
  27. log.info(</span><span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)">insert:</span><span style="color: rgba(128, 0, 0, 1)">"</span>+user.toString()+<span style="color: rgba(128, 0, 0, 1)">"</span><span style="color: rgba(128, 0, 0, 1)"> result:</span><span style="color: rgba(128, 0, 0, 1)">"</span>+<span style="color: rgba(0, 0, 0, 1)">resutl);
  28. num</span>=num+<span style="color: rgba(0, 0, 0, 1)">resutl;
  29. }
  30. </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> num;
  31. }
  32. }</span>

 完成。在浏览器上执行localhost:8080/add,然后去数据库中查询,可以看到test1.t_user_0、test1.t_user_2、test2.t_user_1、test2.t_user_3分别插入了数据。

   然后访问localhost:8080/users,可以查询数据库中四个表中的所有数据。可见Sharding-JDBC在插入数据的时候,根据数据分库分表策略,将数据存储在不同库不同表中,查询时将数据库从多个表中查询并聚合。

Spring Boot整合Sharding-JDBC实现分库分表+读写分离io.shardingsphere(4)

标签:uid   efi   random   rop   efault   author   rod   epo   work   

人气教程排行