A way to bind Java Map<String, Object> to sql varchar in JDBI INSERT statement

Question

Is there a way to bind a java Map<String, Object> to a varchar in the the JDBI @BindBean annotation.

So for example I have a class Something.class and I create a

@SqlBatch("INSERT INTO Something (name, payload) VALUES(:name, :payload)").

Now in my java class the name is of type String and payload is of type Map<String, Object> and I want in the DB table the types are varchar(...). Now I want the Map object to be inserted in the column as a JSON object, is that somehow achievable wihtout creating my own complex Binder as defined in http://jdbi.org/sql_object_api_argument_binding/ ? and other than making my payload be of type String in java.

Answer 1

    public class MapArgument implements Argument {

    private Map<String, Object> payload;

    private ObjectMapper objectMapper;

    public MapArgument(ObjectMapper objectMapper, Map<String, Object> payload) {
        this.objectMapper = objectMapper;
        this.payload = payload;
    }

    @Override
    public void apply(int i, PreparedStatement statement, StatementContext statementContext) throws SQLException {
        try {
            statement.setString(i, objectMapper.writeValueAsString(payload));
        } catch (JsonProcessingException e) {
            log.info("Failed to serialize payload ", e);
        }
    }
}

    public class MapArgumentFactory implements ArgumentFactory<Map<String, Object>> {

    private ObjectMapper mapper;

    public MapArgumentFactory(ObjectMapper mapper) {
        this.mapper = mapper;
    }

    @Override
    public boolean accepts(Class<?> type, Object value, StatementContext statementContext) {
        return value instanceof Map;
    }

    @Override
    public Argument build(Class<?> type, Map<String, Object> map, StatementContext statementContext) {
        return new MapArgument(mapper, map);
    }
}

Answer 2

In kotlin-way using JDBI to insert a data class as JSONB; just create one ArgumentFactory for that datatype and register that factory with JDBI. This is as simple as these 3 lines needed -

internal class CvMetadataArgumentFactory : AbstractArgumentFactory<CvMetadata?>(Types.OTHER) {
override fun build(value: CvMetadata?, config: ConfigRegistry): Argument {
    return Argument { position, statement, _ -> statement.setString(position, value?.toString()?:"") }
}

}

Later register this factory -

jdbi.registerArgument(CvMetadataArgumentFactory())