Skip to content

Commit 8062ba9

Browse files
olavloiteolavloite
authored
feat: jsonb data type support (googleapis#926)
Add support for the jsonb data type for PostgreSQL dialect databases.
1 parent 939e252 commit 8062ba9

18 files changed

Lines changed: 644 additions & 170 deletions

‎java-spanner-jdbc/src/main/java/com/google/cloud/spanner/jdbc/AbstractJdbcWrapper.java‎

Lines changed: 71 additions & 34 deletions
Original file line numberDiff line numberDiff line change
@@ -41,18 +41,32 @@ abstract class AbstractJdbcWrapper implements Wrapper {
4141
*/
4242
static int extractColumnType(Type type) {
4343
Preconditions.checkNotNull(type);
44-
if (type.equals(Type.bool())) return Types.BOOLEAN;
45-
if (type.equals(Type.bytes())) return Types.BINARY;
46-
if (type.equals(Type.date())) return Types.DATE;
47-
if (type.equals(Type.float64())) return Types.DOUBLE;
48-
if (type.equals(Type.int64())) return Types.BIGINT;
49-
if (type.equals(Type.numeric())) return Types.NUMERIC;
50-
if (type.equals(Type.pgNumeric())) return Types.NUMERIC;
51-
if (type.equals(Type.string())) return Types.NVARCHAR;
52-
if (type.equals(Type.json())) return Types.NVARCHAR;
53-
if (type.equals(Type.timestamp())) return Types.TIMESTAMP;
54-
if (type.getCode() == Code.ARRAY) return Types.ARRAY;
55-
return Types.OTHER;
44+
switch (type.getCode()) {
45+
case BOOL:
46+
return Types.BOOLEAN;
47+
case BYTES:
48+
return Types.BINARY;
49+
case DATE:
50+
return Types.DATE;
51+
case FLOAT64:
52+
return Types.DOUBLE;
53+
case INT64:
54+
return Types.BIGINT;
55+
case NUMERIC:
56+
case PG_NUMERIC:
57+
return Types.NUMERIC;
58+
case STRING:
59+
case JSON:
60+
case PG_JSONB:
61+
return Types.NVARCHAR;
62+
case TIMESTAMP:
63+
return Types.TIMESTAMP;
64+
case ARRAY:
65+
return Types.ARRAY;
66+
case STRUCT:
67+
default:
68+
return Types.OTHER;
69+
}
5670
}
5771

5872
/** Extract Spanner type name from {@link java.sql.Types} code. */
@@ -101,29 +115,52 @@ static String getClassName(int sqlType) {
101115
*/
102116
static String getClassName(Type type) {
103117
Preconditions.checkNotNull(type);
104-
if (type == Type.bool()) return Boolean.class.getName();
105-
if (type == Type.bytes()) return byte[].class.getName();
106-
if (type == Type.date()) return Date.class.getName();
107-
if (type == Type.float64()) return Double.class.getName();
108-
if (type == Type.int64()) return Long.class.getName();
109-
if (type == Type.numeric()) return BigDecimal.class.getName();
110-
if (type == Type.pgNumeric()) return BigDecimal.class.getName();
111-
if (type == Type.string()) return String.class.getName();
112-
if (type == Type.json()) return String.class.getName();
113-
if (type == Type.timestamp()) return Timestamp.class.getName();
114-
if (type.getCode() == Code.ARRAY) {
115-
if (type.getArrayElementType() == Type.bool()) return Boolean[].class.getName();
116-
if (type.getArrayElementType() == Type.bytes()) return byte[][].class.getName();
117-
if (type.getArrayElementType() == Type.date()) return Date[].class.getName();
118-
if (type.getArrayElementType() == Type.float64()) return Double[].class.getName();
119-
if (type.getArrayElementType() == Type.int64()) return Long[].class.getName();
120-
if (type.getArrayElementType() == Type.numeric()) return BigDecimal[].class.getName();
121-
if (type.getArrayElementType() == Type.pgNumeric()) return BigDecimal[].class.getName();
122-
if (type.getArrayElementType() == Type.string()) return String[].class.getName();
123-
if (type.getArrayElementType() == Type.json()) return String[].class.getName();
124-
if (type.getArrayElementType() == Type.timestamp()) return Timestamp[].class.getName();
118+
switch (type.getCode()) {
119+
case BOOL:
120+
return Boolean.class.getName();
121+
case BYTES:
122+
return byte[].class.getName();
123+
case DATE:
124+
return Date.class.getName();
125+
case FLOAT64:
126+
return Double.class.getName();
127+
case INT64:
128+
return Long.class.getName();
129+
case NUMERIC:
130+
case PG_NUMERIC:
131+
return BigDecimal.class.getName();
132+
case STRING:
133+
case JSON:
134+
case PG_JSONB:
135+
return String.class.getName();
136+
case TIMESTAMP:
137+
return Timestamp.class.getName();
138+
case ARRAY:
139+
switch (type.getArrayElementType().getCode()) {
140+
case BOOL:
141+
return Boolean[].class.getName();
142+
case BYTES:
143+
return byte[][].class.getName();
144+
case DATE:
145+
return Date[].class.getName();
146+
case FLOAT64:
147+
return Double[].class.getName();
148+
case INT64:
149+
return Long[].class.getName();
150+
case NUMERIC:
151+
case PG_NUMERIC:
152+
return BigDecimal[].class.getName();
153+
case STRING:
154+
case JSON:
155+
case PG_JSONB:
156+
return String[].class.getName();
157+
case TIMESTAMP:
158+
return Timestamp[].class.getName();
159+
}
160+
case STRUCT:
161+
default:
162+
return null;
125163
}
126-
return null;
127164
}
128165

129166
/** Standard error message for out-of-range values. */

‎java-spanner-jdbc/src/main/java/com/google/cloud/spanner/jdbc/JdbcArray.java‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -203,6 +203,9 @@ public ResultSet getResultSet(long startIndex, int count) throws SQLException {
203203
case JSON:
204204
builder = binder.to(Value.json((String) value));
205205
break;
206+
case PG_JSONB:
207+
builder = binder.to(Value.pgJsonb((String) value));
208+
break;
206209
case TIMESTAMP:
207210
builder = binder.to(JdbcTypeConverter.toGoogleTimestamp((Timestamp) value));
208211
break;

‎java-spanner-jdbc/src/main/java/com/google/cloud/spanner/jdbc/JdbcDataType.java‎

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -281,6 +281,37 @@ public Type getSpannerType() {
281281
return Type.json();
282282
}
283283
},
284+
PG_JSONB {
285+
@Override
286+
public int getSqlType() {
287+
return PgJsonbType.VENDOR_TYPE_NUMBER;
288+
}
289+
290+
@Override
291+
public Class<String> getJavaClass() {
292+
return String.class;
293+
}
294+
295+
@Override
296+
public Code getCode() {
297+
return Code.PG_JSONB;
298+
}
299+
300+
@Override
301+
public List<String> getArrayElements(ResultSet rs, int columnIndex) {
302+
return rs.getPgJsonbList(columnIndex);
303+
}
304+
305+
@Override
306+
public String getTypeName() {
307+
return "JSONB";
308+
}
309+
310+
@Override
311+
public Type getSpannerType() {
312+
return Type.pgJsonb();
313+
}
314+
},
284315
TIMESTAMP {
285316
@Override
286317
public int getSqlType() {

‎java-spanner-jdbc/src/main/java/com/google/cloud/spanner/jdbc/JdbcDatabaseMetaData.java‎

Lines changed: 47 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -940,6 +940,7 @@ public ResultSet getTypeInfo() {
940940
StructField.of("SQL_DATETIME_SUB", Type.int64()),
941941
StructField.of("NUM_PREC_RADIX", Type.int64())),
942942
Arrays.asList(
943+
// TODO(#925): Make these dialect-dependent (i.e. 'timestamptz' for PostgreSQL.
943944
Struct.newBuilder()
944945
.set("TYPE_NAME")
945946
.to("STRING")
@@ -1243,7 +1244,52 @@ public ResultSet getTypeInfo() {
12431244
.to((Long) null)
12441245
.set("NUM_PREC_RADIX")
12451246
.to(10)
1246-
.build())));
1247+
.build(),
1248+
getJsonType(connection.getDialect()))));
1249+
}
1250+
1251+
private Struct getJsonType(Dialect dialect) {
1252+
return Struct.newBuilder()
1253+
.set("TYPE_NAME")
1254+
.to(dialect == Dialect.POSTGRESQL ? "JSONB" : "JSON")
1255+
.set("DATA_TYPE")
1256+
.to(
1257+
dialect == Dialect.POSTGRESQL
1258+
? PgJsonbType.VENDOR_TYPE_NUMBER
1259+
: JsonType.VENDOR_TYPE_NUMBER)
1260+
.set("PRECISION")
1261+
.to(2621440L)
1262+
.set("LITERAL_PREFIX")
1263+
.to((String) null)
1264+
.set("LITERAL_SUFFIX")
1265+
.to((String) null)
1266+
.set("CREATE_PARAMS")
1267+
.to((String) null)
1268+
.set("NULLABLE")
1269+
.to(DatabaseMetaData.typeNullable)
1270+
.set("CASE_SENSITIVE")
1271+
.to(true)
1272+
.set("SEARCHABLE")
1273+
.to(DatabaseMetaData.typeSearchable)
1274+
.set("UNSIGNED_ATTRIBUTE")
1275+
.to(true)
1276+
.set("FIXED_PREC_SCALE")
1277+
.to(false)
1278+
.set("AUTO_INCREMENT")
1279+
.to(false)
1280+
.set("LOCAL_TYPE_NAME")
1281+
.to(dialect == Dialect.POSTGRESQL ? "JSONB" : "JSON")
1282+
.set("MINIMUM_SCALE")
1283+
.to(0)
1284+
.set("MAXIMUM_SCALE")
1285+
.to(0)
1286+
.set("SQL_DATA_TYPE")
1287+
.to((Long) null)
1288+
.set("SQL_DATETIME_SUB")
1289+
.to((Long) null)
1290+
.set("NUM_PREC_RADIX")
1291+
.to((Long) null)
1292+
.build();
12471293
}
12481294

12491295
@Override

‎java-spanner-jdbc/src/main/java/com/google/cloud/spanner/jdbc/JdbcParameterStore.java‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -273,6 +273,7 @@ private boolean isTypeSupported(int sqlType) {
273273
case Types.NUMERIC:
274274
case Types.DECIMAL:
275275
case JsonType.VENDOR_TYPE_NUMBER:
276+
case PgJsonbType.VENDOR_TYPE_NUMBER:
276277
return true;
277278
}
278279
return false;
@@ -336,6 +337,12 @@ private boolean isValidTypeAndValue(Object value, int sqlType) {
336337
|| value instanceof InputStream
337338
|| value instanceof Reader
338339
|| (value instanceof Value && ((Value) value).getType().getCode() == Type.Code.JSON);
340+
case PgJsonbType.VENDOR_TYPE_NUMBER:
341+
return value instanceof String
342+
|| value instanceof InputStream
343+
|| value instanceof Reader
344+
|| (value instanceof Value
345+
&& ((Value) value).getType().getCode() == Type.Code.PG_JSONB);
339346
}
340347
return false;
341348
}
@@ -490,6 +497,7 @@ private Builder setParamWithKnownType(ValueBinder<Builder> binder, Object value,
490497
}
491498
return binder.to(stringValue);
492499
case JsonType.VENDOR_TYPE_NUMBER:
500+
case PgJsonbType.VENDOR_TYPE_NUMBER:
493501
String jsonValue;
494502
if (value instanceof String) {
495503
jsonValue = (String) value;
@@ -501,6 +509,9 @@ private Builder setParamWithKnownType(ValueBinder<Builder> binder, Object value,
501509
throw JdbcSqlExceptionFactory.of(
502510
value + " is not a valid JSON value", Code.INVALID_ARGUMENT);
503511
}
512+
if (sqlType == PgJsonbType.VENDOR_TYPE_NUMBER) {
513+
return binder.to(Value.pgJsonb(jsonValue));
514+
}
504515
return binder.to(Value.json(jsonValue));
505516
case Types.DATE:
506517
if (value instanceof Date) {
@@ -750,6 +761,8 @@ private Builder setArrayValue(ValueBinder<Builder> binder, int type, Object valu
750761
return binder.toStringArray(null);
751762
case JsonType.VENDOR_TYPE_NUMBER:
752763
return binder.toJsonArray(null);
764+
case PgJsonbType.VENDOR_TYPE_NUMBER:
765+
return binder.toPgJsonbArray(null);
753766
case Types.DATE:
754767
return binder.toDateArray(null);
755768
case Types.TIME:
@@ -818,6 +831,8 @@ private Builder setArrayValue(ValueBinder<Builder> binder, int type, Object valu
818831
} else if (String[].class.isAssignableFrom(value.getClass())) {
819832
if (type == JsonType.VENDOR_TYPE_NUMBER) {
820833
return binder.toJsonArray(Arrays.asList((String[]) value));
834+
} else if (type == PgJsonbType.VENDOR_TYPE_NUMBER) {
835+
return binder.toPgJsonbArray(Arrays.asList((String[]) value));
821836
} else {
822837
return binder.toStringArray(Arrays.asList((String[]) value));
823838
}

0 commit comments

Comments
 (0)