Add support for Postgres UUID arrays using JDBC

[rdehuyss]

Hi again,

First of all - let me tell you that I love Spring Data JDBC ❤️! It makes life 🎉!

I noticed that the combination of Spring Data JDBC, Postgres and UUID array type doesn't seem to work. My guess is this is because of the fact that java.sql.JDBCType does not contain an entry for UUID - is that correct?

To reproduce:

schema.sql

create table uuid_test
(
    "id"           uuid,
    "version"      int,
    "to_ids"       uuid[],
);

Record to save

@Table(name="uuid_test")
public record UUIDTest(@Id UUID id, @Version int version,  Set<UUID> toIds) {}

The exception message

Caused by: org.postgresql.util.PSQLException: Unable to find server array type for provided name UNKNOWN.

Workaround

My current workaround is to create the JdbcTypeFactory:

static class PostgresJdbcTypeFactory implements JdbcTypeFactory {
        private final JdbcOperations operations;
        private final JdbcArrayColumns arrayColumns;

        /**
         * Creates a new {@link DefaultJdbcTypeFactory}.
         *
         * @param operations must not be {@literal null}.
         * @since 2.3
         */
        public PostgresJdbcTypeFactory(JdbcOperations operations, JdbcArrayColumns arrayColumns) {
            Assert.notNull(operations, "JdbcOperations must not be null");
            Assert.notNull(arrayColumns, "JdbcArrayColumns must not be null");

            this.operations = operations;
            this.arrayColumns = arrayColumns;
        }

        @Override
        public Array createArray(Object[] value) {

            Assert.notNull(value, "Value must not be null");

            Class<?> componentType = arrayColumns.getArrayType(value.getClass());

            if(UUID.class.equals(componentType)) {
                String typeName = "uuid";
                return operations.execute((ConnectionCallback<Array>) c -> c.createArrayOf(typeName, value));
            } else {
                SQLType jdbcType = JdbcUtil.targetSqlTypeFor(componentType);
                Assert.notNull(jdbcType, () -> String.format("Couldn't determine JDBCType for %s", componentType));
                String typeName = arrayColumns.getArrayTypeName(jdbcType);
                return operations.execute((ConnectionCallback<Array>) c -> c.createArrayOf(typeName, value));
            }
        }
    }

Question

Would this (ugly) workaround be accepted as a PR or what would be the best way to get UUID support for arrays into Spring Boot JDBC?

[mp911de]

JDBCType indeed does not contain UUID, however with our Dialect support we should be able to provide type hints for well-known types such as UUID.

[webcane]

I have another workaround

class UUIDSQLType implements SQLType {
    @Override
    public String getName() {
        return "uuid";
    }

    @Override
    public String getVendor() {
        return "Postgres";
    }

    @Override
    public Integer getVendorTypeNumber() {
        return JDBCType.OTHER.getVendorTypeNumber();
    }
}

@Configuration
class DataJdbcConfig extends AbstractJdbcConfiguration {

    @Override
    protected List<?> userConverters() {
        return Arrays.asList(new UUIDWritingConverter());
    }

    @WritingConverter
    static class UUIDWritingConverter implements Converter<UUID, JdbcValue> {
        @Override
        public JdbcValue convert(UUID source) {
            return JdbcValue.of(source, new UUIDSQLType());
        }
    }
}