阿飞Javaer,转载请注明原创出处,谢谢!
本篇文章讲解如何在ssm(spring、springmvc、mybatis)结构的程序上集成sharding-jdbc(版本为1.5.4.1)进行分库分表;
假设分库分表行为如下:
将auth_user表分到4个库(user_0~user_3)中;
其他表不进行分库分表,保留在default_db库中;
1. POM配置
以spring配置文件为例,新增如下POM配置:
com.dangdang
sharding-jdbc-core
1.5.4.1
com.dangdang
sharding-jdbc-config-spring
1.5.4.1
此次集成sharding-jdbc以1.5.4.1版本为例,如果是2.x版本的sharding-jdbc,那么需要将坐标com.dangdang修改为io.shardingjdbc;另外,如果是yaml配置,那么需要将坐标sharding-jdbc-config-spring修改为sharding-jdbc-config-yaml;
2. 配置数据源
spring-datasource.xml配置所有需要的数据源如下--auth_user分库分表后需要的4个库user_0~user_3,以及不分库分表的默认库default_db:
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
init-method="init" destroy-method="close">
init-method="init" destroy-method="close">
init-method="init" destroy-method="close">
init-method="init" destroy-method="close">
init-method="init" destroy-method="close">
properties配置文件内容如下:
sj_user_0.driver=com.mysql.jdbc.Driver
sj_user_0.url=jdbc:mysql://localhost:3306/user_0
sj_user_0.username=root
sj_user_0.password=RootAfei_1
sj_user_1.driver=com.mysql.jdbc.Driver
sj_user_1.url=jdbc:mysql://localhost:3306/user_1
sj_user_1.username=root
sj_user_1.password=RootAfei_1
sj_user_2.driver=com.mysql.jdbc.Driver
sj_user_2.url=jdbc:mysql://localhost:3306/user_2
sj_user_2.username=root
sj_user_2.password=RootAfei_1
sj_user_3.driver=com.mysql.jdbc.Driver
sj_user_3.url=jdbc:mysql://localhost:3306/user_3
sj_user_3.username=root
sj_user_3.password=RootAfei_1
sj_default.driver=com.mysql.jdbc.Driver
sj_default.url=jdbc:mysql://localhost:3306/default_db
sj_default.username=root
sj_default.password=RootAfei_1
3. 集成sharding数据源
spring-sharding.xml配置如下:
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:rdb="http://www.dangdang.com/schema/ddframe/rdb"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.dangdang.com/schema/ddframe/rdb
http://www.dangdang.com/schema/ddframe/rdb/rdb.xsd">
algorithm-class="com.crt.fin.ospsso.service.shardingjdbc.AuthUserDatabaseShardingAlgorithm" />
default-data-source="sj_ds_default">
database-strategy="databaseStrategy"/>
true
2
说明:spring-sharding.xml配置的分库分表规则:auth_user表分到id为sj_ds_${0..3}的四个库中,表名保持不变;其他表在id为sj_ds_default库中,不分库也不分表;集成sharding-jdbc的核心就是将SqlSessionFactoryBean需要的dataSource属性修改为shardingDataSource,把数据源交给sharding-jdbc处理;
分库逻辑AuthUserDatabaseShardingAlgorithm的代码很简单,源码如下:
/**
* @author wangzhenfei9
* @version 1.0.0
* @since 2018年02月08日
*/
public class AuthUserDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm {
private final Logger logger = LoggerFactory.getLogger(this.getClass());
private static final int SHARDING_NUMBER = 4;
@Override
public String doEqualSharding(final Collection availableTargetNames, final ShardingValue shardingValue) {
for (String each : availableTargetNames) {
if (each.endsWith(shardingValue.getValue() % SHARDING_NUMBER + "")) {
logger.debug("the target database name: {}", each);
return each;
}
}
throw new UnsupportedOperationException();
}
@Override
public Collection doInSharding(final Collection availableTargetNames, final ShardingValue shardingValue) {
Collection result = new LinkedHashSet<>(availableTargetNames.size());
Collection values = shardingValue.getValues();
for (Long value : values) {
for (String each : availableTargetNames) {
if (each.endsWith(value % SHARDING_NUMBER + "")) {
result.add(each);
}
}
}
return result;
}
@Override
public Collection doBetweenSharding(final Collection availableTargetNames, final ShardingValue shardingValue) {
Collection result = new LinkedHashSet<>(availableTargetNames.size());
Range range = shardingValue.getValueRange();
for (Long value = range.lowerEndpoint(); value <= range.upperEndpoint(); value++) {
for (String each : availableTargetNames) {
if (each.endsWith(value % SHARDING_NUMBER + "")) {
result.add(each);
}
}
}
return result;
}
}
这段代码参考sharding-jdbc源码中DatabaseShardingAlgorithm.java接口的实现即可,例如ModuloDatabaseShardingAlgorithm.java;
4. 注意事项
无法识别sharding-jdbc分库分表规则inline-expression问题,例如:
根本原因:
根本原因是spring把${}当做占位符,${0..3}这种表达式,spring会尝试去properties文件中找key为0..3的属性。但是这里是sharding-jdbc分库分表规则的inline表达式,需要spring忽略这种行为。否则会抛出异常:
java.lang.IllegalArgumentException: Could not resolve placeholder '0..3' in value "sj_ds_${0..3}.auth_user"
解决办法:
配置:
或者:
5. Main测试
Main.java用来测试分库分表是否OK,其源码如下:
/**
* @author wangzhenfei9
* @version 1.0.0
* @since 2018年02月08日
*/
public class Main {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext(
"/META-INF/spring/spring-*.xml");
// auth_user有进行分库,
AuthUserMapper authUserMapper = context.getBean(AuthUserMapper.class);
AuthUser authUser = authUserMapper.selectByPrimaryKey(7L);
System.out.println("-----> The auth user: "+JSON.toJSONString(authUser));
// user_permission没有分库分表
UserPermissionMapper userPermissionMapper = context.getBean(UserPermissionMapper.class);
UserPermission userPermission = userPermissionMapper.selectPermissionByUsername("wangzhenfei", "FINANCE_WALLET");
System.out.println("-----< The user permission: "+JSON.toJSONString(userPermission));
}
}
AuthUserMapper.selectByPrimaryKey()和UserPermissionMapper.selectPermissionByUsername()的代码和没有分库分表的代码完全一样;
6. 遗留问题
Main方法测试,或者启动服务后的调用测试都没有问题,但是通过junit测试用例访问就会抛出异常,作为一个待解决的遗留问题:
org.springframework.beans.factory.NoUniqueBeanDefinitionException: No qualifying bean of type 'javax.sql.DataSource' available: expected single matching bean but found 6: sj_ds_0,sj_ds_1,sj_ds_2,sj_ds_3,sj_ds_default,shardingDataSource