时间:2021-07-01 10:21:17 帮助过:17人阅读
1、pom.xml配置引入maven依赖
- <dependency>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-web</artifactId>
- </dependency>
- <!--springboot整合mybatis的依赖 -->
- <!-- 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>
- <dependency>
- <groupId>org.mybatis.spring.boot</groupId>
- <artifactId>mybatis-spring-boot-starter</artifactId>
- <version><span style="color: rgba(128, 0, 128, 1)">2.1</span>.<span style="color: rgba(128, 0, 128, 1)">4</span></version>
- </dependency>
- <!-- 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>
- <dependency>
- <groupId>mysql</groupId>
- <artifactId>mysql-connector-java</artifactId>
- </dependency>
- <!-- 这里用的是sharding-jdbc-spring-boot-starter 需要注意的是,此时druid不能用spring-boot-starter版本的,需要用正常的包: -->
- <dependency>
- <groupId>com.alibaba</groupId>
- <artifactId>druid-spring-boot-starter</artifactId>
- <version><span style="color: rgba(128, 0, 128, 1)">1.1</span>.<span style="color: rgba(128, 0, 128, 1)">10</span></version>
- </dependency>
- <dependency>
- <groupId>io.shardingsphere</groupId>
- <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
- <version><span style="color: rgba(128, 0, 128, 1)">3.1</span>.<span style="color: rgba(128, 0, 128, 1)">0</span>.M1</version>
- </dependency>
- <dependency>
- <groupId>org.springframework.boot</groupId>
- <artifactId>spring-boot-starter-test</artifactId>
- <scope>test</scope>
- </dependency>
- <!-- 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>
- <dependency>
- <groupId>org.projectlombok</groupId>
- <artifactId>lombok</artifactId>
- <scope>provided</scope>
- </dependency>
2、在application.yml中配置引用数据源及分库分表信息
- mybatis.config-location: classpath:META-INF/mybatis-<span style="color: rgba(0, 0, 0, 1)">config.xml
- spring:
- profiles:
- active: sharding</span>-tbl-<span style="color: rgba(0, 0, 0, 1)">ms
- main:
- allow</span>-bean-definition-overriding: <span style="color: rgba(0, 0, 255, 1)">true</span><span style="color: rgba(0, 0, 0, 1)">
- sharding:
- jdbc:
- ### 数据库
- dataSource:
- ### 数据库的别名
- 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)">
- # 主库1 ,master数据库
- ds</span>-master-<span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">:
- ### 数据源类别
- type: com.alibaba.druid.pool.DruidDataSource
- driverClassName: com.mysql.cj.jdbc.Driver
- 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>
- <span style="color: rgba(0, 0, 0, 1)"> username: root
- password: root
- maxPoolSize: </span><span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)">
- ### 主库1从库1 ,slave数据库
- 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)">:
- ### 数据源类别
- type: com.alibaba.druid.pool.DruidDataSource
- driverClassName: com.mysql.cj.jdbc.Driver
- 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>
- <span style="color: rgba(0, 0, 0, 1)"> username: root
- password: root
- maxPoolSize: </span><span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)">
- ### 主库1从库1 ,slave数据库
- 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)">:
- ### 数据源类别
- type: com.alibaba.druid.pool.DruidDataSource
- driverClassName: com.mysql.cj.jdbc.Driver
- 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>
- <span style="color: rgba(0, 0, 0, 1)"> username: root
- password: root
- maxPoolSize: </span><span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)">
- # 主库2 ,master数据库
- ds</span>-master-<span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">:
- ### 数据源类别
- type: com.alibaba.druid.pool.DruidDataSource
- driverClassName: com.mysql.cj.jdbc.Driver
- 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>
- <span style="color: rgba(0, 0, 0, 1)"> username: root
- password: root
- maxPoolSize: </span><span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)">
- ### 主库2从库1 ,slave数据库
- 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)">:
- ### 数据源类别
- type: com.alibaba.druid.pool.DruidDataSource
- driverClassName: com.mysql.cj.jdbc.Driver
- 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>
- <span style="color: rgba(0, 0, 0, 1)"> username: root
- password: root
- maxPoolSize: </span><span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)">
- ### 主库2从库2 ,slave数据库
- 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)">:
- ### 数据源类别
- type: com.alibaba.druid.pool.DruidDataSource
- driverClassName: com.mysql.cj.jdbc.Driver
- 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>
- <span style="color: rgba(0, 0, 0, 1)"> username: root
- password: root
- maxPoolSize: </span><span style="color: rgba(128, 0, 128, 1)">20</span><span style="color: rgba(0, 0, 0, 1)">
- props:
- sql:
- show: </span><span style="color: rgba(0, 0, 255, 1)">true</span><span style="color: rgba(0, 0, 0, 1)">
- config:
- masterslave: # 配置读写分离
- # 配置从库选择策略,提供轮询与随机,这里选择用轮询</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>
- load-balance-algorithm-type: round-<span style="color: rgba(0, 0, 0, 1)">robin
- name: datasource
- sharding:
- master</span>-slave-<span style="color: rgba(0, 0, 0, 1)">rules:
- ds_0:
- ###配置的是主库的数据库名,本案例为ds</span>-master-<span style="color: rgba(128, 0, 128, 1)">0</span><span style="color: rgba(0, 0, 0, 1)">,其中ds_0为分区名。
- 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)">
- ###配置的是从库的数据库名,本案例为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)">
- 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)">
- ds_1:
- ###配置的是主库的数据库名,本案例为ds</span>-master-<span style="color: rgba(128, 0, 128, 1)">1</span><span style="color: rgba(0, 0, 0, 1)">,其中ds_1为分区名。
- 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)">
- ###配置的是从库的数据库名,本案例为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)">
- 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)">
- tables:
- ###需要分表的表名
- t_user:
- ###配置的分表信息,真实的数据库信息。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。
- 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)">}
- database</span>-<span style="color: rgba(0, 0, 0, 1)">strategy:
- standard:
- ###是配置数据分库的策略的类,这里是自定义的类MyDBPreciseShardingAlgorithm
- 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
- ###配置的数据分表的字段,是根据id来分的
- sharding</span>-<span style="color: rgba(0, 0, 0, 1)">column: id
- table</span>-<span style="color: rgba(0, 0, 0, 1)">strategy:
- standard:
- ###是配置数据分表的策略的类,这里是自定义的类MyTablePreciseShardingAlgorithm
- 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
- ###配置的数据分表的字段,是根据id来分的
- sharding</span>-column: id
3、配置分库分表分片规则(结合application.yml)
分库规则(结合pplication.yml中database-strategy)
- <span style="color: rgba(0, 0, 0, 1)">package com.demo.shardingjdbc;
- import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
- import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
- import java.util.Collection;
- </span><span style="color: rgba(0, 128, 0, 1)">/*</span><span style="color: rgba(0, 128, 0, 1)">*
- * 自定义分片算法
- *
- * @author hzy
- *
- </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
- <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)"> {
- @Override
- </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) {
- </span><span style="color: rgba(0, 0, 255, 1)">for</span><span style="color: rgba(0, 0, 0, 1)"> (String tableName : availableTargetNames) {
- </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)">)) {
- </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> tableName;
- }
- }
- </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();
- }
- }</span>
分表规则(结合pplication.yml中table-strategy)
- <span style="color: rgba(0, 0, 0, 1)">package com.demo.shardingjdbc;
- import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
- import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
- import java.util.Collection;
- </span><span style="color: rgba(0, 128, 0, 1)">/*</span><span style="color: rgba(0, 128, 0, 1)">*
- * 自定义分片算法
- *
- * @author hzy
- *
- </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
- <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)"> {
- @Override
- </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) {
- </span><span style="color: rgba(0, 0, 255, 1)">for</span><span style="color: rgba(0, 0, 0, 1)"> (String tableName : availableTargetNames) {
- </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)">)) {
- </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> tableName;
- }
- }
- </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();
- }
- }</span>
4、mybatis操作数据库配置
User.java
- <span style="color: rgba(0, 0, 0, 1)">package com.demo.shardingjdbc.entity;
- import java.io.Serializable;
- import lombok.Data;
- @Data
- </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 {
- </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)">;
- </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> Integer id;
- </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String name;
- </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String sex;
- }</span>
mapper层
- <span style="color: rgba(0, 0, 0, 1)">package com.demo.shardingjdbc.mapper;
- import org.apache.ibatis.annotations.Mapper;
- import com.demo.shardingjdbc.entity.User;
- import java.util.List;
- @Mapper
- </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 {
- Integer addUser(User user);
- List</span><User><span style="color: rgba(0, 0, 0, 1)"> list();
- }</span>
mybatis配置文件mybatis-config.xml
- <?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> ?>
- <!<span style="color: rgba(0, 0, 0, 1)">DOCTYPE configuration
- 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>
- <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>>
- <configuration>
- <typeAliases>
- <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>/>
- </typeAliases>
- <mappers>
- <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>/>
- </mappers>
- </configuration>
user.xml
- <?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> ?>
- <!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>>
- <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>>
- <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>>
- <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> />
- <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> />
- <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> />
- </resultMap>
- <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)">
- INSERT INTO t_user (
- id, name, sex
- )
- VALUES (
- #{id,jdbcType</span>=<span style="color: rgba(0, 0, 0, 1)">INTEGER},
- #{name,jdbcType</span>=<span style="color: rgba(0, 0, 0, 1)">VARCHAR},
- #{sex,jdbcType</span>=<span style="color: rgba(0, 0, 0, 1)">VARCHAR}
- )
- </span></insert>
- <<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)">
- SELECT u.</span>*<span style="color: rgba(0, 0, 0, 1)"> FROM t_user u order by u.id
- </span></<span style="color: rgba(0, 0, 255, 1)">select</span>>
- </mapper>
5、service层
- <span style="color: rgba(0, 0, 0, 1)">package com.demo.shardingjdbc.service.impl;
- import com.demo.shardingjdbc.entity.User;
- import com.demo.shardingjdbc.mapper.UserMapper;
- import com.demo.shardingjdbc.service.UserService;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Service;
- import java.util.List;
- @Service
- </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 {
- @Autowired
- </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> UserMapper userMapper;
- @Override
- </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> Integer addUser(User user) {
- </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)"> 强制路由主库</span>
- <span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> userMapper.addUser(user);
- }
- @Override
- </span><span style="color: rgba(0, 0, 255, 1)">public</span> List<User><span style="color: rgba(0, 0, 0, 1)"> list() {
- </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> userMapper.list();
- }
- }</span>
6、controller层
- <span style="color: rgba(0, 0, 0, 1)">package com.demo.shardingjdbc.controller;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.web.bind.annotation.GetMapping;
- import org.springframework.web.bind.annotation.RestController;
- import com.demo.shardingjdbc.entity.User;
- import com.demo.shardingjdbc.service.UserService;
- import lombok.</span><span style="color: rgba(0, 0, 255, 1)">extern</span><span style="color: rgba(0, 0, 0, 1)">.slf4j.Slf4j;
- @RestController
- @Slf4j
- </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 {
- @Autowired
- </span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> UserService userService;
- @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)">)
- </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> Object list() {
- </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> userService.list();
- }
- @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)">)
- </span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> Object add() {
- </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)">;
- </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)">) {
- User user </span>= <span style="color: rgba(0, 0, 255, 1)">new</span><span style="color: rgba(0, 0, 0, 1)"> User();
- user.setId(i);
- 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));
- 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)">;
- user.setSex(sex);
- </span><span style="color: rgba(0, 0, 255, 1)">int</span> resutl=<span style="color: rgba(0, 0, 0, 1)"> userService.addUser(user);
- 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);
- num</span>=num+<span style="color: rgba(0, 0, 0, 1)">resutl;
- }
- </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> num;
- }
- }</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