上篇文章PostgreSQL主从数据库搭建已经搭建了主从数据库，同时能够实现流复制热备，现在就要把它的优势发挥出来。借助当当网开源的ShardingJDBC，来实现后台数据服务的读写分离。
让我们看一下ShardingJDBC的特性

支持项

1.提供了一主多从的读写分离配置，可配合分库分表使用。
2.同一线程且同一数据库连接内，如有写入操作，以后的读操作均从主库读取，用于保证数据一致性。
3.Spring命名空间。
4.基于Hint的强制主库路由。

不支持范围

1.主库和从库的数据同步。
2.主库和从库的数据同步延迟导致的数据不一致。
3.主库双写或多写。

而上篇我们通过PostgreSQL搭建的双机热备、数据同步正好弥补了其不支持范围的第一项（PostgreSQL双机热备数据同步时效还有待研究）。

1.新建一个Spring Boot工程，添加必要的依赖，其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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.dhl</groupId>
    <artifactId>shardingjdbcdemo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>shardingjdbcdemo</name>
    <description>Demo project for ShardingJDBC Demo</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.7.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <sharding-jdbc.version>1.5.4.1</sharding-jdbc.version>
        <mybatis-spring-boot-starter.version>1.1.1</mybatis-spring-boot-starter.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <!--支持面向方面的编程即AOP，包括spring-aop和AspectJ-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis-spring-boot-starter.version}</version>
        </dependency>

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

        <!-- 引入sharding-jdbc核心模块 -->
        <dependency>
            <groupId>com.dangdang</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>${sharding-jdbc.version}</version>
        </dependency>
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

2.定义DataSource

package com.dhl.shardingjdbc.config;

import com.dangdang.ddframe.rdb.sharding.api.MasterSlaveDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.strategy.slave.MasterSlaveLoadBalanceStrategyType;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 * Created by daihl on 2017/9/22.
 */
@Configuration
public class DataSourceConfig {
    @Bean(name = "shardingDataSource")
    @ConfigurationProperties(prefix="spring.datasource")
    public DataSource getDataSource() throws SQLException {
        return buildDataSource();
    }

    private DataSource buildDataSource() throws SQLException {
        BasicDataSource masterDataSource0 = createDataSource("jdbc:postgresql://192.168.8.111:5432/test");
        // 构建读写分离数据源, 读写分离数据源实现了DataSource接口, 可直接当做数据源处理. masterDataSource0, slaveDataSource00, slaveDataSource01等为使用DBCP等连接池配置的真实数据源
        Map<String, DataSource> slaveDataSourceMap0 = new HashMap<>();
        BasicDataSource slaveDataSource00 = createDataSource("jdbc:postgresql://192.168.8.111:5433/test");
        slaveDataSourceMap0.put("slaveDataSource00", slaveDataSource00);
        // 可选择主从库负载均衡策略, 默认是ROUND_ROBIN, 还有RANDOM可以选择, 或者自定义负载策略
        DataSource masterSlaveDs0 = MasterSlaveDataSourceFactory.createDataSource("ms_0", "masterDataSource0", masterDataSource0, slaveDataSourceMap0, MasterSlaveLoadBalanceStrategyType.ROUND_ROBIN);

        return masterSlaveDs0;
    }

    private static BasicDataSource createDataSource(final String dataSourceUrl) {
        BasicDataSource result = new BasicDataSource();
        result.setDriverClassName(Driver.class.getName());
        result.setUrl(dataSourceUrl);
        result.setUsername("postgres");
        result.setPassword("");
        return result;
    }
}

3.Dao层及Mapper定义

package com.dhl.shardingjdbc.repository;

import com.dhl.shardingjdbc.entity.Country;

import java.util.List;

/**
 * Created by daihl on 2017/9/22.
 */
public interface CountryRepository {

    void insert(Country model);

    void deleteAll();

    List<Country> selectAll();
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dhl.shardingjdbc.repository.CountryRepository">
    <resultMap id="baseResultMap" type="com.dhl.shardingjdbc.entity.Country">
        <result column="id" property="id" jdbcType="BIGINT"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="baseColumnList">
        id,
        name
    </sql>

    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO testtable (
        name
        )
        VALUES (
        #{name,jdbcType=VARCHAR}
        )
    </insert>

    <delete id="deleteAll">
        DELETE FROM testtable
    </delete>

    <select id="selectAll" resultMap="baseResultMap">
        SELECT
        <include refid="baseColumnList"/>
        FROM testtable
    </select>
</mapper>

4.在配置文件中添加Mapper配置

mybatis:
  mapperLocations: classpath:mapper/*.xml
  typeAliasesPackage: com.dhl

5.在Application中添加Mapper扫描范围

package com.dhl.shardingjdbc;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.dhl")
public class ShardingjdbcdemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingjdbcdemoApplication.class, args);
    }
}

验证

1.将装有Postgresql数据库容器启动

主备数据库.png

2.启动项目工程，显示查询结果

查询结果.png

3.关闭从库容器

关闭从库容器.png

4.再次查询

查询结果.png

并且程序后台报错

2017-09-26 09:46:56.443 ERROR 7932 --- [nio-8080-exec-4] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.transaction.TransactionSystemException: Could not roll back JDBC transaction; nested exception is java.sql.SQLException] with root cause

java.sql.SQLException: null

由此可见查询操作是由从库提供的。

5.向主库插入一条新数据

插入.png

6.再次查询从库，已与同步主库

查询结果.png