- 读写分离特别适用于读多写少的场景下,通过分散读写到不同的数据库实例上来提高性能,缓解单机数据库的压力:
Name Remark
DQL 数据查询语言,比如select查询语句
DML 数据操纵语言,比如insert、delete、update更新语句
DDL 数据定义语言,比如create/drop/alter等语句
DCL 数据控制语言,比如grant/rollback/commit等语句
- Sharding-JDBC是一个开源的分布式数据库中间件解决方案。它在Java的JDBC层以对业务应用零侵入的方式额外提供数据分片,读写分离,柔性事务和分布式治理能力。并在其基础上提供封装了MySQL协议的服务端版本,用于完成对异构语言的支持。
- 基于JDBC的客户端版本定位为轻量级Java框架,使用客户端直连数据库,以jar包形式提供服务,无需额外部署和依赖,可理解为增强版的JDBC驱动,完全兼容JDBC和各种ORM框架。
- 封装了MySQL协议的服务端版本定位为透明化的MySQL代理端,可以使用任何兼容MySQL协议的访问客户端(如:MySQL Command
Client, MySQL Workbench等)操作数据,对DBA更加友好。
package com.programb.springboot.mybatis.shardingjdbc.masterslave;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class Application {
public static void main(String[] args) throws InterruptedException {
SpringApplication.run(Application.class, args);
Thread.sleep(Long.MAX_VALUE);
}
}
package com.programb.springboot.mybatis.shardingjdbc.masterslave.config;
import com.alibaba.druid.pool.DruidDataSource;
import com.programb.springboot.mybatis.shardingjdbc.masterslave.constants.DataSourceConstants;
import io.shardingjdbc.core.api.ShardingDataSourceFactory;
import io.shardingjdbc.core.api.config.MasterSlaveRuleConfiguration;
import io.shardingjdbc.core.api.config.ShardingRuleConfiguration;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;
public class DataSourceConfig {
private String url;
private String username;
private String password;
@Bean(name = "mybatisDataSource")
public DataSource getDataSource() throws SQLException {
Map<String, DataSource> dataSourceMap = new HashMap<>();
DataSource masterDataSource0 = mybatisDataSource("ds_master_0");
DataSource slaveDataSource00 = mybatisDataSource("ds_master_0_slave_0");
DataSource slaveDataSource01 = mybatisDataSource("ds_master_0_slave_1");
dataSourceMap.put("masterDataSource0", masterDataSource0);
dataSourceMap.put("slaveDataSource00", slaveDataSource00);
dataSourceMap.put("slaveDataSource01", slaveDataSource01);
DataSource masterDataSource1 = mybatisDataSource("ds_master_1");
DataSource slaveDataSource10 = mybatisDataSource("ds_master_1_slave_0");
DataSource slaveDataSource11 = mybatisDataSource("ds_master_1_slave_1");
dataSourceMap.put("masterDataSource1", masterDataSource1);
dataSourceMap.put("slaveDataSource10", slaveDataSource10);
dataSourceMap.put("slaveDataSource11", slaveDataSource11);
// 构建读写分离配置
MasterSlaveRuleConfiguration masterSlaveRuleConfig0 = new MasterSlaveRuleConfiguration();
masterSlaveRuleConfig0.setName("ds_0");
masterSlaveRuleConfig0.setMasterDataSourceName("masterDataSource0");
masterSlaveRuleConfig0.getSlaveDataSourceNames().add("slaveDataSource00");
masterSlaveRuleConfig0.getSlaveDataSourceNames().add("slaveDataSource01");
MasterSlaveRuleConfiguration masterSlaveRuleConfig1 = new MasterSlaveRuleConfiguration();
masterSlaveRuleConfig1.setName("ds_1");
masterSlaveRuleConfig1.setMasterDataSourceName("masterDataSource1");
masterSlaveRuleConfig1.getSlaveDataSourceNames().add("slaveDataSource10");
masterSlaveRuleConfig1.getSlaveDataSourceNames().add("slaveDataSource11");
// 通过ShardingSlaveDataSourceFactory继续创建ShardingDataSource
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getMasterSlaveRuleConfigs().add(masterSlaveRuleConfig0);
shardingRuleConfig.getMasterSlaveRuleConfigs().add(masterSlaveRuleConfig1);
DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig);
return dataSource;
}
private DataSource mybatisDataSource(final String dataSourceName) throws SQLException {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(DataSourceConstants.DRIVER_CLASS);
dataSource.setUrl(String.format(url, dataSourceName));
dataSource.setUsername(username);
dataSource.setPassword(password);
/* 配置初始化大小、最小、最大 */
dataSource.setInitialSize(1);
dataSource.setMinIdle(1);
dataSource.setMaxActive(20);
/* 配置获取连接等待超时的时间 */
dataSource.setMaxWait(60000);
/* 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 */
dataSource.setTimeBetweenEvictionRunsMillis(60000);
/* 配置一个连接在池中最小生存的时间,单位是毫秒 */
dataSource.setMinEvictableIdleTimeMillis(300000);
dataSource.setValidationQuery("SELECT 'x'");
dataSource.setTestWhileIdle(true);
dataSource.setTestOnBorrow(false);
dataSource.setTestOnReturn(false);
dataSource.setPoolPreparedStatements(false);
dataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
/* 配置监控统计拦截的filters */
dataSource.setFilters("stat,wall,log4j");
return dataSource;
}
@Bean(name = "mybatisTransactionManager")
public DataSourceTransactionManager mybatisTransactionManager(@Qualifier("mybatisDataSource") DataSource dataSource) throws SQLException {
return new DataSourceTransactionManager(dataSource);
}
@Bean(name = "mybatisSqlSessionFactory")
public SqlSessionFactory mybatisSqlSessionFactory(@Qualifier("mybatisDataSource") DataSource mybatisDataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(mybatisDataSource);
return sessionFactory.getObject();
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
package com.programb.springboot.mybatis.shardingjdbc.masterslave.constants;
public abstract class DataSourceConstants {
public static final String DATASOURCE_PREFIX = "mybatis.datasource";
public static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
public static final String MAPPER_PACKAGE = "com.programb.springboot.mybatis.shardingjdbc.masterslave.mapper";
}
package com.programb.springboot.mybatis.shardingjdbc.masterslave.constants;
public abstract class SqlConstants {
public static final String USER_CREATE_SQL = "CREATE TABLE IF NOT EXISTS `t_user` (`id` bigint(20) AUTO_INCREMENT, `user_id` bigint(20) NOT NULL COMMENT '用户id',`city_id` int(11) DEFAULT NULL COMMENT '城市id',`user_name` varchar(15) DEFAULT NULL,`age` int(11) DEFAULT NULL COMMENT '年龄',`birth` date DEFAULT NULL COMMENT '生日',PRIMARY KEY (`id`))";
public static final String GET_ALL_SQL = "SELECT * FROM t_user";
public static final String GET_ONE_SQL = "SELECT * FROM t_user WHERE user_id = #{userId}";
public static final String GET_LIMIT_ONE_SQL = "SELECT * FROM t_user LIMIT 1";
public static final String INSERT_SLAVE_SQL = "INSERT INTO t_user(city_id,user_name,age,birth) VALUES(#{cityId}, #{userName}, #{age}, #{birth})";
public static final String UPDATE_SQL = "UPDATE t_user SET user_name=#{userName},age=#{age},birth=#{birth} WHERE user_id =#{userId}";
public static final String DELETE_SQL = "DELETE FROM t_user WHERE user_id =#{userId}";
public static final String DELETE_ALL_SQL = "DELETE FROM t_user";
}
package com.programb.springboot.mybatis.shardingjdbc.masterslave.druid;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class DruidStatsConfig {
@Bean
public ServletRegistrationBean registrationBean() {
ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid-stats/*");
//白名单
bean.addInitParameter("allow", "127.0.0.1");
//IP黑名单 (存在共同时,deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
bean.addInitParameter("deny", "192.168.125.12");
//登录查看信息的账号密码.
bean.addInitParameter("loginUsername", "programb");
bean.addInitParameter("loginPassword", "springboot");
//是否能够重置数据.
bean.addInitParameter("resetEnable", "false");
return bean;
}
@Bean
public FilterRegistrationBean druidStatFilter() {
FilterRegistrationBean bean = new FilterRegistrationBean(new WebStatFilter());
//添加过滤规则.
bean.addUrlPatterns("/*");
//添加不需要忽略的格式信息.
bean.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid-stats/*");
return bean;
}
}
package com.programb.springboot.mybatis.shardingjdbc.masterslave.entity;
import java.io.Serializable;
import java.util.Date;
public class UserEntity implements Serializable {
private Integer id;
private Long userId;
private Integer cityId;
private String userName;
private Integer age;
private Date birth;
private static final long serialVersionUID = 1L;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Long getUserId() {
return userId;
}
public void setUserId(Long userId) {
this.userId = userId;
}
public Integer getCityId() {
return cityId;
}
public void setCityId(Integer cityId) {
this.cityId = cityId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
@Override
public String toString() {
return "UserEntity{" +
"id=" + id +
", userId=" + userId +
", cityId=" + cityId +
", userName='" + userName + '\'' +
", age=" + age +
", birth=" + birth +
'}';
}
}
package com.programb.springboot.mybatis.shardingjdbc.masterslave.mapper;
import org.apache.ibatis.annotations.Mapper;
import com.programb.springboot.mybatis.shardingjdbc.masterslave.entity.UserEntity;
@Mapper
public interface UserMapper {
void createIfNotExistsTable();
void truncateTable();
Long insert(UserEntity model);
UserEntity find(Long userId);
void delete(Long userId);
void dropTable();
}
package com.programb.springboot.mybatis.shardingjdbc.masterslave.mapper;
import org.apache.ibatis.annotations.*;
import com.programb.springboot.mybatis.shardingjdbc.masterslave.constants.SqlConstants;
import com.programb.springboot.mybatis.shardingjdbc.masterslave.entity.UserEntity;
import java.util.List;
public interface UserRepository {
@Select(SqlConstants.GET_ALL_SQL)
@Results(id = "userResultMap", value = {
@Result(property = "userId", column = "user_id"),
@Result(property = "cityId", column = "city_id"),
@Result(property = "userName", column = "user_name"),
@Result(property = "age", column = "age"),
@Result(property = "birth", column = "birth")
})
List<UserEntity> selectAll();
@Select(SqlConstants.GET_ONE_SQL)
@ResultMap(value = "userResultMap")
UserEntity find(Long userId);
@Insert(SqlConstants.INSERT_SLAVE_SQL)
@Options(useGeneratedKeys = true, keyProperty = "userId")
int insert(UserEntity user);
@Update(SqlConstants.UPDATE_SQL)
int update(UserEntity user);
@Delete(SqlConstants.DELETE_SQL)
int delete(Long userId);
@Delete(SqlConstants.DELETE_ALL_SQL)
void truncate();
}
spring.application.name=spring-boot-mybatis-sharding-jdbc-masterslave
server.context-path=/springboot
mybatis.config-location=classpath:mybatis-config.xml
mybatis.datasource.url=jdbc:mysql://127.0.0.1:3306/%s?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
mybatis.datasource.username=travis
mybatis.datasource.password=
sharding.jdbc.datasource.names=ds_master_0,ds_master_1,ds_master_0_slave_0,ds_master_0_slave_1,ds_master_1_slave_0,ds_master_1_slave_1
sharding.jdbc.datasource.ds_master_0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds_master_0.driverClassName=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_0.url=jdbc:mysql://127.0.0.1:3306/ds_master_0?useSSL=false
sharding.jdbc.datasource.ds_master_0.username=travis
sharding.jdbc.datasource.ds_master_0.password=
sharding.jdbc.datasource.ds_master_0_slave_0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds_master_0_slave_0.driverClassName=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_0_slave_0.url=jdbc:mysql://127.0.0.1:3306/ds_master_0_slave_0?useSSL=false
sharding.jdbc.datasource.ds_master_0_slave_0.username=travis
sharding.jdbc.datasource.ds_master_0_slave_0.password=
sharding.jdbc.datasource.ds_master_0_slave_1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds_master_0_slave_1.driverClassName=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_0_slave_1.url=jdbc:mysql://127.0.0.1:3306/ds_master_0_slave_1?useSSL=false
sharding.jdbc.datasource.ds_master_0_slave_1.username=travis
sharding.jdbc.datasource.ds_master_0_slave_1.password=
sharding.jdbc.datasource.ds_master_1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds_master_1.driverClassName=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_1.url=jdbc:mysql://127.0.0.1:3306/ds_master_1?useSSL=false
sharding.jdbc.datasource.ds_master_1.username=travis
sharding.jdbc.datasource.ds_master_1.password=
sharding.jdbc.datasource.ds_master_1_slave_0.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds_master_1_slave_0.driverClassName=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_1_slave_0.url=jdbc:mysql://127.0.0.1:3306/ds_master_1_slave_0?useSSL=false
sharding.jdbc.datasource.ds_master_1_slave_0.username=travis
sharding.jdbc.datasource.ds_master_1_slave_0.password=
sharding.jdbc.datasource.ds_master_1_slave_1.type=com.alibaba.druid.pool.DruidDataSource
sharding.jdbc.datasource.ds_master_1_slave_1.driverClassName=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds_master_1_slave_1.url=jdbc:mysql://127.0.0.1:3306/ds_master_1_slave_1?useSSL=false
sharding.jdbc.datasource.ds_master_1_slave_1.username=travis
sharding.jdbc.datasource.ds_master_1_slave_1.password=
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=city_id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds_${city_id % 2}
sharding.jdbc.config.sharding.tables.t_user.actualDataNodes=ds_${0..1}.t_user_${0..1}
sharding.jdbc.config.sharding.tables.t_user.tableStrategy.inline.shardingColumn=user_id
sharding.jdbc.config.sharding.tables.t_user.tableStrategy.inline.algorithmExpression=t_user_${user_id % 2}
sharding.jdbc.config.sharding.tables.t_user.keyGeneratorColumnName=user_id
sharding.jdbc.config.sharding.master-slave-rules.ds_0.masterDataSourceName=ds_master_0
sharding.jdbc.config.sharding.master-slave-rules.ds_0.slaveDataSourceNames=ds_master_0_slave_0, ds_master_0_slave_1
sharding.jdbc.config.sharding.master-slave-rules.ds_1.masterDataSourceName=ds_master_1
sharding.jdbc.config.sharding.master-slave-rules.ds_1.slaveDataSourceNames=ds_master_1_slave_0, ds_master_1_slave_1
#logging.level.com.alibaba.druid=debug
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<package name="com.programb.springboot.mybatis.shardingjdbc.masterslave.entity"/>
</typeAliases>
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
</configuration>
<?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">
<parent>
<artifactId>spring-boot-programb-examples</artifactId>
<groupId>com.programb</groupId>
<version>0.0.1-SNAPSHOT</version>
</parent>
<modelVersion>4.0.0</modelVersion>
<artifactId>spring-boot-mybatis-sharding-jdbc-masterslave</artifactId>
<dependencies>
<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>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
</dependency>
<dependency>
<groupId>io.shardingjdbc</groupId>
<artifactId>sharding-jdbc-core</artifactId>
</dependency>
<dependency>
<groupId>io.shardingjdbc</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.jacoco</groupId>
<artifactId>jacoco-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>