shardingjdbc 入门demo（jdbc+mybatis+springboot，同库分表）
一.使用场景
本人有两张用户表，想根据id的奇偶进行分表
test_user_1 、test_user_2
二.代码示例
框架使用的是mybatis+plus
全局pom
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.9.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.git</groupId>
<artifactId>mybatis-sharding</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>mybatis-sharding</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.0</version>
</dependency>
<!-- 这个巨坑，官网的找不到 -->
<!--<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core</artifactId>
<version>4.0.0</version>
</dependency>-->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</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>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
2.1 jdbc使用demo
/**
* @author authorZhao
* @since 2020-09-22
*/
@Slf4j
public class JdbcTest {
public static Map<String,DataSource> dataSourceMap=new ConcurrentHashMap<>();
static{
HikariDataSource dataSource = new HikariDataSource();
dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:306/test_user?characterEncoding=utf8&serverTimezone=UTC&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=false");
dataSource.setUsername("root");
dataSource.setPassword("root");
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSourceMap.put("test_user",dataSource);
}
@Test
public void test1(){
//分表规则有几个建立几个,表名 后面库名.表名字
TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("test_user_","test.test_user_{}");
PreciseShardingAlgorithm preciseShardingAlgorithm = new PreciseShardingAlgorithm() {
@Override
public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) {
//拿到id，根据id最后一个数字分表
String id = ((String) shardingValue.getValue());
String lastNum = id.substring(id.length() - 1);
String tableName = shardingValue.getLogicTableName()+((Long.valueOf(lastNum)%2) ==0L?2:1);
return tableName;
}
};
// 配置分表策略
orderTableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("id",preciseShardingAlgorithm));
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
HikariDataSource dataSource = new HikariDataSource();
String url = dataSourceProperties.getUrl();
//数据源
dataSource.setJdbcUrl(url);
dataSource.setUsername(dataSourceProperties.getUsername());
dataSource.setPassword(dataSourceProperties.getPassword());
dataSource.setDriverClassName(dataSourceProperties.getDriverClassName());
String dataBase = url.substring(url.lastIndexOf("/")+1, url.indexOf("?"));
//以数据库名字为key
dataSourceMap.put(dataBase,dataSource);
Properties props=new Properties();
//打印sql
props.put("sql.show", "true");
// 获取数据源对象
//javax.sql.DataSource
try {
DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
querySql(dataSource);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
private void querySql(DataSource dataSource) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try{
connection = dataSource.getConnection();
//实际执行的时候test_user_会被替换成你设置的表明和库名
preparedStatement = connection.prepareStatement("select * from test_user_ where id= '2044501603703006837335334921'");
//preparedStatement.setString(1,"2044501603703006837335334921");
ResultSet resultSet = preparedStatement.executeQuery();
Map<String,Object> map = new HashMap();
int i = 1;
while(resultSet.next()){
System.out.println("resultSet.getString(i) = " + resultSet.getString(i));
map.put(resultSet.getString(i),resultSet.getString(i));
i++;
}
System.out.println(map);
}catch (Exception e){
log.error("sql执行错误",e);
}finally {
try {
if(preparedStatement!=null)preparedStatement.close();
if(connection!=null)connection.close();
}catch (Exception e){
log.error("资源释放错误",e);
}
}
}
}
测试略
2.2mybats使用方式
2.2.1.实体类和接口
@Data
@Accessors(chain = true)
@TableName("test_user_")
class BugUser {//为什么叫bug，因为sharding不支持jdk8的LocalDateTime
@TableId(type = IdType.ASSIGN_ID)
private String id;
private String name;
private String nickName;
private String password;
private Date createTime;
private Date updateTime;
private String tel;
private Integer age;
}
@Mapper
interface BugUserMapper extends BaseMapper<BugUser> {
}
2.2.2.测试类和启动类
//测试类
@SpringBootTest(classes = BugMybatisShardingApplication.class)
class MybatisPlusTests {
@Autowired
private BugUserMapper userMapper;
@Test
void insert() {
BugUser user1 = userMapper.selectById("1308364076644413441");
BugUser user2 = userMapper.selectById("1308364076644413442");
List<BugUser> bugUsers = userMapper.selectList(new QueryWrapper<BugUser>().eq("name", "张三"));
System.out.println(bugUsers);
System.out.println(user1);
System.out.println(user2);
}
}
//启动类
@SpringBootApplication
//为了不影响其他测试，这里手动导入
@Import(LocalDataSourceConfig.class)
//正常的mapper扫描，扫描的是上面的mapper
@MapperScan("com.git.shard.shard")
class BugMybatisShardingApplication {
public static void main(String[] args) { SpringApplication.run(com.git.MybatisShardingApplication.class, args);
}
}
2.2.3.yml配置文件
spring:
datasource:
username: root
password: root
url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8&serverTimezone=UTC&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useSSL=false
driver-class-name: com.mysql.cj.jdbc.Driver
com:
git:
dataSource: local
logging:
level:
com:
git:
shard:
shard: DEBUG
mapper: DEBUG #这俩设置mybatis的日志
2.2.4.datasource配置文件
/**
* @author authorZhao
* @since 2020-10-27
*/
@Configuration
@ConditionalOnMissingBean(DataSource.class)
@Slf4j
@ConditionalOnProperty(name = {"com.git.dataSource"}, havingValue = "local")
public class LocalDataSourceConfig {
@Autowired
private DataSourceProperties dataSourceProperties;
public static final Map<String,DataSource> dataSourceMap=new ConcurrentHashMap<>();
@Bean
DataSource dataSource(){
//分表规则有几个建立几个,表名 后面库名.表名字
TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration("test_user_","test.test_user_{}");
PreciseShardingAlgorithm preciseShardingAlgorithm = new PreciseShardingAlgorithm() {
@Override
public String doSharding(Collection availableTargetNames, PreciseShardingValue shardingValue) {
//拿到id，根据id最后一个数字分表
String id = ((String) shardingValue.getValue());
String lastNum = id.substring(id.length() - 1);
String tableName = shardingValue.getLogicTableName()+((Long.valueOf(lastNum)%2) ==0L?2:1);
return tableName;
}
};
// 配置分表策略
orderTableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("id",preciseShardingAlgorithm));
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
HikariDataSource dataSource = new HikariDataSource();
String url = dataSourceProperties.getUrl();
//数据源
dataSource.setJdbcUrl(url);
dataSource.setUsername(dataSourceProperties.getUsername());
dataSource.setPassword(dataSourceProperties.getPassword());
dataSource.setDriverClassName(dataSourceProperties.getDriverClassName());
String dataBase = url.substring(url.lastIndexOf("/")+1, url.indexOf("?"));
//以数据库名字为key
dataSourceMap.put(dataBase,dataSource);
Properties props=new Properties();
//打印sql
props.put("sql.show", "true");
DataSource dataSource1 = null;
try {
dataSource1 = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, props);
} catch (SQLException throwables) {
dataSource1 = dataSource;
log.warn("sharding 配置失败，分表无效");
}
return dataSource1;
}
}
2.2.5测试结果：
2020-10-30 16:22:30.486 DEBUG 7284 --- [ main] c.g.s.shard.BugUserMapper.selectById : ==> Preparing: SELECT id,name,nick_name,password,create_time,update_time,tel,age FROM test_user_ WHERE id=?
2020-10-30 16:22:30.508 DEBUG 7284 --- [ main] c.g.s.shard.BugUserMapper.selectById : ==> Parameters: 1308364076644413441(String)
2020-10-30 16:22:30.846 INFO 7284 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,name,nick_name,password,create_time,update_time,tel,age FROM test_user_ WHERE id=?
2020-10-30 16:22:30.846 INFO 7284 --- [ main] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@f6cde7e, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@ff8e36d), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@ff8e36d, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=64, distinctRow=false, projections=[ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=null, name=nick_name, alias=Optional.empty), ColumnProjection(owner=null, name=password, alias=Optional.empty), ColumnProjection(owner=null, name=create_time, alias=Optional.empty), ColumnProjection(owner=null, name=update_time, alias=Optional.empty), ColumnProjection(owner=null, name=tel, alias=Optional.empty), ColumnProjection(owner=null, name=age, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@116b0b32, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@4f22fd5d, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@26d445e9, containsSubquery=false)
2020-10-30 16:22:30.846 INFO 7284 --- [ main] ShardingSphere-SQL : Actual SQL: test ::: SELECT id,name,nick_name,password,create_time,update_time,tel,age FROM test_user_1 WHERE id=? ::: [1308364076644413441]
2020-10-30 16:22:30.889 DEBUG 7284 --- [ main] c.g.s.shard.BugUserMapper.selectById : <== Total: 1
2020-10-30 16:22:30.898 DEBUG 7284 --- [ main] c.g.s.shard.BugUserMapper.selectById : ==> Preparing: SELECT id,name,nick_name,password,create_time,update_time,tel,age FROM test_user_ WHERE id=?
2020-10-30 16:22:30.899 DEBUG 7284 --- [ main] c.g.s.shard.BugUserMapper.selectById : ==> Parameters: 1308364076644413442(String)
2020-10-30 16:22:30.900 INFO 7284 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT id,name,nick_name,password,create_time,update_time,tel,age FROM test_user_ WHERE id=?
2020-10-30 16:22:30.900 INFO 7284 --- [ main] ShardingSphere-SQL : SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@f6cde7e, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@51c6e775), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@51c6e775, projectionsContext=ProjectionsContext(startIndex=7, stopIndex=64, distinctRow=false, projections=[ColumnProjection(owner=null, name=id, alias=Optional.empty), ColumnProjection(owner=null, name=name, alias=Optional.empty), ColumnProjection(owner=null, name=nick_name, alias=Optional.empty), ColumnProjection(owner=null, name=password, alias=Optional.empty), ColumnProjection(owner=null, name=create_time, alias=Optional.empty), ColumnProjection(owner=null, name=update_time, alias=Optional.empty), ColumnProjection(owner=null, name=tel, alias=Optional.empty), ColumnProjection(owner=null, name=age, alias=Optional.empty)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@372841d2, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@6c8d638a, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@117069f2, containsSubquery=false)
2020-10-30 16:22:30.900 INFO 7284 --- [ main] ShardingSphere-SQL : Actual SQL: test ::: SELECT id,name,nick_name,password,create_time,update_time,tel,age FROM test_user_2 WHERE id=? ::: [1308364076644413442]
2020-10-30 16:22:30.902 DEBUG 7284 --- [ main] c.g.s.shard.BugUserMapper.selectById : <== Total: 1
BugUser(id=1308364076644413441, name=张三, nickName=张三, password=123456, createTime=Wed Sep 23 03:14:38 CST 2020, updateTime=Wed Sep 23 03:14:38 CST 2020, tel=15674589456, age=18)
BugUser(id=1308364076644413442, name=李四, nickName=李四, password=123456, createTime=Wed Sep 23 03:14:38 CST 2020, updateTime=Wed Sep 23 03:14:38 CST 2020, tel=15674589456, age=18)
3.总结
1.sharding使用方法其实就是配置一些dataSource，与你使用的orm框架无关（前提是你必须使用jdbc），并且配置一些策略
2.对于没有分表的字段用来查询会报错大家可以自试一下
3.从官网上得知sharding的原理是jdbc代理
4.完！
634
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
