How to store JSON Data with JPA and Hibernate
source link: https://www.mastertheboss.com/java-ee/jpa/how-to-store-json-data-with-jpa-and-hibernate/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Several databases provide varying levels of support for JSON data, including storage, indexing, querying, and manipulation capabilities. In this article we will explore how to insert and fetch JSON Data using Jakarta Persistence API and WildFly.
JSON Native Support in Relational Databases
Several relational databases offer support for storing and manipulating JSON data as a column type. Here are some popular relational databases that provide native support for JSON:
- PostgreSQL: Provides robust support for JSON and JSONB (binary JSON) data types, allowing efficient storage, indexing, and querying of JSON data.
- MySQL: Starting from version 5.7, MySQL introduced native JSON data types, allowing storage and retrieval of JSON documents.
- SQL Server: Introduced JSON support in SQL Server 2016, enabling storage of JSON data and providing functions for querying and modifying JSON documents.
- Oracle Database: Introduced native JSON data type support in Oracle Database 12c Release 2 (12.2), allowing storage and processing of JSON data.
In this article, we will be using PostgreSQL which natively supports since version 9.2 the json and jsonb Data Types. You can verify support for these Data types with a simple check:
# create temp table test ( data jsonb ); CREATE TABLE testdb=# INSERT INTO test (data) VALUES ('{"name": "John", "age": 30, "email": "[email protected]"}'); INSERT 0 1 testdb=# select * from test; data ---------------------------------------------------------- {"age": 30, "name": "John", "email": "[email protected]"}
Note: In general terms, you should prefer the jsonb
type in most scenarios due to its more efficient storage and indexing compared to the json
type. Besides, consider using PostgreSQL versions 9.4 and above for improved performance and capabilities when working with JSON data.
Mapping a JSON Type in Hibernate / JPA
Firstly, let’s discuss about the main concern: how to map a JSON Type in a Hibernate / JPA application. As a matter of fact, there is no built-in JSON type in either Hibernate or JPA. However, we can bind the
org.hibernate.annotations.Type
Hibernate annotation with a custom type. The custom JSON type is provided by the Vlad Mihalcea’s Hypersistence library. This library provides the
io.hypersistence.utils.hibernate.type.json.JsonType
.
Here is an example:
import io.hypersistence.utils.hibernate.type.json.JsonType; import org.hibernate.annotations.Type; . . . . @Type(JsonType.class) @Column(columnDefinition = "jsonb") private MyClass;
In this example, all JSON keys will map with the fields of the Class MyClass. Alternatively, we can map the JsonType with a Map as in the following example:
@Type(JsonType.class) @Column(columnDefinition = "jsonb") private Map<String, String> properties = new HashMap<>();
Coding the Entity Class
Next, we will put in practice our knowledge and craft an Entity Class which contains a
JsonType
to store a JSON Document:
@Entity public class Employee { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Type(JsonType.class) @Column(columnDefinition = "jsonb") private Map<String, String> properties = new HashMap<>(); private String name; private String surname; //Getters/ Setters omitted for brevity }
As you can see, the above
Employee
Class includes a properties Column which will persist a set of key/values using a JSON Document. We will now add the Service and Controller layers to access this Entity Class.
Coding the Service and REST Controller
Next, we will add a simple REST Layer to Test our Entity. Firstly, we will add a Service Class which contains a method to insert an Employee and two finder methods:
@ApplicationScoped public class EmployeeService { @PersistenceContext private EntityManager em; @Transactional public void insertEmployee(Employee employee) { em.persist(employee); } public List<Employee> findByRole(String role) { return em.createNativeQuery( "SELECT * FROM Employee WHERE properties ->> 'role' = :role", Employee.class) .setParameter("role", role) .getResultList(); } public List<Employee> findAllEmployees() { return em.createQuery("SELECT e FROM Employee e", Employee.class) .getResultList(); } }
The method
findByRole
contains a PostgreSQL Native Query to filter through a JSON Query:
"SELECT * FROM Employee WHERE properties ->> 'role' = :role"
This SQL query selects all columns (*
) from the Employee
table where the properties
column contains a JSON object and extracts the value associated with the key 'role'
. The
operator is used to access a specific key-value pair in the JSON column. ->>
:role
is a named parameter used to filter the records based on the provided role.
Finally, the
EmployeeResource
is a trivial REST facede to the
EmployeeService
:
@Path("/employees") @Produces(MediaType.APPLICATION_JSON) @Consumes(MediaType.APPLICATION_JSON) public class EmployeeResource { @Inject private EmployeeService employeeService; @GET public Response getAllEmployees() { List<Employee> employees = employeeService.findAllEmployees(); return Response.ok(employees).build(); } @POST public Response createEmployee(Employee employee) { employeeService.insertEmployee(employee); return Response.ok("Employee created").build(); } @GET @Path("/byrole") public List<Employee> findByRole(@QueryParam("role") String role) { return employeeService.findByRole(role); } }
Fixing the Configuration
The last part will be configuring our application to run on WildFly. Let’s begin with the Maven configuration. Besides the Jakarta EE 10 API, we need two dependencies:
- Hibernate core dependency to allow compiling the org.hibernate.annotations.Type
org.hibernate.annotations.Type
- Hypersistence dependency to allow building and running the application with the io.hypersistence.utils.hibernate.type.json.JsonType
io.hypersistence.utils.hibernate.type.json.JsonType
<dependencies> <dependency> <groupId>jakarta.platform</groupId> <artifactId>jakarta.jakartaee-api</artifactId> <version>10.0.0</version> <scope>provided</scope> </dependency> <dependency> <groupId>io.hypersistence</groupId> <artifactId>hypersistence-utils-hibernate-63</artifactId> <version>3.7.0</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>6.4.1.Final</version> <scope>provided</scope> </dependency> </dependencies>
Please note that the above settings reflect my configuration which runs WildFly 31 (and Hibernate 6.4). Please refer to the project source for a complete documentation on the correct version of Hypersistence in other scenarios.
It is worth mentioning that, as we will use the Hibernate Core library only for compiling the project as we don’t want to provide the whole Hibernate core to WildFly. However, we need to activate the
org.hibernate.commons-annotations
module to be able to run Hibernate annotations.
Simply add to
WEB-INF/jboss-deployment-structure.xml
the following content:
<jboss-deployment-structure> <deployment> <dependencies> <module name="org.hibernate.commons-annotations"/> </dependencies> </deployment> </jboss-deployment-structure>
Finally, we will provide in
resources/META-INF/persistence.xml
a Persistence Unit which points to a WildFly Datasource:
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.1" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"> <!-- Define persistence unit --> <persistence-unit name="my-persistence-unit"> <description>Sample Hibernate Postgresql Dialect</description> <jta-data-source>java:/PostGreDS</jta-data-source> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/> <property name="hibernate.hbm2ddl.auto" value="create-drop" /> </properties> </persistence-unit> </persistence>
We will not cover in detail the configuration of a Datasource for PostgreSQL. You can check this article for more details: Configuring a datasource with PostgreSQL and JBoss/WildFly
Testing the application
Lastly, we will test our application with some simple curl commands. First, let’s create an Employee with an HTTP POST:
curl -X POST -H "Content-Type: application/json" -d '{ "name": "Alice", "surname": "Smith", "properties": { "department": "HR", "role": "Manager" } }' http://localhost:8080/postgres-json/employees
As you can see, the curl successfully created an Employee:
Then, let’s use a PostgreSQL Native Statement to fetch the Employee with Role = Manager:
curl http://localhost:8080/postgres-json/employees/byrole?role=Manager
Here is the outcome:
Great! As you can see we are able to filter through our Table using a JSON Key..
Conclusion
With PostgreSQL’s native JSON and JSONB data types, developers can efficiently store, query, and manipulate JSON data within relational databases. When combined with the capabilities of JPA, such as entity mapping and native query execution, this enables seamless integration and interaction between Java entities and JSON data.
Source code: https://github.com/fmarchioni/mastertheboss/tree/master/jpa/postgres-json
Found the article helpful? if so please follow us on Socials
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK