2018. február 10., szombat

Using Oracle JSON functions via JPA and Hibernate

I've recently realized that Oracle 12 has functions that support parsing JSON strings and accessing given keys from that:
How can you use these functions from a JPA or Hibernate application? Here are some options.

Use native queries

Won't give you an example, this works as usual :)

An example query:

Use CriteriaBuilder / Criteria Queries

Let's say one of your entities has a JSON property and you're writing some code to filter this table and only pick entities which has a certain JSON key with given value. Unfortunately there is no easy way to do this using JPA / Hibernate.

If you are using Hibernate, but not JPA, one option is to use Restrictions#sqlRestriction. This works, however is not compatible with JPA's criteria API, which means if the rest of your criteria is written using JPA, you cannot easily conjugate ("AND") it together with this restriction. 

However, since JPA 2.0, there is a new method named CriteriaBuilder#function. This allows calling any function built into the database or defined by yourself. Unfortunately doing the following results in an exception:



The reason is, that in the background, Hibernate will create a templated SQL statement (eg. JSON_VALUE(?, ?) ) which won't work with oracle. The solution is, to inline the function parameters instead of binding them. Unfortunately there is not built-in way in JPA or Hibernate, but with a small helper class, it will work perfectly:




Using calculated (derived) properties

 

Another interesting usage is, when you have a JSON valued column in a table, and would like to extract certain properties from it. You can do this by either creating a custom JPQL query where you select the property by calling JSON_VALUE. This works, but using the calculated property feature of Hibernate is more elegant:


This way, you can avoid mapping the JSON column alltogether and not transmit potentially large amount of data to the client side, just to parse it in Java and read a single property.

Summary

Hope you find this useful, please comment if you've found some other interesting usage!