You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@geode.apache.org by ds...@apache.org on 2018/12/14 01:32:44 UTC
[geode] branch feature/GEODE-6194 updated: WIP: SqlHandler now has
support for multiple keys. Needs more testing
This is an automated email from the ASF dual-hosted git repository.
dschneider pushed a commit to branch feature/GEODE-6194
in repository https://gitbox.apache.org/repos/asf/geode.git
The following commit(s) were added to refs/heads/feature/GEODE-6194 by this push:
new 31db31f WIP: SqlHandler now has support for multiple keys. Needs more testing
31db31f is described below
commit 31db31f9d5e6cd34f0f5715e208e9fa05f03c39f
Author: Darrel Schneider <ds...@pivotal.io>
AuthorDate: Thu Dec 13 17:31:32 2018 -0800
WIP: SqlHandler now has support for multiple keys. Needs more testing
---
.../connectors/jdbc/internal/EntryColumnData.java | 9 +--
.../geode/connectors/jdbc/internal/SqlHandler.java | 74 ++++++++++++++++------
.../jdbc/internal/SqlStatementFactory.java | 70 +++++++++++++-------
.../connectors/jdbc/internal/SqlHandlerTest.java | 12 +++-
.../jdbc/internal/SqlStatementFactoryTest.java | 5 +-
5 files changed, 119 insertions(+), 51 deletions(-)
diff --git a/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/EntryColumnData.java b/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/EntryColumnData.java
index 5630f03..c3a4bbb 100644
--- a/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/EntryColumnData.java
+++ b/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/EntryColumnData.java
@@ -18,16 +18,17 @@ import java.util.Collections;
import java.util.List;
class EntryColumnData {
- private final ColumnData entryKeyColumnData;
+ private final List<ColumnData> entryKeyColumnData;
private final List<ColumnData> entryValueColumnData;
- EntryColumnData(ColumnData entryKeyColumnData, List<ColumnData> entryValueColumnData) {
- this.entryKeyColumnData = entryKeyColumnData;
+ EntryColumnData(List<ColumnData> entryKeyColumnData, List<ColumnData> entryValueColumnData) {
+ this.entryKeyColumnData =
+ entryKeyColumnData != null ? entryKeyColumnData : Collections.emptyList();
this.entryValueColumnData =
entryValueColumnData != null ? entryValueColumnData : Collections.emptyList();
}
- public ColumnData getEntryKeyColumnData() {
+ public List<ColumnData> getEntryKeyColumnData() {
return entryKeyColumnData;
}
diff --git a/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/SqlHandler.java b/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/SqlHandler.java
index 2b7a316..4040ab5 100644
--- a/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/SqlHandler.java
+++ b/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/SqlHandler.java
@@ -22,9 +22,12 @@ import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
+import java.util.Set;
import javax.sql.DataSource;
+import org.json.JSONObject;
+
import org.apache.geode.InternalGemFireException;
import org.apache.geode.annotations.Experimental;
import org.apache.geode.cache.Operation;
@@ -94,7 +97,7 @@ public class SqlHandler {
private ResultSet executeReadQuery(PreparedStatement statement, EntryColumnData entryColumnData)
throws SQLException {
- setValuesInStatement(statement, entryColumnData);
+ setValuesInStatement(statement, entryColumnData, Operation.GET);
return statement.executeQuery();
}
@@ -108,17 +111,23 @@ public class SqlHandler {
return regionMapping;
}
- private void setValuesInStatement(PreparedStatement statement, EntryColumnData entryColumnData)
+ private void setValuesInStatement(PreparedStatement statement, EntryColumnData entryColumnData,
+ Operation operation)
throws SQLException {
int index = 0;
- for (ColumnData columnData : entryColumnData.getEntryValueColumnData()) {
+ if (operation.isCreate() || operation.isUpdate()) {
+ index = setValuesFromColumnData(statement, entryColumnData.getEntryValueColumnData(), index);
+ }
+ setValuesFromColumnData(statement, entryColumnData.getEntryKeyColumnData(), index);
+ }
+
+ private int setValuesFromColumnData(PreparedStatement statement, List<ColumnData> columnDataList,
+ int index) throws SQLException {
+ for (ColumnData columnData : columnDataList) {
index++;
setValueOnStatement(statement, index, columnData);
}
-
- ColumnData keyColumnData = entryColumnData.getEntryKeyColumnData();
- index++;
- setValueOnStatement(statement, index, keyColumnData);
+ return index;
}
private void setValueOnStatement(PreparedStatement statement, int index, ColumnData columnData)
@@ -156,7 +165,7 @@ public class SqlHandler {
public <K, V> void write(Region<K, V> region, Operation operation, K key, PdxInstance value)
throws SQLException {
- if (value == null && operation != Operation.DESTROY) {
+ if (value == null && !operation.isDestroy()) {
throw new IllegalArgumentException("PdxInstance cannot be null for non-destroy operations");
}
RegionMapping regionMapping = getMappingForRegion(region.getName());
@@ -169,7 +178,7 @@ public class SqlHandler {
int updateCount = 0;
try (PreparedStatement statement =
getPreparedStatement(connection, tableMetaData, entryColumnData, operation)) {
- updateCount = executeWriteStatement(statement, entryColumnData);
+ updateCount = executeWriteStatement(statement, entryColumnData, operation);
} catch (SQLException e) {
if (operation.isDestroy()) {
throw e;
@@ -185,7 +194,7 @@ public class SqlHandler {
Operation upsertOp = getOppositeOperation(operation);
try (PreparedStatement upsertStatement =
getPreparedStatement(connection, tableMetaData, entryColumnData, upsertOp)) {
- updateCount = executeWriteStatement(upsertStatement, entryColumnData);
+ updateCount = executeWriteStatement(upsertStatement, entryColumnData, operation);
}
}
@@ -197,9 +206,10 @@ public class SqlHandler {
return operation.isUpdate() ? Operation.CREATE : Operation.UPDATE;
}
- private int executeWriteStatement(PreparedStatement statement, EntryColumnData entryColumnData)
+ private int executeWriteStatement(PreparedStatement statement, EntryColumnData entryColumnData,
+ Operation operation)
throws SQLException {
- setValuesInStatement(statement, entryColumnData);
+ setValuesInStatement(statement, entryColumnData, operation);
return statement.executeUpdate();
}
@@ -230,24 +240,52 @@ public class SqlHandler {
<K> EntryColumnData getEntryColumnData(TableMetaDataView tableMetaData,
RegionMapping regionMapping, K key, PdxInstance value, Operation operation) {
- String keyColumnName = "ERROR";// tableMetaData.getKeyColumnNames();
- ColumnData keyColumnData =
- new ColumnData(keyColumnName, key, tableMetaData.getColumnDataType(keyColumnName));
+ List<ColumnData> keyColumnData = createKeyColumnDataList(tableMetaData, regionMapping, key);
List<ColumnData> valueColumnData = null;
if (operation.isCreate() || operation.isUpdate()) {
- valueColumnData = createColumnDataList(tableMetaData, regionMapping, value);
+ valueColumnData = createValueColumnDataList(tableMetaData, regionMapping, value);
}
return new EntryColumnData(keyColumnData, valueColumnData);
}
- private List<ColumnData> createColumnDataList(TableMetaDataView tableMetaData,
+ private <K> List<ColumnData> createKeyColumnDataList(TableMetaDataView tableMetaData,
+ RegionMapping regionMapping, K key) {
+ List<String> keyColumnNames = tableMetaData.getKeyColumnNames();
+ List<ColumnData> result = new ArrayList<>();
+ if (keyColumnNames.size() == 1) {
+ String keyColumnName = keyColumnNames.get(0);
+ ColumnData columnData =
+ new ColumnData(keyColumnName, key, tableMetaData.getColumnDataType(keyColumnName));
+ result.add(columnData);
+ } else {
+ JSONObject compositeKey = new JSONObject((String) key);
+ Set<String> fieldNames = compositeKey.keySet();
+ if (fieldNames.size() != keyColumnNames.size()) {
+ throw new JdbcConnectorException("The key \"" + key + "\" should have "
+ + keyColumnNames.size() + " fields but has " + fieldNames.size() + " fields.");
+ }
+ for (String fieldName : fieldNames) {
+ String columnName = regionMapping.getColumnNameForField(fieldName, tableMetaData);
+ if (!keyColumnNames.contains(columnName)) {
+ throw new JdbcConnectorException("The key \"" + key + "\" has the field \"" + fieldName
+ + "\" which does not match any of the key columns: " + keyColumnNames);
+ }
+ ColumnData columnData = new ColumnData(columnName, compositeKey.get(fieldName),
+ tableMetaData.getColumnDataType(columnName));
+ result.add(columnData);
+ }
+ }
+ return result;
+ }
+
+ private List<ColumnData> createValueColumnDataList(TableMetaDataView tableMetaData,
RegionMapping regionMapping, PdxInstance value) {
List<ColumnData> result = new ArrayList<>();
for (String fieldName : value.getFieldNames()) {
String columnName = regionMapping.getColumnNameForField(fieldName, tableMetaData);
- if (tableMetaData.getKeyColumnNames().equals(columnName)) {
+ if (tableMetaData.getKeyColumnNames().contains(columnName)) {
continue;
}
ColumnData columnData = new ColumnData(columnName, value.getField(fieldName),
diff --git a/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/SqlStatementFactory.java b/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/SqlStatementFactory.java
index 5087bbe..c2c697f 100644
--- a/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/SqlStatementFactory.java
+++ b/geode-connectors/src/main/java/org/apache/geode/connectors/jdbc/internal/SqlStatementFactory.java
@@ -14,6 +14,9 @@
*/
package org.apache.geode.connectors.jdbc.internal;
+import java.util.Iterator;
+import java.util.stream.Stream;
+
class SqlStatementFactory {
private final String quote;
@@ -22,19 +25,32 @@ class SqlStatementFactory {
}
String createSelectQueryString(String tableName, EntryColumnData entryColumnData) {
- ColumnData keyCV = entryColumnData.getEntryKeyColumnData();
- return "SELECT * FROM " + quoteIdentifier(tableName) + " WHERE "
- + quoteIdentifier(keyCV.getColumnName()) + " = ?";
+ return addKeyColumnsToQuery(entryColumnData,
+ new StringBuilder("SELECT * FROM " + quoteIdentifier(tableName)));
}
String createDestroySqlString(String tableName, EntryColumnData entryColumnData) {
- ColumnData keyCV = entryColumnData.getEntryKeyColumnData();
- return "DELETE FROM " + quoteIdentifier(tableName) + " WHERE "
- + quoteIdentifier(keyCV.getColumnName()) + " = ?";
+ return addKeyColumnsToQuery(entryColumnData,
+ new StringBuilder("DELETE FROM " + quoteIdentifier(tableName)));
+ }
+
+ private String addKeyColumnsToQuery(EntryColumnData entryColumnData, StringBuilder queryBuilder) {
+ queryBuilder.append(" WHERE ");
+ Iterator<ColumnData> iterator = entryColumnData.getEntryKeyColumnData().iterator();
+ while (iterator.hasNext()) {
+ ColumnData keyColumn = iterator.next();
+ boolean onLastColumn = !iterator.hasNext();
+ queryBuilder.append(quoteIdentifier(keyColumn.getColumnName())).append(" = ?");
+ if (!onLastColumn) {
+ queryBuilder.append(" AND ");
+ }
+ }
+ return queryBuilder.toString();
}
String createUpdateSqlString(String tableName, EntryColumnData entryColumnData) {
- StringBuilder query = new StringBuilder("UPDATE " + quoteIdentifier(tableName) + " SET ");
+ StringBuilder query =
+ new StringBuilder("UPDATE ").append(quoteIdentifier(tableName)).append(" SET ");
int idx = 0;
for (ColumnData column : entryColumnData.getEntryValueColumnData()) {
idx++;
@@ -44,31 +60,37 @@ class SqlStatementFactory {
query.append(quoteIdentifier(column.getColumnName()));
query.append(" = ?");
}
-
- ColumnData keyColumnData = entryColumnData.getEntryKeyColumnData();
- query.append(" WHERE ");
- query.append(quoteIdentifier(keyColumnData.getColumnName()));
- query.append(" = ?");
-
- return query.toString();
+ return addKeyColumnsToQuery(entryColumnData, query);
}
String createInsertSqlString(String tableName, EntryColumnData entryColumnData) {
StringBuilder columnNames =
- new StringBuilder("INSERT INTO " + quoteIdentifier(tableName) + " (");
+ new StringBuilder("INSERT INTO ").append(quoteIdentifier(tableName)).append(" (");
StringBuilder columnValues = new StringBuilder(" VALUES (");
-
- for (ColumnData column : entryColumnData.getEntryValueColumnData()) {
- columnNames.append(quoteIdentifier(column.getColumnName())).append(", ");
- columnValues.append("?,");
- }
-
- ColumnData keyColumnData = entryColumnData.getEntryKeyColumnData();
- columnNames.append(quoteIdentifier(keyColumnData.getColumnName())).append(")");
- columnValues.append("?)");
+ addColumnDataToSqlString(entryColumnData, columnNames, columnValues);
+ columnNames.append(')');
+ columnValues.append(')');
return columnNames.append(columnValues).toString();
}
+ private void addColumnDataToSqlString(EntryColumnData entryColumnData, StringBuilder columnNames,
+ StringBuilder columnValues) {
+ Stream<ColumnData> values = entryColumnData.getEntryValueColumnData().stream();
+ Stream<ColumnData> keys = entryColumnData.getEntryKeyColumnData().stream();
+ Stream<ColumnData> columnDataStream = Stream.concat(values, keys);
+ final boolean[] firstTime = new boolean[] {true};
+ columnDataStream.forEachOrdered(column -> {
+ if (!firstTime[0]) {
+ columnNames.append(',');
+ columnValues.append(',');
+ } else {
+ firstTime[0] = false;
+ }
+ columnNames.append(quoteIdentifier(column.getColumnName()));
+ columnValues.append('?');
+ });
+ }
+
private String quoteIdentifier(String identifier) {
return quote + identifier + quote;
}
diff --git a/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/SqlHandlerTest.java b/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/SqlHandlerTest.java
index edb2604..3e281c2 100644
--- a/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/SqlHandlerTest.java
+++ b/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/SqlHandlerTest.java
@@ -512,7 +512,9 @@ public class SqlHandlerTest {
assertThat(entryColumnData.getEntryKeyColumnData()).isNotNull();
assertThat(entryColumnData.getEntryValueColumnData()).isEmpty();
- assertThat(entryColumnData.getEntryKeyColumnData().getColumnName()).isEqualTo(KEY_COLUMN);
+ assertThat(entryColumnData.getEntryKeyColumnData()).hasSize(1);
+ assertThat(entryColumnData.getEntryKeyColumnData().get(0).getColumnName())
+ .isEqualTo(KEY_COLUMN);
}
@Test
@@ -531,7 +533,9 @@ public class SqlHandlerTest {
assertThat(entryColumnData.getEntryValueColumnData()).hasSize(1);
assertThat(entryColumnData.getEntryValueColumnData().get(0).getColumnName())
.isEqualTo(nonKeyColumn);
- assertThat(entryColumnData.getEntryKeyColumnData().getColumnName()).isEqualTo(KEY_COLUMN);
+ assertThat(entryColumnData.getEntryKeyColumnData()).hasSize(1);
+ assertThat(entryColumnData.getEntryKeyColumnData().get(0).getColumnName())
+ .isEqualTo(KEY_COLUMN);
}
@Test
@@ -544,7 +548,9 @@ public class SqlHandlerTest {
assertThat(entryColumnData.getEntryKeyColumnData()).isNotNull();
assertThat(entryColumnData.getEntryValueColumnData()).isEmpty();
- assertThat(entryColumnData.getEntryKeyColumnData().getColumnName()).isEqualTo(KEY_COLUMN);
+ assertThat(entryColumnData.getEntryKeyColumnData()).hasSize(1);
+ assertThat(entryColumnData.getEntryKeyColumnData().get(0).getColumnName())
+ .isEqualTo(KEY_COLUMN);
}
private ResultSet getPrimaryKeysMetaData() throws SQLException {
diff --git a/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/SqlStatementFactoryTest.java b/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/SqlStatementFactoryTest.java
index e4ccc4b..d854c45 100644
--- a/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/SqlStatementFactoryTest.java
+++ b/geode-connectors/src/test/java/org/apache/geode/connectors/jdbc/internal/SqlStatementFactoryTest.java
@@ -17,6 +17,7 @@ package org.apache.geode.connectors.jdbc.internal;
import static org.assertj.core.api.Assertions.assertThat;
import java.util.ArrayList;
+import java.util.Arrays;
import java.util.List;
import org.junit.Before;
@@ -39,7 +40,7 @@ public class SqlStatementFactoryTest {
columnData.add(new ColumnData(VALUE_COLUMN_1_NAME, null, 0));
columnData.add(new ColumnData(VALUE_COLUMN_2_NAME, null, 0));
ColumnData keyColumnData = new ColumnData(KEY_COLUMN_NAME, null, 0);
- entryColumnData = new EntryColumnData(keyColumnData, columnData);
+ entryColumnData = new EntryColumnData(Arrays.asList(keyColumnData), columnData);
}
@Test
@@ -74,7 +75,7 @@ public class SqlStatementFactoryTest {
@Test
public void getInsertSqlString() throws Exception {
- String expectedStatement = String.format("INSERT INTO %s (%s, %s, %s) VALUES (?,?,?)",
+ String expectedStatement = String.format("INSERT INTO %s (%s,%s,%s) VALUES (?,?,?)",
TABLE_NAME, VALUE_COLUMN_1_NAME, VALUE_COLUMN_2_NAME, KEY_COLUMN_NAME);
String statement = factory.createInsertSqlString(TABLE_NAME, entryColumnData);