Search This Blog

Guide to Hibernate Native SQL Queries

Hibernate provides an option to execute queries in the native SQL dialect of your database. This is useful if you want to utilize database-specific features such as window functions, Common Table Expressions (CTE) or the CONNECT BY option in Oracle. It also provides a clean migration path from a direct SQL/JDBC based application to Hibernate/JPA. Hibernate also allows you to specify handwritten SQL (including stored procedures) for all create, update, delete, and retrieve operations.

Hibernate native query with a custom column selection

These will return a List of Object arrays ( Object[] ) with scalar values for each column in the PERSON table. Hibernate will use java.sql.ResultSetMetadata to deduce the actual order and types of the returned scalar values.

To avoid the overhead of using ResultSetMetadata, or simply to be more explicit in what is returned, one can use addScalar():

Although it still returns an Object array, this query will not use the ResultSetMetadata anymore since it explicitly gets the id and name columns as respectively a BigInteger and a String from the underlying ResultSet. This also means that only these two columns will be returned, even though the query is still using * and the ResultSet contains more than the three listed columns.

It is possible to leave out the type information for all or some of the scalars.

Hibernate native query with result set selection that’s a partially explicit

2. Entity queries

The above queries were all about returning scalar values, basically returning the raw values from the ResultSet. In this section, we will look into how to express Hibernate native query for selecting entities.

3. Handling associations and collections

If the entity is mapped with a many-to-one or a child-side one-to-one to another entity, it is required to also return this when performing the native query, otherwise, a database-specific column not found error will occur.

The below example will allow the Phone#person to function properly since the many-to-one or one-to-one association is going to use a proxy that will be initialized when being navigated for the first time.

By default, when using the addJoin() method, the result set will contain both entities that are joined. To construct the entity hierarchy, you need to use a ROOT_ENTITY or DISTINCT_ROOT_ENTITYResultTransformer.

Notice that you added an alias name pr to be able to specify the target property path of the join. It is possible to do the same eager joining for collections (e.g. the Phone#calls one-to-many association).

4. Returning multiple entities

Until now, the result set column names are assumed to be the same as the column names specified in the mapping document. This can be problematic for SQL queries that join multiple tables since the same column names can appear in more than one table.

Column alias injection is needed in the following query which otherwise throws NonUniqueDiscoveredSqlAliasException.

Hibernate native query selecting entities with the same column names

The query was intended to return all Person and Partner instances with the same name. The query fails because there is a conflict of names since the two entities are mapped to the same column names (e.g. id, name, version). Also, on some databases, the returned column aliases will most likely be on the form pr.id, pr.name, etc. which are not equal to the columns specified in the mappings (id and name).

The following form is not vulnerable to column name duplication:

Hibernate native query selecting entities with the same column names and aliases