目录

1. 准备
2. 本文场景介绍
   1. POM文件
   2. 相关代码实现
      1. Controller代码
         1. CicodingController代码
         2. UserController代码
      2. Service代码
         1. CicodingService代码
         2. UserService代码
         3. CicodingServiceImpl代码
         4. UserServiceImpl代码
      3. Repository代码
         1. CicodingRepository代码
         2. UserRepository代码
      4. Mapper.xml代码实现
         1. CicodingMapper.xml
         2. UserMapper.xml
      5. 实体类
      6. 启动类
      7. 测试演示

上一篇介绍的了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

http://localhost:8084/cis

分别进入不同的库！

到此我们就实现了sharding-jdbc主从读写分离实现，更多配置请参考此处！

相关文章

• Spring Boot版 Sharding JDBC 垂直拆分（不同的表在不同的库中）+ 读写分离
• Sharding-JDBC不分库，只分表例子
• Sharding-JDBC读写分离案例
• Sharding-JDBC、Mycat、drds对比
• Sharding-JDBC系列教程