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 ->> :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 - Hypersistence dependency to allow building and running the application with the
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
