You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by du...@apache.org on 2021/07/28 05:27:31 UTC
[shardingsphere] branch master updated: Single table insert when
not used sharding rule for table (#11457)
This is an automated email from the ASF dual-hosted git repository.
duanzhengqiang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shardingsphere.git
The following commit(s) were added to refs/heads/master by this push:
new d35999e Single table insert when not used sharding rule for table (#11457)
d35999e is described below
commit d35999e49d7b25710ba80d1fb301cf71ab4ef3ab
Author: zhaojinchao <33...@users.noreply.github.com>
AuthorDate: Wed Jul 28 13:26:54 2021 +0800
Single table insert when not used sharding rule for table (#11457)
* fixed single table insert when no used sharding rule for table
* return GeneratedKeysResultSet add condition
* add unit test
* table rename
* fixed
* update
* update broadcast
* add shardingspherestatement ut
* merge statement
* update
* choose dbsource
---
.../ShardingGeneratedKeyInsertValueParameterRewriter.java | 6 ++++--
.../keygen/GeneratedKeyInsertColumnTokenGenerator.java | 4 +++-
.../keygen/GeneratedKeyInsertValuesTokenGenerator.java | 3 ++-
.../engine/impl/InsertClauseShardingConditionEngine.java | 2 +-
.../schema/builder/loader/ColumnMetaDataLoader.java | 5 +++--
.../core/statement/ShardingSpherePreparedStatement.java | 13 ++++++++-----
.../jdbc/core/statement/ShardingSphereStatement.java | 12 ++++++++----
.../statement/ShardingSpherePreparedStatementTest.java | 15 +++++++++++++++
.../jdbc/core/statement/ShardingSphereStatementTest.java | 11 +++++++++++
.../src/test/resources/config/config-sharding.yaml | 1 +
.../src/test/resources/sql/jdbc_data.sql | 1 +
.../src/test/resources/sql/jdbc_init.sql | 2 ++
12 files changed, 59 insertions(+), 16 deletions(-)
diff --git a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rewrite/parameter/impl/ShardingGeneratedKeyInsertValueParameterRewriter.java b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rewrite/parameter/impl/ShardingGeneratedKeyInsertValueParameterRewriter.java
index 4b3f663..68d6632 100644
--- a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rewrite/parameter/impl/ShardingGeneratedKeyInsertValueParameterRewriter.java
+++ b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rewrite/parameter/impl/ShardingGeneratedKeyInsertValueParameterRewriter.java
@@ -37,8 +37,10 @@ public final class ShardingGeneratedKeyInsertValueParameterRewriter implements P
@Override
public boolean isNeedRewrite(final SQLStatementContext sqlStatementContext) {
- return sqlStatementContext instanceof InsertStatementContext
- && ((InsertStatementContext) sqlStatementContext).getGeneratedKeyContext().isPresent() && ((InsertStatementContext) sqlStatementContext).getGeneratedKeyContext().get().isGenerated();
+ return sqlStatementContext instanceof InsertStatementContext
+ && ((InsertStatementContext) sqlStatementContext).getGeneratedKeyContext().isPresent()
+ && ((InsertStatementContext) sqlStatementContext).getGeneratedKeyContext().get().isGenerated()
+ && !((InsertStatementContext) sqlStatementContext).getGeneratedKeyContext().get().getGeneratedValues().isEmpty();
}
@Override
diff --git a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rewrite/token/generator/impl/keygen/GeneratedKeyInsertColumnTokenGenerator.java b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rewrite/token/generator/impl/keygen/GeneratedKeyInsertColumnTokenGenerator.java
index e4ef2cc..2bbedd2 100644
--- a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rewrite/token/generator/impl/keygen/GeneratedKeyInsertColumnTokenGenerator.java
+++ b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rewrite/token/generator/impl/keygen/GeneratedKeyInsertColumnTokenGenerator.java
@@ -33,7 +33,9 @@ public final class GeneratedKeyInsertColumnTokenGenerator extends BaseGeneratedK
@Override
protected boolean isGenerateSQLToken(final InsertStatementContext insertStatementContext) {
Optional<InsertColumnsSegment> sqlSegment = insertStatementContext.getSqlStatement().getInsertColumns();
- return sqlSegment.isPresent() && !sqlSegment.get().getColumns().isEmpty();
+ return sqlSegment.isPresent() && !sqlSegment.get().getColumns().isEmpty()
+ && insertStatementContext.getGeneratedKeyContext().isPresent()
+ && !insertStatementContext.getGeneratedKeyContext().get().getGeneratedValues().isEmpty();
}
@Override
diff --git a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rewrite/token/generator/impl/keygen/GeneratedKeyInsertValuesTokenGenerator.java b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rewrite/token/generator/impl/keygen/GeneratedKeyInsertValuesTokenGenerator.java
index 5ae9adf..1a3f6c4 100644
--- a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rewrite/token/generator/impl/keygen/GeneratedKeyInsertValuesTokenGenerator.java
+++ b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/rewrite/token/generator/impl/keygen/GeneratedKeyInsertValuesTokenGenerator.java
@@ -44,7 +44,8 @@ public final class GeneratedKeyInsertValuesTokenGenerator extends BaseGeneratedK
@Override
protected boolean isGenerateSQLToken(final InsertStatementContext insertStatementContext) {
- return !insertStatementContext.getSqlStatement().getValues().isEmpty();
+ return !insertStatementContext.getSqlStatement().getValues().isEmpty() && insertStatementContext.getGeneratedKeyContext().isPresent()
+ && !insertStatementContext.getGeneratedKeyContext().get().getGeneratedValues().isEmpty();
}
@Override
diff --git a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/impl/InsertClauseShardingConditionEngine.java b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/impl/InsertClauseShardingConditionEngine.java
index 4af96d2..8659e69 100644
--- a/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/impl/InsertClauseShardingConditionEngine.java
+++ b/shardingsphere-features/shardingsphere-sharding/shardingsphere-sharding-core/src/main/java/org/apache/shardingsphere/sharding/route/engine/condition/engine/impl/InsertClauseShardingConditionEngine.java
@@ -121,7 +121,7 @@ public final class InsertClauseShardingConditionEngine implements ShardingCondit
private void appendGeneratedKeyConditions(final InsertStatementContext sqlStatementContext, final List<ShardingCondition> shardingConditions) {
Optional<GeneratedKeyContext> generatedKey = sqlStatementContext.getGeneratedKeyContext();
String tableName = sqlStatementContext.getSqlStatement().getTable().getTableName().getIdentifier().getValue();
- if (generatedKey.isPresent() && generatedKey.get().isGenerated()) {
+ if (generatedKey.isPresent() && generatedKey.get().isGenerated() && shardingRule.findTableRule(tableName).isPresent()) {
generatedKey.get().getGeneratedValues().addAll(generateKeys(tableName, sqlStatementContext.getValueListCount()));
if (shardingRule.isShardingColumn(generatedKey.get().getColumnName(), tableName)) {
appendGeneratedKeyCondition(generatedKey.get(), tableName, shardingConditions);
diff --git a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/ColumnMetaDataLoader.java b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/ColumnMetaDataLoader.java
index 3055fa7..663054a 100644
--- a/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/ColumnMetaDataLoader.java
+++ b/shardingsphere-infra/shardingsphere-infra-common/src/main/java/org/apache/shardingsphere/infra/metadata/schema/builder/loader/ColumnMetaDataLoader.java
@@ -79,10 +79,11 @@ public final class ColumnMetaDataLoader {
try (Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery(generateEmptyResultSQL(tableNamePattern, databaseType))) {
for (int i = 0; i < columnNames.size(); i++) {
isCaseSensitives.add(resultSet.getMetaData().isCaseSensitive(resultSet.findColumn(columnNames.get(i))));
- result.add(new ColumnMetaData(columnNames.get(i), columnTypes.get(i), isPrimaryKeys.get(i),
- resultSet.getMetaData().isAutoIncrement(i + 1), isCaseSensitives.get(i)));
}
}
+ for (int i = 0; i < columnNames.size(); i++) {
+ result.add(new ColumnMetaData(columnNames.get(i), columnTypes.get(i), isPrimaryKeys.get(i), false, isCaseSensitives.get(i)));
+ }
return result;
}
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/main/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSpherePreparedStatement.java b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/main/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSpherePreparedStatement.java
index 0bc4a6e..65e01f7 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/main/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSpherePreparedStatement.java
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/main/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSpherePreparedStatement.java
@@ -406,17 +406,20 @@ public final class ShardingSpherePreparedStatement extends AbstractPreparedState
return executionContext.getSqlStatementContext() instanceof InsertStatementContext
? ((InsertStatementContext) executionContext.getSqlStatementContext()).getGeneratedKeyContext() : Optional.empty();
}
-
+
@Override
public ResultSet getGeneratedKeys() throws SQLException {
Optional<GeneratedKeyContext> generatedKey = findGeneratedKey(executionContext);
- if (statementOption.isReturnGeneratedKeys() && generatedKey.isPresent()) {
+ if (generatedKey.isPresent() && statementOption.isReturnGeneratedKeys() && !generatedValues.isEmpty()) {
return new GeneratedKeysResultSet(generatedKey.get().getColumnName(), generatedValues.iterator(), this);
}
- if (1 == statements.size()) {
- return statements.iterator().next().getGeneratedKeys();
+ for (PreparedStatement statement : statements) {
+ ResultSet resultSet = statement.getGeneratedKeys();
+ while (resultSet.next()) {
+ generatedValues.add((Comparable<?>) resultSet.getObject(1));
+ }
}
- return new GeneratedKeysResultSet();
+ return new GeneratedKeysResultSet(null, generatedValues.iterator(), this);
}
@Override
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/main/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSphereStatement.java b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/main/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSphereStatement.java
index 4b37404..0a04f49 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/main/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSphereStatement.java
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/main/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSphereStatement.java
@@ -476,13 +476,17 @@ public final class ShardingSphereStatement extends AbstractStatementAdapter {
@Override
public ResultSet getGeneratedKeys() throws SQLException {
Optional<GeneratedKeyContext> generatedKey = findGeneratedKey();
- if (returnGeneratedKeys && generatedKey.isPresent()) {
+ if (returnGeneratedKeys && generatedKey.isPresent() && !generatedKey.get().getGeneratedValues().isEmpty()) {
return new GeneratedKeysResultSet(generatedKey.get().getColumnName(), generatedKey.get().getGeneratedValues().iterator(), this);
}
- if (1 == getRoutedStatements().size()) {
- return getRoutedStatements().iterator().next().getGeneratedKeys();
+ Collection<Comparable<?>> generatedValues = new LinkedList<>();
+ for (Statement statement : statements) {
+ ResultSet resultSet = statement.getGeneratedKeys();
+ while (resultSet.next()) {
+ generatedValues.add((Comparable<?>) resultSet.getObject(1));
+ }
}
- return new GeneratedKeysResultSet();
+ return new GeneratedKeysResultSet(null, generatedValues.iterator(), this);
}
private Optional<GeneratedKeyContext> findGeneratedKey() {
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSpherePreparedStatementTest.java b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSpherePreparedStatementTest.java
index c111351..8a79d49 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSpherePreparedStatementTest.java
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSpherePreparedStatementTest.java
@@ -48,6 +48,8 @@ public final class ShardingSpherePreparedStatementTest extends AbstractShardingS
private static final String INSERT_WITH_GENERATE_KEY_SQL_WITH_MULTI_VALUES = "INSERT INTO t_order_item (item_id, order_id, user_id, status) VALUES (1, ?, ?, ?), (2, ?, ?, ?)";
private static final String INSERT_ON_DUPLICATE_KEY_SQL = "INSERT INTO t_order_item (item_id, order_id, user_id, status) VALUES (?, ?, ?, ?), (?, ?, ?, ?) ON DUPLICATE KEY UPDATE status = ?";
+
+ private static final String INSERT_WITH_NO_GENERATED_VALUES_GENERATE_KEYS = "INSERT INTO t_sys (param_key, param_value) VALUES (?, ?)";
private static final String SELECT_SQL_WITHOUT_PARAMETER_MARKER = "SELECT item_id FROM t_order_item WHERE user_id = %d AND order_id= %s AND status = 'BATCH'";
@@ -350,6 +352,19 @@ public final class ShardingSpherePreparedStatementTest extends AbstractShardingS
}
@Test
+ public void assertAddGetGeneratedKeysForNoGeneratedValues() throws SQLException {
+ try (Connection connection = getShardingSphereDataSource().getDataSourceMap().get("jdbc_1").getConnection();
+ PreparedStatement preparedStatement = connection.prepareStatement(INSERT_WITH_NO_GENERATED_VALUES_GENERATE_KEYS, Statement.RETURN_GENERATED_KEYS)) {
+ preparedStatement.setString(1, "show");
+ preparedStatement.setString(2, "yes");
+ preparedStatement.execute();
+ ResultSet generateKeyResultSet = preparedStatement.getGeneratedKeys();
+ assertTrue(generateKeyResultSet.next());
+ assertThat(generateKeyResultSet.getInt(1), is(1));
+ }
+ }
+
+ @Test
public void assertAddOnDuplicateKey() throws SQLException {
int itemId = 1;
int userId1 = 101;
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSphereStatementTest.java b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSphereStatementTest.java
index d521931..938081c 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSphereStatementTest.java
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSphereStatementTest.java
@@ -64,6 +64,17 @@ public final class ShardingSphereStatementTest extends AbstractShardingSphereDat
}
}
+ @Test
+ public void assertAddGetGeneratedKeysForNoGeneratedValues() throws SQLException {
+ String sql = "INSERT INTO t_sys (param_key, param_value) VALUES ('%s', '%s')";
+ try (Statement statement = getShardingSphereDataSource().getDataSourceMap().get("jdbc_0").getConnection().createStatement()) {
+ statement.execute(String.format(sql, "show", "yes"), Statement.RETURN_GENERATED_KEYS);
+ ResultSet generatedKeysResultSet = statement.getGeneratedKeys();
+ assertTrue(generatedKeysResultSet.next());
+ assertThat(generatedKeysResultSet.getInt(1), is(1));
+ }
+ }
+
@Test(expected = SQLException.class)
public void assertQueryWithNull() throws SQLException {
try (Statement statement = getShardingSphereDataSource().getConnection().createStatement()) {
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/config/config-sharding.yaml b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/config/config-sharding.yaml
index aac91f5..a2f29e5 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/config/config-sharding.yaml
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/config/config-sharding.yaml
@@ -52,6 +52,7 @@ rules:
- t_order,t_order_item
broadcastTables:
- t_config
+ - t_sys
defaultTableStrategy:
standard:
shardingColumn: order_id
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_data.sql b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_data.sql
index dd6424f..ee20521 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_data.sql
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_data.sql
@@ -20,6 +20,7 @@ DELETE FROM t_order_item;
DELETE FROM t_order_auto;
DELETE FROM t_order_item_auto;
DELETE FROM t_config;
+DELETE FROM t_sys;
INSERT INTO t_order VALUES(1000, 10, 'init');
INSERT INTO t_order VALUES(1001, 10, 'init');
diff --git a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_init.sql b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_init.sql
index dde4846..1f74033 100644
--- a/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_init.sql
+++ b/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/resources/sql/jdbc_init.sql
@@ -29,3 +29,5 @@ CREATE TABLE IF NOT EXISTS t_config (id INT NOT NULL, status VARCHAR(45) NULL, P
CREATE TABLE IF NOT EXISTS t_user_0 (id INT NOT NULL, name VARCHAR(45) NULL, PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS t_user_1 (id INT NOT NULL, name VARCHAR(45) NULL, PRIMARY KEY (id));
+
+CREATE TABLE IF NOT EXISTS t_sys (id INT NOT NULL AUTO_INCREMENT, param_key VARCHAR(45) NULL, param_value VARCHAR(45) NULL, PRIMARY KEY (id));