ShardingSphere Raw JDBC 主从示例
发布于: 2020 年 12 月 05 日
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-alphasharding.jdbc.datasource.names=master,slave0,slave1
sharding.jdbc.datasource.ds-master.driver-class-name=com.mysql.jdbc.Driversharding.jdbc.datasource.ds-master.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&useUnicode=true\ &characterEncoding=utf-8\ &useSSL=false&allowPublicKeyRetrieval=truesharding.jdbc.datasource.ds-master.username=rootsharding.jdbc.datasource.ds-master.password=root
sharding.jdbc.datasource.ds-slave0.driver-class-name=com.mysql.jdbc.Driversharding.jdbc.datasource.ds-slave0.url=jdbc:mysql://localhost:3309/test?serverTimezone=UTC&useUnicode=true\ &characterEncoding=utf-8\ &useSSL=false&allowPublicKeyRetrieval=truesharding.jdbc.datasource.ds-slave0.username=rootsharding.jdbc.datasource.ds-slave0.password=root
sharding.jdbc.datasource.ds-slave1.driver-class-name=com.mysql.jdbc.Driversharding.jdbc.datasource.ds-slave1.url=jdbc:mysql://localhost:3310/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8\ &useSSL=false&allowPublicKeyRetrieval=truesharding.jdbc.datasource.ds-slave1.username=rootsharding.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@Componentpublic 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-slaveSharding-Sphere-SQL : SQL: select * from stores limit 5 ::: DataSources: slave0Sharding-Sphere-SQL : Rule Type: master-slaveSharding-Sphere-SQL : SQL: select * from stores limit 5 ::: DataSources: slave1Sharding-Sphere-SQL : Rule Type: master-slaveSharding-Sphere-SQL : SQL: insert into stores (name, description) VALUES ("name103", "description103"); ::: DataSources: masterSharding-Sphere-SQL : Rule Type: master-slaveSharding-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); }}复制代码
划线
评论
复制
发布于: 2020 年 12 月 05 日阅读数: 34
版权声明: 本文为 InfoQ 作者【萧】的原创文章。
原文链接:【http://xie.infoq.cn/article/f341e066a725e79e0bdc084fe】。
本文遵守【CC-BY 4.0】协议，转载请保留原文出处及本版权声明。
萧
关注
还未添加个人签名 2018.09.09 加入
代码是门手艺活，也是门艺术活
评论