8 Ways to use the features of your database with Hibernate

Datetime:2017-04-19 05:20:47         Topic: DataBase  Hibernate          Share        Original >>
Here to See The Original Article!!!

A common misconception when working with Hibernate is that you can only map database tables and don’t use any other database features. That’s not the case, as I want to show you in this post.

You can also use lots of other features which allow you to:

  • Map read-only database views
  • Call database functions
  • Execute stored procedures
  • Use database columns whose values are generated by database triggers
  • Map SQL expressions to entity attributes
  • Generate unique primary key values with sequences
  • Use autoincremented columns as primary keys
  • Map custom and database-specific datatypes

Watch it on YouTube

Read-only Views

Views are one of the database features you can easily use with Hibernate. You can map a view in the same way as any database table. As long as you follow the default naming strategy, you just need a class with an @Entity annotation and an attribute for each database column.

You canmap a read-only view in the same way. You just need an additional annotation to tell Hibernate that it should ignore the entity for all write operations. You can do that with Hibernate’s @Immutable annotation.

Get this post as a free cheat sheet!

I prepared a free cheat sheet with the most important information and code snippets of this post. As always, you can download it for free from the Thoughts on Java Library.

Join Now!

Already a member?Login here.

Call Database Functions

Calling a database function to perform simple operations, like counting the number of characters in a String , is a standard feature in SQL. You can do the same with JPA and Hibernate.JPQL and the Criteria API support a set of standard functions which you can use in your queries. Since JPA 2.1, there is also an easy option to call functions that are not directly supported.

Call a standard function

JPA and Hibernate support the following set of standard functions which you can use in aJPQL query. The Criteria API supports the same functions and provides one or more methods for each of them.

  • upper(String s) : transforms String s to upper case
  • lower(String s) : transforms String s to lower case
  • current_date() : returns the current date of the database
  • current_time() : returns the current time of the database
  • current_timestamp() : returns a timestamp of the current date and time of the database
  • substring(String s, int offset, int length) : returns a substring of the given String s
  • trim(String s) : removes leading and trailing whitespaces from the given String s
  • length(String s) : returns the length of the given String s
  • locate(String search, String s, int offset) : returns the position of the String search in s . The search starts at the position offset
  • abs(Numeric n) : returns the absolute value of the given number
  • sqrt(Numeric n) : returns the square root of the given number
  • mod(Numeric dividend, Numeric divisor) : returns the remainder of a division
  • treat(x as Type) : downcasts x to the given Type
  • size(c) : returns the size of a given Collection c
  • index(orderdCollection) : returns the index of the given value in an ordered Collection

You can use these functions in the SELECT and WHERE clause of your query. You can see a simple example in the following code snippet.

As you can see in the log output, Hibernate translates the call of the JPQL function size into a JOIN clause with the BookAuthor table and a call of the SQL function count .

Call an unsupported function

Most databases support a lot more functions than the ones directly supported by Hibernate or JPA. But don’t worry, you can call them anyways.

SinceJPA 2.1, you can use the function function to call any function supported by your database. You just need to provide the name of the database function as the first parameter followed by the arguments you want to provide to the function call.

I use the function function in the following code snippet, to call the user-defined function calculate with the price of the book and a bind parameter as arguments.

As you can see in the log messages, Hibernate uses the parameters provided to the function function to call the calculate function in the SQL statement. If you want to learn more about JPA’s and Hibernate’s support for custom database function calls, take a look at How to call custom database functions with JPA and Hibernate .

Stored Procedures

Stored procedures provide another option to perform logic within your database. That can be beneficial, if you need to share logic between multiple applications that use the same database or if you’re looking for the most efficient way to implement data-heavy operations.

In the past, you had to use anative SQL query to call a stored procedure. Since JPA 2.1, you also have 2 other options. You can either use a @NamedStoredProcedureQuery to define the stored procedure call via annotations or you can do that programmatically .

As you can see in the following code snippets, the annotation-based definition of a stored procedure call isn’t complicated. In the first step, you define the stored procedure call with a @NamedStoredProcedure annotation by providing the name of the stored procedure and its input and output parameters.

You can then use the @NamedStoredProcedureQuery in a similar way as you call a named query. You just have to call the createNamedStoredProcedureQuery method of the EntityManager with the name of your @NamedStoredProcedureQuery to instantiate it. Then you can set the input parameters, execute the query and read the output parameter.

You can learn more about stored procedure calls and the different ParameterModes in How to call a stored procedure in JPA and How to programmatically call a stored procedure in JPA .

Watch it on YouTube

Database columns with generated values

Another often used feature of relational databases are triggers that initialize or update certain database columns. You can use them, for example, to automatically persist the timestamp of the last update. While you could also do that with Hibernate , most database administrators prefer to handle that on a database level.

But this approach has a drawback. Hibernate has to perform an additional query to retrieve the generated values from the database. That slows down your application and Hibernate doesn’t execute the extra query by default.

You need to annotate the attributes that map a database column with a generated value with Hibernate’s @Generated(GenerationTime value) annotation. The GenerationTime annotation tells Hibernate when it has to check for a new value. It can either do that NEVER , after each INSERT or after each INSERT and UPDATE ( GenerationTime.ALWAYS ) operation.

The following code snippet and log messages show an example of such a mapping and of the SQL statements Hibernate has to perform.

Get this post as a free cheat sheet!

I prepared a free cheat sheet with the most important information and code snippets of this post. As always, you can download it for free from the Thoughts on Java Library.

Join Now!

Already a member?Login here.

Watch it on YouTube

Map SQL expressions

Your domain and table model don’t need to be identical. You can also map the result of an SQL expression to a read-only attribute of your domain model.

You can do that with Hibernate’s @Formula annotation . It allows you to specify an SQL expression which Hibernate executes when it reads the entity from the database.

I use it in the following example to calculate the age of an Author based on her date of birth.

As you can see in the following log messages, Hibernate takes the specified SQL snippet and adds it to the query when it reads the Author entity from the database.

Watch it on YouTube


Database sequences are often used to generate unique primary key values. Hibernate and JPA support different options to generate primary key values and database sequences are, of course, one of them.

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

You can also use a custom database sequence when you add a @SequenceGenerator annotation. It allows you to define the name and database schema of your sequence and the allocation size Hibernate shall use to retrieve primary key values.

Watch it on YouTube

Autoincremented Database Columns

Autoincremented columns provide another option to generate unique primary key values . The database automatically increments the value of this column for each new record.

The mapping of such a column is similar to the one I showed in the previous example. You just need to tell Hibernate to use a different strategy to generate the primary key values. The GenerationType.IDENTIFIER tells Hibernate that the database provides the primary key value.

But please be aware that this approach prevents Hibernate from using different optimization techniques like JDBC batching.

Custom and Database-Specific Datatypes

Most databases support a set of custom data types, like PostgreSQL’s JSONB . JPA and Hibernate don’t support them. But that doesn’t mean that you can’t use them. You just have to implement the mapping yourself.

With Hibernate’s UserType interface, that is not as complicated as it sounds. Let’s have a quick look at the most important steps. If you want to dive deeper into this topic, please take a look at my post How to use PostgreSQL’s JSONB data type with Hibernate .

Hibernate’s UserType interface allows you to define the mapping between any Java type and any supported JDBC data type. That requires the implementation of several methods. The 2 most important ones are nullSafeGet and nullSafeSet . They implement the mapping from the JDBC to the Java type and vice versa.

The following code snippet shows the implementation of these methods for a UserType which maps a Java class to a JSONB database column.

After you implemented your own UserType , you need to register it. You can do that with a @TypeDef annotation which you should add to the package-info.java file.

If the Hibernate dialect doesn’t already support the column type, as it’s the case for the JSONB type, you also need to extend the dialect. As you can see in the following code snippet, this requires only a few lines of code.

Get this post as a free cheat sheet!

I prepared a free cheat sheet with the most important information and code snippets of this post. As always, you can download it for free from the Thoughts on Java Library.

Join Now!

Already a member?Login here.


As you’ve seen, there are several ways to use common database features with JPA and Hibernate. These allow you to do a lot more than to just map simple database tables to entities.

Autoincremented database columns and sequences are used in most projects to generate unique primary key values. But also the mapping of read-only views and the execution of database functions and stored procedure provide an easy way to use the database more efficiently and improve the performance of your application.