Repository: googleapis/java-spanner-jdbc (java)

Issue:
feat: Support getting/setting parameter and column values as com.google.cloud.spanner.Value instances
- The JDBC `ResultSet#getObject(int, Class type)` method should be able to return a `com.google.cloud.spanner.Value` instance
- The JDBC `PreparedStatement#setObject(int, Object value)` method should be able to accept a `com.google.cloud.spanner.Value` instance

Pull request:
feat: allow get/set Spanner Value instances
Adds support for getting a value from a `java.sql.ResultSet` as a `com.google.cloud.spanner.Value` instance, and for setting a parameter on a `java.sql.PreparedStatement` using a `com.google.cloud.spanner.Value` instance.

Fixes #452


Patch:
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 0507d5d..2a90566 100644
--- a/src/main/java/com/google/cloud/spanner/jdbc/AbstractJdbcPreparedStatement.java
+++ b/src/main/java/com/google/cloud/spanner/jdbc/AbstractJdbcPreparedStatement.java
@@ -198,7 +198,7 @@ abstract class AbstractJdbcPreparedStatement extends JdbcStatement implements Pr
   @Override
   public void setObject(int parameterIndex, Object value) throws SQLException {
     checkClosed();
-    parameters.setParameter(parameterIndex, value, null);
+    parameters.setParameter(parameterIndex, value, (SQLType) 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 2c529c4..75f16cd 100644
--- a/src/main/java/com/google/cloud/spanner/jdbc/JdbcParameterStore.java
+++ b/src/main/java/com/google/cloud/spanner/jdbc/JdbcParameterStore.java
@@ -19,6 +19,7 @@ package com.google.cloud.spanner.jdbc;
 import com.google.cloud.ByteArray;
 import com.google.cloud.spanner.Statement;
 import com.google.cloud.spanner.Statement.Builder;
+import com.google.cloud.spanner.Value;
 import com.google.cloud.spanner.ValueBinder;
 import com.google.cloud.spanner.jdbc.JdbcSqlExceptionFactory.JdbcSqlExceptionImpl;
 import com.google.common.io.CharStreams;
@@ -39,6 +40,7 @@ import java.sql.NClob;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.SQLType;
 import java.sql.Time;
 import java.sql.Timestamp;
 import java.sql.Types;
@@ -129,7 +131,8 @@ class JdbcParameterStore {
         getParameter(parameterIndex),
         getType(parameterIndex),
         getScaleOrLength(parameterIndex),
-        column);
+        column,
+        null);
   }
 
   void setType(int parameterIndex, Integer type) throws SQLException {
@@ -138,26 +141,71 @@ class JdbcParameterStore {
         getParameter(parameterIndex),
         type,
         getScaleOrLength(parameterIndex),
-        getColumn(parameterIndex));
+        getColumn(parameterIndex),
+        null);
   }
 
+  /** Sets a parameter value. The type will be determined based on the type of the value. */
+  void setParameter(int parameterIndex, Object value) throws SQLException {
+    setParameter(parameterIndex, value, null, null, null, null);
+  }
+
+  /** Sets a parameter value as the specified vendor-specific {@link SQLType}. */
+  void setParameter(int parameterIndex, Object value, SQLType sqlType) throws SQLException {
+    setParameter(parameterIndex, value, null, null, null, sqlType);
+  }
+
+  /**
+   * Sets a parameter value as the specified vendor-specific {@link SQLType} with the specified
+   * scale or length. This method is only here to support the {@link
+   * PreparedStatement#setObject(int, Object, SQLType, int)} method.
+   */
+  void setParameter(int parameterIndex, Object value, SQLType sqlType, Integer scaleOrLength)
+      throws SQLException {
+    setParameter(parameterIndex, value, null, scaleOrLength, null, sqlType);
+  }
+
+  /**
+   * Sets a parameter value as the specified sql type. The type can be one of the constants in
+   * {@link Types} or a vendor specific type code supplied by a vendor specific {@link SQLType}.
+   */
   void setParameter(int parameterIndex, Object value, Integer sqlType) throws SQLException {
     setParameter(parameterIndex, value, sqlType, null);
   }
 
+  /**
+   * Sets a parameter value as the specified sql type with the specified scale or length. The type
+   * can be one of the constants in {@link Types} or a vendor specific type code supplied by a
+   * vendor specific {@link SQLType}.
+   */
   void setParameter(int parameterIndex, Object value, Integer sqlType, Integer scaleOrLength)
       throws SQLException {
-    setParameter(parameterIndex, value, sqlType, scaleOrLength, null);
+    setParameter(parameterIndex, value, sqlType, scaleOrLength, null, null);
   }
 
+  /**
+   * Sets a parameter value as the specified sql type with the specified scale or length. Any {@link
+   * SQLType} instance will take precedence over sqlType. The type can be one of the constants in
+   * {@link Types} or a vendor specific type code supplied by a vendor specific {@link SQLType}.
+   */
   void setParameter(
-      int parameterIndex, Object value, Integer sqlType, Integer scaleOrLength, String column)
+      int parameterIndex,
+      Object value,
+      Integer sqlType,
+      Integer scaleOrLength,
+      String column,
+      SQLType sqlTypeObject)
       throws SQLException {
-    // check that only valid type/value combinations are entered
-    if (sqlType != null) {
-      checkTypeAndValueSupported(value, sqlType);
-    }
-    // set the parameter
+    // Ignore the sql type if the application has created a Spanner Value object.
+    if (!(value instanceof Value)) {
+      // check that only valid type/value combinations are entered
+      if (sqlTypeObject != null && sqlType == null) {
+        sqlType = sqlTypeObject.getVendorTypeNumber();
+      }
+      if (sqlType != null) {
+        checkTypeAndValueSupported(value, sqlType);
+      }
+    } // set the parameter
     highestIndex = Math.max(parameterIndex, highestIndex);
     int arrayIndex = parameterIndex - 1;
     if (arrayIndex >= parametersList.size() || parametersList.get(arrayIndex) == null) {
@@ -416,7 +464,11 @@ class JdbcParameterStore {
   /** Set a value from a JDBC parameter on a Spanner {@link Statement}. */
   Builder setValue(ValueBinder<Builder> binder, Object value, Integer sqlType) throws SQLException {
     Builder res;
-    if (sqlType != null && sqlType == Types.ARRAY) {
+    if (value instanceof Value) {
+      // If a Value has been constructed, then that should override any sqlType that might have been
+      // supplied.
+      res = binder.to((Value) value);
+    } else if (sqlType != null && sqlType == Types.ARRAY) {
       if (value instanceof Array) {
         Array array = (Array) value;
         value = array.getArray();
diff --git a/src/main/java/com/google/cloud/spanner/jdbc/JdbcPreparedStatement.java b/src/main/java/com/google/cloud/spanner/jdbc/JdbcPreparedStatement.java
index b0032e4..919aad3 100644
--- a/src/main/java/com/google/cloud/spanner/jdbc/JdbcPreparedStatement.java
+++ b/src/main/java/com/google/cloud/spanner/jdbc/JdbcPreparedStatement.java
@@ -24,6 +24,7 @@ import com.google.cloud.spanner.Struct;
 import com.google.cloud.spanner.Type;
 import com.google.cloud.spanner.connection.StatementParser;
 import com.google.cloud.spanner.jdbc.JdbcParameterStore.ParametersInfo;
+import com.google.common.annotations.VisibleForTesting;
 import com.google.common.collect.ImmutableList;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
@@ -48,7 +49,8 @@ class JdbcPreparedStatement extends AbstractJdbcPreparedStatement {
     return parameters;
   }
 
-  private Statement createStatement() throws SQLException {
+  @VisibleForTesting
+  Statement createStatement() throws SQLException {
     ParametersInfo paramInfo = getParametersInfo();
     Statement.Builder builder = Statement.newBuilder(paramInfo.sqlWithNamedParameters);
     for (int index = 1; index <= getParameters().getHighestIndex(); index++) {
diff --git a/src/main/java/com/google/cloud/spanner/jdbc/JdbcTypeConverter.java b/src/main/java/com/google/cloud/spanner/jdbc/JdbcTypeConverter.java
index c1f4b92..6aa9b48 100644
--- a/src/main/java/com/google/cloud/spanner/jdbc/JdbcTypeConverter.java
+++ b/src/main/java/com/google/cloud/spanner/jdbc/JdbcTypeConverter.java
@@ -21,6 +21,7 @@ import com.google.cloud.Date;
 import com.google.cloud.Timestamp;
 import com.google.cloud.spanner.Type;
 import com.google.cloud.spanner.Type.Code;
+import com.google.cloud.spanner.Value;
 import java.math.BigDecimal;
 import java.math.BigInteger;
 import java.nio.charset.Charset;
@@ -28,6 +29,7 @@ import java.sql.Array;
 import java.sql.SQLException;
 import java.sql.Time;
 import java.util.ArrayList;
+import java.util.Arrays;
 import java.util.Calendar;
 import java.util.List;
 import java.util.concurrent.TimeUnit;
@@ -59,8 +61,13 @@ class JdbcTypeConverter {
     JdbcPreconditions.checkArgument(targetType != null, "targetType may not be null");
     checkValidTypeAndValueForConvert(type, value);
 
-    if (value == null) return null;
+    if (value == null) {
+      return null;
+    }
     try {
+      if (targetType.equals(Value.class)) {
+        return convertToSpannerValue(value, type);
+      }
       if (targetType.equals(String.class)) {
         if (type.getCode() == Code.BYTES) return new String((byte[]) value, UTF8);
         if (type.getCode() == Code.TIMESTAMP) {
@@ -155,6 +162,58 @@ class JdbcTypeConverter {
         com.google.rpc.Code.INVALID_ARGUMENT);
   }
 
+  private static Value convertToSpannerValue(Object value, Type type) throws SQLException {
+    switch (type.getCode()) {
+      case ARRAY:
+        switch (type.getArrayElementType().getCode()) {
+          case BOOL:
+            return Value.boolArray(Arrays.asList((Boolean[]) ((java.sql.Array) value).getArray()));
+          case BYTES:
+            return Value.bytesArray(toGoogleBytes((byte[][]) ((java.sql.Array) value).getArray()));
+          case DATE:
+            return Value.dateArray(
+                toGoogleDates((java.sql.Date[]) ((java.sql.Array) value).getArray()));
+          case FLOAT64:
+            return Value.float64Array(
+                Arrays.asList((Double[]) ((java.sql.Array) value).getArray()));
+          case INT64:
+            return Value.int64Array(Arrays.asList((Long[]) ((java.sql.Array) value).getArray()));
+          case NUMERIC:
+            return Value.numericArray(
+                Arrays.asList((BigDecimal[]) ((java.sql.Array) value).getArray()));
+          case STRING:
+            return Value.stringArray(Arrays.asList((String[]) ((java.sql.Array) value).getArray()));
+          case TIMESTAMP:
+            return Value.timestampArray(
+                toGoogleTimestamps((java.sql.Timestamp[]) ((java.sql.Array) value).getArray()));
+          case STRUCT:
+          default:
+            throw JdbcSqlExceptionFactory.of(
+                "invalid argument: " + value, com.google.rpc.Code.INVALID_ARGUMENT);
+        }
+      case BOOL:
+        return Value.bool((Boolean) value);
+      case BYTES:
+        return Value.bytes(ByteArray.copyFrom((byte[]) value));
+      case DATE:
+        return Value.date(toGoogleDate((java.sql.Date) value));
+      case FLOAT64:
+        return Value.float64((Double) value);
+      case INT64:
+        return Value.int64((Long) value);
+      case NUMERIC:
+        return Value.numeric((BigDecimal) value);
+      case STRING:
+        return Value.string((String) value);
+      case TIMESTAMP:
+        return Value.timestamp(toGoogleTimestamp((java.sql.Timestamp) value));
+      case STRUCT:
+      default:
+        throw JdbcSqlExceptionFactory.of(
+            "invalid argument: " + value, com.google.rpc.Code.INVALID_ARGUMENT);
+    }
+  }
+
   private static void checkValidTypeAndValueForConvert(Type type, Object value)
       throws SQLException {
     if (value == null) return;


Test patch:
diff --git a/src/test/java/com/google/cloud/spanner/jdbc/JdbcParameterStoreTest.java b/src/test/java/com/google/cloud/spanner/jdbc/JdbcParameterStoreTest.java
index 7f68601..0515188 100644
--- a/src/test/java/com/google/cloud/spanner/jdbc/JdbcParameterStoreTest.java
+++ b/src/test/java/com/google/cloud/spanner/jdbc/JdbcParameterStoreTest.java
@@ -44,6 +44,7 @@ import java.sql.Time;
 import java.sql.Timestamp;
 import java.sql.Types;
 import java.util.Arrays;
+import java.util.Collections;
 import java.util.UUID;
 import org.junit.Test;
 import org.junit.runner.RunWith;
@@ -52,6 +53,61 @@ import org.junit.runners.JUnit4;
 @RunWith(JUnit4.class)
 public class JdbcParameterStoreTest {
 
+  /**
+   * Tests setting a {@link Value} as a parameter value.
+   *
+   * @throws SQLException
+   */
+  @Test
+  public void testSetValueAsParameter() throws SQLException {
+    JdbcParameterStore params = new JdbcParameterStore();
+    params.setParameter(1, Value.bool(true));
+    verifyParameter(params, Value.bool(true));
+    params.setParameter(1, Value.bytes(ByteArray.copyFrom("test")));
+    verifyParameter(params, Value.bytes(ByteArray.copyFrom("test")));
+    params.setParameter(1, Value.date(com.google.cloud.Date.fromYearMonthDay(2021, 5, 3)));
+    verifyParameter(params, Value.date(com.google.cloud.Date.fromYearMonthDay(2021, 5, 3)));
+    params.setParameter(1, Value.float64(3.14d));
+    verifyParameter(params, Value.float64(3.14d));
+    params.setParameter(1, Value.int64(1L));
+    verifyParameter(params, Value.int64(1L));
+    params.setParameter(1, Value.numeric(BigDecimal.TEN));
+    verifyParameter(params, Value.numeric(BigDecimal.TEN));
+    params.setParameter(1, Value.string("test"));
+    verifyParameter(params, Value.string("test"));
+    params.setParameter(
+        1, Value.timestamp(com.google.cloud.Timestamp.ofTimeSecondsAndNanos(9999L, 101)));
+    verifyParameter(
+        params, Value.timestamp(com.google.cloud.Timestamp.ofTimeSecondsAndNanos(9999L, 101)));
+
+    params.setParameter(1, Value.boolArray(new boolean[] {true, false}));
+    verifyParameter(params, Value.boolArray(new boolean[] {true, false}));
+    params.setParameter(1, Value.bytesArray(Collections.singleton(ByteArray.copyFrom("test"))));
+    verifyParameter(params, Value.bytesArray(Collections.singleton(ByteArray.copyFrom("test"))));
+    params.setParameter(
+        1,
+        Value.dateArray(Collections.singleton(com.google.cloud.Date.fromYearMonthDay(2021, 5, 3))));
+    verifyParameter(
+        params,
+        Value.dateArray(Collections.singleton(com.google.cloud.Date.fromYearMonthDay(2021, 5, 3))));
+    params.setParameter(1, Value.float64Array(Collections.singleton(3.14d)));
+    verifyParameter(params, Value.float64Array(Collections.singleton(3.14d)));
+    params.setParameter(1, Value.int64Array(Collections.singleton(1L)));
+    verifyParameter(params, Value.int64Array(Collections.singleton(1L)));
+    params.setParameter(1, Value.numericArray(Collections.singleton(BigDecimal.TEN)));
+    verifyParameter(params, Value.numericArray(Collections.singleton(BigDecimal.TEN)));
+    params.setParameter(1, Value.stringArray(Collections.singleton("test")));
+    verifyParameter(params, Value.stringArray(Collections.singleton("test")));
+    params.setParameter(
+        1,
+        Value.timestampArray(
+            Collections.singleton(com.google.cloud.Timestamp.ofTimeSecondsAndNanos(9999L, 101))));
+    verifyParameter(
+        params,
+        Value.timestampArray(
+            Collections.singleton(com.google.cloud.Timestamp.ofTimeSecondsAndNanos(9999L, 101))));
+  }
+
   /** Tests setting a parameter value together with a sql type */
   @SuppressWarnings("deprecation")
   @Test
@@ -422,55 +478,55 @@ public class JdbcParameterStoreTest {
   @Test
   public void testSetParameterWithoutType() throws SQLException {
     JdbcParameterStore params = new JdbcParameterStore();
-    params.setParameter(1, (byte) 1, null);
+    params.setParameter(1, (byte) 1, (Integer) null);
     assertEquals(1, ((Byte) params.getParameter(1)).byteValue());
     verifyParameter(params, Value.int64(1));
-    params.setParameter(1, (short) 1, null);
+    params.setParameter(1, (short) 1, (Integer) null);
     assertEquals(1, ((Short) params.getParameter(1)).shortValue());
     verifyParameter(params, Value.int64(1));
-    params.setParameter(1, 1, null);
+    params.setParameter(1, 1, (Integer) null);
     assertEquals(1, ((Integer) params.getParameter(1)).intValue());
     verifyParameter(params, Value.int64(1));
-    params.setParameter(1, 1L, null);
+    params.setParameter(1, 1L, (Integer) null);
     assertEquals(1, ((Long) params.getParameter(1)).longValue());
     verifyParameter(params, Value.int64(1));
-    params.setParameter(1, (float) 1, null);
+    params.setParameter(1, (float) 1, (Integer) null);
     assertEquals(1.0f, ((Float) params.getParameter(1)).floatValue(), 0.0f);
     verifyParameter(params, Value.float64(1));
-    params.setParameter(1, (double) 1, null);
+    params.setParameter(1, (double) 1, (Integer) null);
     assertEquals(1.0d, ((Double) params.getParameter(1)).doubleValue(), 0.0d);
     verifyParameter(params, Value.float64(1));
-    params.setParameter(1, new Date(1970 - 1900, 0, 1), null);
+    params.setParameter(1, new Date(1970 - 1900, 0, 1), (Integer) null);
     assertEquals(new Date(1970 - 1900, 0, 1), params.getParameter(1));
     verifyParameter(params, Value.date(com.google.cloud.Date.fromYearMonthDay(1970, 1, 1)));
-    params.setParameter(1, new Time(0L), null);
+    params.setParameter(1, new Time(0L), (Integer) null);
     assertEquals(new Time(0L), params.getParameter(1));
     verifyParameter(
         params, Value.timestamp(com.google.cloud.Timestamp.ofTimeSecondsAndNanos(0L, 0)));
-    params.setParameter(1, new Timestamp(0L), null);
+    params.setParameter(1, new Timestamp(0L), (Integer) null);
     assertEquals(new Timestamp(0L), params.getParameter(1));
     verifyParameter(
         params, Value.timestamp(com.google.cloud.Timestamp.ofTimeSecondsAndNanos(0L, 0)));
-    params.setParameter(1, new byte[] {1, 2, 3}, null);
+    params.setParameter(1, new byte[] {1, 2, 3}, (Integer) null);
     assertArrayEquals(new byte[] {1, 2, 3}, (byte[]) params.getParameter(1));
     verifyParameter(params, Value.bytes(ByteArray.copyFrom(new byte[] {1, 2, 3})));
 
-    params.setParameter(1, new JdbcBlob(new byte[] {1, 2, 3}), null);
+    params.setParameter(1, new JdbcBlob(new byte[] {1, 2, 3}), (Integer) null);
     assertEquals(new JdbcBlob(new byte[] {1, 2, 3}), params.getParameter(1));
     verifyParameter(params, Value.bytes(ByteArray.copyFrom(new byte[] {1, 2, 3})));
-    params.setParameter(1, new JdbcClob("test"), null);
+    params.setParameter(1, new JdbcClob("test"), (Integer) null);
     assertEquals(new JdbcClob("test"), params.getParameter(1));
     verifyParameter(params, Value.string("test"));
-    params.setParameter(1, true, null);
+    params.setParameter(1, true, (Integer) null);
     assertTrue((Boolean) params.getParameter(1));
     verifyParameter(params, Value.bool(true));
-    params.setParameter(1, "test", null);
+    params.setParameter(1, "test", (Integer) null);
     assertEquals("test", params.getParameter(1));
     verifyParameter(params, Value.string("test"));
-    params.setParameter(1, new JdbcClob("test"), null);
+    params.setParameter(1, new JdbcClob("test"), (Integer) null);
     assertEquals(new JdbcClob("test"), params.getParameter(1));
     verifyParameter(params, Value.string("test"));
-    params.setParameter(1, UUID.fromString("83b988cf-1f4e-428a-be3d-cc712621942e"), null);
+    params.setParameter(1, UUID.fromString("83b988cf-1f4e-428a-be3d-cc712621942e"), (Integer) null);
     assertEquals(UUID.fromString("83b988cf-1f4e-428a-be3d-cc712621942e"), params.getParameter(1));
     verifyParameter(params, Value.string("83b988cf-1f4e-428a-be3d-cc712621942e"));
   }
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 b19aa54..40ad1ad 100644
--- a/src/test/java/com/google/cloud/spanner/jdbc/JdbcPreparedStatementTest.java
+++ b/src/test/java/com/google/cloud/spanner/jdbc/JdbcPreparedStatementTest.java
@@ -25,6 +25,7 @@ import static org.mockito.Matchers.anyString;
 import static org.mockito.Mockito.mock;
 import static org.mockito.Mockito.when;
 
+import com.google.cloud.ByteArray;
 import com.google.cloud.spanner.ReadContext.QueryAnalyzeMode;
 import com.google.cloud.spanner.ResultSet;
 import com.google.cloud.spanner.ResultSets;
@@ -32,10 +33,12 @@ import com.google.cloud.spanner.Statement;
 import com.google.cloud.spanner.Struct;
 import com.google.cloud.spanner.Type;
 import com.google.cloud.spanner.Type.StructField;
+import com.google.cloud.spanner.Value;
 import com.google.cloud.spanner.connection.Connection;
 import com.google.rpc.Code;
 import java.io.ByteArrayInputStream;
 import java.io.StringReader;
+import java.math.BigDecimal;
 import java.net.MalformedURLException;
 import java.net.URL;
 import java.sql.Date;
@@ -51,6 +54,7 @@ import java.sql.Timestamp;
 import java.sql.Types;
 import java.util.Arrays;
 import java.util.Calendar;
+import java.util.Collections;
 import java.util.TimeZone;
 import java.util.UUID;
 import org.junit.Test;
@@ -99,6 +103,41 @@ public class JdbcPreparedStatementTest {
     return connection;
   }
 
+  @Test
+  public void testValueAsParameter() throws SQLException {
+    String sql = generateSqlWithParameters(1);
+    JdbcConnection connection = createMockConnection();
+    for (Value value :
+        new Value[] {
+          Value.bool(true),
+          Value.bool(false),
+          Value.bytes(ByteArray.copyFrom("foo")),
+          Value.date(com.google.cloud.Date.fromYearMonthDay(2021, 5, 17)),
+          Value.float64(6.626d),
+          Value.int64(13L),
+          Value.numeric(new BigDecimal("3.14")),
+          Value.string("bar"),
+          Value.timestamp(com.google.cloud.Timestamp.ofTimeSecondsAndNanos(999L, 99)),
+          Value.boolArray(Collections.singleton(true)),
+          Value.bytesArray(Collections.singleton(ByteArray.copyFrom("foo"))),
+          Value.dateArray(
+              Collections.singleton(com.google.cloud.Date.fromYearMonthDay(2021, 5, 17))),
+          Value.float64Array(Collections.singleton(6.626d)),
+          Value.int64Array(Collections.singleton(13L)),
+          Value.numericArray(Collections.singleton(new BigDecimal("3.14"))),
+          Value.stringArray(Collections.singleton("bar")),
+          Value.timestampArray(
+              Collections.singleton(com.google.cloud.Timestamp.ofTimeSecondsAndNanos(999L, 99))),
+        }) {
+
+      try (JdbcPreparedStatement ps = new JdbcPreparedStatement(connection, sql)) {
+        ps.setObject(1, value);
+        Statement statement = ps.createStatement();
+        assertEquals(statement.getParameters().get("p1"), value);
+      }
+    }
+  }
+
   @SuppressWarnings("deprecation")
   @Test
   public void testParameters() throws SQLException, MalformedURLException {
diff --git a/src/test/java/com/google/cloud/spanner/jdbc/JdbcResultSetTest.java b/src/test/java/com/google/cloud/spanner/jdbc/JdbcResultSetTest.java
index 2ad4a6d..9fa5f3e 100644
--- a/src/test/java/com/google/cloud/spanner/jdbc/JdbcResultSetTest.java
+++ b/src/test/java/com/google/cloud/spanner/jdbc/JdbcResultSetTest.java
@@ -35,6 +35,7 @@ import com.google.cloud.spanner.ResultSets;
 import com.google.cloud.spanner.Struct;
 import com.google.cloud.spanner.Type;
 import com.google.cloud.spanner.Type.StructField;
+import com.google.cloud.spanner.Value;
 import com.google.cloud.spanner.jdbc.JdbcSqlExceptionFactory.JdbcSqlExceptionImpl;
 import com.google.rpc.Code;
 import java.io.IOException;
@@ -52,6 +53,7 @@ import java.util.Arrays;
 import java.util.Calendar;
 import java.util.Collections;
 import java.util.HashMap;
+import java.util.List;
 import java.util.Map;
 import java.util.TimeZone;
 import org.junit.Test;
@@ -125,6 +127,28 @@ public class JdbcResultSetTest {
   private static final String STRING_COL_TIME = "STRING_COL_TIME";
   private static final int STRING_COLINDEX_TIME = 24;
   private static final String STRING_TIME_VALUE = "10:31:15";
+  private static final String NUMERIC_COL_NULL = "NUMERIC_COL_NULL";
+  private static final String NUMERIC_COL_NOT_NULL = "NUMERIC_COL_NOT_NULL";
+  private static final BigDecimal NUMERIC_VALUE = new BigDecimal("3.14");
+  private static final int NUMERIC_COLINDEX_NULL = 25;
+  private static final int NUMERIC_COLINDEX_NOTNULL = 26;
+
+  private static final String BOOL_ARRAY_COL = "BOOL_ARRAY";
+  private static final List<Boolean> BOOL_ARRAY_VALUE = Arrays.asList(true, null, false);
+  private static final String BYTES_ARRAY_COL = "BYTES_ARRAY";
+  private static final List<ByteArray> BYTES_ARRAY_VALUE = Arrays.asList(BYTES_VALUE, null);
+  private static final String DATE_ARRAY_COL = "DATE_ARRAY";
+  private static final List<Date> DATE_ARRAY_VALUE = Arrays.asList(DATE_VALUE, null);
+  private static final String FLOAT64_ARRAY_COL = "FLOAT64_ARRAY";
+  private static final List<Double> FLOAT64_ARRAY_VALUE = Arrays.asList(DOUBLE_VALUE, null);
+  private static final String INT64_ARRAY_COL = "INT64_ARRAY";
+  private static final List<Long> INT64_ARRAY_VALUE = Arrays.asList(LONG_VALUE, null);
+  private static final String NUMERIC_ARRAY_COL = "NUMERIC_ARRAY";
+  private static final List<BigDecimal> NUMERIC_ARRAY_VALUE = Arrays.asList(NUMERIC_VALUE, null);
+  private static final String STRING_ARRAY_COL = "STRING_ARRAY";
+  private static final List<String> STRING_ARRAY_VALUE = Arrays.asList(STRING_VALUE, null);
+  private static final String TIMESTAMP_ARRAY_COL = "TIMESTAMP_ARRAY";
+  private static final List<Timestamp> TIMESTAMP_ARRAY_VALUE = Arrays.asList(TIMESTAMP_VALUE, null);
 
   private JdbcResultSet subject;
 
@@ -154,7 +178,17 @@ public class JdbcResultSetTest {
             StructField.of(STRING_COL_NUMBER, Type.string()),
             StructField.of(STRING_COL_DATE, Type.string()),
             StructField.of(STRING_COL_TIMESTAMP, Type.string()),
-            StructField.of(STRING_COL_TIME, Type.string())),
+            StructField.of(STRING_COL_TIME, Type.string()),
+            StructField.of(NUMERIC_COL_NULL, Type.numeric()),
+            StructField.of(NUMERIC_COL_NOT_NULL, Type.numeric()),
+            StructField.of(BOOL_ARRAY_COL, Type.array(Type.bool())),
+            StructField.of(BYTES_ARRAY_COL, Type.array(Type.bytes())),
+            StructField.of(DATE_ARRAY_COL, Type.array(Type.date())),
+            StructField.of(FLOAT64_ARRAY_COL, Type.array(Type.float64())),
+            StructField.of(INT64_ARRAY_COL, Type.array(Type.int64())),
+            StructField.of(NUMERIC_ARRAY_COL, Type.array(Type.numeric())),
+            StructField.of(STRING_ARRAY_COL, Type.array(Type.string())),
+            StructField.of(TIMESTAMP_ARRAY_COL, Type.array(Type.timestamp()))),
         Arrays.asList(
             Struct.newBuilder()
                 .set(STRING_COL_NULL)
@@ -205,6 +239,26 @@ public class JdbcResultSetTest {
                 .to(STRING_TIMESTAMP_VALUE)
                 .set(STRING_COL_TIME)
                 .to(STRING_TIME_VALUE)
+                .set(NUMERIC_COL_NULL)
+                .to((BigDecimal) null)
+                .set(NUMERIC_COL_NOT_NULL)
+                .to(NUMERIC_VALUE)
+                .set(BOOL_ARRAY_COL)
+                .toBoolArray(BOOL_ARRAY_VALUE)
+                .set(BYTES_ARRAY_COL)
+                .toBytesArray(BYTES_ARRAY_VALUE)
+                .set(DATE_ARRAY_COL)
+                .toDateArray(DATE_ARRAY_VALUE)
+                .set(FLOAT64_ARRAY_COL)
+                .toFloat64Array(FLOAT64_ARRAY_VALUE)
+                .set(INT64_ARRAY_COL)
+                .toInt64Array(INT64_ARRAY_VALUE)
+                .set(NUMERIC_ARRAY_COL)
+                .toNumericArray(NUMERIC_ARRAY_VALUE)
+                .set(STRING_ARRAY_COL)
+                .toStringArray(STRING_ARRAY_VALUE)
+                .set(TIMESTAMP_ARRAY_COL)
+                .toTimestampArray(TIMESTAMP_ARRAY_VALUE)
                 .build()));
   }
 
@@ -835,7 +889,7 @@ public class JdbcResultSetTest {
   }
 
   @Test
-  public void testGetBigDecimalIndex() throws SQLException {
+  public void testGetBigDecimalFromDouble_usingIndex() throws SQLException {
     assertNotNull(subject.getBigDecimal(DOUBLE_COLINDEX_NOTNULL));
     assertEquals(BigDecimal.valueOf(DOUBLE_VALUE), subject.getBigDecimal(DOUBLE_COLINDEX_NOTNULL));
     assertFalse(subject.wasNull());
@@ -844,7 +898,7 @@ public class JdbcResultSetTest {
   }
 
   @Test
-  public void testGetBigDecimalLabel() throws SQLException {
+  public void testGetBigDecimalFromDouble_usingLabel() throws SQLException {
     assertNotNull(subject.getBigDecimal(DOUBLE_COL_NOT_NULL));
     assertEquals(BigDecimal.valueOf(DOUBLE_VALUE), subject.getBigDecimal(DOUBLE_COL_NOT_NULL));
     assertFalse(subject.wasNull());
@@ -852,6 +906,24 @@ public class JdbcResultSetTest {
     assertTrue(subject.wasNull());
   }
 
+  @Test
+  public void testGetBigDecimalIndex() throws SQLException {
+    assertNotNull(subject.getBigDecimal(NUMERIC_COLINDEX_NOTNULL));
+    assertEquals(NUMERIC_VALUE, subject.getBigDecimal(NUMERIC_COLINDEX_NOTNULL));
+    assertFalse(subject.wasNull());
+    assertNull(subject.getBigDecimal(NUMERIC_COLINDEX_NULL));
+    assertTrue(subject.wasNull());
+  }
+
+  @Test
+  public void testGetBigDecimalLabel() throws SQLException {
+    assertNotNull(subject.getBigDecimal(NUMERIC_COL_NOT_NULL));
+    assertEquals(NUMERIC_VALUE, subject.getBigDecimal(NUMERIC_COL_NOT_NULL));
+    assertFalse(subject.wasNull());
+    assertNull(subject.getBigDecimal(NUMERIC_COL_NULL));
+    assertTrue(subject.wasNull());
+  }
+
   @Test
   public void testGetStatement() throws SQLException {
     assertNotNull(subject.getStatement());
@@ -1618,4 +1690,38 @@ public class JdbcResultSetTest {
   public void testGetHoldability() throws SQLException {
     assertEquals(java.sql.ResultSet.CLOSE_CURSORS_AT_COMMIT, subject.getHoldability());
   }
+
+  @Test
+  public void testGetObjectAsValue() throws SQLException {
+    assertEquals(
+        Value.bool(BOOLEAN_VALUE), subject.getObject(BOOLEAN_COLINDEX_NOTNULL, Value.class));
+    assertEquals(Value.bytes(BYTES_VALUE), subject.getObject(BYTES_COLINDEX_NOTNULL, Value.class));
+    assertEquals(Value.date(DATE_VALUE), subject.getObject(DATE_COLINDEX_NOTNULL, Value.class));
+    assertEquals(
+        Value.float64(DOUBLE_VALUE), subject.getObject(DOUBLE_COLINDEX_NOTNULL, Value.class));
+    assertEquals(Value.int64(LONG_VALUE), subject.getObject(LONG_COLINDEX_NOTNULL, Value.class));
+    assertEquals(
+        Value.numeric(NUMERIC_VALUE), subject.getObject(NUMERIC_COLINDEX_NOTNULL, Value.class));
+    assertEquals(
+        Value.string(STRING_VALUE), subject.getObject(STRING_COLINDEX_NOTNULL, Value.class));
+    assertEquals(
+        Value.timestamp(TIMESTAMP_VALUE),
+        subject.getObject(TIMESTAMP_COLINDEX_NOTNULL, Value.class));
+
+    assertEquals(Value.boolArray(BOOL_ARRAY_VALUE), subject.getObject(BOOL_ARRAY_COL, Value.class));
+    assertEquals(
+        Value.bytesArray(BYTES_ARRAY_VALUE), subject.getObject(BYTES_ARRAY_COL, Value.class));
+    assertEquals(Value.dateArray(DATE_ARRAY_VALUE), subject.getObject(DATE_ARRAY_COL, Value.class));
+    assertEquals(
+        Value.float64Array(FLOAT64_ARRAY_VALUE), subject.getObject(FLOAT64_ARRAY_COL, Value.class));
+    assertEquals(
+        Value.int64Array(INT64_ARRAY_VALUE), subject.getObject(INT64_ARRAY_COL, Value.class));
+    assertEquals(
+        Value.numericArray(NUMERIC_ARRAY_VALUE), subject.getObject(NUMERIC_ARRAY_COL, Value.class));
+    assertEquals(
+        Value.stringArray(STRING_ARRAY_VALUE), subject.getObject(STRING_ARRAY_COL, Value.class));
+    assertEquals(
+        Value.timestampArray(TIMESTAMP_ARRAY_VALUE),
+        subject.getObject(TIMESTAMP_ARRAY_COL, Value.class));
+  }
 }
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 2890ed9..d52e560 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
@@ -26,7 +26,9 @@ import static org.junit.Assert.assertTrue;
 import static org.junit.Assert.fail;
 import static org.junit.Assume.assumeFalse;
 
+import com.google.cloud.ByteArray;
 import com.google.cloud.spanner.IntegrationTest;
+import com.google.cloud.spanner.Value;
 import com.google.cloud.spanner.jdbc.ITAbstractJdbcTest;
 import com.google.common.base.Strings;
 import com.google.common.io.BaseEncoding;
@@ -46,6 +48,7 @@ import java.sql.Statement;
 import java.sql.Timestamp;
 import java.sql.Types;
 import java.util.ArrayList;
+import java.util.Arrays;
 import java.util.Calendar;
 import java.util.List;
 import java.util.Scanner;
@@ -875,6 +878,111 @@ public class ITJdbcPreparedStatementTest extends ITAbstractJdbcTest {
     }
   }
 
+  @Test
+  public void test11_InsertDataUsingSpannerValue() throws SQLException {
+    try (Connection con = createConnection()) {
+      try (PreparedStatement ps =
+          con.prepareStatement(
+              "INSERT INTO TableWithAllColumnTypes ("
+                  + "ColInt64, ColFloat64, ColBool, ColString, ColStringMax, ColBytes, ColBytesMax, ColDate, ColTimestamp, ColCommitTS, ColNumeric, "
+                  + "ColInt64Array, ColFloat64Array, ColBoolArray, ColStringArray, ColStringMaxArray, ColBytesArray, ColBytesMaxArray, ColDateArray, ColTimestampArray, ColNumericArray"
+                  + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, PENDING_COMMIT_TIMESTAMP(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) {
+        ps.setObject(1, Value.int64(2L));
+        ps.setObject(2, Value.float64(2D));
+        ps.setObject(3, Value.bool(true));
+        ps.setObject(4, Value.string("testvalues"));
+        ps.setObject(5, Value.string("2d37f522-e0a5-4f22-8e09-4d77d299c967"));
+        ps.setObject(6, Value.bytes(ByteArray.copyFrom("test".getBytes())));
+        ps.setObject(7, Value.bytes(ByteArray.copyFrom("testtest".getBytes())));
+        ps.setObject(8, Value.date(com.google.cloud.Date.fromYearMonthDay(2021, 5, 3)));
+        ps.setObject(
+            9, Value.timestamp(com.google.cloud.Timestamp.ofTimeSecondsAndNanos(99999L, 99)));
+        ps.setObject(10, Value.numeric(BigDecimal.TEN));
+        ps.setObject(11, Value.int64Array(new long[] {1L, 2L, 3L}));
+        ps.setObject(12, Value.float64Array(new double[] {1.1D, 2.2D, 3.3D}));
+        ps.setObject(13, Value.boolArray(Arrays.asList(Boolean.TRUE, null, Boolean.FALSE)));
+        ps.setObject(14, Value.stringArray(Arrays.asList("1", "2", "3")));
+        ps.setObject(15, Value.stringArray(Arrays.asList("3", "2", "1")));
+        ps.setObject(
+            16,
+            Value.bytesArray(
+                Arrays.asList(
+                    ByteArray.copyFrom("1"), ByteArray.copyFrom("2"), ByteArray.copyFrom("3"))));
+        ps.setObject(
+            17,
+            Value.bytesArray(
+                Arrays.asList(
+                    ByteArray.copyFrom("333"),
+                    ByteArray.copyFrom("222"),
+                    ByteArray.copyFrom("111"))));
+        ps.setObject(
+            18,
+            Value.dateArray(
+                Arrays.asList(com.google.cloud.Date.fromYearMonthDay(2021, 5, 3), null)));
+        ps.setObject(
+            19,
+            Value.timestampArray(
+                Arrays.asList(com.google.cloud.Timestamp.ofTimeSecondsAndNanos(99999L, 99), null)));
+        ps.setObject(20, Value.numericArray(Arrays.asList(BigDecimal.ONE, null, BigDecimal.TEN)));
+        assertEquals(1, ps.executeUpdate());
+      }
+      try (ResultSet rs =
+          con.createStatement()
+              .executeQuery("SELECT * FROM TableWithAllColumnTypes WHERE ColInt64=2")) {
+        assertTrue(rs.next());
+        assertEquals(Value.int64(2L), rs.getObject(1, Value.class));
+        assertEquals(Value.float64(2d), rs.getObject(2, Value.class));
+        assertEquals(Value.bool(true), rs.getObject(3, Value.class));
+        assertEquals(Value.string("testvalues"), rs.getObject(4, Value.class));
+        assertEquals(
+            Value.string("2d37f522-e0a5-4f22-8e09-4d77d299c967"), rs.getObject(5, Value.class));
+        assertEquals(Value.bytes(ByteArray.copyFrom("test")), rs.getObject(6, Value.class));
+        assertEquals(Value.bytes(ByteArray.copyFrom("testtest")), rs.getObject(7, Value.class));
+        assertEquals(
+            Value.date(com.google.cloud.Date.fromYearMonthDay(2021, 5, 3)),
+            rs.getObject(8, Value.class));
+        assertEquals(
+            Value.timestamp(com.google.cloud.Timestamp.ofTimeSecondsAndNanos(99999L, 99)),
+            rs.getObject(9, Value.class));
+        assertNotNull(rs.getObject(10, Value.class)); // Commit timestamp
+        assertEquals(Value.numeric(BigDecimal.TEN), rs.getObject(11, Value.class));
+        assertEquals(Value.int64Array(new long[] {1L, 2L, 3L}), rs.getObject(12, Value.class));
+        assertEquals(
+            Value.float64Array(new double[] {1.1D, 2.2D, 3.3D}), rs.getObject(13, Value.class));
+        assertEquals(
+            Value.boolArray(Arrays.asList(true, null, false)), rs.getObject(14, Value.class));
+        assertEquals(
+            Value.stringArray(Arrays.asList("1", "2", "3")), rs.getObject(15, Value.class));
+        assertEquals(
+            Value.stringArray(Arrays.asList("3", "2", "1")), rs.getObject(16, Value.class));
+        assertEquals(
+            Value.bytesArray(
+                Arrays.asList(
+                    ByteArray.copyFrom("1"), ByteArray.copyFrom("2"), ByteArray.copyFrom("3"))),
+            rs.getObject(17, Value.class));
+        assertEquals(
+            Value.bytesArray(
+                Arrays.asList(
+                    ByteArray.copyFrom("333"),
+                    ByteArray.copyFrom("222"),
+                    ByteArray.copyFrom("111"))),
+            rs.getObject(18, Value.class));
+        assertEquals(
+            Value.dateArray(
+                Arrays.asList(com.google.cloud.Date.fromYearMonthDay(2021, 5, 3), null)),
+            rs.getObject(19, Value.class));
+        assertEquals(
+            Value.timestampArray(
+                Arrays.asList(com.google.cloud.Timestamp.ofTimeSecondsAndNanos(99999L, 99), null)),
+            rs.getObject(20, Value.class));
+        assertEquals(
+            Value.numericArray(Arrays.asList(BigDecimal.ONE, null, BigDecimal.TEN)),
+            rs.getObject(21, Value.class));
+        assertFalse(rs.next());
+      }
+    }
+  }
+
   private void assertDefaultParameterMetaData(ParameterMetaData pmd, int expectedParamCount)
       throws SQLException {
     assertEquals(expectedParamCount, pmd.getParameterCount());