SET data type

[gpsfl]

How do you use JDBI with the MySQL SET data type? I've tried mapping it into a Java Set and List, but to no success.

Table:

create table test {
  val SET('A', 'B', 'C') not null
}

Data Model:

@Data // lombok for setters, getters, etc.
class Test {
    private Set<String> val;
}

Insert (doesn't work)

handle.createUpdate("insert into test value(:val)")
    .bind("val", Set.of("A", "B"))
    .execute()

[stevenschlansker]

Hi @gpsfl , I don't know of anyone who has done this before. The set type is new to me.
If you can provide the JDBC code that does what you want, we can try to figure out what the right way to do it in JDBI is.

What error do you get when it doesn't work now?

[gpsfl]

The SET type is an enumeration of which 0-n can be selected. MySQL represents it as a comma separated string and I think that's also the way you read/write it using JDBC. I managed to read them as a string in JDBI, but this means you have to split/join it every time you want to access the values.

When defining it as a Set or a Set (where MyEnum is an enum containing all possible options), JDBI will attempt to bind it as an array instead of transforming it to the appropriate string representation. MySQL doesn't support arrays, which is why it throws an exception.

Is it possible to tell JDBI about the SQL type of the field, so it can bind/map it differently?

[stevenschlansker]

Yes, it should be possible to override the default handling. The Array handling is just a normal ArgumentFactory (SqlArrayArgumentFactory) and ColumnMapperFactory (SqlArrayMapperFactory). If you register a new column and argument factory to cover your desired type you can override the behavior. (in this case Set.class, beware that generic information like Set<String> is lost with normal bind unless you bindByType)

If we can get it working, we could ship it in a new jdbi3-mysql artifact so others can use it too.

[gpsfl]

I've made it work using the following code. Do you think it's fine this way?

public static void main(String[] args) throws Exception {
    Handle handle = getHandle();

    handle.registerRowMapper(Test.class, BeanMapper.of(Test.class));

    handle.registerArgument(new AbstractArgumentFactory<Set<String>>(Types.VARCHAR) {
        @Override
        protected Argument build(Set<String> value, ConfigRegistry config) {
            return (pos, stm, ctx) -> {
                stm.setString(pos, String.join(",", value));
            };
        }
    });

    handle.registerColumnMapper((ColumnMapperFactory) (type, config) -> {
        if (!(type instanceof ParameterizedType paramType)) return Optional.empty();

        final Type[] typeArguments = paramType.getActualTypeArguments();

        if (paramType.getRawType() == Set.class && typeArguments.length == 1 && typeArguments[0] == String.class) {
            return Optional.of((rs, column, ctx) -> {
                final String value = rs.getString(column);

                if (rs.wasNull()) {
                    return null;
                }

                if (value.isEmpty()) {
                    return new HashSet<>();
                }

                final String[] values = value.split(",");
                return new HashSet<>(Set.of(values));
            });
        }

        return Optional.empty();
    });

    handle.createUpdate("truncate table test;").execute();

    PreparedBatch insertBatch = handle.prepareBatch("insert into test value (:abc, :normalString)");

    insertBatch.bindBean(new Test(Set.of("a", "b", "c"), "1")).add();
    insertBatch.bindBean(new Test(Set.of("a", "b"), "2")).add();
    insertBatch.bindBean(new Test(Set.of("a"), "3")).add();
    insertBatch.bindBean(new Test(Set.of(), "4")).add();
    insertBatch.bindBean(new Test(null, "5")).add();
    insertBatch.execute();
    handle.commit();

    handle.select("select * from test")
            .mapTo(Test.class)
            .list()
            .forEach(System.out::println);
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public static class Test {
    private Set<String> abc;
    private String normalString;
}

create table test (abc set('a', 'b', 'c'), normalString varchar(10));