springboot2集成shardingJdbc实现对水平分表的新增和查询_shardingjdbc dosharding 在新增和查询时都会用到么-优快云博客

本文链接：https://blog.youkuaiyun.com/zhutao_java/article/details/107260071

一、环境信息

springboot 2.2.1.RELEASE

sharding jdbc 4.1.1

mysql 5.7

jdk 1.8

java工程的截图

二、数据库表

新建一个数据库，库名我这里用seal,然后在这个数据库中新建两张表user_1,user_2，两张表的结构一样，建表脚本如下：

CREATE TABLE user_1 (
  id bigint(20) NOT NULL,
  name varchar(100) NOT NULL,
  age int(11) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE user_2 (
  id bigint(20) NOT NULL,
  name varchar(100) NOT NULL,
  age int(11) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

三、springboot工程

新建好一个maven工程，pom中的内容如下：

<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.zhut.sharding</groupId>
  <artifactId>sharding</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  
  <properties>
  	<shardingsphere.version>4.1.1</shardingsphere.version>
  </properties>
  
  <dependencyManagement>
		<dependencies>
			<dependency>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-dependencies</artifactId>
				<version>2.2.1.RELEASE</version>
				<type>pom</type>
				<scope>import</scope>
			</dependency>
		</dependencies>
	</dependencyManagement>
	
	<dependencies>
		<dependency>
	    	<groupId>org.apache.shardingsphere</groupId>
    		<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
	    	<version>${shardingsphere.version}</version>
		</dependency>
		
		<dependency>
			<groupId>mysql</groupId>
        	<artifactId>mysql-connector-java</artifactId>
		</dependency>
		
		<dependency>
		    <groupId>org.mybatis.spring.boot</groupId>
		    <artifactId>mybatis-spring-boot-starter</artifactId>
		    <version>2.1.3</version>
		</dependency>
		
		<dependency>
		    <groupId>org.springframework.boot</groupId>
		    <artifactId>spring-boot-starter-test</artifactId>
		    <scope>test</scope>
		</dependency>
		
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.1.10</version>
		</dependency>
	</dependencies>
</project>

三、springboot的启动类

package com.zhut.sharding;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;

@SpringBootApplication(scanBasePackages="com.zhut",exclude= {DruidDataSourceAutoConfigure.class})
@EnableAsync
@EnableTransactionManagement
@MapperScan("com.zhut.**.dao")
public class ShardingApplication {

	public static void main(String[] args) throws Exception {
		SpringApplication.run(ShardingApplication.class, args);
	}

}

四、user的dao

user_1、user_2的逻辑表对应的实体类User

package com.zhut.sharding.entity;

public class User {

	private Long id;
	private String name;
	private Integer age;

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}
}

dao接口

package com.zhut.sharding.dao;

import com.zhut.sharding.entity.User;

public interface IUserDao {

	void insert(User user);
	
	User findByPK(Long id);
}

dao接口对应的映射文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://www.mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.zhut.sharding.dao.IUserDao">
    <resultMap type="com.zhut.sharding.entity.User" id="baseResultMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
    </resultMap>

    <sql id="baseWhere">
        <where>
            <if test='id!=null'>
                and id=#{id}
            </if>
            <if test='name!=null and name!=""'>
                and name=#{name}
            </if>
            
            <if test='age!=null and age!=""'>
                and age=#{age}
            </if>
        </where>
    </sql>

    

    <sql id="baseColumn">
        id,
        name,
        age
    </sql>

    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        <trim prefix="insert into user (" suffix=") values" suffixOverrides=",">
            <if test='id!=null'>
                id,
            </if>
            <if test='name!=null'>
                name,
            </if>
            <if test='age!=null'>
                age,
            </if>
            
        </trim>
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test='id!=null'>
                #{id},
            </if>
            <if test='name!=null'>
                #{name},
            </if>
            <if test='age!=null'>
                #{age},
            </if>
           
        </trim>
    </insert>

    
    <select id="findByPK" resultMap="baseResultMap">
        select <include refid="baseColumn"/> from user
        where id=#{id}
    </select>

</mapper>

springboot的配置文件application.yml，在这个配置文件中配置sharding jdbc的数据源、表的数据节点、分片策略、主键的生成等

spring: 
  shardingsphere: 
    props:
      sql: 
        show: true #控制台打印sql
    datasource: 
      names: ds0
      ds0: 
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/seal?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
        username: root
        password: 123456
    sharding: 
      tables: 
        user: 
          actual-data-nodes: ds0.user_$->{1..2}
          key-generator: 
            column: id
            type: SNOWFLAKE
          table-strategy: 
            inline:
              sharding-column: id
              algorithm-expression: user_$->{id%2+1}  
mybatis:
  config-location: classpath:mybatis-config.xml
  mapper-locations:
  - classpath*:com/zhut/**/sqlmap/*.xml

五、springboot的测试用例

package com.zhut.test;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.zhut.sharding.ShardingApplication;
import com.zhut.sharding.dao.IUserDao;
import com.zhut.sharding.entity.User;


@RunWith(SpringRunner.class)
@SpringBootTest(classes= {ShardingApplication.class})
public class UserTest {

	@Autowired
	private IUserDao userDao;
	
	@Test
	public void testInsert() {
		for(int i=0;i<20;i++) {
			User user = new User();
			user.setName("张三");
			user.setAge(10);
			userDao.insert(user);
		}
	}
	
	@Test
	public void testFindByPK() {
		User user = userDao.findByPK(488415598989017089L);
		System.out.println(user.getId()+"--"+user.getName()+"--"+user.getAge());
	}
}

通过运行这个测试用例来验证水平分表入库和查询