1, Foreword
Xiaobian has been studying about database and table splitting recently. A few days ago, docker installed mycat to implement database and table splitting, but they all said that mycat has many bug s. Many people still prefer shardingsphere. In fact, it is a family bucket, which consists of JDBC, Proxy and Sidecar. Today, Xiaobian will simply integrate it with the simplest JDBC!
Now the latest version is 5.1.1. After a day of research, all problems have been solved and a single database and table have been completed!!
To learn about version 4.0.0, please take a look at what Xiaobian just wrote: Spring Boot+mybatis plus integrates Sharding-JDBC4.0.0 to realize single database and separate tables
If you want to see MYCAT, you can take a look at the article written by Xiaobian before. Ha: Docker installs MYCAT and Mysql for the practice of horizontal database and table classification
2, Trampled pit
1. data source problem
Do not use the Druid spring boot starter dependency. There will be problems during startup
<dependency>--> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.21</version> /dependency>
Error message:
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'userMapper' defined in file [D:\jiawayun\demo\target\classes\com\example\demo\mapper\UserMapper.class]: Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Property 'sqlSessionFactory' or 'sqlSessionTemplate' are required
Solution:
Use a separate druid
<dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.8</version> </dependency>
It is recommended to use the default data source. Sharding JDBC is also the default data source used. It comes with Xiaobian. If you forget the druid, will there be a problem!!
type: com.zaxxer.hikari.HikariDataSource
2. the insert statement does not support table splitting routing to multiple data nodes
Error message:
Insert statement does not support sharding table routing to multiple data nodes.
Solution:
Look at the small article: solve the problem that table splitting routing is not supported
3, Import maven dependencies
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> <exclusions> <exclusion> <groupId>org.junit.vintage</groupId> <artifactId>junit-vintage-engine</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId> <version>5.1.1</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!-- lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.10</version> </dependency> <!--jdbc--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <!-- mysql --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <!-- mybatis-plus --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.1</version> </dependency>
4, New table
1. create two tables
Named: user_0,user_ one
CREATE TABLE `user_0` ( `cid` bigint(25) NOT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `gender` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `data` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`cid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1;
2. database structure
5, Frame global display
1. User entity class
@Data
public class User implements Serializable {
private static final long serialVersionUID = 337361630075002456L;
private Long cid;
private String name;
private String gender;
private String data;
}
2. controller
@RestController
@RequestMapping("/test")
public class UserController {
@Autowired
private UserMapper userMapper;
@GetMapping("/insertTest")
public void insertTest(){
for (int i = 1 ; i < 10; i++) {
User test = new User("king"+i,"male","data" + i);
userMapper.insert(test);
}
}
}
3. mapper
We have omitted the service directly. Let's make it simple!!
public interface UserMapper extends BaseMapper<User> {
}
4. application.yml configuration
server: port: 8089 spring: shardingsphere: mode: type: memory # Open or not datasource: # Data source (logical name) names: m1 # Configure data source m1: type: com.zaxxer.hikari.HikariDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://localhost:3306/test?useSSL=false&autoReconnect=true&characterEncoding=UTF-8&serverTimezone=UTC username: root password: root # Slice configuration rules: sharding: # Table fragmentation strategy tables: # Name of logical table user: # Data node configuration using Groovy expression actual-data-nodes: m1.user_$->{0..1} # Configure policy table-strategy: # Standard sharding scenarios for single sharding keys standard: sharding-column: cid # Partition algorithm name sharding-algorithm-name: user_inline key-generate-strategy: # Primary key generation policy column: cid # Primary key column key-generator-name: snowflake # Policy algorithm name (snowflake algorithm is recommended) key-generators: snowflake: type: SNOWFLAKE sharding-algorithms: user_inline: type: inline props: algorithm-expression: user_$->{cid % 2} props: # The log shows the specific SQL sql-show: true logging: level: com.wang.test.demo: DEBUG mybatis-plus: mapper-locations: classpath:mapper/*.xml type-aliases-package: com.example.demo.entity configuration: #When mapping entities or attributes, remove the underscores in the table names and field names in the database, and map the address according to the hump naming method_ book ---> addressBook map-underscore-to-camel-case: true
5. startup class
@MapperScan("com.example.demo.mapper")
@SpringBootApplication
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
}
6, Test insert nine pieces of data
This test strategy is: line expression fragmentation strategy: inline
1. insert data
Input: localhost:8089/test/insertTest
Partition succeeded
2. single query
@GetMapping("/selectOneTest")
public void selectOneTest(){
User user = userMapper.selectOne(Wrappers.<User>lambdaQuery().eq(User::getCid,736989417020850176L));
System.out.println(user);
}
At this time, it will automatically obtain data from that table according to cid
3. full query
@GetMapping("/selectListTest")
public void selectListTest(){
List<User> list = userMapper.selectList(null);
System.out.println(list);
}
Since there are no conditions, he will summarize the two tables UNION ALL
4. paging query
You need to configure the mybatis plus paging configuration class first:
@Configuration
public class MybatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
@GetMapping("/selectListPage")
public void selectListPage(){
IPage<User> page = new Page(1,6);
IPage<User> userIPage = userMapper.selectPage(page,null);
List<User> records = userIPage.getRecords();
System.out.println(records);
}
We user_0 has 5 pieces of data, user_1 there are 4 pieces of data
We found that it will perform a paging query on all tables. If the data of the first table is insufficient, the value obtained from the paging of the other table will be added
When the page size is 3, a user_0 can satisfy the paging condition, and the user will be ignored_ 1.
5. non partition attribute query
Let's put the user_ Change two gender of table 0 to female, and then query! See if the fields without fragmentation can only go to user_0 to query
@GetMapping("/selectListByGender")
public void selectListByGender(){
List<User> list = userMapper.selectList(Wrappers.<User>lambdaQuery().eq(User::getGender, "female"));
System.out.println(list);
}
It can be seen from the figure that the query is not a fragmented field query. Go back to the full connection table to query again. The efficiency is the same as that of the non fragmented table!!
6. fragment attribute comes from a table in query
@GetMapping("/selectInList")
public void selectList(){
List<User> users = userMapper.selectList(Wrappers.<User>lambdaQuery().in(User::getCid,736989417020850176L,736989418119757824L));
System.out.println(users);
}
We can find that when we perform an in query based on the fragment field, the sharding JDBC will identify the table from which it comes and improve efficiency. All tables will not be fully connected.
7, Summary
In this way, the simple test of the latest version of sharding JDBC and the solution of some pits have been completed. In general, the configuration is very laborious and there can be no errors!
See here, don't give Xiaobian a key to walk three times, thank you!!