水平分库分表：
1工具：idea，技术：springboot+mybatis+shardingsphere
2 主库为es0和es1,逻辑表为t_user，（就是数据库操作语句中写这个表，然后具体的表名在配置文件中写，例如，t_user_1,t_user_2）
3 创建数据库表：(正常id应该为Long类型，因为id是从外部加入的，避免多个库中受自增主键影响),分别在2个库中创建以下2个表
CREATE TABLE t_user0(
id BIGINT(20) NOT NULL AUTO_INCREMENT,
user_name VARCHAR(32) NOT NULL,
pass_word VARCHAR(32) NOT NULL,
age INT(11) NOT NULL,
PRIMARY KEY(id)
)
CREATE TABLE t_user1(
id BIGINT(20) NOT NULL AUTO_INCREMENT,
user_name VARCHAR(32) NOT NULL,
pass_word VARCHAR(32) NOT NULL,
age INT(11) NOT NULL,
PRIMARY KEY(id)
)
4pom.xml配置文件
<properties>
<!--设置jdk1.8，不然会报数据源过时警告-->
<java.version>1.8</java.version>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<!--定义包的版本-->
<springframework.boot.version>2.1.3.RELEASE</springframework.boot.version>
<sharding-sphere.version>3.1.0</sharding-sphere.version>
</properties>
<dependencies>
<!--springboot包-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>${springframework.boot.version}</version>
</dependency>
<!--springboo测试包-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>${springframework.boot.version}</version>
<scope>test</scope>
</dependency>
<!-mybatis与springboot结合包--->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<！--shardingsphere包-->
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<！--mysql包-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<！--插件lombok包-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
</dependencies>
5 新建bean包，在包下新建User实体类
package com.bean;
import lombok.Data;
import java.io.Serializable;
@Data
public class User implements Serializable {
private Long id;
private String user_name;
private String pass_word;
private int age;
}
6 新建mapper包，在包下新建UserRespoistory类
package com.mapper;
import com.bean.User;
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.Repository;
import java.util.List;
@Mapper
public interface UserRespoistory {
/**
* 添加
*/
@Insert({
" INSERT INTO t_user (id,user_name, pass_word,age) ",
" VALUES ( ",
"#{id,jdbcType=INTEGER}, ",
"#{user_name,jdbcType=VARCHAR}, ",
"#{pass_word,jdbcType=VARCHAR}, ",
"#{age,jdbcType=INTEGER})"
})
int addUser(@Param("id")Long id,
@Param("user_name")String user_name,
@Param("pass_word")String pass_word,
@Param("age")Integer age);
/**
* 查询
*/
@Select({
"select id,user_name,pass_word,age ",
"from t_user "
})
List<User> getAllUsers();
}
7新建service包，在包下新建ServiceImp包和UserService类，在ServiceImp包下新建UserviceImp类
package com.service;
import com.bean.User;
import com.util.ResponseUser;
import org.apache.ibatis.annotations.Param;
public interface UserService {
ResponseUser addUser(User user);
ResponseUser getAllUsers();
}
//UserviceImp类代码
package com.service.ServiceImp;
import com.bean.User;
import com.mapper.UserRespoistory;
import com.service.UserService;
import com.util.ResponseUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
@Service
public class UserviceImp implements UserService {
@Resource
private UserRespoistory userRespoistory;
@Autowired
private ResponseUser responseUser;
/**
* 添加user
* @param user
* @return
*/
public ResponseUser addUser(User user) {
int count=userRespoistory.addUser(
user.getId(),user.getUser_name(),user.getPass_word(),user.getAge());
if(count==0)
responseUser.setAll(1,"添加失败",0);
else
responseUser.setAll(0,"添加成功",count);
return responseUser;
}
/**
* 得到所有user
* @return
*/
public ResponseUser getAllUsers() {
List<User> users= userRespoistory.getAllUsers();
if(users.size()==0)
responseUser.setAll(1,"查询失败",null);
else
responseUser.setAll(0,"查询成功",users);
return responseUser;
}
}
8 新建启动类
package com;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan(basePackages = "com.mapper")
public class SpringSharding0403 {
public static void main(String[] args){
SpringApplication.run(SpringSharding0403.class);
}
}
9新建util包，在包下新建ResponseUser类
package com.util;
import lombok.Data;
import org.springframework.stereotype.Repository;
/**
* 用于装响应的信息
*/
@Data
@Repository
public class ResponseUser {
private int code;
private String msg;
private Object data;
/**
* 传参
*/
public ResponseUser setAll(int code,String msg,Object data){
this.setCode(code);
this.setMsg(msg);
this.setData(data);
return this;
}
}
10重头戏，在resources文件夹下新建application.properties文件
server.port=8011
# 数据源 es0,es1 就是2个数据库
sharding.jdbc.datasource.names=es0,es1
# 第一个数据库的配置参数，每个数据库都如此配置
sharding.jdbc.datasource.es0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.es0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.es0.jdbc-url=jdbc:mysql://localhost:3306/es0?characterEncoding=utf-8&&serverTimezone=GMT%2B8
sharding.jdbc.datasource.es0.username=root
sharding.jdbc.datasource.es0.password=1234
# 第二个数据库的配置参数，每个数据库都如此配置
sharding.jdbc.datasource.es1.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.es1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.es1.jdbc-url=jdbc:mysql://localhost:3306/es1?characterEncoding=utf-8&&serverTimezone=GMT%2B8
sharding.jdbc.datasource.es1.username=root
sharding.jdbc.datasource.es1.password=1234
# 水平拆分的数据库（表） 配置分库 + 分表策略 行表达式分片策略
# 分库策略,根据什么条件分库？这里用存入的id对2取余，判断存入哪个库，判断后的库名有es0和es1
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=es$->{id % 2}
# 分表策略 其中t_user为逻辑表 分表主要取决于age行，根据什么条件分表的？根据age对2取余来判断存入的数据放到哪个表
sharding.jdbc.config.sharding.tables.t_user.actual-data-nodes=es$->{0..1}.t_user$->{0..1}
sharding.jdbc.config.sharding.tables.t_user.table-strategy.inline.sharding-column=age
# 分片算法表达式 根据什么条件判断存入的数据该存在哪个表？判断后的表名有t_use0和t_use1
sharding.jdbc.config.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user$->{age % 2}
# 打印执行的数据库以及语句
sharding.jdbc.config.props.sql.show=true
# springboot中此设置项默认为false，相同的bean不会覆盖之前的bean，咱们设置成true
spring.main.allow-bean-definition-overriding=true
11，新建controller包，在包下新建
package com.controller;
import com.bean.User;
import com.service.ServiceImp.UserviceImp;
import com.service.UserService;
import com.util.ResponseUser;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;
@RestController
public class UserController {
@Autowired
private UserviceImp userService;
@GetMapping("/users")
public ResponseUser list() {
return userService.getAllUsers();
}
@GetMapping("/add")
public ResponseUser add(User user) {
return userService.addUser(user);
}
}
903
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
