A way to bind Java Map 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.

Then convert your map to a JSON object as you said x) check [this answer](https://stackoverflow.com/questions/8360836/gson-is-there-an-easier-way-to-serialize-a-map?noredirect=1&lq=1) — Turtle, Jun 22 '17 at 13:24
I cannot do that Nathan since I need it to be a Map for some other stuff I need it for. But anyway thanks for suggestion. — Yantes, Jun 22 '17 at 13:26
If you try to store a `Map` in a row, it's clearly not well designed... each key/value should be stored in a row into a secondary table, linked to the `Something.name` (or one row per `name + playload_key`). From that, it will be easy to iterate the Map to create each rows, but also easy to get the rows to build the map. Without the need to parse the Map into a formatted `String` of some sort — AxelH, Jun 22 '17 at 13:27
@AxelH thanks - yeah it is a bad design, but I am not allowed to change this now, since if changed it is going to break a lot of legacy stuff, which we are not allowed to do currently. First when we get rid of all the legacy stuff, I can do whatever changes I want to :). But I found this [post](https://stackoverflow.com/questions/12022452/does-jdbi-accept-uuid-parameters) so gonna try to do some kind of a global binding and see if that works :). — Yantes, Jun 22 '17 at 14:26

score 2 · Answer 1 · answered Jul 06 '18 at 17:53

    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);
    }
}

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Fixed my problem with creating an ArgumentFactory binder suggested in this post.

So what I needed was to create a class that just contained one field of type Map<String, Object> implemented the Arugment interface from org.skife.jdbi.v2.tweak so I ended up with the following

public class NotificationPayloadArgument implements Argument {
  private NotificationPayload payload;

  NotificationPayloadArgument(NotificationPayload payload) {
      this.payload = payload;
  }
    
  @Override
  public void apply(int i, PreparedStatement preparedStatement, StatementContext statementContext)
    throws SQLException {
      preparedStatement.setString(i, toString());
  }

  @Override
  public String toString() {
      return new JSONObject(payload).toString();
  } 

}

To make this work I of course needed to implement a Factory class which implements the org.skife.jdbi.v2.tweak.ArgumentFactory<T> interface with my newly created type, so the factory ended up as such:

public class NotificationPayloadFactory implements ArgumentFactory<NotificationPayload> {

    @Override
    public boolean accepts(Class<?> expectedType, Object value, StatementContext ctx) {
        return value instanceof NotificationPayload;
    }

    @Override
    public Argument build(Class<?> expectedType, NotificationPayload value, StatementContext ctx) {
        return value;
    }

}

and of course lastly also as mentioned in Does JDBI accept UUID parameters? I had to register my factory:

jdbi.registerArgumentFactory(new NotificationPayloadFactory());

I tried to do this with just a factory for a Map<String, Object> but could not make it work for that, and there was a risk for NPE.

EDIT

The reason I am Overriding the toString() in NotificationPayload is because I need the payload in json format some places where I need a String object. But else it can be removed and then just use the new JSONObject(payload).toString() in the preparedStatement.setString() where toString() is called.

I think it should be value instanceof Map as described here https://stackoverflow.com/questions/12022452/does-jdbi-accept-uuid-parameters — Arpit Agrawal, Jul 06 '18 at 11:22
@ArpitAgrawal you were right I have edited my answer to be correct, so now `value instanceof NotificationPayload` is correct. — Yantes, Jul 06 '18 at 12:20

score 0 · Answer 3 · answered Feb 01 '22 at 12:14

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())

A way to bind Java Map to sql varchar in JDBI INSERT statement

3 Answers3

EDIT