How to Extend JPA to Work with PostgreSQL

Hi all! My name is Anton, I am an architect at ITFB Group. Having experienced several projects where the PostgreSQL stack was found using the PostgreSQL + JPA combination, I was able to eliminate a large number of problems associated with suboptimal integration of PostgresSQL functionality in Java applications. This is what motivated me to write this article:

• misunderstanding regarding the stack for small business tasks, the so-called “banal overengineering”;

• often unjustified use of string concatenation to “assemble” a query and the planting of “pasta” in the code;

• reinventing your own wheels in JPA and Hibernate.

As an example, we will take two PostgreSQL functionalities, also known as data types, – tsquery and JSONB.

Let's go into battle with a stack:

• Hibernate 6.x

• SpringDataJPA3.+

• PostgreSQL 15+

In this article, we will analyze in maximum detail how to configure JPA for effective work with PostgreSQL. Anyone interested in this topic, welcome under the cut)

What's wrong with JPA

JPA, and its most famous implementation Hibernate, is a generic specification/library designed to interact with various relational databases. However, there are certain features that are not supported natively in JPA when working with PostgreSQL. For example, there is JSONB out of the box in Hibernate. We have the annotation @JdbcTypeCode(SqlTypes.JSON) and that's basically it. If you want to use native JSONB operators and methods, you can write native SQL queries. However, there are suggestions that can improve the functionality using the available Hibernate tools.

One of the key classes to pay attention to when extending
JPA for PostgreSQL is Dialect. Dialect acts as an adapter for translating the “general” SQL query syntax (also known as a JPQL/HQL derivative) to the specific SQL query syntax of a particular DBMS and is provided by Hibernate through its properties.

What you have to work with when expanding Dialect

FunctionContributions interface – helps to register a custom implementation of an HQL function.

TypeContributions interface – Registers custom types in your application.

JdbcType and JavaType are interfaces that help describe the serialization and deserialization of custom types from POJOs to query parameters, field values, and vice versa.

In this article we will not touch on the tools listed below, but we will remember that we have them in our arsenal:

1. Statement interceptors. Comes into play when you need to intercept a database-specific SQL string before a flush and make changes to it if necessary. You can return a modified SQL string with or without modifications.

2. Query rewriter. New feature in Spring Data, works similarly to Statement interceptors.

3. Attribute converter. Useful for simple cases such as converting a string to a boolean and vice versa, etc.

DIY mapping in Hibernate 6

Let's look at how to map PostgreSQL to a Java/POJO type. For this we have two main library objects:

1. JdbcType Defines the data type used to pass parameters to a PreparedStatement and to retrieve values ​​from a ResultSet or called method.

2. JavaType. Complements JdbcType and defines methods to wrap a value into a data type that will be passed to the PreparedStatement as a query parameter, and also helps cast values ​​from a ResultSet to a specific model/POJO value.

When developing mapping types, you will have to open the PostgreSQL documentation and try to transfer the specified syntax to the logic for extracting and packing data in a query.

Example with JSONB

Let's assume we want to use the JSONB type without using external libraries or out-of-the-box approaches.

Let's write our own JavaType:

We use java.util.Map as the value type on the Java side since for our test case we expected the JSON value to store arbitrary data.

To pass a POJO as a query parameter, we use Jackson to serialize the object into a json-like string and wrap the resulting string in a PGobject.

Next, let's ask ourselves: how do we get a value from a ResultSet? For our case, the appropriate type would be a string or binary stream, and for deserialization we'll use the getExtractor method in JdbcType.

In the class below, we are trying to describe a function that checks if a JSONB value on the right includes a value on the left using the JSON path syntax. In the AST arguments, we are dealing with two types of parameters:

• QueryLiteral – Argument types passed as a constant string to Criteria Builder and used in the specification.

• SqmParameterInterpretation – Argument types used for queries in a method with the Query annotation in JpaRepository that contains a query parameter.

After describing the function, let's register it with Dialect:

There are several ways to implement a custom Dialect:

In addition to the above, we can use the following syntactic constructs in specifications:

The second stage is complete, and we are ready to write and test our queries. As an example, let's use the new function in the Criteria API specification:

In a JPA request to work with JSON, we need to perform a minor trick:

I suggest we consolidate the material and add a bit of tsquery to our application. At the input we have the following native SQL query:

This query performs a full-text search on a phrase, a single word, and its synonyms.

To set up the database environment for full-text search, we need to perform some actions on the PostgreSQL side.

Download the dictionary files and convert them:

Place these files in the specified folder on the PostgreSQL host:

Run the initialization script to create the dictionary:

We did the basic things on the database side. Let's describe our function:

Next we need to register the function description in the dialect:

Create a JPA repository with the tsquery method for our example:

Time to test the code:

I hope that my experience can be useful on your projects. I suggest you look at the example code at GitGub.

Anton, architect of ITFB Group