Single JSON column in the database, multiple properties on the wire

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Single JSON column in the database, multiple properties on the wire

Brian Pontarelli
I have an interesting case that I’m wondering if anyone on the list has solved. We have a class that currently is using a single column in the database to store multiple values as JSON. We have a TypeHandler that is converting the field value to and from JSON using Jackson. The data model looks like this:

public class User {
  // This is a single column in the database called “data" that is of type TEXT and stores the JSON representation of the field
  public UserData data;
}

public class UserData {
  public String someKnownValue;
  public String someOtherKnownValue;
  public Map<String, Object> data;
}

The JSON format of this on the wire (in and out of REST services) looks like this currently:

{
  “user”: {
    “data”: {
      “someKnownValue”: “foo”,
      “data”: {
        “freeformValue”: “value”
      }
    }
  }
}

We want to flatten this out but maintain the format in the database. With Jackson, this is pretty simple to do by annotating the UserData field in the User class with @JsonUnwrapped. This works great and produces this JSON on the wire:

{
  “user”: {
    “someKnownValue”: “foo”,
    “data”: {
      “freeformValue”: “value”
    }
  }
}

The only issue is that the Java code is still pretty ugly because it is nested (with the same field names in some cases) and the classes doesn’t match the JSON format. It looks like this:

user.data.data.put(“freeformValue”, “value”);

Our idea was to remove the UserData class and put all the properties in the User class like this:

public class User {
  public String someKnownValue;
  public String someOtherKnownValue;
  public Map<String, Object> data;
}

However, we can’t figure out a way to get MyBatis to take the three fields and convert them to JSON and vice-versa. Is there a way to accomplish is with a Plugin or some other MyBatis interface that we can implement? 

I’m fine with adding annotations to each field that could be used for reflection if needed. The annotations might look like this:

public class User {
  @MyBatisJSONColumn(“data”) public String someKnownValue;
  @MyBatisJSONColumn(“data”) public String someOtherKnownValue;
  @MyBatisJSONColumn(“data”) public Map<String, Object> data;
}

Any ideas or existing solutions?

Thanks,
-- Brian


--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: Single JSON column in the database, multiple properties on the wire

Brian Pontarelli
Just an update on this in case anyone is watching the thread. I found a solution that looks like it works nicely. 

I created an interface that provides two default methods: getInternalData and setInternalData. I then map these to a column in the database called “data”. In these methods, I’m able to collect everything I want to store in the JSON column into a HashMap and return the HashMap. I wrote a TypeHandler for Map.class that converts any Map to a JSON string to be stored in the column.

Here’s the code for my interface with the default methods:

public interface _InternalJSONColumn {
/**
* Getter for use in the MyBatis SQL.
*
* @return The Map to set into SQL.
* @throws IllegalAccessException If a field could not be retrieve for some reason.
*/
@JsonIgnore
@SuppressWarnings("unchecked")
default Map<String, Object> getInternalData() throws IllegalAccessException, NoSuchFieldException {
Map<String, Object> fromInstance = (Map<String, Object>) getClass().getField("data").get(this);
Map<String, Object> forDatabase = new HashMap<>(fromInstance);

Field[] fields = getClass().getFields();
for (Field field : fields) {
InternalJSONColumn annotation = field.getAnnotation(InternalJSONColumn.class);
if (annotation != null) {
Object value = field.get(this);
forDatabase.put(field.getName(), value);
}
}

return forDatabase;
}

/**
* Setter for use in the MyBatis SQL.
*
* @param fromDatabase The Map that comes out of the database.
* @throws IllegalAccessException If a field could not be set for some reason.
*/
@JsonIgnore
@SuppressWarnings("unchecked")
default void setInternalData(Map<String, Object> fromDatabase) throws IllegalAccessException, NoSuchFieldException {
Field[] fields = getClass().getFields();
for (Field field : fields) {
InternalJSONColumn annotation = field.getAnnotation(InternalJSONColumn.class);
if (annotation != null) {
field.set(this, fromDatabase.remove(field.getName()));
}
}

// Put the remainder into the data map
Map<String, Object> fromInstance = (Map<String, Object>) getClass().getField("data").get(this);
fromInstance.clear();
fromInstance.putAll(fromDatabase);
}
}

Here’s how one of my MyBatis classes uses this interface:

public class AuditLog implements Buildable<AuditLog>, _InternalJSONColumn {
public final Map<String, Object> data = new LinkedHashMap<>();

public Long id;

public ZonedDateTime insertInstant;

public String insertUser;

public String message;

@InternalJSONColumn
public Object newValue;

@InternalJSONColumn
public Object oldValue;

@InternalJSONColumn
public String reason;


-- Brian


On Aug 15, 2018, at 3:21 PM, Brian Pontarelli <[hidden email]> wrote:

I have an interesting case that I’m wondering if anyone on the list has solved. We have a class that currently is using a single column in the database to store multiple values as JSON. We have a TypeHandler that is converting the field value to and from JSON using Jackson. The data model looks like this:

public class User {
  // This is a single column in the database called “data" that is of type TEXT and stores the JSON representation of the field
  public UserData data;
}

public class UserData {
  public String someKnownValue;
  public String someOtherKnownValue;
  public Map<String, Object> data;
}

The JSON format of this on the wire (in and out of REST services) looks like this currently:

{
  “user”: {
    “data”: {
      “someKnownValue”: “foo”,
      “data”: {
        “freeformValue”: “value”
      }
    }
  }
}

We want to flatten this out but maintain the format in the database. With Jackson, this is pretty simple to do by annotating the UserData field in the User class with @JsonUnwrapped. This works great and produces this JSON on the wire:

{
  “user”: {
    “someKnownValue”: “foo”,
    “data”: {
      “freeformValue”: “value”
    }
  }
}

The only issue is that the Java code is still pretty ugly because it is nested (with the same field names in some cases) and the classes doesn’t match the JSON format. It looks like this:

user.data.data.put(“freeformValue”, “value”);

Our idea was to remove the UserData class and put all the properties in the User class like this:

public class User {
  public String someKnownValue;
  public String someOtherKnownValue;
  public Map<String, Object> data;
}

However, we can’t figure out a way to get MyBatis to take the three fields and convert them to JSON and vice-versa. Is there a way to accomplish is with a Plugin or some other MyBatis interface that we can implement? 

I’m fine with adding annotations to each field that could be used for reflection if needed. The annotations might look like this:

public class User {
  @MyBatisJSONColumn(“data”) public String someKnownValue;
  @MyBatisJSONColumn(“data”) public String someOtherKnownValue;
  @MyBatisJSONColumn(“data”) public Map<String, Object> data;
}

Any ideas or existing solutions?

Thanks,
-- Brian



--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.