2, Sub database and sub table_ Middleware sharding JDBC User Guide

1, ShardingSphere introduction

Official address: https://shardingsphere.apache.org/index_zh.html

1.1 description on the official website

Positioned as a lightweight Java framework, additional services are provided in the JDBC layer of Java. It uses the client to directly connect to the database and provides services in the form of jar package without additional deployment and dependency. It can be understood as an enhanced jdbc driver and is fully compatible with JDBC and various ORM frameworks.

It is applicable to any ORM framework based on JDBC, such as JPA, Hibernate, Mybatis, Spring JDBC Template or directly using JDBC.
Support any third-party database connection pool, such as DBCP, C3P0, BoneCP, Druid, HikariCP, etc.
Support any database that implements JDBC specification. At present, it supports MySQL, Oracle, SQL server, PostgresSQL and any database that complies with the SQL92 standard.

1.2 own understanding:

For the enhanced jdbc driver, when the client uses it, just like the normal jdbc driver, it introduces the sharding JDBC dependency package, connects the database, configures the database and table rules, and separates the read and write configuration. Then, for the SQL operation of the client, sharding JDBC will automatically complete the database and table separation and read and write separation operations according to the configuration.

1.3 purpose:

Main purpose: to simplify data related operations after database and table separation and read-write separation.

2, Sharding JDBC for data segmentation

2.1 construction environment

Technology: springboot + mybatisplus + sharding JDBC + Druid connection pool
Create SpringBoot project
Introduce required dependencies

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core</artifactId>
    <version>${latest.release.version}</version>
</dependency>

Note: please change ${latest.release.version} to the actual version number.

pom.xml

<?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>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
		<version>2.2.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.xbmu</groupId>
	<artifactId>shardingjdbcdemo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>shardingjdbcdemo</name>
	<description>ShardingJDBC 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>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>1.1.20</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
			<version>4.0.0-RC1</version>
		</dependency>
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.0.5</version>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</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>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

2.2 horizontal segmentation

2.2. 1. Horizontal sub table

1, Create databases and database tables in the way of horizontal tables

Create database course_db
Create two tables course in the database_ 1 and course_ two
Convention rule: if the added course id is even, the data will be added to course_1 ； If it is an odd number, it is added to course_ two

2, Write code to realize the operation of data after database and table division

Create entity class and Mapper interface

3, Configure sharding JDBC sharding policy
Reference link: https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/spring-boot-starter/

# Sharding JDBC sharding strategy
# Configure the data source and give the data source a name
spring.shardingsphere.datasource.names=m1

# Configure the specific contents of the data source, including connection pool, driver, address, user name and password
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/course_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

# Specify the distribution of the course table, configure which database the table is in, and what the table name is M1 course_ 1 , m2. course_ two
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}

# Specify the primary key cid generation policy SNOWFLAKE (SNOWFLAKE algorithm) in the course table
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# Specify sharding policy: add even cid value to course_1 table, cid values are odd numbers added to course_ Table 2
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}

# Open sql output log
spring.shardingsphere.props.sql.show=true

4, Write test code

package com.xbmu;

import com.xbmu.entity.Course;
import com.xbmu.mapper.CourseMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest
class ShardingjdbcdemoApplicationTests {

    @Autowired
    private CourseMapper courseMapper;

    @Test
    public void addCourse(){
        for (int i = 0; i <= 10 ; i++) {
            Course course = new Course();
            course.setCname("java_"+i);
            course.setUserId(100L);
            course.setCstatus("Normal"+i);
            courseMapper.insert(course);
        }
    }
}

Execute the test code and run the error report

Solution: add the configuration according to the error prompt

# One entity class corresponds to two tables, covering
spring.main.allow-bean-definition-overriding=true

Run again

2.2. 2. Horizontal sub database

1, Create two databases

2, Configure database fragmentation rules in the SpringBoot configuration file

# Sharding JDBC sharding strategy
# Configure the data source and give the data source a name
spring.shardingsphere.datasource.names=m1,m2

# One entity class corresponds to two tables, covering
spring.main.allow-bean-definition-overriding=true

# Configure the specific contents of the first data source, including connection pool, driver, address, user name and password
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/edu_db_1?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

# Configure the specific contents of the second data source, including connection pool, driver, address, user name and password
spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/edu_db_2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root

# Specify the database distribution and the table distribution in the database
# m1 m2 course_1 course_2
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m$->{1..2}.course_$->{1..2}

# Specify the primary key cid generation policy SNOWFLAKE (SNOWFLAKE algorithm) in the course table
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# Specify data table fragmentation policy: add even cid value to course_1 table, cid values are odd numbers added to course_ Table 2
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}

# Specify database fragmentation policy: user_id is an even number plus m1 and an odd number plus m2
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression=m$->{user_id % 2 + 1}

# Open sql output log
spring.shardingsphere.props.sql.show=true

3, Run test

2.3 vertical segmentation

2.3. 1. Vertical sub database

1, Demand analysis, create database:

2, Writing entity classes and Mapper interfaces

package com.xbmu.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName("t_user") // Specify table
public class User {
    private Long userId;
    private String userName;
    private String ustatus;
}

package com.xbmu.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xbmu.entity.User;
import org.springframework.stereotype.Repository;

@Repository
public interface UserMapper extends BaseMapper<User> {
}

3, Configure vertical sub database policy:

# Sharding JDBC sharding strategy
# Configure the data source and give the data source a name
spring.shardingsphere.datasource.names=m1,m2

# One entity class corresponds to two tables, covering
spring.main.allow-bean-definition-overriding=true

# Configure the specific contents of the data source, including connection pool, driver, address, user name and password
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/course_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/user_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root

# Specify the distribution of the course table, configure which database the table is in, and what the table name is M1 course_ 1 , m2. course_ two
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}

# Specify user_ T in DB database_ Special database and table
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m$->{2}.t_user

# Specify the primary key cid generation policy SNOWFLAKE (SNOWFLAKE algorithm) in the course table
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# Specify user_ T in DB Library_ Primary key user in user table_ ID generation policy SNOWFLAKE (SNOWFLAKE algorithm)
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE

# Specify sharding policy: add even cid value to course_1 table, cid values are odd numbers added to course_ Table 2
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}

spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user

# Open sql output log
spring.shardingsphere.props.sql.show=true

4, Operation results:

2.3. 2. Vertical sub table

It is mainly used to operate public tables

Public table
A table that stores fixed data. Table data rarely changes and is often associated during query;
Create a common table with the same structure in each database;
Create common tables with the same structure in multiple databases

1, Create common tables with the same structure in multiple databases

2, In the project configuration file application Properties configuration common table

# Sharding JDBC sharding strategy
# Configure the data source and give the data source a name
spring.shardingsphere.datasource.names=m1,m2

# One entity class corresponds to two tables, covering
spring.main.allow-bean-definition-overriding=true

# Configure the specific contents of the data source, including connection pool, driver, address, user name and password
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/course_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root

spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/user_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root

# Specify the distribution of the course table, configure which database the table is in, and what the table name is M1 course_ 1 , m2. course_ two
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}

# Specify user_ T in DB database_ Special database and table
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m$->{2}.t_user

# Specify the primary key cid generation policy SNOWFLAKE (SNOWFLAKE algorithm) in the course table
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE

# Specify user_ T in DB Library_ Primary key user in user table_ ID generation policy SNOWFLAKE (SNOWFLAKE algorithm)
spring.shardingsphere.sharding.tables.t_user.key-generator.column=user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type=SNOWFLAKE

# Specify sharding policy: add even cid value to course_1 table, cid values are odd numbers added to course_ Table 2
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}

spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column=user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user

# Configure public tables
spring.shardingsphere.sharding.broadcast-tables=t_udict
spring.shardingsphere.sharding.tables.t_udict.key-generator.column=dictid
spring.shardingsphere.sharding.tables.t_udict.key-generator.type=SNOWFLAKE
# Open sql output log
spring.shardingsphere.props.sql.show=true

3, Writing entity classes and Mapper interfaces

package com.xbmu.entity;

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

@Data
@TableName(value = "t_udict")
public class Udict {
    private Long dictId;
    private String ustatus;
    private String uvalue;
}

package com.xbmu.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xbmu.entity.Udict;
import com.xbmu.entity.User;
import org.springframework.stereotype.Repository;

@Repository
public interface UdictMapper extends BaseMapper<Udict> {
}

4, Test run

Keywords: Java Database Middleware

Added by gabereiser on Thu, 23 Dec 2021 01:38:11 +0200

Programming VIP