Repository: googleapis/java-spanner-jdbc
Pull request #1224 (2023-05-10 08:56:05)

feat: support untyped NULL value parameters
Support untyped null parameter values for `PreparedStatement`. This will allow users to add null values as a statement parameter value without having to know what type the column that the parameter is bound to uses.


Patch:
diff --git a/pom.xml b/pom.xml
index 64082e8..3085d49 100644
--- a/pom.xml
+++ b/pom.xml
@@ -393,7 +393,7 @@
       <plugin>
         <groupId>org.apache.maven.plugins</groupId>
         <artifactId>maven-project-info-reports-plugin</artifactId>
-        <version>3.4.4</version>
+        <version>3.4.5</version>
         <reportSets>
           <reportSet>
             <reports>
diff --git a/samples/snippets/pom.xml b/samples/snippets/pom.xml
index cf10eb5..06de2d0 100644
--- a/samples/snippets/pom.xml
+++ b/samples/snippets/pom.xml
@@ -30,7 +30,7 @@
       <dependency>
         <groupId>com.google.cloud</groupId>
         <artifactId>libraries-bom</artifactId>
-        <version>26.15.0</version>
+        <version>26.16.0</version>
         <type>pom</type>
         <scope>import</scope>
       </dependency>
diff --git a/src/main/java/com/google/cloud/spanner/jdbc/AbstractJdbcPreparedStatement.java b/src/main/java/com/google/cloud/spanner/jdbc/AbstractJdbcPreparedStatement.java
index 847f54d..cd1c50a 100644
--- a/src/main/java/com/google/cloud/spanner/jdbc/AbstractJdbcPreparedStatement.java
+++ b/src/main/java/com/google/cloud/spanner/jdbc/AbstractJdbcPreparedStatement.java
@@ -81,7 +81,8 @@ abstract class AbstractJdbcPreparedStatement extends JdbcStatement implements Pr
   @Override
   public void setNull(int parameterIndex, int sqlType) throws SQLException {
     checkClosed();
-    parameters.setParameter(parameterIndex, null, sqlType, null);
+    parameters.setParameter(
+        parameterIndex, /* value = */ null, sqlType, /* scaleOrLength = */ null);
   }
 
   @Override
diff --git a/src/main/java/com/google/cloud/spanner/jdbc/JdbcParameterStore.java b/src/main/java/com/google/cloud/spanner/jdbc/JdbcParameterStore.java
index 3ce105a..5fb4177 100644
--- a/src/main/java/com/google/cloud/spanner/jdbc/JdbcParameterStore.java
+++ b/src/main/java/com/google/cloud/spanner/jdbc/JdbcParameterStore.java
@@ -24,6 +24,7 @@ import com.google.cloud.spanner.Type;
 import com.google.cloud.spanner.Value;
 import com.google.cloud.spanner.ValueBinder;
 import com.google.common.io.CharStreams;
+import com.google.protobuf.NullValue;
 import com.google.rpc.Code;
 import java.io.IOException;
 import java.io.InputStream;
@@ -231,6 +232,10 @@ class JdbcParameterStore {
   }
 
   private void checkTypeAndValueSupported(Object value, int sqlType) throws SQLException {
+    if (value == null) {
+      // null is always supported, as we will just fall back to an untyped NULL value.
+      return;
+    }
     if (!isTypeSupported(sqlType)) {
       throw JdbcSqlExceptionFactory.of(
           "Type " + sqlType + " is not supported", Code.INVALID_ARGUMENT);
@@ -775,8 +780,13 @@ class JdbcParameterStore {
         case Types.LONGVARBINARY:
         case Types.BLOB:
           return binder.toBytesArray(null);
+        default:
+          return binder.to(
+              Value.untyped(
+                  com.google.protobuf.Value.newBuilder()
+                      .setNullValue(NullValue.NULL_VALUE)
+                      .build()));
       }
-      throw JdbcSqlExceptionFactory.unsupported("Unknown/unsupported array base type: " + type);
     }
 
     if (boolean[].class.isAssignableFrom(value.getClass())) {
@@ -864,7 +874,9 @@ class JdbcParameterStore {
    */
   private Builder setNullValue(ValueBinder<Builder> binder, Integer sqlType) {
     if (sqlType == null) {
-      return binder.to((String) null);
+      return binder.to(
+          Value.untyped(
+              com.google.protobuf.Value.newBuilder().setNullValue(NullValue.NULL_VALUE).build()));
     }
     switch (sqlType) {
       case Types.BIGINT:
@@ -924,8 +936,14 @@ class JdbcParameterStore {
         return binder.to((ByteArray) null);
       case Types.VARCHAR:
         return binder.to((String) null);
+      case JsonType.VENDOR_TYPE_NUMBER:
+        return binder.to(Value.json(null));
+      case PgJsonbType.VENDOR_TYPE_NUMBER:
+        return binder.to(Value.pgJsonb(null));
       default:
-        throw new IllegalArgumentException("Unsupported sql type for setting to null: " + sqlType);
+        return binder.to(
+            Value.untyped(
+                com.google.protobuf.Value.newBuilder().setNullValue(NullValue.NULL_VALUE).build()));
     }
   }
 }


Test patch:
diff --git a/src/test/java/com/google/cloud/spanner/jdbc/JdbcPreparedStatementTest.java b/src/test/java/com/google/cloud/spanner/jdbc/JdbcPreparedStatementTest.java
index b4c33a8..8f21e68 100644
--- a/src/test/java/com/google/cloud/spanner/jdbc/JdbcPreparedStatementTest.java
+++ b/src/test/java/com/google/cloud/spanner/jdbc/JdbcPreparedStatementTest.java
@@ -20,7 +20,6 @@ import static org.junit.Assert.assertEquals;
 import static org.junit.Assert.assertNull;
 import static org.junit.Assert.assertThrows;
 import static org.junit.Assert.assertTrue;
-import static org.junit.Assert.fail;
 import static org.mockito.Mockito.any;
 import static org.mockito.Mockito.anyString;
 import static org.mockito.Mockito.mock;
@@ -39,7 +38,6 @@ import com.google.cloud.spanner.Type.StructField;
 import com.google.cloud.spanner.Value;
 import com.google.cloud.spanner.connection.AbstractStatementParser;
 import com.google.cloud.spanner.connection.Connection;
-import com.google.rpc.Code;
 import java.io.ByteArrayInputStream;
 import java.io.StringReader;
 import java.math.BigDecimal;
@@ -47,7 +45,6 @@ import java.net.MalformedURLException;
 import java.net.URL;
 import java.sql.Date;
 import java.sql.JDBCType;
-import java.sql.PreparedStatement;
 import java.sql.ResultSetMetaData;
 import java.sql.SQLException;
 import java.sql.Time;
@@ -197,7 +194,10 @@ public class JdbcPreparedStatementTest {
       ps.setObject(35, "TEST");
       ps.setObject(36, "TEST", Types.NVARCHAR);
       ps.setObject(37, "TEST", Types.NVARCHAR, 20);
+      ps.setRef(38, null);
+      ps.setRowId(39, null);
       ps.setShort(40, (short) 1);
+      ps.setSQLXML(41, null);
       ps.setString(42, "TEST");
       ps.setTime(43, new Time(1000L));
       ps.setTime(44, new Time(1000L), Calendar.getInstance(TimeZone.getTimeZone("GMT")));
@@ -211,8 +211,6 @@ public class JdbcPreparedStatementTest {
       ps.setObject(52, "{}", JsonType.VENDOR_TYPE_NUMBER);
       ps.setObject(53, "{}", PgJsonbType.VENDOR_TYPE_NUMBER);
 
-      testSetUnsupportedTypes(ps);
-
       JdbcParameterMetaData pmd = ps.getParameterMetaData();
       assertEquals(numberOfParams, pmd.getParameterCount());
       assertEquals(JdbcArray.class.getName(), pmd.getParameterClassName(1));
@@ -274,33 +272,9 @@ public class JdbcPreparedStatementTest {
     }
   }
 
-  private void testSetUnsupportedTypes(PreparedStatement ps) {
-    try {
-      ps.setRef(38, null);
-      fail("missing expected exception");
-    } catch (SQLException e) {
-      assertTrue(e instanceof JdbcSqlException);
-      assertEquals(Code.INVALID_ARGUMENT, ((JdbcSqlException) e).getCode());
-    }
-    try {
-      ps.setRowId(39, null);
-      fail("missing expected exception");
-    } catch (SQLException e) {
-      assertTrue(e instanceof JdbcSqlException);
-      assertEquals(Code.INVALID_ARGUMENT, ((JdbcSqlException) e).getCode());
-    }
-    try {
-      ps.setSQLXML(41, null);
-      fail("missing expected exception");
-    } catch (SQLException e) {
-      assertTrue(e instanceof JdbcSqlException);
-      assertEquals(Code.INVALID_ARGUMENT, ((JdbcSqlException) e).getCode());
-    }
-  }
-
   @Test
   public void testSetNullValues() throws SQLException {
-    final int numberOfParameters = 27;
+    final int numberOfParameters = 31;
     String sql = generateSqlWithParameters(numberOfParameters);
     try (JdbcPreparedStatement ps = new JdbcPreparedStatement(createMockConnection(), sql)) {
       int index = 0;
@@ -331,6 +305,10 @@ public class JdbcPreparedStatementTest {
       ps.setNull(++index, Types.BIT);
       ps.setNull(++index, Types.VARBINARY);
       ps.setNull(++index, Types.VARCHAR);
+      ps.setNull(++index, JsonType.VENDOR_TYPE_NUMBER);
+      ps.setNull(++index, PgJsonbType.VENDOR_TYPE_NUMBER);
+      ps.setNull(++index, Types.OTHER);
+      ps.setNull(++index, Types.NULL);
       assertEquals(numberOfParameters, index);
 
       JdbcParameterMetaData pmd = ps.getParameterMetaData();
diff --git a/src/test/java/com/google/cloud/spanner/jdbc/JdbcPreparedStatementWithMockedServerTest.java b/src/test/java/com/google/cloud/spanner/jdbc/JdbcPreparedStatementWithMockedServerTest.java
index 9da17d7..d3607d8 100644
--- a/src/test/java/com/google/cloud/spanner/jdbc/JdbcPreparedStatementWithMockedServerTest.java
+++ b/src/test/java/com/google/cloud/spanner/jdbc/JdbcPreparedStatementWithMockedServerTest.java
@@ -25,6 +25,7 @@ import com.google.cloud.spanner.ErrorCode;
 import com.google.cloud.spanner.MockSpannerServiceImpl;
 import com.google.cloud.spanner.MockSpannerServiceImpl.StatementResult;
 import com.google.cloud.spanner.Statement;
+import com.google.cloud.spanner.Value;
 import com.google.cloud.spanner.connection.SpannerPool;
 import com.google.cloud.spanner.jdbc.JdbcSqlExceptionFactory.JdbcSqlBatchUpdateException;
 import io.grpc.Server;
@@ -36,6 +37,7 @@ import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.PreparedStatement;
 import java.sql.SQLException;
+import java.sql.Types;
 import java.util.Arrays;
 import java.util.Collection;
 import org.junit.After;
@@ -193,4 +195,69 @@ public class JdbcPreparedStatementWithMockedServerTest {
       }
     }
   }
+
+  @Test
+  public void testInsertUntypedNullValues() throws SQLException {
+    mockSpanner.putStatementResult(
+        StatementResult.update(
+            Statement.newBuilder(
+                    "insert into all_nullable_types (ColInt64, ColFloat64, ColBool, ColString, ColBytes, ColDate, ColTimestamp, ColNumeric, ColJson, ColInt64Array, ColFloat64Array, ColBoolArray, ColStringArray, ColBytesArray, ColDateArray, ColTimestampArray, ColNumericArray, ColJsonArray) "
+                        + "values (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18)")
+                .bind("p1")
+                .to((Value) null)
+                .bind("p2")
+                .to((Value) null)
+                .bind("p3")
+                .to((Value) null)
+                .bind("p4")
+                .to((Value) null)
+                .bind("p5")
+                .to((Value) null)
+                .bind("p6")
+                .to((Value) null)
+                .bind("p7")
+                .to((Value) null)
+                .bind("p8")
+                .to((Value) null)
+                .bind("p9")
+                .to((Value) null)
+                .bind("p10")
+                .to((Value) null)
+                .bind("p11")
+                .to((Value) null)
+                .bind("p12")
+                .to((Value) null)
+                .bind("p13")
+                .to((Value) null)
+                .bind("p14")
+                .to((Value) null)
+                .bind("p15")
+                .to((Value) null)
+                .bind("p16")
+                .to((Value) null)
+                .bind("p17")
+                .to((Value) null)
+                .bind("p18")
+                .to((Value) null)
+                .build(),
+            1L));
+    try (Connection connection = createConnection()) {
+      for (int type : new int[] {Types.OTHER, Types.NULL}) {
+        try (PreparedStatement statement =
+            connection.prepareStatement(
+                "insert into all_nullable_types ("
+                    + "ColInt64, ColFloat64, ColBool, ColString, ColBytes, ColDate, ColTimestamp, ColNumeric, ColJson, "
+                    + "ColInt64Array, ColFloat64Array, ColBoolArray, ColStringArray, ColBytesArray, ColDateArray, ColTimestampArray, ColNumericArray, ColJsonArray) "
+                    + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
+          for (int param = 1;
+              param <= statement.getParameterMetaData().getParameterCount();
+              param++) {
+            statement.setNull(param, type);
+          }
+          assertEquals(1, statement.executeUpdate());
+        }
+        mockSpanner.clearRequests();
+      }
+    }
+  }
 }
diff --git a/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcDatabaseMetaDataTest.java b/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcDatabaseMetaDataTest.java
index ea7c236..7e2481a 100644
--- a/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcDatabaseMetaDataTest.java
+++ b/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcDatabaseMetaDataTest.java
@@ -512,7 +512,8 @@ public class ITJdbcDatabaseMetaDataTest extends ITAbstractJdbcTest {
           new IndexInfo("TableWithRef", false, "PRIMARY_KEY", 1, "Id", "A"),
           new IndexInfo("TableWithRef", true, "FOREIGN_KEY", 1, "RefFloat", "A"),
           new IndexInfo("TableWithRef", true, "FOREIGN_KEY", 2, "RefString", "A"),
-          new IndexInfo("TableWithRef", true, "FOREIGN_KEY", 3, "RefDate", "A"));
+          new IndexInfo("TableWithRef", true, "FOREIGN_KEY", 3, "RefDate", "A"),
+          new IndexInfo("all_nullable_types", false, "PRIMARY_KEY", 1, "ColInt64", "A"));
 
   @Test
   public void testGetIndexInfo() throws SQLException {
@@ -860,7 +861,8 @@ public class ITJdbcDatabaseMetaDataTest extends ITAbstractJdbcTest {
           new Table("SingersView", "VIEW"),
           new Table("Songs"),
           new Table("TableWithAllColumnTypes"),
-          new Table("TableWithRef"));
+          new Table("TableWithRef"),
+          new Table("all_nullable_types"));
 
   @Test
   public void testGetTables() throws SQLException {
diff --git a/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcPgDatabaseMetaDataTest.java b/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcPgDatabaseMetaDataTest.java
index bbd3712..ff0bff7 100644
--- a/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcPgDatabaseMetaDataTest.java
+++ b/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcPgDatabaseMetaDataTest.java
@@ -420,6 +420,7 @@ public class ITJdbcPgDatabaseMetaDataTest extends ITAbstractJdbcTest {
           new IndexInfo("albums", false, "PRIMARY_KEY", 1, "singerid", "A"),
           new IndexInfo("albums", false, "PRIMARY_KEY", 2, "albumid", "A"),
           new IndexInfo("albums", true, "albumsbyalbumtitle", 1, "albumtitle", "A"),
+          new IndexInfo("all_nullable_types", false, "PRIMARY_KEY", 1, "colint64", "A"),
           new IndexInfo("concerts", false, "PRIMARY_KEY", 1, "venueid", "A"),
           new IndexInfo("concerts", false, "PRIMARY_KEY", 2, "singerid", "A"),
           new IndexInfo("concerts", false, "PRIMARY_KEY", 3, "concertdate", "A"),
@@ -790,6 +791,7 @@ public class ITJdbcPgDatabaseMetaDataTest extends ITAbstractJdbcTest {
   private static final List<Table> EXPECTED_TABLES =
       Arrays.asList(
           new Table("albums"),
+          new Table("all_nullable_types"),
           new Table("concerts"),
           new Table("singers"),
           // TODO: Enable when views are supported for PostgreSQL dialect databases.
diff --git a/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcPreparedStatementTest.java b/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcPreparedStatementTest.java
index c1d8416..2864559 100644
--- a/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcPreparedStatementTest.java
+++ b/src/test/java/com/google/cloud/spanner/jdbc/it/ITJdbcPreparedStatementTest.java
@@ -1294,6 +1294,39 @@ public class ITJdbcPreparedStatementTest extends ITAbstractJdbcTest {
     }
   }
 
+  @Test
+  public void test13_InsertUntypedNullValues() throws SQLException {
+    try (Connection connection = createConnection(env, database)) {
+      try (PreparedStatement preparedStatement =
+          connection.prepareStatement(
+              "insert into all_nullable_types ("
+                  + "ColInt64, ColFloat64, ColBool, ColString, ColBytes, ColDate, ColTimestamp, ColNumeric, ColJson, "
+                  + "ColInt64Array, ColFloat64Array, ColBoolArray, ColStringArray, ColBytesArray, ColDateArray, ColTimestampArray, ColNumericArray, ColJsonArray) "
+                  + "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
+        for (int param = 1;
+            param <= preparedStatement.getParameterMetaData().getParameterCount();
+            param++) {
+          preparedStatement.setNull(param, Types.OTHER);
+        }
+        if (getDialect() == Dialect.POSTGRESQL) {
+          // PostgreSQL-dialect databases do not allow NULLs in primary keys.
+          preparedStatement.setLong(1, 1L);
+        }
+        assertEquals(1, preparedStatement.executeUpdate());
+
+        // Verify that calling preparedStatement.setObject(index, null) works.
+        for (int param = 1;
+            param <= preparedStatement.getParameterMetaData().getParameterCount();
+            param++) {
+          preparedStatement.setObject(param, null);
+        }
+        // We need a different primary key value to insert another row.
+        preparedStatement.setLong(1, 2L);
+        assertEquals(1, preparedStatement.executeUpdate());
+      }
+    }
+  }
+
   private List<String> readValuesFromFile(String filename) {
     StringBuilder builder = new StringBuilder();
     try (InputStream stream = ITJdbcPreparedStatementTest.class.getResourceAsStream(filename)) {
diff --git a/src/test/resources/com/google/cloud/spanner/jdbc/it/CreateMusicTables.sql b/src/test/resources/com/google/cloud/spanner/jdbc/it/CreateMusicTables.sql
index 24579b2..153dd77 100644
--- a/src/test/resources/com/google/cloud/spanner/jdbc/it/CreateMusicTables.sql
+++ b/src/test/resources/com/google/cloud/spanner/jdbc/it/CreateMusicTables.sql
@@ -97,6 +97,29 @@ CREATE TABLE TableWithAllColumnTypes (
 ) PRIMARY KEY (ColInt64)
 ;
 
+CREATE TABLE all_nullable_types (
+    ColInt64	    INT64,
+    ColFloat64	    FLOAT64,
+    ColBool		    BOOL,
+    ColString		STRING(100),
+    ColBytes		BYTES(100),
+    ColDate		    DATE,
+    ColTimestamp	TIMESTAMP,
+    ColNumeric	    NUMERIC,
+    ColJson		    JSON,
+    
+    ColInt64Array		ARRAY<INT64>,
+    ColFloat64Array     ARRAY<FLOAT64>,
+    ColBoolArray		ARRAY<BOOL>,
+    ColStringArray	    ARRAY<STRING(100)>,
+    ColBytesArray		ARRAY<BYTES(100)>,
+    ColDateArray		ARRAY<DATE>,
+    ColTimestampArray	ARRAY<TIMESTAMP>,
+    ColNumericArray	    ARRAY<NUMERIC>,
+    ColJsonArray		ARRAY<JSON>,
+) PRIMARY KEY (ColInt64)
+;
+
 CREATE TABLE TableWithRef (
   Id          INT64       NOT NULL,
   RefFloat    FLOAT64     NOT NULL,
diff --git a/src/test/resources/com/google/cloud/spanner/jdbc/it/CreateMusicTables_Emulator.sql b/src/test/resources/com/google/cloud/spanner/jdbc/it/CreateMusicTables_Emulator.sql
index aaba70c..6aa8e84 100644
--- a/src/test/resources/com/google/cloud/spanner/jdbc/it/CreateMusicTables_Emulator.sql
+++ b/src/test/resources/com/google/cloud/spanner/jdbc/it/CreateMusicTables_Emulator.sql
@@ -92,6 +92,29 @@ CREATE TABLE TableWithAllColumnTypes (
 ) PRIMARY KEY (ColInt64)
 ;
 
+CREATE TABLE all_nullable_types (
+    ColInt64	    INT64,
+    ColFloat64	    FLOAT64,
+    ColBool		    BOOL,
+    ColString		STRING(100),
+    ColBytes		BYTES(100),
+    ColDate		    DATE,
+    ColTimestamp	TIMESTAMP,
+    ColNumeric	    NUMERIC,
+    ColJson		    JSON,
+
+    ColInt64Array		ARRAY<INT64>,
+    ColFloat64Array     ARRAY<FLOAT64>,
+    ColBoolArray		ARRAY<BOOL>,
+    ColStringArray	    ARRAY<STRING(100)>,
+    ColBytesArray		ARRAY<BYTES(100)>,
+    ColDateArray		ARRAY<DATE>,
+    ColTimestampArray	ARRAY<TIMESTAMP>,
+    ColNumericArray	    ARRAY<NUMERIC>,
+    ColJsonArray		ARRAY<JSON>,
+) PRIMARY KEY (ColInt64)
+;
+
 CREATE TABLE TableWithRef (
   Id          INT64       NOT NULL,
   RefFloat    FLOAT64     NOT NULL,
diff --git a/src/test/resources/com/google/cloud/spanner/jdbc/it/CreateMusicTables_PG.sql b/src/test/resources/com/google/cloud/spanner/jdbc/it/CreateMusicTables_PG.sql
index 438d2d5..c77968b 100644
--- a/src/test/resources/com/google/cloud/spanner/jdbc/it/CreateMusicTables_PG.sql
+++ b/src/test/resources/com/google/cloud/spanner/jdbc/it/CreateMusicTables_PG.sql
@@ -74,6 +74,27 @@ CREATE TABLE TableWithAllColumnTypes (
     ColNumeric   NUMERIC NOT NULL,
     ColJson      VARCHAR NOT NULL
 );
+CREATE TABLE all_nullable_types (
+    ColInt64	    bigint primary key,
+    ColFloat64	    float8,
+    ColBool		    boolean,
+    ColString		varchar(100),
+    ColBytes		bytea,
+    ColDate		    date,
+    ColTimestamp	timestamptz,
+    ColNumeric	    numeric,
+    ColJson		    jsonb,
+
+    ColInt64Array		bigint[],
+    ColFloat64Array     float8[],
+    ColBoolArray		boolean[],
+    ColStringArray	    varchar(100)[],
+    ColBytesArray		bytea[],
+    ColDateArray		date[],
+    ColTimestampArray	timestamptz[],
+    ColNumericArray	    numeric[],
+    ColJsonArray		jsonb[]
+);
 
 CREATE TABLE TableWithRef (
     Id          BIGINT NOT NULL PRIMARY KEY,