时间：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://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.4</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<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>1.1.10</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.1.0.M1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>provided</scope>
</dependency>
2、在application.yml中配置引用数据源及分库分表信息
mybatis.config-location: classpath:META-INF/mybatis-config.xml spring: profiles: active: sharding-tbl-ms main: allow-bean-definition-overriding: true sharding: jdbc: ### 数据库 dataSource: ### 数据库的别名 names: ds-master-0,ds-master-1,ds-master-0-slave-0,ds-master-0-slave-1,ds-master-1-slave-0,ds-master-1-slave-1 # 主库1 ,master数据库 ds-master-0: ### 数据源类别 type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.cj.jdbc.Driver url: jdbc:mysql://192.168.8.162:3306/test1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8 username: root password: root maxPoolSize: 20 ### 主库1从库1 ,slave数据库 ds-master-0-slave-0: ### 数据源类别 type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.cj.jdbc.Driver url: jdbc:mysql://192.168.8.134:3306/test1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8 username: root password: root maxPoolSize: 20 ### 主库1从库1 ,slave数据库 ds-master-0-slave-1: ### 数据源类别 type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.cj.jdbc.Driver url: jdbc:mysql://192.168.8.176:3306/test1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8 username: root password: root maxPoolSize: 20 # 主库2 ,master数据库 ds-master-1: ### 数据源类别 type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.cj.jdbc.Driver url: jdbc:mysql://192.168.8.162:3306/test2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8 username: root password: root maxPoolSize: 20 ### 主库2从库1 ,slave数据库 ds-master-1-slave-0: ### 数据源类别 type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.cj.jdbc.Driver url: jdbc:mysql://192.168.8.134:3306/test2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8 username: root password: root maxPoolSize: 20 ### 主库2从库2 ,slave数据库 ds-master-1-slave-1: ### 数据源类别 type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.cj.jdbc.Driver url: jdbc:mysql://192.168.8.176:3306/test2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8 username: root password: root maxPoolSize: 20 props: sql: show: true config: masterslave: # 配置读写分离 # 配置从库选择策略，提供轮询与随机，这里选择用轮询//random 随机 //round-robin 轮询 load-balance-algorithm-type: round-robin name: datasource sharding: master-slave-rules: ds_0: ###配置的是主库的数据库名，本案例为ds-master-0，其中ds_0为分区名。 master-data-source-name: ds-master-0 ###配置的是从库的数据库名，本案例为ds-master-0-slave-0,ds-master-0-slave-1 slave-data-source-names: ds-master-0-slave-0,ds-master-0-slave-1 ds_1: ###配置的是主库的数据库名，本案例为ds-master-1，其中ds_1为分区名。 master-data-source-name: ds-master-1 ###配置的是从库的数据库名，本案例为ds-master-1-slave-0,ds-master-1-slave-1 slave-data-source-names: ds-master-1-slave-0,ds-master-1-slave-1 tables: ###需要分表的表名 t_user: ###配置的分表信息，真实的数据库信息。ds_0.t_user_$->{0…3}，表示读取ds_0数据源的user_0、user_1、user_2、user_3。 actual-data-nodes: ds_$->{0..1}.t_user_$->{0..3} database-strategy: standard: ###是配置数据分库的策略的类，这里是自定义的类MyDBPreciseShardingAlgorithm precise-algorithm-class-name: com.demo.shardingjdbc.MyDBPreciseShardingAlgorithm ###配置的数据分表的字段，是根据id来分的 sharding-column: id table-strategy: standard: ###是配置数据分表的策略的类，这里是自定义的类MyTablePreciseShardingAlgorithm precise-algorithm-class-name: com.demo.shardingjdbc.MyTablePreciseShardingAlgorithm ###配置的数据分表的字段，是根据id来分的 sharding-column: id
3、配置分库分表分片规则（结合application.yml）
分库规则（结合pplication.yml中database-strategy）
package com.demo.shardingjdbc; import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue; import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm; import java.util.Collection; /** * 自定义分片算法 * * @author hzy * */ public class MyDBPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> { @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) { for (String tableName : availableTargetNames) { if (tableName.endsWith(shardingValue.getValue() % 2 + "")) { return tableName; } } throw new IllegalArgumentException(); } }
分表规则（结合pplication.yml中table-strategy）
package com.demo.shardingjdbc; import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue; import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm; import java.util.Collection; /** * 自定义分片算法 * * @author hzy * */ public class MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> { @Override public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) { for (String tableName : availableTargetNames) { if (tableName.endsWith(shardingValue.getValue() % 4 + "")) { return tableName; } } throw new IllegalArgumentException(); } }
4、mybatis操作数据库配置
User.java
package com.demo.shardingjdbc.entity; import java.io.Serializable; import lombok.Data; @Data public class User implements Serializable { private static final long serialVersionUID = -1205226416664488559L; private Integer id; private String name; private String sex; }
mapper层
package com.demo.shardingjdbc.mapper; import org.apache.ibatis.annotations.Mapper; import com.demo.shardingjdbc.entity.User; import java.util.List; @Mapper public interface UserMapper { Integer addUser(User user); List<User> list(); }
mybatis配置文件mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <typeAliases> <package name="com.demo.shardingjdbc.entity"/> </typeAliases> <mappers> <mapper resource="META-INF/mappers/User.xml"/> </mappers> </configuration>
user.xml
<?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.demo.shardingjdbc.mapper.UserMapper"> <resultMap id="baseResultMap" type="com.demo.shardingjdbc.entity.User"> <result column="id" property="id" jdbcType="INTEGER" /> <result column="name" property="name" jdbcType="VARCHAR" /> <result column="sex" property="sex" jdbcType="VARCHAR" /> </resultMap> <insert id="addUser" parameterType="com.demo.shardingjdbc.entity.User"> INSERT INTO t_user ( id, name, sex ) VALUES ( #{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{sex,jdbcType=VARCHAR} ) </insert> <select id="list" resultMap="baseResultMap"> SELECT u.* FROM t_user u order by u.id </select> </mapper>
5、service层
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 public class UserServiceImpl implements UserService { @Autowired private UserMapper userMapper; @Override public Integer addUser(User user) { // 强制路由主库 return userMapper.addUser(user); } @Override public List<User> list() { return userMapper.list(); } }
6、controller层
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.extern.slf4j.Slf4j; @RestController @Slf4j public class UserController { @Autowired private UserService userService; @GetMapping("/users") public Object list() { return userService.list(); } @GetMapping("/add") public Object add() { int num=0; for(int i=1;i<=300;i++) { User user = new User(); user.setId(i); user.setName("hzy"+(i)); String sex=(i%2==0)? "男":"女"; user.setSex(sex); int resutl= userService.addUser(user); log.info("insert:"+user.toString()+" result:"+resutl); num=num+resutl; } return num; } }
完成。在浏览器上执行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