工具类DataRespository(该类来源sharding-sphere-example项目)
/* * Copyright 2016-2018 shardingsphere.io. * <p> * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. * </p> */ package io.shardingsphere.example.jdbc.fixture; import io.shardingsphere.core.api.HintManager; import javax.sql.DataSource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class DataRepository { private final DataSource dataSource; public DataRepository(final DataSource dataSource) { this.dataSource = dataSource; } public void demo() throws SQLException { createTable(); insertData(); System.out.println("1.Query with EQUAL--------------"); queryWithEqual(); System.out.println("2.Query with IN--------------"); queryWithIn(); System.out.println("3.Query with Hint--------------"); queryWithHint(); System.out.println("4.Drop tables--------------"); dropTable(); System.out.println("5.All done-----------"); } private void createTable() throws SQLException { execute("CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id))"); execute("CREATE TABLE IF NOT EXISTS t_order_item (order_item_id BIGINT NOT NULL AUTO_INCREMENT, order_id BIGINT NOT NULL, user_id INT NOT NULL, PRIMARY KEY (order_item_id))"); } private void insertData() throws SQLException { for (int i = 1; i < 10; i++) { long orderId = insertAndGetGeneratedKey("INSERT INTO t_order (user_id, status) VALUES (10, 'INIT')"); execute(String.format("INSERT INTO t_order_item (order_id, user_id) VALUES (%d, 10)", orderId)); orderId = insertAndGetGeneratedKey("INSERT INTO t_order (user_id, status) VALUES (11, 'INIT')"); execute(String.format("INSERT INTO t_order_item (order_id, user_id) VALUES (%d, 11)", orderId)); } } private long insertAndGetGeneratedKey(final String sql) throws SQLException { long result = -1; try ( Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement()) { statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); try (ResultSet resultSet = statement.getGeneratedKeys()) { if (resultSet.next()) { result = resultSet.getLong(1); } } } return result; } private void queryWithEqual() throws SQLException { String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id=?"; try ( Connection connection = dataSource.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(sql)) { preparedStatement.setInt(1, 10); printQuery(preparedStatement); } } private void queryWithIn() throws SQLException { String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id WHERE o.user_id IN (?, ?)"; try ( Connection connection = dataSource.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(sql)) { preparedStatement.setInt(1, 10); preparedStatement.setInt(2, 11); printQuery(preparedStatement); } } private void queryWithHint() throws SQLException { String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id"; try ( HintManager hintManager = HintManager.getInstance(); Connection connection = dataSource.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(sql)) { hintManager.addDatabaseShardingValue("t_order", "user_id", 11); printQuery(preparedStatement); } } private void printQuery(final PreparedStatement preparedStatement) throws SQLException { try (ResultSet resultSet = preparedStatement.executeQuery()) { while (resultSet.next()) { System.out.print("order_item_id:" + resultSet.getLong(1) + ", "); System.out.print("order_id:" + resultSet.getLong(2) + ", "); System.out.print("user_id:" + resultSet.getInt(3)); System.out.println(); } } } private void dropTable() throws SQLException { execute("DROP TABLE t_order_item"); execute("DROP TABLE t_order"); } private void execute(final String sql) throws SQLException { try ( Connection connection = dataSource.getConnection(); Statement statement = connection.createStatement()) { statement.execute(sql); } } }注意
1、createTable
该方法会根据配置的数据节点表达式创建分表。这里分别创建t_order和t_order_item两张逻辑表。
2、insertData
该方法同样根据配置的数据分片表达书创建数据
3、queryWithEqual等方法
这些方法是不同的查询场景，有精确查询也有范围查询
4、queryWithHint
该方法比较特殊。
通过解析SQL语句提取分片键列与值并进行分片是Sharding-Sphere对SQL零侵入的实现方式。若SQL语句中没有分片条件，则无法进行分片，需要全路由。
好比queryWithHint这个方法中的"String sql = "SELECT i.* FROM t_order o JOIN t_order_item i ON o.order_id=i.order_id";"就没有包含路由信息，即where
条件语句中没有order_id和user_id的信息。