Using MySQL JSON columns to simplify your data storage: Part 2

Note: Read part 1 first, otherwise you might be a little lost!

Converting Java objects to JSON

The first part we need to take care of is converting Java objects to and from JSON. When retrieving a JSON string from the database, we need to convert it into our Java object. We also need to make changes and send it back to the database as a JSON string. Let’s look at a simple implementation of this utility class. We will use the excellent Jackson library for this:

public final class EntityUtil {
	private static final ObjectMapper mapper;	
	private static final ObjectWriter writer;

	static {
        mapper = new ObjectMapper();        
		writer = mapper.writer();
	}

	public static String objectToJson(Object object) {	    
		try {
			return object == null ? "" : writer.writeValueAsString(object);
		} catch (Exception e) {			
			throw new RuntimeException(e.getMessage());
		}
    }
	public static <V> V jsonToObject(String data, Class<V> valueType) {
		try {
			return StringUtils.isBlank(data) ? null : mapper.readValue(data, valueType);
		} catch (Exception e) {		
			return null;
		}
    }
}

Creating a Hibernate JSON column type

The next step is to create a JSON Hibernate type mapping and descriptor. This will allow us to add the ‘detail’ objects we discussed in part 1 as columns to Hibernate entities. The descriptor uses our EntityUtil helper to seamlessly convert between MySQL’s JSON column and our detail object.

The full code for the mapping and descriptor can be found on GitHub.

Defining a Hibernate entity with a details column

Now that we have our JSON descriptor, we can use it to define Hibernate entities that store detail columns. In part 1, we looked at the scenario of a User entity that stores it’s details inside of a UserDetail column. Let’s look at how to define this entity:

@Entity
@Table(name = "complex_user")
public class ComplexUser implements Serializable {
	private static final long serialVersionUID=1L;

	@GeneratedValue(strategy = GenerationType.IDENTITY) @Id @Column(name = "id")	
	private int id;

	@Column(name = "email", nullable = false)
	private String email;

	@Column(name = "display_name", nullable = false)
	private String displayName;

	@Type(type= "org.coderdreams.hibernate.JsonStringType")
	@Column(name = "user_details")
	private ComplexUserDetails userDetails;

	public ComplexUserDetails getUserDetails() {
		if(userDetails == null) {
			userDetails = new ComplexUserDetails();
		}
		return userDetails;
	}
	public void setUserDetails(ComplexUserDetails userDetails) {
		this.userDetails = userDetails;
	}
}

As you can see in the highlighted lines, we use the Hibernate type mapping we created to define the userDetails field. In your database, the “user_details” will be a JSON column. With the exception of the @Type annotation, the column declaration is just like any other column declaration inside of a Hibernate entity!

Creating/Updating our user objects

Now that we have all of that boilerplate code in place, let’s look at how to use it. You might be pleasantly surprised that your app code doesn’t change much and doesn’t need to know the details of how ComplexUserDetails is being stored:

//create a user
ComplexUserDetails details = new ComplexUserDetails();
details.setHeight(95.0d);
details.setWeight(220.0d);
details.getFavoriteMovies().add("Terminator");

PhysicalAddress a1 = new PhysicalAddress();
a1.setId(System.nanoTime());
a1.setAddress1("123 test");
a1.setCity("NY");
details.getAddresses().add(a1);

ComplexUser u = new ComplexUser();
u.setEmail("test@test.com");
u.setDisplayName("roman");
u.setUserDetails(details);
u = crudService.create(u);

//update a user
ComplexUser u2 = crudService.findById(ComplexUser.class, 5);
u2.getUserDetails().setMobilePhone("123-666-6666");
crudService.save(u2);

One important thing to note in the above highlighted line is the use of System.nanoTime().

Because PhysicalAddress objects are stored in a collection inside of ComplexUserDetails, we use System.nanoTime() to give it a unique ID. In the traditional approach, a PhysicalAddress would be stored in a separate database table and would be assigned an identifier using an auto-incrementing primary key, or something similar.

Sometimes you should just ignore database “normal forms”. Relax and have fun going against your professor’s advice!

Thanks to Vlad Mihalcea, who writes lots of insightful hibernate articles, his post on this topic helped me a lot!