Spring整合ShardingJDBC实现MySQL读写分离

1. 首先需要搭建MySQL读写分离的服务器环境

   1. 安装MySQL参见 CentOS下安装MySQL5.7(图文)
   2. 搭建MySQL读写分离参见 ContOS下搭建MySQL主从复制

2. 新建一个Maven项目spring-sharding-masterslave

3. 引入相关的依赖

   <!-- sharding的核心包 -->
   <dependency>
       <groupId>org.apache.shardingsphere</groupId>
       <artifactId>sharding-jdbc-core</artifactId>
       <version>4.0.0-RC1</version>
   </dependency>
   
   <!-- sharding整合spring依赖 -->
   <dependency>
       <groupId>org.apache.shardingsphere</groupId>
       <artifactId>sharding-jdbc-spring-namespace</artifactId>
       <version>4.0.0-RC1</version>
   </dependency>
   
   <!--spring的依赖包-->
   <dependency>
       <groupId>org.springframework</groupId>
       <artifactId>spring-context</artifactId>
       <version>5.1.6.RELEASE</version>
   </dependency>
   
   <!--jdbc驱动-->
   <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>5.1.47</version>
   </dependency>
   
   <!--HikariCP数据库连接池-->
   <dependency>
       <groupId>com.zaxxer</groupId>
       <artifactId>HikariCP</artifactId>
       <version>3.2.0</version>
   </dependency>
   
   <!-- 单元测试 -->
   <dependency>
       <groupId>org.springframework</groupId>
       <artifactId>spring-test</artifactId>
       <version>5.1.6.RELEASE</version>
   </dependency>
   
   <dependency>
       <groupId>junit</groupId>
       <artifactId>junit</artifactId>
       <version>4.12</version>
   </dependency>
   
   <dependency>
         <groupId>ch.qos.logback</groupId>
         <artifactId>logback-classic</artifactId>
         <version>1.2.3</version>
   </dependency>
   

4. 在resources添加Spring的配置文件applicationContext.xml

   <?xml version="1.0" encoding="UTF-8"?>
   <beans xmlns="http://www.springframework.org/schema/beans"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xmlns:master-slave="http://shardingsphere.apache.org/schema/shardingsphere/masterslave"
          xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://shardingsphere.apache.org/schema/shardingsphere/encrypt http://shardingsphere.apache.org/schema/shardingsphere/encrypt/encrypt.xsd http://shardingsphere.apache.org/schema/shardingsphere/sharding http://shardingsphere.apache.org/schema/shardingsphere/sharding/sharding.xsd http://shardingsphere.apache.org/schema/shardingsphere/masterslave http://shardingsphere.apache.org/schema/shardingsphere/masterslave/master-slave.xsd">
   
       <!--数据库0 主库-->
       <bean id="ds0" class="com.zaxxer.hikari.HikariDataSource">
           <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
           <property name="jdbcUrl" value="jdbc:mysql://192.168.9.174:3306/test?useSSL=false"/>
           <property name="username" value="root"/>
           <property name="password" value="root"/>
       </bean>
   
       <!--数据库0 从库-->
       <bean id="ds1" class="com.zaxxer.hikari.HikariDataSource">
           <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
           <property name="jdbcUrl" value="jdbc:mysql://192.168.9.184:3306/test?useSSL=false"/>
           <property name="username" value="root"/>
           <property name="password" value="root"/>
       </bean>
   
       <!--
           定义了shardingJDBC主从(读写分离)数据源
           ds0 是主服务器，用于写入操作
           ds0,ds1  是从服务器，用于读取操作
   
       -->
       <master-slave:data-source id="msDataSource" master-data-source-name="ds0" slave-data-source-names="ds0,ds1">
           <master-slave:props>
               <prop key="sql.show">true</prop>
           </master-slave:props>
       </master-slave:data-source>
   </beans>
   

   此时使用ShardingJDBC搭建读写分离的配置已经完成

5. 在test下新建一个测试类ShardingTest，先测试数据写入的操作

   import org.junit.Test;
   import org.junit.runner.RunWith;
   import org.springframework.test.context.ContextConfiguration;
   import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
   
   import javax.annotation.Resource;
   import javax.sql.DataSource;
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.SQLException;
   
   @RunWith(SpringJUnit4ClassRunner.class)
   @ContextConfiguration(locations = {"classpath:applicationContext.xml"})
   public class ShardingTest {
   
       @Resource(name = "msDataSource")
       private DataSource dataSource;
   
       /**
        * 利用ShardingJDBC的读写分离进行写入数据的操作
        * @throws SQLException
        */
       @Test
       public void testInsert() throws SQLException {
           Connection connection = dataSource.getConnection();
           String sql = "INSERT INTO t_user (id, username, password, birthday) VALUES (null, 'test insert', '134', '2019-07-23 14:52:05')";
           PreparedStatement preparedStatement = connection.prepareStatement(sql);
           preparedStatement.executeUpdate();
           preparedStatement.close();
           connection.close();
       }
   
   }
   
   

   通过查看日志，我们发现不管进行多少次的写入操作，一直使用的是ds0数据库进行写入操作，如下图

   

6. 在test下新建一个测试类ShardingTest，测试数据读取的操作

   import org.junit.Test;
   import org.junit.runner.RunWith;
   import org.springframework.test.context.ContextConfiguration;
   import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
   
   import javax.annotation.Resource;
   import javax.sql.DataSource;
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.SQLException;
   
   @RunWith(SpringJUnit4ClassRunner.class)
   @ContextConfiguration(locations = {"classpath:applicationContext.xml"})
   public class ShardingTest {
   
       @Resource(name = "msDataSource")
       private DataSource dataSource;
   
       /**
        * 利用ShardingJDBC的读写分离进行数据读取的操作
        * @throws SQLException
        */
       @Test
       public void selectTest() throws SQLException {
           Connection connection = dataSource.getConnection();
           String sql = "select * from t_user";
           for (int i = 0; i < 10; i++) {
               PreparedStatement preparedStatement = connection.prepareStatement(sql);
               preparedStatement.executeQuery();
               preparedStatement.close();
           }
           connection.close();
       }
   }
   
   

   通过查看日志，我们发现随着不同次数的查询，执行查询的数据库在ds0和ds1之间一直切换，如下图

   

7. 至此，Spring整合ShardingJDBC实现读写分离的功能已经完全实现。

8. 相关的测试代码详见 Spring整合ShardingJDBC实现MySQL读写分离演示代码