Sharding-JDBC 垂直拆分（不同的表在不同的库中）
上一篇介绍的了Sharding-JDBC不分库，只分表例子，接下来我们写demo，介绍SpringBoot使用Sharding-JDBC垂直拆分（不同的表在不同的库中）。话不多说，直接写代码。
准备
- SpringBoot 2.1.12
- Sharding-JDBC 4.0.0
- Mybatis 3.x
- Mysql 8.0
- lombok
本文场景介绍
一个数据库，将user表分表分为四个一样的表，根据取模算法分别向user0-3的表里插入数据。
POM文件
pom文件引入如下相关依赖：
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>cn.cicoding</groupId>
<artifactId>shardingsphere-example</artifactId>
<version>1.0-SNAPSHOT</version>
</parent>
<groupId>cn.cicoding</groupId>
<artifactId>sharding-jdbc-db-sharding</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sharding-jdbc-db-sharding</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
相关代码实现
Controller代码
CicodingController代码
package cn.cicoding.controller;
import cn.cicoding.entity.Cicoding;
import cn.cicoding.service.CicodingService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class CicodingController {
@Autowired
private CicodingService cicodingService;
@GetMapping("/cis")
public Object list() {
return cicodingService.list();
}
@GetMapping("/ci/add")
public Object add() {
for (long i = 0; i < 10; i++) {
Cicoding cicoding = new Cicoding();
cicoding.setId(i+"a");
cicoding.setCity("深圳");
cicoding.setRegion("宝安");
cicoding.setName("李四");
cicoding.setLdNum("A");
cicoding.setUnitNum("2");
cicodingService.addCicoding(cicoding);
}
return "success";
}
}
UserController代码
package cn.cicoding.controller;
import cn.cicoding.entity.User;
import cn.cicoding.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class UserController {
@Autowired
private UserService userService;
@GetMapping("/users")
public Object list() {
return userService.list();
}
@GetMapping("/add")
public Object add() {
for (long i = 0; i < 100; i++) {
User user = new User();
user.setCity("深圳");
user.setName("李四");
userService.add(user);
}
return "success";
}
@GetMapping("/users/{id}")
public Object get(@PathVariable Long id) {
return userService.findById(id);
}
@GetMapping("/users/query")
public Object get(String name) {
return userService.findByName(name);
}
}
Service代码
CicodingService代码
package cn.cicoding.service;
import java.util.List;
import cn.cicoding.entity.Cicoding;
public interface CicodingService {
List<Cicoding> list();
Long addCicoding(Cicoding cicoding);
}
UserService代码
package cn.cicoding.service;
import cn.cicoding.entity.User;
import java.util.List;
public interface UserService {
List<User> list();
Long add(User user);
User findById(Long id);
User findByName(String name);
}
CicodingServiceImpl代码
package cn.cicoding.service;
import java.util.List;
import cn.cicoding.entity.Cicoding;
import cn.cicoding.repository.CicodingRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class CicodingServiceImpl implements CicodingService {
@Autowired
private CicodingRepository cicodingRepository;
@Override
public List<Cicoding> list() {
return cicodingRepository.list();
}
@Override
public Long addCicoding(Cicoding cicoding) {
return cicodingRepository.addCicoding(cicoding);
}
}
UserServiceImpl代码
package cn.cicoding.service;
import java.util.List;
import cn.cicoding.entity.User;
import cn.cicoding.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserRepository userRepository;
public List<User> list() {
return userRepository.list();
}
public Long add(User user) {
return userRepository.addUser(user);
}
@Override
public User findById(Long id) {
return userRepository.findById(id);
}
@Override
public User findByName(String name) {
return userRepository.findByName(name);
}
}
Repository代码
CicodingRepository代码
package cn.cicoding.repository;
import java.util.List;
import cn.cicoding.entity.Cicoding;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface CicodingRepository {
Long addCicoding(Cicoding cicoding);
List<Cicoding> list();
}
UserRepository代码
package cn.cicoding.repository;
import java.util.List;
import cn.cicoding.entity.User;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface UserRepository {
Long addUser(User user);
List<User> list();
User findById(Long id);
User findByName(String name);
}
Mapper.xml代码实现
CicodingMapper.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="cn.cicoding.repository.CicodingRepository">
<resultMap id="baseResultMap" type="cn.cicoding.entity.Cicoding">
<result column="id" property="id" jdbcType="VARCHAR" />
<result column="city" property="city" jdbcType="VARCHAR" />
<result column="region" property="region" jdbcType="VARCHAR" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="ld_num" property="ldNum" jdbcType="VARCHAR" />
<result column="unit_num" property="unitNum" jdbcType="VARCHAR" />
</resultMap>
<insert id="addCicoding">
INSERT INTO cicoding (
id, city, region, name, ld_num, unit_num
)
VALUES (
#{id,jdbcType=VARCHAR},
#{city,jdbcType=VARCHAR},
#{region,jdbcType=VARCHAR},
#{name,jdbcType=VARCHAR},
#{ldNum,jdbcType=VARCHAR},
#{unitNum,jdbcType=VARCHAR}
)
</insert>
<select id="list" resultMap="baseResultMap">
SELECT ld.* FROM cicoding ld
</select>
</mapper>
UserMapper.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="cn.cicoding.repository.UserRepository">
<resultMap id="baseResultMap" type="cn.cicoding.entity.User">
<result column="id" property="id" jdbcType="INTEGER" />
<result column="city" property="city" jdbcType="VARCHAR" />
<result column="name" property="name" jdbcType="VARCHAR" />
</resultMap>
<insert id="addUser">
INSERT INTO user (
city, name
)
VALUES (
#{city,jdbcType=VARCHAR},
#{name,jdbcType=VARCHAR}
)
</insert>
<select id="list" resultMap="baseResultMap">
SELECT u.* FROM user u
</select>
<select id="findById" resultMap="baseResultMap">
SELECT u.* FROM user u WHERE u.id=#{id,jdbcType=INTEGER}
</select>
<select id="findByName" resultMap="baseResultMap">
SELECT u.* FROM user u WHERE u.name=#{name,jdbcType=VARCHAR}
</select>
</mapper>
实体类
Cicoding
package cn.cicoding.entity;
import lombok.Data;
/**
* 不分表
* @author zhaokejin
*
*/
@Data
public class Cicoding {
private String id;
private String city;
private String region;
private String name;
private String ldNum;
private String unitNum;
}
User
package cn.cicoding.entity;
import java.io.Serializable;
/**
* 分表
* @author zhaokejin
*
*/
public class User implements Serializable {
private static final long serialVersionUID = -1205226416664488559L;
private Long id;
private String city = "";
private String name = "";
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
到这我们完成了基本的代码编写，由于sharding-jdbc是jar包，我们来看主要的配置信息
#Sharding JDBC 垂直拆分（不同的表在不同的库中）
server.port=8084
# mybatis对应的映射文件路径
mybatis.mapper-locations=classpath:mapper/*.xml
# mybatis对应的实体类
mybatis.type-aliases-package=cn.cicoding.model
spring.shardingsphere.datasource.names=ds0,ds1
# 数据源
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3309/ds0?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3309/ds1?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=root
# 绑定cicoding表所在节点
spring.shardingsphere.sharding.tables.cicoding.actual-data-nodes=ds1.cicoding
# 绑定user表所在节点
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds0.user
spring.shardingsphere.sharding.tables.user.key-generator.column=id
spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
# 显示SQL
spring.shardingsphere.props.sql.show=true
启动类
package cn.cicoding;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* Sharding JDBC 垂直拆分（不同的表在不同的库中）
*/
@SpringBootApplication
public class ShardingJdbcDbShardingApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingJdbcDbShardingApplication.class, args);
}
}
测试演示
启动启动类，访问http://localhost:8084/add
分别进入不同的库！
到此我们就实现了sharding-jdbc主从读写分离实现，更多配置请参考此处！
