ShardingSphere Raw JDBC 主从示例

ShardingSphere Raw JDBC 主从示例

---

总览

    这个示例不结合ORM之类的，直接执行SQL语句。通过写两个简单的文件就可以了，大致的步骤如下：

• 1.配置Maven依赖
• 2.配置文件写入相关配置
• 3.获取DataSource类编写
• 4.测试

配置Maven依赖

    其主要依赖如下，还有一些spring、mysql的依赖，总的Maven文件链接在这：pom.xml，自定查找添加吧

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
	<artifactId>shardingsphere-jdbc-core</artifactId>
	<version>5.0.0-alpha</version>
</dependency>

application.properties文件写入

    写入主从库的相关配置

# 读写分离 - 数据库框架版本 2.0 ShardingSphere-jdbc 5.0.0-alpha
sharding.jdbc.datasource.names=master,slave0,slave1

sharding.jdbc.datasource.ds-master.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds-master.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true\
  &characterEncoding=utf-8\
  &useSSL=false&allowPublicKeyRetrieval=true
sharding.jdbc.datasource.ds-master.username=root
sharding.jdbc.datasource.ds-master.password=root

sharding.jdbc.datasource.ds-slave0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds-slave0.url=jdbc:mysql://localhost:3309/test?serverTimezone=UTC&useUnicode=true\
  &characterEncoding=utf-8\
  &useSSL=false&allowPublicKeyRetrieval=true
sharding.jdbc.datasource.ds-slave0.username=root
sharding.jdbc.datasource.ds-slave0.password=root

sharding.jdbc.datasource.ds-slave1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds-slave1.url=jdbc:mysql://localhost:3310/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8\
  &useSSL=false&allowPublicKeyRetrieval=true
sharding.jdbc.datasource.ds-slave1.username=root
sharding.jdbc.datasource.ds-slave1.password=root

ShardingMasterSlaveDataSource编写

    这里默认使用HikariDataSource,思路主要是生成各个主从库的链接，放到Sharding中，代码中也有说明，总体比较简单：

package com.example.demo.shardingsphere.raw.jdbc;

import com.zaxxer.hikari.HikariDataSource;
import io.shardingsphere.api.config.MasterSlaveRuleConfiguration;
import io.shardingsphere.shardingjdbc.api.MasterSlaveDataSourceFactory;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;

/**
 * @author lw
 */
@Slf4j
@Component
public class ShardingMasterSlaveDataSource {

    private final String DRIVER = ".driver-class-name";
    private final String URL = ".url";
    private final String USERNAME = ".username";
    private final String PASSWORD = ".password";
    private final String DBS = "sharding.jdbc.datasource.names";

    @Autowired
    private Environment environment;

    DataSource createDataSource() throws SQLException {
        // 获取数据库列表
        String[] dbs = Objects.requireNonNull(environment.getProperty(DBS)).split(",");
        log.info("DBS::" + Arrays.toString(dbs));

        // 设置主从，约定第一个为主，其他为从
        MasterSlaveRuleConfiguration configuration = new MasterSlaveRuleConfiguration(dbs[0], dbs[0],
                Arrays.asList(Arrays.copyOfRange(dbs, 1, dbs.length)));
        log.info("ShardingMasterSlaveDataSource master :: " + configuration.getMasterDataSourceName());
        log.info("ShardingMasterSlaveDataSource slave :: " + configuration.getSlaveDataSourceNames());

        // 设置打印SQL语句，查看主从配置和切换是否成功
        Properties properties = new Properties();
        properties.setProperty("sql.show", "true");

        return MasterSlaveDataSourceFactory.createDataSource(createDataSourceMap(dbs), configuration, new HashMap<>(0),
                properties);
    }

    /**
     * 返回DataSource列表
     */
    private Map<String, DataSource> createDataSourceMap(String[] dbs) {
        Map<String, DataSource> result = new HashMap<>(dbs.length);
        for (String db: dbs) {
            log.info("Create data source ::" + db);
            result.put(db, createDataSource("sharding.jdbc.datasource.ds-" + db));
        }
        return result;
    }

    private DataSource createDataSource(String prefix) {
        log.info(DRIVER + "::" + environment.getProperty(prefix + DRIVER));
        log.info(URL + "::" + environment.getProperty(prefix + URL));
        log.info(USERNAME + "::" + environment.getProperty(prefix + USERNAME));
        log.info(PASSWORD + "::" + environment.getProperty(prefix + PASSWORD));

        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName(environment.getProperty(prefix + DRIVER));
        dataSource.setJdbcUrl(environment.getProperty(prefix + URL));
        dataSource.setUsername(environment.getProperty(prefix + USERNAME));
        dataSource.setPassword(environment.getProperty(prefix + PASSWORD));
        return dataSource;
    }
}

测试

    直接生写SQL了，就不结合ORM之类的了，这里是通过日志看出是否生效的，日志大致如下

Sharding-Sphere-SQL                      : Rule Type: master-slave
Sharding-Sphere-SQL                      : SQL: select * from stores limit 5 ::: DataSources: slave0
Sharding-Sphere-SQL                      : Rule Type: master-slave
Sharding-Sphere-SQL                      : SQL: select * from stores limit 5 ::: DataSources: slave1
Sharding-Sphere-SQL                      : Rule Type: master-slave
Sharding-Sphere-SQL                      : SQL: insert into stores (name, description) VALUES ("name103", "description103"); ::: DataSources: master
Sharding-Sphere-SQL                      : Rule Type: master-slave
Sharding-Sphere-SQL                      : SQL: select * from stores limit 5 ::: DataSources: master

    代码如下：

package com.example.demo.shardingsphere.raw.jdbc;

import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit.jupiter.SpringExtension;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

@Slf4j
@SpringBootTest
@ExtendWith(SpringExtension.class)
public class ShardingMasterSlaveDataSourceTest {

    @Autowired
    ShardingMasterSlaveDataSource shardingMasterSlaveDataSource;

    /**
     * 运行后从日志可以看出主从之间的切换和从库负载均衡
     */
    @Test @Transactional
    public void test() throws SQLException {
        DataSource dataSource = shardingMasterSlaveDataSource.createDataSource();
        log.info("ShardingMasterSlaveDataSource info::" + dataSource.getConnection().getMetaData().getURL());

        Connection conn = dataSource.getConnection();
        Statement statement = conn.createStatement();

        String sql = "select * from stores limit 5";
        statement.execute(sql);
        statement.execute(sql);

        sql = "insert into stores (name, description) VALUES (\"name103\", \"description103\");";
        statement.execute(sql);

        sql = "select * from stores limit 5";
        statement.execute(sql);
    }
}