Introduction to the
After seeing this issue first, I read the Official Sharding JDBC website.
First of all, they built a demo, the implementation of the sub-library sub-table, and the primary and secondary table Settings.
1. Introduce dependencies
The sharding-JDBC dependency is introduced in the project pom.xml file
<dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> < version > 4.0.0 - RC1 < / version > < / dependency >Copy the code2. Import the configuration file
Create table manually; DDL -auto=none Spring.jpa. show-sql=true Spring. Jpa. Database - platform = org. Hibernate. The dialect. MySQL5InnoDBDialect # name to consistent with the db spring.shardingsphere.datasource.names=db0,db1 spring.shardingsphere.datasource.db0.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.db0.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.db0.jdbc-url=jdbc:mysql://localhost:3306/db0?useUnicode=true&characterEncoding=utf-8 spring.shardingsphere.datasource.db0.username=root spring.shardingsphere.datasource.db0.password= spring.shardingsphere.datasource.db1.type=com.zaxxer.hikari.HikariDataSource spring.shardingsphere.datasource.bd1.driver-class-name=com.mysql.jdbc.Driver spring.shardingsphere.datasource.db1.jdbc-url=jdbc:mysql://localhost:3306/db1?useUnicode=true&characterEncoding=utf-8 spring.shardingsphere.datasource.db1.username=root spring.shardingsphere.datasource.db1.password= # If a SNOWFLAKE is a primary key for the LONG spring. Shardingsphere. Sharding. Name the user. The key - the generator. The column = user_id spring.shardingsphere.sharding.tables.user.key-generator.type=SNOWFLAKE spring.shardingsphere.sharding.tables.item.key-generator.column=item_id spring.shardingsphere.sharding.tables.item.key-generator.type=SNOWFLAKE #spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=user_id #spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=db$->{user_id % 2} #db$->{0.. 1}.user_$->{0.. 1} could not satisfy the current situation spring. Shardingsphere. Sharding. Name the user. The actual data - nodes = db0. User_0, db1. The user_1 Spring. Shardingsphere. Sharding. Tables. Item. Actual data - nodes. = db0 item_0, db1. The item_1 # depots fields spring.shardingsphere.sharding.tables.user.database-strategy.inline.sharding-column=user_id Spring. Shardingsphere. Sharding. Tables. Item. The database - the strategy. The inline. Sharding - column = item_id # table fields spring.shardingsphere.sharding.tables.user.table-strategy.inline.sharding-column=user_id Spring. Shardingsphere. Sharding. Tables. Item. The table - strategy. The inline. Sharding - column = item_id # table strategy, You want to make sure that it's even or there will be some tables that will never have data spring.shardingsphere.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{user_id % 2} Spring. Shardingsphere. Sharding. Tables. Item. The table - strategy. The inline. Algorithm - expression = item_ $- > % 2} {item_id # depots strategy, To ensure the uniform or you will have some table data is never spring. Shardingsphere. Sharding. Name user. Database - strategy. The inline. Algorithm - expression = db $- > { user_id % 2} spring.shardingsphere.sharding.tables.item.database-strategy.inline.algorithm-expression=db$->{ item_id % 2} # print log spring. Shardingsphere. Props. SQL. The show = trueCopy the codeSQL > create table
DROP TABLE IF EXISTS `user_0`;
CREATE TABLE `user_0` (
`user_id` bigint DEFAULT NULL,
`name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
SET FOREIGN_KEY_CHECKS = 1;
CREATE TABLE `item_0` (
`item_id` bigint DEFAULT NULL,
`user_id` bigint DEFAULT NULL,
`item_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
SET FOREIGN_KEY_CHECKS = 1;
Copy the codeCreate tables USER_0 and ITEM_0 at db0 and user_1 and ITEM_1 at DB1
4. Write test interfaces
1. Write test classes
@Entity public class User { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long userId; private String name; public Long getUserId() { return userId; } public void setUserId(Long userId) { this.userId = userId; } public String getName() { return name; } public void setName(String name) { this.name = name; } } @Entity public class Item { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long itemId; private Long userId; private String itemName; public Long getItemId() { return itemId; } public void setItemId(Long itemId) { this.itemId = itemId; } public Long getUserId() { return userId; } public void setUserId(Long userId) { this.userId = userId; } public String getItemName() { return itemName; } public void setItemName(String itemName) { this.itemName = itemName; }}Copy the codeThe test class for the sum
public class UserItemVO { private Long itemId; private Long userId; private String itemName; private String userName; public UserItemVO(Long itemId, Long userId, String itemName, String userName) { this.itemId = itemId; this.userId = userId; this.itemName = itemName; this.userName = userName; } public Long getItemId() { return itemId; } public void setItemId(Long itemId) { this.itemId = itemId; } public Long getUserId() { return userId; } public void setUserId(Long userId) { this.userId = userId; } public String getItemName() { return itemName; } public void setItemName(String itemName) { this.itemName = itemName; } public String getUserName() { return userName; } public void setUserName(String userName) { this.userName = userName; } @Override public String toString() { return "UserItemVO{" + "itemId=" + itemId + ", userId=" + userId + ", itemName='" + itemName + ''' + ", userName='" + userName + ''' + '}'; }}Copy the code2. Write test controllers
@GetMapping("/insert")
public void get(){
for (int i = 0; i < 10; i++) {
User user = new User();
user.setName("my name is " + i);
// user.setUserId(i+99L);
User userResult = userRepository.save(user);
Item u = new Item();
//u.setUserId(userResult.getUserId());
u.setItemName("my item is " + i);
itemRepository.save(u);
}
List<User> all1 = userRepository.findAll();
List<Item> all = itemRepository.findAll();
all1.forEach(System.out::println);
all.forEach(System.out::println);
// return all;
List<UserItemVO> bySql = userRepository.findBySql();
bySql.forEach(System.out::println);
return bySql;
}
Copy the code5. Test results
Insert 10 items into item with item_name ranging from 1 to 10 and user_id generated using the snowflake algorithm.
- Item_name = 2,4,6,8,10; item_name = 1,3,5,7,9;
Code address and discussion
Github.com/a58492906/s… I uploaded the project to Github, but I encountered some difficulties in encryption and decryption, so I did not implement it in my demo. After discussion in the group, I recompiled the Example part of Sharding source code downloaded yesterday, and studied part of the code in Example to achieve encryption and decryption
Unable to load authentication plugin ‘caching_sha2_password’ Unable to load authentication plugin ‘caching_sha2_password’
On the first day, I spent too much time on my own demo. I used Spring’s Sharing-Starter, but the version of stater was only 4.1.1, which seemed to cause some exceptions. After switching to the official example, I solved the problem.