springboot+mybatisplus+sharding-jdbc分库分表实例
项目实践
现在Java项目使用mybatis多一些,所以我也做了一个springboot+mybatisplus+sharding-jdbc分库分表项目例子分享给大家。
要是用的springboot+jpa可以看这篇文章:https://www.cnblogs.com/owenma/p/11364624.html
其它的框架内容不做赘述,直接上代码。
数据准备
装备两个数据库。并在两个库中建表,建表sql如下:
DROP TABLE IF EXISTS `t_user_0`; CREATE TABLE `t_user_0` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id', `order_id` BIGINT(20) DEFAULT '0' COMMENT '顺序编号', `user_id` BIGINT(20) DEFAULT '0' COMMENT '用户编号', `user_name` varchar(32) DEFAULT NULL COMMENT '用户名', `pass_word` varchar(32) DEFAULT NULL COMMENT '密码', `nick_name` varchar(32) DEFAULT NULL COMMENT '倪名', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; DROP TABLE IF EXISTS `t_user_1`; CREATE TABLE `t_user_1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id', `order_id` BIGINT(20) DEFAULT '0' COMMENT '顺序编号', `user_id` BIGINT(20) DEFAULT '0' COMMENT '用户编号', `user_name` varchar(32) DEFAULT NULL COMMENT '用户名', `pass_word` varchar(32) DEFAULT NULL COMMENT '密码', `nick_name` varchar(32) DEFAULT NULL COMMENT '倪名', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
POM配置
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> <optional>true</optional> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>com.dangdang</groupId> <artifactId>sharding-jdbc-core</artifactId> <version>1.5.4</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.3</version> </dependency> <dependency> <groupId>commons-dbcp</groupId> <artifactId>commons-dbcp</artifactId> <version>1.4</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.44</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- mybatis-plus begin --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatisplus-spring-boot-starter</artifactId> <version>${mybatisplus-spring-boot-starter.version}</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus</artifactId> <version>${mybatisplus.version}</version> </dependency> <dependency> <groupId>org.apache.velocity</groupId> <artifactId>velocity</artifactId> <version>${velocity.version}</version> </dependency> <!-- mybatis-plus end --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.51</version> </dependency> </dependencies>
application.properties配置
spring.devtools.remote.restart.enabled=false spring.jdbc1.type=com.alibaba.druid.pool.DruidDataSource spring.jdbc1.driverClassName=com.mysql.jdbc.Driver spring.jdbc1.url=jdbc:mysql://localhost:3306/mazhq?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8 spring.jdbc1.username=root spring.jdbc1.password=123456 spring.jdbc1.connectionProperties=config.decrypt=true;druid.stat.slowSqlMillis=3000;druid.stat.logSlowSql=true;druid.stat.mergeSql=true spring.jdbc1.filters=stat spring.jdbc1.maxActive=100 spring.jdbc1.initialSize=1 spring.jdbc1.maxWait=15000 spring.jdbc1.minIdle=1 spring.jdbc1.timeBetweenEvictionRunsMillis=30000 spring.jdbc1.minEvictableIdleTimeMillis=180000 spring.jdbc1.validationQuery=SELECT 'x' spring.jdbc1.testWhileIdle=true spring.jdbc1.testOnBorrow=false spring.jdbc1.testOnReturn=false spring.jdbc1.poolPreparedStatements=false spring.jdbc1.maxPoolPreparedStatementPerConnectionSize=20 spring.jdbc1.removeAbandoned=true spring.jdbc1.removeAbandonedTimeout=600 spring.jdbc1.logAbandoned=false spring.jdbc1.connectionInitSqls= spring.jdbc2.type=com.alibaba.druid.pool.DruidDataSource spring.jdbc2.driverClassName=com.mysql.jdbc.Driver spring.jdbc2.url=jdbc:mysql://localhost:3306/liugh?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8 spring.jdbc2.username=root spring.jdbc2.password=123456 spring.jdbc2.connectionProperties=config.decrypt=true;druid.stat.slowSqlMillis=3000;druid.stat.logSlowSql=true;druid.stat.mergeSql=true spring.jdbc2.filters=stat spring.jdbc2.maxActive=100 spring.jdbc2.initialSize=1 spring.jdbc2.maxWait=15000 spring.jdbc2.minIdle=1 spring.jdbc2.timeBetweenEvictionRunsMillis=30000 spring.jdbc2.minEvictableIdleTimeMillis=180000 spring.jdbc2.validationQuery=SELECT 'x' spring.jdbc2.testWhileIdle=true spring.jdbc2.testOnBorrow=false spring.jdbc2.testOnReturn=false spring.jdbc2.poolPreparedStatements=false spring.jdbc2.maxPoolPreparedStatementPerConnectionSize=20 spring.jdbc2.removeAbandoned=true spring.jdbc2.removeAbandonedTimeout=600 spring.jdbc2.logAbandoned=false spring.jdbc2.connectionInitSqls= mybatis-plus.mapper-locations=classpath:/com/mazhq/web/mapper/xml/*Mapper.xml mybatis-plus.type-aliases-package=com.mazhq.web.entity #1:数据库ID自增 2:用户输入id 3:全局唯一id(IdWorker) 4:全局唯一ID(uuid) mybatis-plus.global-config.id-type=3 mybatis-plus.global-config.db-column-underline=true mybatis-plus.global-config.refresh-mapper=true mybatis-plus.configuration.map-underscore-to-camel-case=true #配置的缓存的全局开关 mybatis-plus.configuration.cache-enabled=true #延时加载的开关 mybatis-plus.configuration.lazy-loading-enabled=true #开启的话,延时加载一个属性时会加载该对象全部属性,否则按需加载属性 mybatis-plus.configuration.multiple-result-sets-enabled=true #打印sql语句,调试用 mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
分库分表最主要有几个配置:
1. 有多少个数据源 (2个:database0和database1)
@Data @ConfigurationProperties(prefix = "spring.jdbc1") public class ShardDataSource1 { private String driverClassName; private String url; private String username; private String password; private String filters; private int maxActive; private int initialSize; private int maxWait; private int minIdle; private int timeBetweenEvictionRunsMillis; private int minEvictableIdleTimeMillis; private String validationQuery; private boolean testWhileIdle; private boolean testOnBorrow; private boolean testOnReturn; private boolean poolPreparedStatements; private int maxPoolPreparedStatementPerConnectionSize; private boolean removeAbandoned; private int removeAbandonedTimeout; private boolean logAbandoned; private List<String> connectionInitSqls; private String connectionProperties; }
2. 用什么列进行分库以及分库算法
/** * @author mazhq * @date 2019/8/7 17:23 */ public class DataBaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> { @Override public String doEqualSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) { 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); } } } return result; } @Override public Collection<String> doBetweenSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) { Collection<String> result = new LinkedHashSet<>(databaseNames.size()); Range<Long> range = (Range<Long>) shardingValue.getValueRange(); for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) { for (String each : databaseNames) { if (each.endsWith(i % 2 + "")) { result.add(each); } } } return result; } }
3. 用什么列进行分表以及分表算法
/** * @author mazhq * @Title: TableShardingAlgorithm * @date 2019/8/12 16:40 */ public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> { @Override public String doEqualSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) { 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); } } } return result; } @Override public Collection<String> doBetweenSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) { Collection<String> result = new LinkedHashSet<>(tableNames.size()); Range<Long> range = (Range<Long>) shardingValue.getValueRange(); for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) { for (String each : tableNames) { if (each.endsWith(i % 2 + "")) { result.add(each); } } } return result; } }
4. 每张表的逻辑表名和所有物理表名和集成调用
/** * @author mazhq * @Title: DataSourceConfig * @date 2019/8/7 17:05 */ @Configuration @EnableTransactionManagement @ConditionalOnClass(DruidDataSource.class) @EnableConfigurationProperties({ShardDataSource1.class, ShardDataSource2.class}) public class DataSourceConfig { @Autowired private ShardDataSource1 dataSource1; @Autowired private ShardDataSource2 dataSource2; /** * 配置数据源0,数据源的名称最好要有一定的规则,方便配置分库的计算规则 * @return */ private DataSource db1() throws SQLException { return this.getDB1(dataSource1); } /** * 配置数据源1,数据源的名称最好要有一定的规则,方便配置分库的计算规则 * @return */ private DataSource db2() throws SQLException { return this.getDB2(dataSource2); } /** * 配置数据源规则,即将多个数据源交给sharding-jdbc管理,并且可以设置默认的数据源, * 当表没有配置分库规则时会使用默认的数据源 * @return */ @Bean public DataSourceRule dataSourceRule() throws SQLException { Map<String, DataSource> dataSourceMap = new HashMap<>(); dataSourceMap.put("dataSource0", this.db1()); dataSourceMap.put("dataSource1", this.db2()); return new DataSourceRule(dataSourceMap, "dataSource0"); } /** * 配置数据源策略和表策略,具体策略需要自己实现 * @param dataSourceRule * @return */ @Bean public ShardingRule shardingRule(@Qualifier("dataSourceRule") DataSourceRule dataSourceRule){ //具体分库分表策略 TableRule orderTableRule = TableRule.builder("t_user") .actualTables(Arrays.asList("t_user_0", "t_user_1")) .tableShardingStrategy(new TableShardingStrategy("order_id", new TableShardingAlgorithm())) .dataSourceRule(dataSourceRule) .build(); //绑定表策略,在查询时会使用主表策略计算路由的数据源,因此需要约定绑定表策略的表的规则需要一致,可以一定程度提高效率 List<BindingTableRule> bindingTableRuleList = new ArrayList<BindingTableRule>(); bindingTableRuleList.add(new BindingTableRule(Arrays.asList(orderTableRule))); return ShardingRule.builder().dataSourceRule(dataSourceRule) .tableRules(Arrays.asList(orderTableRule)) .bindingTableRules(bindingTableRuleList) .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new DataBaseShardingAlgorithm())) .tableShardingStrategy(new TableShardingStrategy("order_id", new TableShardingAlgorithm())) .build(); } /** * 创建sharding-jdbc的数据源DataSource,MybatisAutoConfiguration会使用此数据源 * @param shardingRule * @return * @throws SQLException */ @Bean public DataSource shardingDataSource(@Qualifier("shardingRule") ShardingRule shardingRule) throws SQLException { return ShardingDataSourceFactory.createDataSource(shardingRule); } private DruidDataSource getDB1(ShardDataSource1 shardDataSource1) throws SQLException { DruidDataSource ds = new DruidDataSource(); ds.setDriverClassName(shardDataSource1.getDriverClassName()); ds.setUrl(shardDataSource1.getUrl()); ds.setUsername(shardDataSource1.getUsername()); ds.setPassword(shardDataSource1.getPassword()); ds.setFilters(shardDataSource1.getFilters()); ds.setMaxActive(shardDataSource1.getMaxActive()); ds.setInitialSize(shardDataSource1.getInitialSize()); ds.setMaxWait(shardDataSource1.getMaxWait()); ds.setMinIdle(shardDataSource1.getMinIdle()); ds.setTimeBetweenEvictionRunsMillis(shardDataSource1.getTimeBetweenEvictionRunsMillis()); ds.setMinEvictableIdleTimeMillis(shardDataSource1.getMinEvictableIdleTimeMillis()); ds.setValidationQuery(shardDataSource1.getValidationQuery()); ds.setTestWhileIdle(shardDataSource1.isTestWhileIdle()); ds.setTestOnBorrow(shardDataSource1.isTestOnBorrow()); ds.setTestOnReturn(shardDataSource1.isTestOnReturn()); ds.setPoolPreparedStatements(shardDataSource1.isPoolPreparedStatements()); ds.setMaxPoolPreparedStatementPerConnectionSize( shardDataSource1.getMaxPoolPreparedStatementPerConnectionSize()); ds.setRemoveAbandoned(shardDataSource1.isRemoveAbandoned()); ds.setRemoveAbandonedTimeout(shardDataSource1.getRemoveAbandonedTimeout()); ds.setLogAbandoned(shardDataSource1.isLogAbandoned()); ds.setConnectionInitSqls(shardDataSource1.getConnectionInitSqls()); ds.setConnectionProperties(shardDataSource1.getConnectionProperties()); return ds; } private DruidDataSource getDB2(ShardDataSource2 shardDataSource2) throws SQLException { DruidDataSource ds = new DruidDataSource(); ds.setDriverClassName(shardDataSource2.getDriverClassName()); ds.setUrl(shardDataSource2.getUrl()); ds.setUsername(shardDataSource2.getUsername()); ds.setPassword(shardDataSource2.getPassword()); ds.setFilters(shardDataSource2.getFilters()); ds.setMaxActive(shardDataSource2.getMaxActive()); ds.setInitialSize(shardDataSource2.getInitialSize()); ds.setMaxWait(shardDataSource2.getMaxWait()); ds.setMinIdle(shardDataSource2.getMinIdle()); ds.setTimeBetweenEvictionRunsMillis(shardDataSource2.getTimeBetweenEvictionRunsMillis()); ds.setMinEvictableIdleTimeMillis(shardDataSource2.getMinEvictableIdleTimeMillis()); ds.setValidationQuery(shardDataSource2.getValidationQuery()); ds.setTestWhileIdle(shardDataSource2.isTestWhileIdle()); ds.setTestOnBorrow(shardDataSource2.isTestOnBorrow()); ds.setTestOnReturn(shardDataSource2.isTestOnReturn()); ds.setPoolPreparedStatements(shardDataSource2.isPoolPreparedStatements()); ds.setMaxPoolPreparedStatementPerConnectionSize( shardDataSource2.getMaxPoolPreparedStatementPerConnectionSize()); ds.setRemoveAbandoned(shardDataSource2.isRemoveAbandoned()); ds.setRemoveAbandonedTimeout(shardDataSource2.getRemoveAbandonedTimeout()); ds.setLogAbandoned(shardDataSource2.isLogAbandoned()); ds.setConnectionInitSqls(shardDataSource2.getConnectionInitSqls()); ds.setConnectionProperties(shardDataSource2.getConnectionProperties()); return ds; } }
接口测试代码
entity层
User.java
@Data @TableName("t_user") public class User extends Model<User> { private static final long serialVersionUID = 1L; /** * 主键id */ @TableId(value = "id", type = IdType.AUTO) private Long id; /** * 顺序编号 */ @TableField("order_id") private Long orderId; /** * 用户编号 */ @TableField("user_id") private Long userId; /** * 用户名 */ @TableField("user_name") private String userName; /** * 密码 */ @TableField("pass_word") private String passWord; /** * 倪名 */ @TableField("nick_name") private String nickName; @Override protected Serializable pkVal() { return this.id; } @Override public String toString() { return "User{" + "id=" + id + ", orderId=" + orderId + ", userId=" + userId + ", userName=" + userName + ", passWord=" + passWord + ", nickName=" + nickName + "}"; } }
mapper层
User.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.mazhq.web.mapper.UserMapper"> <!-- 通用查询映射结果 --> <resultMap id="BaseResultMap" type="com.mazhq.web.entity.User"> <id column="id" property="id" /> <result column="order_id" property="orderId" /> <result column="user_id" property="userId" /> <result column="user_name" property="userName" /> <result column="pass_word" property="passWord" /> <result column="nick_name" property="nickName" /> </resultMap> <!-- 通用查询结果列 --> <sql id="Base_Column_List"> id, order_id AS orderId, user_id AS userId, user_name AS userName, pass_word AS passWord, nick_name AS nickName </sql> </mapper>
UserMapper.java
/** * <p> * Mapper 接口 * </p> * * @author mazhq123 * @since 2019-08-20 */ public interface UserMapper extends BaseMapper<User> { }
service层
IUserService.java
/** * <p> * 服务类 * </p> * * @author mazhq123 * @since 2019-08-20 */ public interface IUserService extends IService<User> { }
UserServiceImpl.java
/** * <p> * 服务实现类 * </p> * * @author mazhq123 * @since 2019-08-20 */ @Service public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService { }
controller层
UserController.java
/** * <p> * 前端控制器 * </p> * @author mazhq123 * @since 2019-08-20 */ @RestController @RequestMapping("/web/user") public class UserController { @Autowired IUserService userService; @RequestMapping("/save") public String save(){ User user2 = new User(); for (int i = 0; i < 40; i++) { user2.setId((long)i); user2.setUserId((long)i); Random r = new Random(); user2.setOrderId((long)r.nextInt(100)); user2.setNickName("owenma"+i); user2.setPassWord("password"+i); user2.setUserName("userName"+i); userService.insert(user2); } return "success"; } @RequestMapping("/findAll") public String findAll(){ Wrapper<User> userWrapper = new Wrapper<User>() { @Override public String getSqlSegment() { return "order by order_id desc"; } }; return JSONObject.toJSONString(userService.selectList(userWrapper)); } }
测试方式:
先新增: http://localhost:8080/web/user/save
再查询: http://localhost:8080/web/user/findAll
作者:森林木马
-------------------------------------------
特此声明:所有评论和私信都会在第一时间回复。也欢迎朋友们指正错误,共同进步!
如果觉得这篇文章对你有小小的帮助的话,记得在右下角点个“推荐”哦,博主在此感谢!
个性签名:好记性不如勤随笔,好随笔还请多关注!