垂直分库操作
垂直分库其实是指按照业务将表进行分类,分布到不同的数据库上面,每个库可以放在不同的服务器
上,它的核心理念是专库专用。接下来看一下如何使用Sharding-JDBC实现垂直分库
1.建库建表 编写分片策略
CREATE DATABASE `user_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`user_id` bigint(20) NOT NULL COMMENT '用户id',
`fullname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户姓名', `user_type` char(1) DEFAULT NULL COMMENT '用户类型',
PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
spring.shardingsphere.datasource.names = m0,m1,m2,s0
spring.shardingsphere.datasource.m0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m0.driver-class-name = com.mysql.jdbc.Driver
spring.shardingsphere.datasource.m0.url = jdbc:mysql://172.16.14.128:3306/user_db?serverTimezone=Asia/Shanghai&characterEncoding=UTF-8&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull&useSSL=false
spring.shardingsphere.datasource.m0.username = root
spring.shardingsphere.datasource.m0.password = Vv12345!
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = m$->{0}.t_user
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column = user_id
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression = t_user
2.Dao层代码
package com.itheima.dbsharding.simple.dao;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;
import java.util.List;
import java.util.Map;
/**
* Created by jlm on 2020-05-12 19:26
*/
@Mapper
@Component
public interface UserDao {
/**
* 新增用户
* @param userId 用户id
* @param fullname 用户姓名 * @return
*/
@Insert("insert into t_user(user_id, fullname) value(#{userId},#{fullname})")
int insertUser(@Param("userId")Long userId, @Param("fullname")String fullname);
/**
* 根据id列表查询多个用户
* @param userIds 用户id列表 * @return
*/
@Select({"<script>",
" select",
" * ",
" from t_user t ",
" where t.user_id in",
"<foreach collection='userIds' item='id' open='(' separator=',' close=')'>",
"#{id}",
"</foreach>",
"</script>"
})
List<Map> selectUserbyIds(@Param("userIds") List<Long> userIds);
}
3.单元测试
@Test
public void testInsertUser(){
for (int i = 0 ; i<10; i++){
Long id = i + 1L;
userDao.insertUser(id,"姓名"+ id ); }
}
@Test
public void testSelectUserbyIds(){
List<Long> userIds = new ArrayList<>();
userIds.add(1L);
userIds.add(2L);
List<Map> users = userDao.selectUserbyIds(userIds);
System.out.println(users);
}
执行结果我们可以看到,对数据源m0进行操作,查询操作也是同样,这里就不贴了
至此我们已经实现了垂直分库操作,使得各业务库独立