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

This article mainly introduces "how to use sharding-jdbc to achieve horizontal sub-table". In daily operation, I believe that many people have doubts about how to use sharding-jdbc to achieve horizontal sub-table. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubt of "how to use sharding-jdbc to achieve horizontal sub-table". Next, please follow the editor to study!

Create a new database sharding_db in mysql and add two tables student_1 and student_2 with the same structure. CREATE TABLE `student_ 1` (`ID` bigint (20) NOT NULL, `NAME` varchar (50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, `AGE` int (11) NOT NULL, `GENDER` varchar (1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, PRIMARY KEY (`ID`))

No primary key increment is specified here, because the id of the two tables cannot be duplicated, so you can only pass in id from the backend.

Add dependency org.springframework.boot spring-boot-starter-jdbc org.springframework.boot spring-boot-starter-web com.alibaba druid 1.1.20 mysql mysql-connector-java com.baomidou mybatis-plus-boot-starter 3.0.5 org.apache.shardingsphere sharding-jdbc-spring-boot-starter 4.0.0-RC1 org.projectlombok lombok org.springframework.boot spring-boot-test test junit junit Test org.springframework.boot spring-boot-starter-test test org.junit.vintage junit-vintage-engine Writing configuration File spring.main.allow-bean-definition-overriding=true# configuring Sharding-JDBC sharding Policy # configuring data Source Give the data source the name G1J G2. Multi-data source spring.shardingsphere.datasource.names=g1# can be configured here to configure data source details: connection pool, driver, address, user name Password # since there is only G1 for the above configuration data source, the following configuration is only g1.typerecoverg1.driverMurnamerecoverg1.urlrecoverg1.username Distribution of g1.passwordspring.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_db?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTCspring.shardingsphere.datasource.g1.username=rootspring.shardingsphere.datasource.g1.password=123456# configuration table The policy spring.shardingsphere.sharding.tables.student.actual-data-nodes=g1.student_$- > {1.. 2} # of the table specifies that the student primary key gid generation policy specifies that the sharding policy convention id value is even to be added to the student_1 table for SNOWFLAKEspring.shardingsphere.sharding.tables.student.key-generator.column=idspring.shardingsphere.sharding.tables.student.key-generator.type=SNOWFLAKE# If id is odd added to student_2 table spring.shardingsphere.sharding.tables.student.table-strategy.inline.sharding-column=idspring.shardingsphere.sharding.tables.student.table-strategy.inline.algorithm-expression=student_$- > {id% 2 + 1} # Open sql output log spring.shardingsphere.props.sql.show=true

Or yml format.

Spring: main: allow-bean-definition-overriding: true shardingsphere: datasource: g1: driver-class-name: com.mysql.cj.jdbc.Driver password: 123456 type: com.alibaba.druid.pool.DruidDataSource url: jdbc:mysql://localhost:3306/sharding_db?characterEncoding=utf-8&useUnicode=true&useSSL=false&serverTimezone=UTC username: root names: g1 props: sql: show: true Sharding: tables: student: actual-data-nodes: g1.studentwriters-> {1.. 2} key-generator: column: id type: SNOWFLAKE table-strategy: inline: algorithm-expression: student_$- > {id% 2 + 1} sharding-column: id Writing entity Class @ Datapublic class Student {private Long id Private String name; private int age; private String gender;} write mapper interface @ Repositorypublic interface StudentMapper extends BaseMapper {} write test class @ SpringBootTestclass ShardingJdbcDemoApplicationTests {@ Autowired private StudentMapper studentMapper; @ Test public void test01 () {for (int I = 0; I < 10; iTunes +) {Student student = new Student (); student.setName ("wuwl"); student.setAge (27) Student.setGender ("male"); studentMapper.insert (student);} execute the test

After successful execution, the primary key is generated at the back end through the snowflake algorithm, which is passed into the database and divided into tables according to parity.

Student_1 data:

Student_2 data:

There are 5 items of data in each of the two tables, but this is only because there are 5 odd and even numbers of id generated by the snowflake algorithm, not 1:1, which requires attention.

After the primary key is generated, it is inserted into the corresponding table according to the policy, as evidenced by the printed sql.

When querying through the selectById method of the mapper interface, it will first determine which library it is based on the primary key policy, and then go directly to that library to query according to the primary key. What if it is queried through other conditions, or through the selectById methods of multiple id?

@ Test public void test03 () {List list = new ArrayList (); list.add (1362282042768609282l); list.add (1362282040277192705l); List studentList = studentMapper.selectBatchIds (list); System.out.println (studentList);}

The id of two tables is taken for query.

Perform the same sql, query both tables, and then combine the results.

If all id come from the same table, will you repeat the query in multiple tables?

It was only executed once. Therefore, when executing a query, sharding will first determine whether it is possible to determine which table the required data comes from. If so, it can directly query the data in that table, and if not, multiple tables will repeat the query to determine the integrity of the query results.

At this point, the study on "how to use sharding-jdbc to achieve horizontal sub-table" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.