Shulou(Shulou.com)06/02 Report--

This article is about how to use sharding-jdbc to achieve horizontal sub-library + horizontal sub-table. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Sub-database and sub-table strategy: deposit the even number of id into library 1, the odd number into library 2, and then in each library, according to the gender of the students to table 1 and table 2 respectively.

Create two new databases, sharding_db1 and sharding_db2, and create two tables with the same structure, student_1 and student_2, respectively. CREATE TABLE `NewTable` (`ID` bigint (20) NOT NULL, `NAME` varchar (50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `AGE` int (11) NOT NULL, `GENDER` int (1) NOT NULL, PRIMARY KEY (`ID`))

Compared with the previous article, the gender gender field is set to the int type to facilitate sub-table according to gender.

Modify the configuration file spring.main.allow-bean-definition-overriding=true# configure Sharding-JDBC 's sharding policy # configure the data source, and give the data source the name G1 Die G2. Multi-data source spring.shardingsphere.datasource.names=g1,g2# can be configured here to configure data source details: connection pool, driver, address, user name Password spring.shardingsphere.datasource.g1.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.g1.driver-class-name=com.mysql.cj.jdbc.Driverspring.shardingsphere.datasource.g1.url=jdbc:mysql://localhost:3306/sharding_db1?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTCspring.shardingsphere.datasource.g1.username=rootspring.shardingsphere.datasource.g1.password=123456spring.shardingsphere.datasource.g2.type=com.alibaba.druid.pool.DruidDataSourcespring.shardingsphere.datasource.g2.driver-class-name=com.mysql.cj.jdbc. Distribution of Driverspring.shardingsphere.datasource.g2.url=jdbc:mysql://localhost:3306/sharding_db2?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTCspring.shardingsphere.datasource.g2.username=rootspring.shardingsphere.datasource.g2.password=123456# configuration database Table distribution spring.shardingsphere.sharding.tables.student.actual-data-nodes=g$- > {1.. 2} .student _ $- > {1.. 2} # specify student table primary key gid generation policy for SNOWFLAKEspring.shardingsphere.sharding.tables.student.key-generator.column=idspring.shardingsphere.sharding.tables.student.key-generator.type=SNOWFLAKE# specify database sharding policy convention id value is even added to sharding_db1 Add odd number to sharding_db2 spring.shardingsphere.sharding.tables.student.database-strategy.inline.sharding-column=idspring.shardingsphere.sharding.tables.student.database-strategy.inline.algorithm-expression=g$- > {id% 2 + 1} # specify table sharding policy convention gender value is 0 add to student_1 table If gender is 1, add to student_2 table spring.shardingsphere.sharding.tables.student.table-strategy.inline.sharding-column=genderspring.shardingsphere.sharding.tables.student.table-strategy.inline.algorithm-expression=student_$- > {gender% 2 + 1} # Open sql output log spring.shardingsphere.props.sql.show=true

When configuring multiple data sources, separate them with commas to configure their properties. In addition to configuring the table sharding policy, you also need to configure the library allocation policy.

Test class @ SpringBootTestclass ShardingJdbcDemoApplicationTests {@ Autowired private StudentMapper studentMapper; @ Test public void test01 () {for (int I = 0; I)

< 15; i++) { Student student = new Student(); student.setName("wuwl"); student.setAge(27); student.setGender(i%2); studentMapper.insert(student); } }} 运行效果： 看样子是成功了，查看数据库数据。 sharding_db1.student_1：

Sharding_db1.student_2:

Sharding_db2.student_1:

Sharding_db2.student_2:

Thank you for reading! On "how to use sharding-jdbc to achieve horizontal sub-library + horizontal sub-table" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.