Hibernate with PostgreSQL – 6 things you need to know

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

PostgreSQL is one of the most popular relational databases, and Hibernate is probably the most popular JPA implementation. So, it’s no surprise that they’re often used together and that you don’t have to expect any major issues doing that.

But as so often, there is a difference between “it works” and “it works great”. Out of the box, Hibernate and PostgreSQL work well together. But if you want to use both systems to its fullest extent, you need to know a few things and avoid some others.

Here is my list of the 5 most important things you should know when you want to use Hibernate with PostgreSQL.

1. Mappings: Primary Keys

Primary keys and the generation of unique values seem to be basic features. The JPA specification defines different strategies to generate primary key values and you can use all of them with PostgreSQL.

But that doesn’t mean that you should just pick any one of them.

The TABLE strategy uses a database table to generate unique primary key values. This requires pessimistic locking and isn’t the most efficient approach.

The IDENTITY strategy forces Hibernate to execute the SQL INSERT statement immediately. Due to this, Hibernate can’t use any of its performance optimization strategies that require a delayed execution of the statement. One example of that is JDBC batching. But it can also affect simple things, like updating an attribute before the entity gets persisted. When Hibernate has to execute the INSERT statement immediately, it has to perform an additional UPDATE statement to persist the changed value instead of using that value in the INSERT statement.

The best generation strategy you can use with a PostgreSQL database is the SEQUENCE strategy. It uses a simple database sequence and is highly optimized by PostgreSQL. And Hibernate uses an optimized algorithm by default to avoid unnecessary SELECT statements.

If you want to use Hibernate’s default sequence, you just need to add a @GeneratedValue annotation to your primary key attribute and set the strategy to GenerationType.SEQUENCE.

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
@Column(name = "id", updatable = false, nullable = false)
private Long id;

Or you can create a custom sequence with the following statement.

CREATE SEQUENCE book_seq;

You can then reference it in a @SequenceGenerator annotation.

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "book_generator")
@SequenceGenerator(name="book_generator", sequenceName = "book_seq", allocationSize=50)
@Column(name = "id", updatable = false, nullable = false)
private Long id;

2. Mappings: Custom data types, like JSONB

PostgreSQL supports a set proprietary data type which Hibernate doesn’t map by default. Popular examples for that are the JSON and JSONB data types which allow you to persist and query JSON documents in a PostgreSQL database.

If you want to use these types with Hibernate, you need to define the mapping yourself. That requires additional code, but it’s not as complicated as it might sound. You just need to implement and register a UserType which tells Hibernate how to map the Java object to a supported JDBC type and vice versa.

I explained the required implementation in great detail in How to use PostgreSQL’s JSONB data type with Hibernate. You can use the same approach to implement a custom mapping for all PostgreSQL types that are not supported by Hibernate.

3. Mappings: Read-only Views

From a mapping point of view, database tables and views are pretty similar and you can map both of them to an entity class. The only difference is that some views are read-only. And it’s a good practice to map them to a read-only entity.

By default, Hibernate supports read and write operations for all entities. If you want to make an entity read-only, you have to tell Hibernate that it is immutable. You can do that by annotating the entity class with an @Immutable annotation.

@Entity
@Immutable
public class BookView {
	
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name = "id", updatable = false, nullable = false)
	private Long id;
	@Version
	@Column(name = "version")
	private int version;

	@Column
	private String title;

	@Column
	@Temporal(TemporalType.DATE)
	private Date publishingDate;

	@Column
	private String authors;
	
	...
	
}

4. Queries: Use PostgreSQL-specific query features

When you’re familiar with JPQL and SQL, you already know that JPQL supports only a small subset of the SQL standard. But that’s not a real issue, because if JPQL isn’t powerful enough to implement your use cases, you can use a native SQL query instead.

Native SQL queries allow you to use the full SQL feature set including all database-specific query features. You create them in a similar way as your JPQL queries. You can define a named native query with a @NamedNativeQuery annotation or create an ad-hoc native query by calling the createNativeQuery method on the EntityManager.

@NamedNativeQuery(name = "selectAuthorNames", query = "SELECT a.firstname, a.lastname FROM Author a")

Query q = em.createNativeQuery("SELECT a.firstname, a.lastname FROM Author a");
List<Object[]> authors = q.getResultList();

for (Object[] a : authors) {
    System.out.println("Author "
            + a[0]
            + " "
            + a[1]);
}

5. Queries: Call PostgreSQL-specific SQL Functions

You can, of course, use a native SQL query to call a PostgreSQL-specific SQL function in the same way as any other proprietary query feature. But since JPA 2.1, you can also call these functions in your JPQL queries.

The JPQL function function allows you to call any SQL function supported by your database. You just need to provide the name of the function as the first parameter, followed by an optional list of parameters that will be used to call the SQL function.

The following code snippet shows a simple example that calls the SQL function calculate with the parameters 1 and 2.

Author a = em.createQuery("SELECT a FROM Author a WHERE a.id = function('calculate', 1, 2)", Author.class).getSingleResult();

6. Queries: Call Stored Procedures

PostgreSQL doesn’t distinguish between functions and stored procedures. It only supports very flexible functions which can be used like stored procedures or as SQL functions. I already showed you how to call a non-standard SQL function. So, let’s take a look at the stored procedures.

When your PostgreSQL function returns an REF_CURSOR parameter, which is a cursor on a result set, you need to call it like a stored procedure. The following code snippet shows an example of such a function.

CREATE OR REPLACE FUNCTION get_reviews(bookid bigint)
  RETURNS refcursor AS
$BODY$
    DECLARE
      reviews refcursor;           -- Declare cursor variables                         
    BEGIN
      OPEN reviews FOR SELECT id, comment, rating, version, book_id FROM review WHERE book_id = bookId;
      RETURN reviews;
    END;
  $BODY$
  LANGUAGE plpgsql

Since JPA 2.1, you can call a stored procedure with a @NamedStoredProcedureQuery or an ad-hoc StoredProcedureQuery.

@NamedStoredProcedureQuery

With a @NamedStoredProcedureQuery annotation you can define a function call which you can use in your business code. The following code snippet defines a call of the get_reviews function. It tells Hibernate to provide an input parameter of type Long and to expect an REF_CURSOR as a result. The resultClass parameter tells Hibernate to map all records of the REF_CURSOR to Review objects.

@NamedStoredProcedureQuery(
	name = "getReviews", 
	procedureName = "get_reviews", 
	resultClasses = Review.class, 
	parameters = {
		@StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class), 
		@StoredProcedureParameter(mode = ParameterMode.IN, type = Long.class)
	}
)

You can call the @NamedStoredProcedureQuery in a similar way as you would call a @NamedQuery. You just need to call the createNamedStoredProcedureQuery method to instantiate the query, set the input parameters and retrieve the result.

StoredProcedureQuery q = this.em.createNamedStoredProcedureQuery("getReviews");
q.setParameter(2, b.getId());
List<Review> reviews = q.getResultList();

Ad-hoc StoredProcedureQuery

When you want to define the function call programmatically, you can do that with an ad-hoc query. It follows the same concept as the definition and execution of a @NamedStoredProcedureQuery.

You first need to call the createStoredProcedureQuery method of the EntityManager with the name of the database function and its return type, to instantiate a StoredProcedureQuery. In the next step, you need to register all function parameters. You can do that by calling the registerStoredProcedureParameter method of the StoredProcedureQuery for each parameter.

After you defined the function call, you just need to provide the values for all input parameters and execute the query by calling the getResultList method on the StoredProcedureQuery.

StoredProcedureQuery query = this.em.createStoredProcedureQuery("get_reviews", Review.class);
query.registerStoredProcedureParameter(1, void.class, ParameterMode.REF_CURSOR);
query.registerStoredProcedureParameter(2, Long.class, ParameterMode.IN);

query.setParameter(2, b.getId());
List<Review> reviews = query.getResultList();

Summary

Out of the box, Hibernate works pretty well with PostgreSQL databases. But as you’ve seen, there are a few things you should know if you want to use all database features and avoid performance problems.

Especially the generation of unique primary key values is a common pitfall. But also things, like mapping a read-only database view or calling a PostgreSQL function, can be quite useful for most projects.