通过sharding-jdbc做分表这里就不多做介绍了,需要的可以看上一片文章
当项目中配置了多数据源,并且其中一个数据源需要使用sharding-jdbc分表时,配置如下
导入shardingjdbc依赖
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.1</version>
</dependency>
这里使用的是4.0.1的配置,和上篇的3.1.0差别不大,详细配置大伙可以上官网查阅。
多数据源配置类
shardingsphere数据源初始化
package com.efuav.parkingapron.config;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
@Configuration
@MapperScan(basePackages = "com.efuav.parkingapron.dao.db1", sqlSessionFactoryRef = "db1SqlSessionFactory")
public class DataSourceConfig1 {
@Value("${spring.shardingsphere.datasource.efuavsystem.username}")
private String userName;
@Value("${spring.shardingsphere.datasource.efuavsystem.jdbc-url}")
private String url;
@Value("${spring.shardingsphere.datasource.efuavsystem.password}")
private String userPwd;
@Value("${spring.shardingsphere.datasource.names}")
private String dataName;
// @Primary // 表示这个数据源是默认数据源, 这个注解必须要加,因为不加的话spring将分不清楚那个为主数据源(默认数据源)
// @Bean(name="db1DataSource")
@ConfigurationProperties(prefix = "spring.datasource.db1") //读取application.yml中的配置参数映射成为一个对象
// public DataSource getDb1DataSource(){
//
// return DataSourceBuilder.create().build();
// }
@Bean(name = "db1DataSource")
@Qualifier("db1DataSource")
public DataSource deviceDataSource() throws SQLException {
return getShardingDataSource();
}
@Primary
@Bean("db1SqlSessionFactory")
public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db1DataSource") DataSource dataSource) throws Exception {
MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
bean.setDataSource(dataSource);
// mapper的xml形式文件位置必须要配置,不然将报错:no statement (这种错误也可能是mapper的xml中,namespace与项目的路径不一致导致)
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/db1/*.xml"));
return bean.getObject();
}
@Primary
@Bean("db1SqlSessionTemplate")
public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db1SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}
DataSource getShardingDataSource() throws SQLException {
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
// 配置分表规则
shardingRuleConfig.getTableRuleConfigs().add(getRealTableRuleConfiguration());
shardingRuleConfig.getBindingTableGroups().add("ef_uav_realtimedata");
Properties props = new Properties();
// 配置shardingsphere是否打印日志
props.setProperty("sql.show", "true");
// 创建数据源
return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, props);
}
// 返回ef_uav_realtimedata表的分表规则配置
TableRuleConfiguration getRealTableRuleConfiguration() {
// 指定数据库及表配置规则
TableRuleConfiguration result = new TableRuleConfiguration("ef_uav_realtimedata", "efuavsystem.ef_uav_realtimedata$->{1..5}");
// 指定分表字段及分表规则类 TaTable2Algorithm为自定义分表规则类
result.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("uav_id", new UavPreciseShardingAlgorithm()));
return result;
}
private Map<String, DataSource> createDataSourceMap() {
Map<String, DataSource> result = new HashMap<>();
HikariDataSource dbs = new HikariDataSource();
dbs.setDriverClassName("com.mysql.cj.jdbc.Driver");
dbs.setJdbcUrl(url);
dbs.setUsername(userName);
dbs.setPassword(userPwd);
result.put(dataName, dbs);
return result;
}
}
普通数据源初始化
package com.efuav.parkingapron.config;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;
@Configuration
@MapperScan(basePackages = "com.efuav.parkingapron.dao.db2", sqlSessionFactoryRef = "db2SqlSessionFactory")
public class DataSourceConfig2 {
@Bean("db2DataSource")
// @ConfigurationProperties(prefix = "spring.datasource.db2")
@ConfigurationProperties(prefix = "spring.datasource.infrared")
public DataSource getDb1DataSource(){
return DataSourceBuilder.create().build();
}
@Bean("db2SqlSessionFactory")
public SqlSessionFactory db1SqlSessionFactory(@Qualifier("db2DataSource") DataSource dataSource) throws Exception {
MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/db2/*.xml"));
return bean.getObject();
}
@Bean("db2SqlSessionTemplate")
public SqlSessionTemplate db1SqlSessionTemplate(@Qualifier("db2SqlSessionFactory") SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}
}
分表自定义配置规则(上篇文章有解释)
package com.efuav.parkingapron.config;
import lombok.extern.slf4j.Slf4j;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import javax.annotation.PostConstruct;
import java.util.Collection;
//分表规则
@Slf4j
public class UavPreciseShardingAlgorithm implements PreciseShardingAlgorithm<String> {
/**
* 插入数据 改写表的名称
* 查询 改写表的名称
*
* @param collection
* @param preciseShardingValue
* @return
*/
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
// UAV07JDE6E0020240
String value = preciseShardingValue.getValue();
int las = value.charAt(value.length() - 1);
String tableName = "ef_uav_realtimedata" + ((las % 4)+1);
log.info("<tableName{}>", tableName);
return tableName;
}
}
4.yml配置文件
spring:
main:
allow-bean-definition-overriding: true
datasource:
infrared:
jdbc-url: jdbc:mysql://192.168.0.5:13306/infrared?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true
username: root #用户名
password: 123456 #密码
driver-class-name: com.mysql.cj.jdbc.Driver #数据库链接驱动
type: com.zaxxer.hikari.HikariDataSource
hikari.minimum-idle: 5
hikari.maximum-pool-size: 15
hikari.auto-commit: true
hikari.idle-timeout: 30000
hikari.pool-name: DatebookHikariCP
# 不小于30s 否则会回到默认的1800秒, 用来设置一个connection在连接池中的存活时间,默认是1800000,即30分钟。如果设置为0,表示存活时间无限大。如果不等于0且小于30秒则会被重置回30分钟。
hikari.max-lifetime: 28800000
hikari.connection-timeout: 30000
hikari.connection-test-query: SELECT 1
shardingsphere:
datasource:
names: efuavsystem
efuavsystem:
jdbc-url: jdbc:mysql://127.0.0.1:3306/ef_uav_system?characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&allowPublicKeyRetrieval=true
username: root #用户名
password: 123456 #密码
driver-class-name: com.mysql.cj.jdbc.Driver #数据库链接驱动
type: com.zaxxer.hikari.HikariDataSource
# 水平拆分的数据库(表) 配置分库 + 分表策略 行表达式分片策略
# sharding:
# tables:
# ef_uav_realtimedata:
# actual-data-nodes: efuavsystem.ef_uav_realtimedata$->{1..5}
# table-strategy:
# standard:
# precise-algorithm-class-name: com.efuav.parkingapron.config.UavPreciseShardingAlgorithm
# sharding-column: uav_id
# 打印执行的数据库
# props:
# sql:
# show: true
这样就不会冲突,以上就是sharding-jdbc多数据源配置了。文章来源:https://www.toymoban.com/news/detail-624651.html
记录....文章来源地址https://www.toymoban.com/news/detail-624651.html
到了这里,关于sharding-jdbc多数据源配置的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!