We also analyzed the integration of shardingsphere component with spring boot to realize database and table division. Sub database and sub table are mainly used to solve IO problems and slow query caused by massive data.
ShardingSphere is an ecosystem composed of a set of open-source distributed database middleware solutions. It is composed of three products that are independent of each other but can be deployed and used together. They all provide standardized data fragmentation, distributed transaction and database governance functions.
1. Shardingsphere JDBC: positioned as a lightweight Java framework, it provides additional services in the JDBC layer of Java. It uses the client to connect directly to the database and provides services in the form of jar package without additional deployment and dependence. It can be understood as an enhanced version of JDBC Driver and is fully compatible with JDBC and various ORM frameworks.
It is applicable to any Java based ORM framework, such as JPA, Hibernate, Mybatis, Spring JDBC Template or directly using JDBC. Database connection pool based on any third party, such as DBCP, C3P0, BoneCP, Druid, HikariCP, etc. Support any database that implements JDBC specification. At present, MySQL, Oracle, SQLServer and PostgreSQL are supported.
2. Shardingsphere proxy: positioned as a transparent database proxy, it provides a server version encapsulating the database binary protocol to support heterogeneous languages. At present, MySQL/PostgreSQL is available. It can use any client compatible with MySQL/PostgreSQL protocol to access and operate data, which is more friendly to DBA.
3. Shardingsphere Sidecar (under planning): it is positioned as the cloud native database agent of Kubernetes, which acts as a Sidecar for all access to the database. The meshing layer that interacts with the database is provided through the scheme of no center and zero intrusion, that is, Database Mesh, also known as data grid.
Examples
- pom dependency
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.21</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/sharding-jdbc-spring-boot-starter -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<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>
<resources>
<!-- compile src/main/java Under directory mapper file -->
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
The springboot version is 2.6.2. The problem of unable to find the data source caused by the version is not found.
- application.properties configuration file
server.port=9001
#spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#spring.datasource.url=jdbc:mysql://localhost:3306/ds0
#spring.datasource.username=root
#spring.datasource.password=
spring.application.name=sharding_sphere
spring.shardingsphere.datasource.names=ds0,ds1
# data source
spring.shardingsphere.datasource.ds0.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.url=jdbc:mysql://localhost:3306/ds0?characterEncoding=utf-8
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=
spring.shardingsphere.datasource.ds1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.url=jdbc:mysql://localhost:3306/ds1?characterEncoding=utf-8
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=
# If a sub table is selected, the default sub database data source policy is selected
#spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
#spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 2}
# Regardless of tables (application.properties is not separately configured for tables), the default data source policy is
spring.shardingsphere.sharding.default-data-source-name=ds1
# Split table configuration of user table
spring.shardingsphere.sharding.tables.user.actual-data-nodes=ds0.user_$->{[0,2]},ds1.user_$->{[1,3]}
# user library policy (you can also use the default)
spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.database-strategy.inline.algorithm-expression=ds$->{id % 2}
# user table policy
spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{id % 4}
#spring.shardingsphere.sharding.tables.user.key-generator.column=id
#spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE
Note the type and driver class name of the data source. You need to find the class used
- mapper
@Mapper
public interface UserMapper {
Long addUser(User user);
List<User> queryAllUser();
User queryUserById(Long id);
}
<?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.ldc.springboot_shardingsphere.mapper.UserMapper">
<resultMap id="userResultMap" type="com.ldc.springboot_shardingsphere.model.User">
<result column="id" property="id"/>
<result column="user_name" property="userName"/>
<result column="age" property="age"/>
<result column="address" property="address"/>
</resultMap>
<!-- Insert user -->
<insert id="addUser" useGeneratedKeys="true" keyProperty="id">
insert into user (id,user_name,age,address )
values (
#{id},
#{userName},
#{age},
#{address}
)
</insert>
<!-- Query all users -->
<select id="queryAllUser" resultMap="userResultMap">
SELECT * FROM user
</select>
<select id="queryUserById" resultMap="userResultMap">
SELECT * FROM user WHERE id=#{id}
</select>
</mapper>
- test
/**
* Insert several records
*/
@Test
public void addUser() {
for (long i = 1; i < 11; i++) {
User user = User.builder().id(i).userName("caocao").age(29).address("Hangzhou").build();
userMapper.addUser(user);
System.out.println("Successfully inserted user, uid=" + user.getId());
}
}
/**
* Query all records
*/
@Test
public void queryAllUser() {
List<User> users = userMapper.queryAllUser();
users.sort((t1, t2) -> {
if (t1.getId() < t2.getId()) {
return -1;
} else if (t1.getId() > t2.getId()) {
return 1;
} else {
return 0;
}
});
System.out.println(users);
}
Pay attention to the use of druid data source and remove the automatic configuration of DruidDataSourceAutoConfigure.