基于SpringBoot+Mybaits+sharding jdbc
由于sharding-jdbc是不支持动态进行建库的SQL,那么就需要一次把需要的数据库和数据表都建好
数据库脚本:
SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for t_order_0 -- ---------------------------- DROP TABLE IF EXISTS `t_order_0`; CREATE TABLE `t_order_0` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键id', `order_id` VARCHAR(32) DEFAULT NULL COMMENT '顺序编号', `user_id` VARCHAR(32) DEFAULT NULL COMMENT '用户编号', `userName` VARCHAR(32) DEFAULT NULL COMMENT '用户名', `passWord` VARCHAR(32) DEFAULT NULL COMMENT '密码', `nick_name` VARCHAR(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8; -- ---------------------------- -- Table structure for t_order_1 -- ---------------------------- DROP TABLE IF EXISTS `t_order_1`; CREATE TABLE `t_order_1` ( `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '主键id', `order_id` VARCHAR(32) DEFAULT NULL COMMENT '顺序编号', `user_id` VARCHAR(32) DEFAULT NULL COMMENT '用户编号', `userName` VARCHAR(32) DEFAULT NULL COMMENT '用户名', `passWord` VARCHAR(32) DEFAULT NULL COMMENT '密码', `nick_name` VARCHAR(32) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8;
pom文件
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.1.1.RELEASE</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.example</groupId> <artifactId>sharding-jdbc</artifactId> <version>0.0.1-SNAPSHOT</version> <name>sharding-jdbc</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.1.1</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.37</version> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <!--sharding-jdbc --> <dependency> <groupId>com.dangdang</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>1.5.4</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
配置文件
mybatis.config-locations=classpath:mybatis/mybatis-config.xml #datasource spring.devtools.remote.restart.enabled=false #data source1 spring.datasource.test1.driverClassName=com.mysql.jdbc.Driver spring.datasource.test1.jdbcUrl=jdbc:mysql://127.0.0.1:3336/sharding-jdbc?serverTimezone=UTC spring.datasource.test1.username=root spring.datasource.test1.password= #data source2 spring.datasource.test2.driverClassName=com.mysql.jdbc.Driver spring.datasource.test2.jdbcUrl=jdbc:mysql://127.0.0.1:3336/sharding-jdbc2?serverTimezone=UTC spring.datasource.test2.username=root spring.datasource.test2.password=
启动类:
import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.EnableAutoConfiguration; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration; import org.springframework.transaction.annotation.EnableTransactionManagement; @SpringBootApplication @EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class}) //排除DataSourceConfiguratrion @EnableTransactionManagement(proxyTargetClass = true) //开启事物管理功能 public class ShardingJdbcApplication { public static void main(String[] args) { SpringApplication.run(ShardingJdbcApplication.class, args); } }
xml文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.example.shardingjdbc.mapper.UserMapper" > <resultMap id="BaseResultMap" type="com.example.shardingjdbc.entity.User" > <id column="id" property="id" jdbcType="BIGINT" /> <result column="order_id" property="order_id" jdbcType="BIGINT" /> <result column="user_id" property="user_id" jdbcType="BIGINT" /> <result column="userName" property="userName" jdbcType="VARCHAR" /> <result column="passWord" property="passWord" jdbcType="VARCHAR" /> <result column="nick_name" property="nickName" jdbcType="VARCHAR" /> </resultMap> <sql id="Base_Column_List" > id, userName, passWord, user_sex, nick_name </sql> <insert id="insert" parameterType="com.example.shardingjdbc.entity.User" > INSERT INTO t_order (order_id,user_id,userName,passWord) VALUES (#{order_id},#{user_id},#{userName}, #{passWord}) </insert> </mapper>
实体类:
public class User { private Long id; private Long order_id; private Long user_id; private String userName; private String passWord; private String nickName;
}
service层
import javax.annotation.Resource; import org.springframework.stereotype.Service; import com.example.shardingjdbc.entity.User; import com.example.shardingjdbc.mapper.UserMapper; import lombok.extern.slf4j.Slf4j; @Slf4j @Service public class UserService { @Resource private UserMapper userMapper; public void insert(User user) { int a = 1; for (int i = 0; i < 10; i++) { for (int j = 0; j < 10; j++) { User u = new User(); u.setUser_id(Long.valueOf(i+"")); u.setOrder_id(Long.valueOf(a+"")); u.setUserName("UserName" + a); u.setNickName("NickName" + a); userMapper.insert(u); ++a; } } } public Object selectByUserId(Long id) { return userMapper.selectByUserId(id); } public void update(Long orderId){ userMapper.updateByOrderId(orderId); } }
controller 层
import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import com.example.shardingjdbc.entity.User; import com.example.shardingjdbc.service.UserService; @Service @RestController @RequestMapping("/user") public class UserController { @Autowired private UserService userService; //测试 @RequestMapping(value="/a")// localhost:8080/user/a public String updateTransactional() { User user2 = new User(); userService.insert(user2); return "success"; } @RequestMapping(value="/select")// localhost:8080/user/a public Object selectUser(Integer id) { Long idLong = Long.valueOf(id+""); return userService.selectByUserId(idLong); } @RequestMapping(value="/update")// localhost:8080/user/a public void updateUser(Long id) { userService.update(id); } }
- 数据源配置和Mybatis配置和分库分表规则(重要)
这里,我们是将多个数据源交给sharding-jdbc进行管理,并且有默认的数据源,当没有设置分库分表规则的时候就可以使用默认的数据源
分表:user_id%2 = 0的数据存储到sharding-jdbc,为1的存储到sharding-jdbc2
分表:order_id%2 = 0的数据存储到 t_order_0,为1的存储到 t_order_1
数据源配置和Mybatis配置和分库分表规则
import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory; import com.dangdang.ddframe.rdb.sharding.api.rule.BindingTableRule; import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule; import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule; import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule; import com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy; import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy; import com.example.shardingjdbc.strategy.ModuloDatabaseShardingAlgorithm; import com.example.shardingjdbc.strategy.ModuloTableShardingAlgorithm; import org.apache.ibatis.session.SqlSessionFactory; import org.mybatis.spring.SqlSessionFactoryBean; 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.context.annotation.Primary; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import javax.sql.DataSource; import java.sql.SQLException; import java.util.*; /** * @Auther: Tinko * @Date: 2018/12/19 16:27 * @Description: 数据源配置和Mybatis配置和分库分表规则 */ @Configuration @MapperScan(basePackages = "com.example.shardingjdbc.mapper", sqlSessionTemplateRef = "test1SqlSessionTemplate") public class DataSourceConfig { /** * 配置数据源0,数据源的名称最好要有一定的规则,方便配置分库的计算规则 * @return */ @Bean(name="dataSource0") @ConfigurationProperties(prefix = "spring.datasource.test1") public DataSource dataSource0(){ return DataSourceBuilder.create().build(); } /** * 配置数据源1,数据源的名称最好要有一定的规则,方便配置分库的计算规则 * @return */ @Bean(name="dataSource1") @ConfigurationProperties(prefix = "spring.datasource.test2") public DataSource dataSource1(){ return DataSourceBuilder.create().build(); } /** * 配置数据源规则,即将多个数据源交给sharding-jdbc管理,并且可以设置默认的数据源, * 当表没有配置分库规则时会使用默认的数据源 * @param dataSource0 * @param dataSource1 * @return */ @Bean public DataSourceRule dataSourceRule(@Qualifier("dataSource0") DataSource dataSource0, @Qualifier("dataSource1") DataSource dataSource1){ Map<String, DataSource> dataSourceMap = new HashMap<>(); //设置分库映射 dataSourceMap.put("dataSource0", dataSource0); dataSourceMap.put("dataSource1", dataSource1); return new DataSourceRule(dataSourceMap, "dataSource0"); //设置默认库,两个库以上时必须设置默认库。默认库的数据源名称必须是dataSourceMap的key之一 } /** * 配置数据源策略和表策略,具体策略需要自己实现 * @param dataSourceRule * @return */ @Bean public ShardingRule shardingRule(DataSourceRule dataSourceRule){ //具体分库分表策略 TableRule orderTableRule = TableRule.builder("t_order") .actualTables(Arrays.asList("t_order_0", "t_order_1")) .tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm())) .dataSourceRule(dataSourceRule) .build(); //绑定表策略,在查询时会使用主表策略计算路由的数据源,因此需要约定绑定表策略的表的规则需要一致,可以一定程度提高效率 List<BindingTableRule> bindingTableRules = new ArrayList<BindingTableRule>(); bindingTableRules.add(new BindingTableRule(Arrays.asList(orderTableRule))); return ShardingRule.builder() .dataSourceRule(dataSourceRule) .tableRules(Arrays.asList(orderTableRule)) .bindingTableRules(bindingTableRules) .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new ModuloDatabaseShardingAlgorithm())) .tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm())) .build(); } /** * 创建sharding-jdbc的数据源DataSource,MybatisAutoConfiguration会使用此数据源 * @param shardingRule * @return * @throws SQLException */ @Bean(name="dataSource") public DataSource shardingDataSource(ShardingRule shardingRule) throws SQLException { return ShardingDataSourceFactory.createDataSource(shardingRule); } /** * 需要手动配置事务管理器 * @param dataSource * @return */ @Bean public DataSourceTransactionManager transactitonManager(@Qualifier("dataSource") DataSource dataSource){ return new DataSourceTransactionManager(dataSource); } @Bean(name = "test1SqlSessionFactory") @Primary public SqlSessionFactory testSqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dataSource); bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/*.xml")); return bean.getObject(); } @Bean(name = "test1SqlSessionTemplate") @Primary public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("test1SqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception { return new SqlSessionTemplate(sqlSessionFactory); } }
分库规则
import java.util.Collection; import java.util.LinkedHashSet; import com.dangdang.ddframe.rdb.sharding.api.ShardingValue; import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm; import com.google.common.collect.Range; /** * @Auther: Tinko * @Date: 2018/12/19 16:31 * @Description: 分库规则 */ public class ModuloDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> { @Override public String doEqualSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) { System.out.println("分库规则 doEqualSharding"); System.out.println(databaseNames.toString());// [dataSource0, dataSource1] // ShardingValue(logicTableName=t_order, columnName=user_id, value=0, values=[], valueRange=null) // ShardingValue(logicTableName=t_order, columnName=user_id, value=1, values=[], valueRange=null) // ... System.out.println(shardingValue.toString()); for (String each : databaseNames) { if (each.endsWith(Long.parseLong(shardingValue.getValue().toString()) % 2 + "")) { return each; } } throw new IllegalArgumentException(); } @Override public Collection<String> doInSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) { Collection<String> result = new LinkedHashSet<>(databaseNames.size()); for (Long value : shardingValue.getValues()) { for (String tableName : databaseNames) { if (tableName.endsWith(value % 2 + "")) { result.add(tableName); } } } System.out.println("分库规则 doInSharding"); System.out.println(databaseNames.toString()); System.out.println(shardingValue.toString()); System.out.println(result.toString()); return result; } @Override public Collection<String> doBetweenSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) { Collection<String> result = new LinkedHashSet<>(databaseNames.size()); Range<Long> range = shardingValue.getValueRange(); for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) { for (String each : databaseNames) { if (each.endsWith(i % 2 + "")) { result.add(each); } } } System.out.println("分库规则 doBetweenSharding"); System.out.println(databaseNames.toString()); System.out.println(shardingValue.toString()); System.out.println(result.toString()); return result; } }
分表规则
import java.util.Collection; import java.util.LinkedHashSet; import com.dangdang.ddframe.rdb.sharding.api.ShardingValue; import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm; import com.google.common.collect.Range; /** * @Auther: Tinko * @Date: 2018/12/19 16:30 * @Description: 分表规则 */ public class ModuloTableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> { @Override public String doEqualSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) { System.out.println("分表规则 doEqualSharding"); System.out.println(tableNames.toString());// [t_order_0, t_order_1] // ShardingValue(logicTableName=t_order, columnName=order_id, value=0, values=[], valueRange=null) // ShardingValue(logicTableName=t_order, columnName=order_id, value=1, values=[], valueRange=null) // ... System.out.println(shardingValue.toString()); for (String each : tableNames) { if (each.endsWith(shardingValue.getValue() % 2 + "")) { return each; } } throw new IllegalArgumentException(); } @Override public Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) { Collection<String> result = new LinkedHashSet<>(tableNames.size()); for (Long value : shardingValue.getValues()) { for (String tableName : tableNames) { if (tableName.endsWith(value % 2 + "")) { result.add(tableName); } } } System.out.println("分表规则 doInSharding"); System.out.println(tableNames.toString()); System.out.println(shardingValue.toString()); System.out.println(result.toString()); return result; } @Override public Collection<String> doBetweenSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) { Collection<String> result = new LinkedHashSet<>(tableNames.size()); Range<Long> range = shardingValue.getValueRange(); for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) { for (String each : tableNames) { if (each.endsWith(i % 2 + "")) { result.add(each); } } } System.out.println("分表规则 doBetweenSharding"); System.out.println(tableNames.toString()); System.out.println(shardingValue.toString()); System.out.println(result.toString()); return result; } }
测试结果:
1) 插入数据
2) 查询 http://localhost:8080/user/select?id=1
/*[{"id":6,"order_id":22,"user_id":2,"userName":"UserName22","passWord":null,"nickName":null},
{"id":7,"order_id":24,"user_id":2,"userName":"UserName24","passWord":null,"nickName":null},
{"id":8,"order_id":26,"user_id":2,"userName":"UserName26","passWord":null,"nickName":null},
{"id":9,"order_id":28,"user_id":2,"userName":"UserName28","passWord":null,"nickName":null},
{"id":10,"order_id":30,"user_id":2,"userName":"UserName30","passWord":null,"nickName":null},
{"id":6,"order_id":21,"user_id":2,"userName":"UserName21","passWord":null,"nickName":null},
{"id":7,"order_id":23,"user_id":2,"userName":"UserName23","passWord":null,"nickName":null},
{"id":8,"order_id":25,"user_id":2,"userName":"UserName25","passWord":null,"nickName":null},
{"id":9,"order_id":27,"user_id":2,"userName":"UserName27","passWord":null,"nickName":null},
{"id":10,"order_id":29,"user_id":2,"userName":"UserName29","passWord":null,"nickName":null}]*/
3) 修改
http://localhost:8080/user/update?id=1
原文链接: https://blog.csdn.net/a992795427/article/details/85102918?utm_medium=distribute.pc_relevant_right.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param_right&depth_1-utm_source=distribute.pc_relevant_right.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param_right