Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly.
Hypersistence Optimizer is that tool!
This article is part of a series of posts related to calling various relational database systems stored procedures and database functions from Hibernate. The reason for writing this down is because there are many peculiarities related to the underlying JDBC driver support and not every JPA or Hibernate feature is supported on every relational database.
From a syntax perspective, PostgreSQL supports only the FUNCTION keyword.
However, a PostgreSQL function can take an OUT parameter as well, meaning that the PostgreSQL function can behave just like some other RDBMS stored procedure.
PostgreSQL function outputting a simple value
CREATE OR REPLACE FUNCTION count_comments(
IN postId bigint,
OUT commentCount bigint)
RETURNS bigint AS
SELECT COUNT(*) INTO commentCount
WHERE post_id = postId;
This function has two parameters: an input parameter (e.g. postId) and an output parameter (e.g. commentCount) which is used to return the count of post_comment records associated with a given post_id parent row.
To call this stored procedure, you can use the following Java Persistence API 2.1 syntax:
When calling a PostgreSQL function through the JDBC API, parameters must be supplied by index and not by name, as otherwise the following exception is thrown:
java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc4.Jdbc4CallableStatement
.registerOutParameter(String,int) is not yet implemented.
PostgreSQL function outputting a REFCURSOR
A function can also define a REFCURSOR output parameter which is associated with a database cursor that can be iterated to fetch multiple database records:
CREATE OR REPLACE FUNCTION post_comments(postId BIGINT)
RETURNS REFCURSOR AS
OPEN postComments FOR
WHERE post_id = postId;
On PostgreSQL, it’s possible to call this stored procedure using the JPA 2.1 syntax:
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Calling PostgreSQL functions is not difficult at all, but it requires knowing some details about Hibernate and the underlying JDBC driver capabilities. The next article is about calling MySQL stored procedures and functions, so stay tuned!
Based on my book, High-Performance Java Persistence, this workshop teaches you various data access performance optimizations from JDBC, to JPA, Hibernate and jOOQ for the major rational database systems (e.g. Oracle, SQL Server, MySQL and PostgreSQL).
The SQL Master Class for Java Developers training is aimed to level up your SQL skills with techniques such as Window Functions, recursive queries, Pivoting, JSON processing, and many other database querying features supported by Oracle, SQL Server, MySQL, or PostgreSQL.