Introduction

In this article, you are going to learn how to map a JPA entity to the ResultSet of a SQL query using the @Subselect Hibernate-specific annotation.

List all PostgreSQL functions

Let’s assume we have two PostgreSQL functions in our database:

CREATE OR REPLACE FUNCTION public.count_comments(
    IN postid bigint,
    OUT commentcount bigint)
  RETURNS bigint AS
'     BEGIN         
      SELECT COUNT(*) INTO commentCount         
      FROM post_comment          
      WHERE post_id = postId;     
      END; 
'
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.count_comments(bigint)
  OWNER TO postgres;

CREATE OR REPLACE FUNCTION public.post_comments(postid bigint)
  RETURNS refcursor AS
'     DECLARE         
           postComments REFCURSOR;     
      BEGIN         
      OPEN postComments FOR              
      SELECT *              
      FROM post_comment               
      WHERE post_id = postId;          
      RETURN postComments;     
      END; 
'
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.post_comments(bigint)
  OWNER TO postgres;

In our application, we want to know all the PostgreSQL functions we can call, and, for this purpose, we can use the following SQL query:

SELECT 
    functions.routine_name as name,
    string_agg(functions.data_type, ',') as params
FROM (
    SELECT 
        routines.routine_name, 
        parameters.data_type, 
        parameters.ordinal_position
    FROM 
        information_schema.routines
    LEFT JOIN 
        information_schema.parameters 
    ON 
        routines.specific_name = parameters.specific_name
    WHERE 
        routines.specific_schema='public'
    ORDER BY 
        routines.routine_name, 
        parameters.ordinal_position
) AS functions
GROUP BY functions.routine_name

When running the SQL query above, we get the following result set:

That’s great, but we want this result set to be mapped as a JPA entity, like in the following diagram.

Hibernate @Subselect annotation

Knowing how to query the PostgreSQL database functions, our DatabaseFunction can be mapped like this:

@Entity
@Immutable
@Subselect(
    "SELECT " +
    "    functions.routine_name as name, " +
    "    string_agg(functions.data_type, ',') as params " +
    "FROM (" +
    "    SELECT " +
    "        routines.routine_name, " +
    "        parameters.data_type, " +
    "        parameters.ordinal_position " +
    "    FROM " +
    "        information_schema.routines " +
    "    LEFT JOIN " +
    "        information_schema.parameters " +
    "    ON " +
    "        routines.specific_name = parameters.specific_name " +
    "    WHERE " +
    "        routines.specific_schema='public' " +
    "    ORDER BY " +
    "        routines.routine_name, " +
    "        parameters.ordinal_position " +
    ") AS functions " +
    "GROUP BY functions.routine_name"
)
public class DatabaseFunction {

    @Id
    private String name;

    private String params;

    public String getName() {
        return name;
    }

    public String[] getParams() {
        return params.split(",");
    }
}

The @Subselect Hibernate-specific annotation allows you to map a read-only entity directly to the ResultSet of a given SQL query.

Notice that the entity is mapped with the @Immutable annotation since the query is just a read-only projection.

Database view

You can also encapsulate the query in a database view, like this:

CREATE OR REPLACE VIEW database_functions AS
    SELECT 
        functions.routine_name as name,
        string_agg(functions.data_type, ',') as params
    FROM (
        SELECT 
            routines.routine_name, 
            parameters.data_type, 
            parameters.ordinal_position
        FROM 
            information_schema.routines
        LEFT JOIN 
            information_schema.parameters 
        ON 
            routines.specific_name = parameters.specific_name
        WHERE 
            routines.specific_schema='public'
        ORDER BY routines.routine_name, parameters.ordinal_position
    ) AS functions
    GROUP BY functions.routine_name;

Mapping a JPA entity to a database view is even simpler and can be done using plain JPA mappings:

@Entity
@Immutable
@Table(name = "database_functions")
public class DatabaseFunction {

    @Id
    private String name;

    private String params;

    public String getName() {
        return name;
    }

    public String[] getParams() {
        return params.split(",");
    }
}

Testing time

We can query the DatabaseFunction entity using JPQL, as illustrated by the following example:

List<DatabaseFunction> databaseFunctions = 
entityManager.createQuery(
    "select df " +
    "from DatabaseFunction df", DatabaseFunction.class)
.getResultList();

DatabaseFunction countComments = databaseFunctions.get(0);

assertEquals(
    "count_comments", 
    countComments.getName()
);
assertEquals(
    2, 
    countComments.getParams().length
);
assertEquals(
    "bigint", 
    countComments.getParams()[0]
);

DatabaseFunction postComments = databaseFunctions.get(1);

assertEquals(
    "post_comments", 
    postComments.getName()
);
assertEquals(
    1,  
    postComments.getParams().length
);
assertEquals(
    "bigint", 
    postComments.getParams()[0]
);

Online Workshops

If you enjoyed this article, I bet you are going to love my upcoming Online Workshops!

• Caching Best Practices with JPA and Hibernate (2.5 hours) on the 30th of September
• High-Performance SQL (4 hours) on the 6th of October in collaboration with Voxxed Days Ticino
• High-Performance SQL (12 hours) starting on the 28th of October in collaboration with Bouvet

That’s it!

Conclusion

Mapping an entity to an SQL result set is actually very easy with JPA and Hibernate.

You can either use the Hibernate-specific @Subselect annotation, in case you don’t want to map the query to a database view. Or, if you map the query as a view, you can simply use that instead of an actual database table.

Insert details about how the information is going to be processed

DOWNLOAD NOW