本文介绍 Sharding-JDBC 数据源分片之使用 Java 配置实现基于原生 JDBC 的精确分片方案。
目录
开发环境
基础示例
总结
开发环境
Oracle JDK 1.8.0_201
Apache Maven 3.6.0
IntelliJ IDEA (Version 2018.3.3)
MySQL 5.6.38
基础示例
创建 2 个数据库实例 ds_0 和 ds_1。
在生成的 pom 文件中添加 Sharding-JDBC 和 MySQL 驱动依赖,使用阿里巴巴 Druid 数据库连接池。
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">
shardingsphere-tutorial
tutorial.shardingsphere
1.0-SNAPSHOT
4.0.0
shardingsphere-raw-jdbc
UTF-8
UTF-8
1.8
org.apache.maven.plugins
maven-compiler-plugin
3.8.0
${java.version}
${java.version}
${project.build.sourceEncoding}
org.apache.shardingsphere
sharding-jdbc-core
4.0.0-RC1
mysql
mysql-connector-java
5.1.47
com.alibaba
druid
1.1.17
junit
junit
4.12
test
创建一个领域类 Order(订单),包含 3 个属性:orderId(订单编号),userId(订单所属用户ID),details(订单详情)。
package tutorial.shardingsphere.jdbc.bean;
import java.io.Serializable;
import java.util.Objects;
public class Order implements Serializable {
private static final long serialVersionUID = -3614937227437805644L;
private Long orderId;
private Long userId;
private String details;
public Order() {
}
public Order(Long userId, String details) {
this.userId = userId;
this.details = details;
}
public Long getOrderId() {
return orderId;
}
public void setOrderId(Long orderId) {
this.orderId = orderId;
}
public Long getUserId() {
return userId;
}
public void setUserId(Long userId) {
this.userId = userId;
}
public String getDetails() {
return details;
}
public void setDetails(String details) {
this.details = details;
}
@Override
public boolean equals(Object o) {
if (this == o) {
return true;
}
if (!(o instanceof Order)) {
return false;
}
Order order = (Order) o;
return Objects.equals(orderId, order.orderId) &&
Objects.equals(userId, order.userId) &&
Objects.equals(details, order.details);
}
@Override
public int hashCode() {
return Objects.hash(orderId, userId, details);
}
@Override
public String toString() {
return "Order{" +
"orderId=" + orderId +
", userId=" + userId +
", details='" + details + '\'' +
'}';
}
}
定义通用数据访问接口(DAO:Data Access Object)。
package tutorial.shardingsphere.jdbc.dao;
import java.util.List;
public interface IBasicDao {
void createTableIfNotExists();
void dropTable();
void truncateTable();
Long insert(T entity);
List select();
void update(T entity);
void delete(P key);
}
定义 Order(订单)数据访问接口,继承通用 DAO。
package tutorial.shardingsphere.jdbc.dao;
import tutorial.shardingsphere.jdbc.bean.Order;
public interface IOrderDao extends IBasicDao {
}
定义 Order(订单)数据访问实现。
package tutorial.shardingsphere.jdbc.dao.impl;
import tutorial.shardingsphere.jdbc.bean.Order;
import tutorial.shardingsphere.jdbc.dao.IOrderDao;
import javax.sql.DataSource;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;
public class OrderDaoImpl implements IOrderDao {
private final DataSource dataSource;
public OrderDaoImpl(DataSource dataSource) {
this.dataSource = dataSource;
}
@Override
public void createTableIfNotExists() {
String sql = "CREATE TABLE IF NOT EXISTS t_order "
+ "(order_id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, details VARCHAR(100), PRIMARY KEY (order_id))";
try (Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement()) {
statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void dropTable() {
String sql = "DROP TABLE t_order";
try (Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement()) {
statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void truncateTable() {
String sql = "TRUNCATE TABLE t_order";
try (Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement()) {
statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public Long insert(Order order) {
String sql = "INSERT INTO t_order (user_id, details) VALUES (?, ?)";
try (Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
preparedStatement.setLong(1, order.getUserId());
preparedStatement.setString(2, order.getDetails());
preparedStatement.executeUpdate();
try (ResultSet resultSet = preparedStatement.getGeneratedKeys()) {
if (resultSet.next()) {
order.setOrderId(resultSet.getLong(1));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
return order.getOrderId();
}
@Override
public List select() {
String sql = "SELECT * FROM t_order";
return listOrders(sql);
}
protected List listOrders(String sql) {
List result = new LinkedList<>();
try (Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
ResultSet resultSet = preparedStatement.executeQuery()) {
while (resultSet.next()) {
Order order = new Order();
order.setOrderId(resultSet.getLong(1));
order.setUserId(resultSet.getLong(2));
order.setDetails(resultSet.getString(3));
result.add(order);
}
} catch (SQLException e) {
e.printStackTrace();
}
return result;
}
@Override
public void update(Order order) {
String sql = "UPDATE t_order SET user_id=?, details=? WHERE order_id=?";
try (Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
preparedStatement.setLong(1, order.getUserId());
preparedStatement.setString(2, order.getDetails());
preparedStatement.setLong(3, order.getOrderId());
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public void delete(Long id) {
String sql = "DELETE FROM t_order WHERE order_id=?";
try (Connection connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
preparedStatement.setLong(1, id);
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
定义数据源工具类。
package tutorial.shardingsphere.jdbc.util;
import com.alibaba.druid.pool.DruidDataSource;
import javax.sql.DataSource;
public class DataSourceUtils {
/**
* 创建数据源对象
*/
public static DataSource createDataSource(final String dataSourceName) {
DruidDataSource dataSource = new DruidDataSource();
String jdbcUrl =
String.format(
"jdbc:mysql://localhost:3306/%s?useSSL=false&useUnicode=true&characterEncoding=UTF-8",
dataSourceName);
dataSource.setUrl(jdbcUrl);
dataSource.setUsername("root");
dataSource.setPassword("123456");
// 数据源其它配置(略)
return dataSource;
}
}
定义获取数据源的工厂类。
package tutorial.shardingsphere.jdbc.util;
import org.apache.shardingsphere.api.config.sharding.KeyGeneratorConfiguration;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.InlineShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
public class DataSourceFactory {
/**
* 配置数据源映射
*/
private static Map createDataSourceMap() {
Map result = new HashMap<>();
result.put("ds_0", DataSourceUtils.createDataSource("ds_0"));
result.put("ds_1", DataSourceUtils.createDataSource("ds_1"));
return result;
}
public static DataSource getDataSource() throws SQLException {
// 配置数据源映射
Map dataSourceMap = createDataSourceMap();
// 配置表规则
TableRuleConfiguration tableRuleConfiguration = new TableRuleConfiguration("t_order");
tableRuleConfiguration.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "order_id"));
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfiguration = new ShardingRuleConfiguration();
shardingRuleConfiguration.getTableRuleConfigs().add(tableRuleConfiguration);
// 配置默认分库策略
shardingRuleConfiguration.setDefaultDatabaseShardingStrategyConfig(
new InlineShardingStrategyConfiguration("user_id", "ds_${user_id % 2}")
);
// 获取数据源对象
return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfiguration, new Properties());
}
}
编写单元测试。
package tutorial.shardingsphere.jdbc;
import org.junit.Assert;
import org.junit.BeforeClass;
import org.junit.Test;
import tutorial.shardingsphere.jdbc.bean.Order;
import tutorial.shardingsphere.jdbc.dao.IOrderDao;
import tutorial.shardingsphere.jdbc.dao.impl.OrderDaoImpl;
import tutorial.shardingsphere.jdbc.util.DataSourceFactory;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
public class JdbcConfigPreciseShardingDatabaseTest {
private static IOrderDao orderDao;
@BeforeClass
public static void init() throws SQLException {
DataSource dataSource = DataSourceFactory.getDataSource();
orderDao = new OrderDaoImpl(dataSource);
}
@Test
public void test() {
orderDao.createTableIfNotExists();
orderDao.truncateTable();
Assert.assertEquals(0, orderDao.select().size());
Order order1 = new Order(1L, "Order 1");
Order order2 = new Order(2L, "Order 2");
order1.setOrderId(orderDao.insert(order1));
order2.setOrderId(orderDao.insert(order2));
List orders = orderDao.select();
List expected = Arrays.asList(order1, order2);
Assert.assertEquals(expected, orders);
order1.setUserId(2L);
order1.setDetails("Order 1 changed user id!");
orderDao.update(order1);
order2.setUserId(1L);
order2.setDetails("Order 2 changed user id!");
orderDao.update(order2);
orderDao.delete(0L);
orders = orderDao.select();
Assert.assertEquals(expected, orders);
orderDao.delete(order1.getOrderId());
orders = orderDao.select();
Assert.assertEquals(Collections.singletonList(order2), orders);
orderDao.delete(order2.getOrderId());
orders = orderDao.select();
Assert.assertEquals(0, orders.size());
orderDao.dropTable();
}
}
测试执行结果略。
总结
本示例实现了基于 t_order 表 user_id 字段的精确数据分片,user_id 为单数时存储在 ds_1 中,为双数时存储在 ds_0 中。
使用 Sharding-JDBC 实现基于原生 JDBC 的单表分库关键步骤:
配置数据源映射 Map;
配置数据分片规则 ShardingRuleConfiguration;
在数据分片规则中添加表规则配置 TableRuleConfiguration;
在数据分片规则中设置默认数据库分片策略 setDefaultDatabaseShardingStrategyConfig;
调用 ShardingDataSourceFactory.createDataSource 创建分片后数据源对象。
过程中使用的关键类:
ShardingDataSourceFactory:数据分片的数据源创建工厂。
ShardingRuleConfiguration:分片规则配置对象。
TableRuleConfiguration:表分片规则配置对象。
ShardingStrategyConfiguration:分片策略配置对象。
Sharding-JDBC 提供了几个 ShardingStrategyConfiguration 的实现类处理不同的分片场景:
StandardShardingStrategyConfiguration:用于单分片键的标准分片场景。
ComplexShardingStrategyConfiguration:用于多分片键的复合分片场景。
InlineShardingStrategyConfiguration:用于配置行表达式分片策略。
HintShardingStrategyConfiguration:用于配置Hint方式分片策略。
NoneShardingStrategyConfiguration:用于配置不分片的策略。