springboot集成sharding-jdbc

          sharding-jdbc是当当开源的第三方分库分表插件，目前属于Apache顶级开源项目shardingsphere的一部分，官网Apache ShardingSphere ，  sharding-jdbc支持垂直分库，水平分库，垂直分表，水平分表，今天的实验是使用MySQL做主备，主库写，改，删，备库只做查询。

在windows下面实验

第一步：mysql主备设置

安装mysql MySQL Server 5.5 用来做主库

         copy一份 MySQL Server 5.5_slave1 用来做备库

mysql主库配置my.ini

#主库设置

[mysqld]

#开启日志

log-bin=mysql-bin

#设置服务id

server-id=1

#设置需要同步的数据

binlog-do-db=study

#屏蔽系统库同步

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

#从库设置

[mysqld]

#开启日志

log-bin=mysql-bin

#设置服务id

server-id=2

#设置需要同步的数据

binlog-do-db=study.%

#屏蔽系统库同步

binlog-ignore-db=mysql.%

binlog-ignore-db=information_schema.%

binlog-ignore-db=performance_schema.%

设置好了以后，ctrl+alt+del先停止之前的mysqld.exe，再双击重启主和备库。

通过客户端dbvisualizer连上主库master

    --授权主备复制专用账号db_sync

    GRANT REPLICATION SLAVE ON *.* TO 'db_sync'@'%' IDENTIFIED BY 'db_sync';

    --刷新权限

    FLUSH PRIVILEGES;

    --mysql.user里面已经创建db_sync

    select * from mysql.user;

    --查看主库状态

SHOW MASTER STATUS;

 --通过dbvisualizer登录备库slave，设置master

    --停止同步

    STOP SLAVE;

    --设置主库

    CHANGE MASTER TO

    MASTER_HOST='localhost',

    MASTER_USER='db_sync',

    MASTER_PASSWORD='db_sync',

    MASTER_LOG_FILE='mysql-bin.000001',

    MASTER_LOG_POS=338;

    --启动同步

    START SLAVE;

    --检查备库状态

    SHOW SLAVE STATUS;

第二步：创建数据库和表

CREATE DATABASE study;

CREATE TABLE
    t_user
    (
        userId INT NOT NULL AUTO_INCREMENT,
        userName VARCHAR(255),
        password VARCHAR(255),
        tel VARCHAR(255),
        sex VARCHAR(255),
        description VARCHAR(255),
        PRIMARY KEY (userId)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;

第三步：创建springboot项目

pom.xml依赖

<?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 https://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.7.0</version>
		<relativePath /> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.figo</groupId>
	<artifactId>ShardingJDBCTest</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>ShardingJDBCTest</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-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
		</dependency>

		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.0.0</version>
		</dependency>
		<!-- <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> 
			<version>1.1.20</version> </dependency> -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.20</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>

		<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
			<version>4.0.0-RC1</version>
		</dependency>


	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
			<plugin>
				<groupId>org.mybatis.generator</groupId>
				<artifactId>mybatis-generator-maven-plugin</artifactId>
				<configuration>
					<verbose>true</verbose>
					<overwrite>true</overwrite>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

application.properties配置sharding-jdbc数据源

#root
server.servlet.context-path=/ShardingJDBCTest
#port
server.port=9091

#mybatis
#mybatis.config-location=classpath:mybatis/mybatis-config.xml

spring.main.allow-bean-definition-overriding = true

mybatis.configuration.map-underscore-to-camel-case = true

#sharding-jdbc分片规则配置
#数据源
spring.shardingsphere.datasource.names = m0,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.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m0.url =jdbc:mysql://localhost:3306/study?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.shardingsphere.datasource.m0.username = root
spring.shardingsphere.datasource.m0.password = 123456
#因为在同一台机器实验，那么端口号改一下
spring.shardingsphere.datasource.s0.type = com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.s0.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.s0.url =jdbc:mysql://localhost:3307/study?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.shardingsphere.datasource.s0.username = root
spring.shardingsphere.datasource.s0.password = 123456


# 主库从库逻辑数据源定义 ds0为user_db
spring.shardingsphere.sharding.master-slave-rules.ds0.master-data-source-name=m0
spring.shardingsphere.sharding.master-slave-rules.ds0.slave-data-source-names=s0

# 指定t_order表的数据分布情况，配置数据节点 m1.t_order_1,m1.t_order_2,m2.t_order_1,m2.t_order_2
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes = ds0.t_user



spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column = userId
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression = t_user


# 打开sql输出日志
spring.shardingsphere.props.sql.show = true


logging.level.root = info
logging.level.org.springframework.web = info
logging.level.com.itheima.dbsharding  = debug
logging.level.druid.sql = debug

第四步：DAO层编写

package com.figo.test.mapper;

import com.figo.test.model.TUser;
import java.util.List;
import java.util.Map;

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;
@Mapper
@Component
public interface TUserMapper {

    
    
    /**
     * 新增用户
     * @param userId 用户id
     * @param userName 用户姓名
     * @return
     */
    @Insert("insert into t_user(userId, userName) value(#{userId},#{userName})")
    int insertUser(@Param("userId")Integer userId, @Param("userName")String userName);

    /**
     * 根据id列表查询多个用户
     * @param userIds 用户id列表
     * @return
     */
    @Select({"<script>",
            " select",
            " * ",
            " from t_user t ",
            " where t.userId in",
            "<foreach collection='userIds' item='id' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    List<Map> selectUserbyIds(@Param("userIds") List<Integer> userIds);

    /**
     * 根据id列表查询多个用户
     * @param userIds 用户id列表
     * @return
     */
    @Select({"<script>",
            " select",
            " * ",
            " from t_user t ,t_dict b",
            " where t.user_type = b.code and t.user_id in",
            "<foreach collection='userIds' item='id' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    List<Map> selectUserInfobyIds(@Param("userIds") List<Long> userIds);
}

第五步：编写单元测试

/**
 * 
 */
package com.figo.test.daotest;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.figo.test.ShardingJdbcTestApplication;
import com.figo.test.mapper.TUserMapper;
import com.figo.test.model.TUser;
/**
 * @author figo
 *
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = {ShardingJdbcTestApplication.class})
public class ShardingJDBCUnitTest {

	@Autowired
	TUserMapper userMapper;
	

	
	@Test
	public void insertUser()
	{
		userMapper.insertUser(102, "jacky");
	
	}

	
	@Test
	public void selectUserByIds()
	{
		List<Integer> userIds=new ArrayList<Integer>();
		userIds.add(1);
		userIds.add(12);
		List<Map> users=userMapper.selectUserbyIds(userIds);
		if(users!=null)
		{
			System.out.println(users.toString());
		}else
		{
			System.out.println("无数据");
		}
	
	}
}

通过实验可以看到，插入操作会自动入主库m0，查询自动轮询到备库s0。另外通过观察数据库，发现mysql主库发生的变动会通过binlog同步到备库。

//逻辑SQL

Logic SQL: insert into t_user(userId, userName) value(?,?)

//实际SQL

Actual SQL: m0 ::: insert into t_user (userId, userName) VALUES (?, ?) 

Logic SQL: select  *   from t_user t   where t.userId in  (   ?  ,  ?  )

Actual SQL: s0 ::: select  *   from t_user t   where t.userId in  (   ?  ,  ?  )