I am having a field of type "text" in mysql and storing json data in it. Eg:- "["android_app","iphone_app","windows_app"]";
I am interacting with mysql using hibernate and while reading this field I am deserializing it to an arraylist in Java.
My question is, is this the best and fastest way to handle such cases or there are some better ways of doing it.
If you're able to take advantage of some JPA 2.1 features, you could use anAttributeConverter to handle this for you automatically without having to deal with this in your business code.
public class YourEntity {
// other stuff
@Convert(converter = StringArrayToJsonConverter.class)
List<String> textValues;
}
Then you just define the converter as follows:
public class StringArraytoJsonConverter
implements AttributeConverter<List<String>, String> {
@Override
public string convertToDatabaseColumn(List<String> list) {
// convert the list to a json string here and return it
}
@Override
public List<String> convertToEntityAttribute(String dbValue) {
// convert the json string to the array list here and return it
}
}
The best part is this becomes a reusable component that you can simply place anywhere you need to represent a json array as a List<> in your java classes but would rather store it as JSON in the database using a single text field.
Another alternative would be to avoid storing the data as JSON but instead use a real table where that it would allow you to actually query on the JSON values. To do this, you'd rewrite that mapping using JPA's @ElementCollection
@ElementCollection
private List<String> textValues;
Internally, Hibernate creates a secondary table where it stores the text string values for the array with a reference to the owning entity's primary key, where the entity primary key and the string value are considered the PK of this secondary table.
You then either handle serializing the List<> as a JSON array in your controller/business code to avoid mixing persistence with that type of medium, particularly given that most databases have not yet introduced a real JSON data-type yet :).
