java jdbc getschema_Sharding-JDBC 数据源分片：Java 配置实现基于原生 JDBC 的精确分片方案...

最新推荐文章于 2024-03-20 06:00:00 发布

原创最新推荐文章于 2024-03-20 06:00:00 发布 · 227 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java jdbc getschema

本文详细介绍了如何通过Java配置实现基于Sharding-JDBC的精确分片数据库操作，包括数据源配置、表规则设置、分片策略配置等，以用户ID进行精确分片，实现数据在不同数据库实例间的分布。同时，文章提供了一个完整的示例，从创建数据源、DAO接口到执行SQL操作，展示了如何在实际应用中部署和使用Sharding-JDBC。

本文介绍 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

1.8

org.apache.maven.plugins

maven-compiler-plugin

3.8.0

${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

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：用于配置不分片的策略。