JSON-Based Serialized LOB Pattern

Serialized LOB (Large Object) pattern is explained in the Patterns of Enterprise Application Architecture book by Martin Fowler. The original paper describes an implementation of the pattern with an example of XML-based serialization. Although nowadays, JSON format is ubiquitous. So, adding a fresh flavor of “JSONization” to the pattern seems fully justifiable.

There was mentioned that a binary serialization might win in size and performance, though the main problem with BLOBs (Binary Large Objects) remains the versioning. The changes in classes’ structure may lead to data incompatibility, so it can be problematic to de-serialize your objects from the database. Also, it is hardly achievable to query data inside BLOB. On the other hand, CLOB (Character Large Object) type is human readable, which at least allows for investigating issues. In addition, modern RDBM systems provide instruments to manipulate JSON, like querying and modifying data inside JSON.

Before diving into implementation details, let’s review the objectives of the pattern:

• Simplifying a database structure by decreasing the number of related tables. This helps to reduce the number of joins and works better with small and immutable data. In general, this might be considered database denormalization.
• Grouping table columns that together represent some object, and this object is an entity’s attribute. In other words, nonprimitive types are serialized and stored in a single column instead of multiple ones.
• Arrange an entity’s attributes by their importance or by the frequency of usage. For example, key attributes that identify entities and participate in search should be mapped onto their own columns – a column per attribute. Other attributes, which are used occasionally, might be combined into a helper object, which is serialized and presented in the database as a single column.

Query Simplification

Imagine a site that shows basic information about products. At the minimum, products the table would have the structure:

If the site supports multiple languages, a localization might end up with something like this nightmare:

Or with the better solution that uses a separate localization table:

The first variant is difficult to extend, and retrieving data by a locale would be awkward enough to avoid this option. SQL request for the second variant may be like this:

SELECT
  p.id, pl.name, pl.description 
FROM
  products AS p
  JOIN product_localization AS pl ON (p.id=pl.product_id) 
  JOIN languages AS l ON (pl.langauge_id=l.id)
WHERE 
  p.id=12345 AND l.code='de'

We can select data for a required locale, though we must join three tables.

With Serialized LOB pattern, it is possible to use an original structure of products the table. What is changed is the meaning and content of text columns. Instead of storing a single value of an attribute for some locale, the columns contain JSON string that presents LocalizedString an object, which in Java class with Lombok annotations and convenient method getByLocale might be implemented as the following:

@Getter
@Setter
@EqualsAndHashCode
@ToString
public class LocalizedString implements Serializable {
    private static final long serialVersionUID = 1L;
    private String en;
    private String it;
    private String de;
    public String getByLocale(@NotNull Locale locale) {
        switch (locale.getLanguage()) {
            case "it":
                return it;
            case "de":
                return de;
            default:
                return en; // a fallback in English
        }
    }
}

An example of JSON presentation of name attribute would like {“en”:”Hand plow”,”it”:”Aratro a mano”,”de”:”Handpflug”}.

Both name and description JSON can be stored directly in corresponding columns. So, it is possible to select data just by-product id without extra joins. Though this requires serialization/deserialization to and from JSON strings. A bit later, we will see how it can be implemented.

Object-Oriented Strategy

Serialized LOB pattern may also be helpful when your entity classes have attributes of object types. For instance, if you need to add size columns into products table, this can be done like this:

But logically, these four new attributes present dimensions of the product. So, with the OO (object-oriented) approach, the following looks less verbose:

@Getter
@Setter
@EqualsAndHashCode
@ToString
public static class Dimensions implements Serializable {
    private static final long serialVersionUID = 1L;
    private LocalizedString unit;
    private String height;
    private String length;
    private String weight;
    private String width;
}

Where the column dimensions store the JSON presentation of Dimensions object: {"unit":{"en":"meter","fr":"mètre","it":"metro"},"length":"1","width":"2","height":"3"}.

The OO approach works perfectly with immutable objects of stable, non-modifiable classes. Another example of such an immutable class is Location which might be a part of Address class:

@Getter
@Setter
@EqualsAndHashCode
@ToString
public static class Location implements Serializable {
    private static final long serialVersionUID = 1L;
    private String latitude;
    private String longitude;
}

JSON presentation of Location the object looks like this: {"latitude":"-15.95615673677379", "longitude":"-5.69888813195389"}.

Multiple Attribute Packing

This approach is similar to the previous one – the packing attributes of an entity into an object, which is serialized into JSON. However, the requirements of the immutability of serialized classes might be dropped. The main reason to use patterns here is convenience.

For instance, you have Store a class with dozens of attributes. Some of them are necessary for identifying, searching, or displaying, like code, name, description. Others might be used only in rare specific cases, for example, defaultStorage, isEligibleForHomeDelivery, timeZone, etc. As a result, the stores the table might have so many columns that make it inconvenient to operate with. Instead of working with multiple queries, it is suggested to split attributes into groups. Key attributes are mapped onto dedicated columns, whereas other attributes, which are not used often, will be packed into a helper class as shown below:

Where the column store_details contains the JSON presentation of an object of StoreDetails class:

@Getter
@Setter
@EqualsAndHashCode
  public static class StoreDetails implements Serializable {
    private static final long serialVersionUID = 1L;
    private String timeZone;
    private String timeZoneDescription;
    private Boolean isEligibleForHomeDelivery;
    private Boolean isExpessDeliverySupported;
    private Boolean isLockerSupported;
    private Boolean defaultLocationId;
    private Location location; // safe, as Location class is considered as final
    private Address address; // Unsafe! Possible duplications and deserialization issues.
}

Pay attention to the last two attributes. It is quite safe to use fields of quasi-final types, like Location, whereas the usage of mutable classes, like Address, is rather risky. Firstly, Serialized LOBs might contain copies of the same data, e.g., the same address shared by different stores. Secondly, if a serialized LOB contains mutable objects inside JSON, the changes in those object classes may break compatibility between versions. For example, if JSON contains a fully serialized store address, StoreDetails the object cannot be restored from the JSON string if some attribute is removed from Address class.

JSON Serialization

Now let’s see how the pattern can be implemented with JSON serialization.

An obvious and straightforward technique, especially if you do not use ORM, is to serialize and de-serialize with the help of Gson or Jackson libraries. With that, while communicating with the DAO layer, you have to convert objects into JSON strings or parse JSON back from the string to the object. This is a simple approach, though it requires an implementation of custom logic in DAO.

A more elegant way to work with JSON data can be implemented if you use JPA and Hibernate. Actually, you do not need to create any intermediate layers at all. Instead, with the help of an excellent project hibernate-types, all you ought to do is declare and annotate your entities correspondingly. All the heavy lifting for converting to/from JSON will be done by custom type definitions which are provided by the hibernate-types project.

Here are the updated definitions of entities mentioned earlier for Hibernate 5:

@Entity
@TypeDef(
    name = "json", typeClass = JsonStringType.class
)
@Getter
@Setter
public class Product implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Type(type = "json")
    @Column(columnDefinition = "varchar(max)")
    private LocalizedString name;

    @Type(type = "json")
    @Column(columnDefinition = "varchar(max)")
    private LocalizedString description;

    @Type(type = "json")
    @Column(columnDefinition = "varchar(max)")
    private Dimensions dimensions;

    @Getter
    @Setter
    @EqualsAndHashCode
    @ToString
    public static class Dimensions implements Serializable {
        private static final long serialVersionUID = 1L;
        private LocalizedString unit;
        private String height;
        private String length;
        private String weight;
        private String width;
    }
}

In the fragment above, it is shown how to declare JsonStringType the type to use with Product entity. Another approach is to place this declaration into a package info file. This is explained in the hibernate-types project documentation.

After declaration, the type can be used in annotations of attributes that are to be serialized into JSON when an entity is saved into the database and which are restored from JSON strings into Java objects. The above LocalizedString is annotated as json type. Highly likely, this class is used by multiple entities which require localized attributes. So, LocalizedString is declared as an independent class. On the other hand, Dimensions is defined as an embedded class, as hard it will be used in more than one entity.

Finally, here is an extended example of the definition Store the entity that uses all types of Serialized LOB patterns:

@Entity
@TypeDef(
    name = "json", typeClass = JsonStringType.class
)
@Getter
@Setter
public class Store implements Serializable {

    private static final long serialVersionUID = 1;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String code;

    @Type(type = "json")
    @Column(columnDefinition = "varchar(max)")
    private LocalizedString name;

    @Type(type = "json")
    @Column(columnDefinition = "varchar(max)")
    private LocalizedString description;

    @Type(type = "json")
    @Column(columnDefinition = "varchar(max)")
    private StoreDetails storeDetails;

    @Getter
    @Setter
    @EqualsAndHashCode
    public static class StoreDetails implements Serializable {
        private static final long serialVersionUID = 1L;
        private String timeZone;
        private String timeZoneDescription;
        private Boolean isEligibleForHomeDelivery;
        private Boolean isExpessDeliverySupported;
        private Boolean isLockerSupported;
        private Boolean defaultLocationId;
        private Location location; // Safe as Location class is final
        private Address address; // Unsafe! Possible duplications and deserialization issues.
    }
    
    @Getter
    @Setter
    @EqualsAndHashCode
    @ToString
    public static class Location implements Serializable {
        private static final long serialVersionUID = 1L;
        private String latitude;
        private String longitude;
    }
}

Again, beware of using modifiable or referenced types inside objects which are the subject to Serialized LOB.

Conclusion

Prudently used, Serialized LOB pattern suggests an elegant and solid OO solution. With an implementation based on Hibernate custom types, all JSON manipulations are hidden behind annotations. So, a developer may work with objects not thinking about the mapping between classes and database tables or columns and perhaps even not suspecting that some objects are persisted as strings.