概述
场景
建库、建表
CREATE TABLE ` t_user` (
` user_id` bigint ( 20 ) NOT NULL ,
` username` varchar ( 255 ) DEFAULT NULL ,
` ustatus` varchar ( 255 ) DEFAULT NULL ,
PRIMARY KEY ( ` user_id` )
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
Springboot环境搭建
参考:ShardingSphere之水平分表实战(三)
编写代码
创建User实体类、mapper
package com. study. shardingjdbcdemo. entity ;
import lombok. Data ;
import javax. persistence. Id ;
import javax. persistence. Table ;
@Data
@Table ( name = "t_user" )
public class User {
@Id
private Long userId;
private String username;
private String ustatus;
}
package com. study. shardingjdbcdemo. mapper ;
import com. study. shardingjdbcdemo. entity. User ;
import org. springframework. stereotype. Repository ;
import tk. mybatis. mapper. common. Mapper ;
@Repository
public interface UserMapper extends Mapper < User > {
}
application.properties配置
spring.shardingsphere.datasource.names= m0,m1,m2
spring.shardingsphere.datasource.m1.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name= com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url= jdbc:mysql://localhost:3306/edu_db_1?serverTimezone= GMT%2B8
spring.shardingsphere.datasource.m1.username= root
spring.shardingsphere.datasource.m1.password= 123456
spring.shardingsphere.datasource.m2.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name= com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url= jdbc:mysql://localhost:3306/edu_db_2?serverTimezone= GMT%2B8
spring.shardingsphere.datasource.m2.username= root
spring.shardingsphere.datasource.m2.password= 123456
spring.shardingsphere.datasource.m0.type= com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name= com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url= jdbc:mysql://localhost:3306/user_db?serverTimezone= GMT%2B8
spring.shardingsphere.datasource.m0.username= root
spring.shardingsphere.datasource.m0.password= 123456
spring.shardingsphere.sharding.tables.course.actual-data-nodes= m$-> { 1 .. 2 } .course_$-> { 1 .. 2 }
spring.shardingsphere.sharding.tables.course.key-generator.column= cid
spring.shardingsphere.sharding.tables.course.key-generator.type= SNOWFLAKE
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column= cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression= course_$-> { cid % 2 + 1 }
spring.shardingsphere.sharding.tables.course.database-strategy.inline.sharding-column= user_id
spring.shardingsphere.sharding.tables.course.database-strategy.inline.algorithm-expression= m$-> { user_id % 2 + 1 }
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes= m0.t_user
spring.shardingsphere.sharding.tables.t_user.key-generator.column= user_id
spring.shardingsphere.sharding.tables.t_user.key-generator.type= SNOWFLAKE
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column= user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression= t_user
spring.shardingsphere.props.sql.show= true
spring.main.allow-bean-definition-overriding= true
测试
@Test
void addUser ( ) {
for ( int i = 0 ; i < 10 ; i++ ) {
User user = new User ( ) ;
user. setUsername ( "lucy" ) ;
user. setUstatus ( "good" ) ;
userMapper. insertSelective ( user) ;
}
}
@Test
public void findUser ( ) {
Example example = new Example ( User . class ) ;
Example. Criteria criteria = example. createCriteria ( ) ;
criteria. andEqualTo ( "userId" , 760195997497294849L ) ;
User user = userMapper. selectOneByExample ( example) ;
System . out. println ( user) ;
}