8 Ways to use the features of your database with Hibernate

---

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!

---

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

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 can map 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.

@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;
	
	...
	
}

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 a JPQL 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.

Query q = em.createQuery(“SELECT a, size(a.books) FROM Author a GROUP BY a.id”);

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.

14:45:45,952 DEBUG [org.hibernate.SQL] - 
    select
        author0_.id as col_0_0_,
        count(books1_.authorId) as col_1_0_,
        author0_.id as id1_0_,
        author0_.firstName as firstNam2_0_,
        author0_.lastName as lastName3_0_,
        author0_.version as version4_0_ 
    from
        Author author0_ cross 
    join
        BookAuthor books1_ 
    where
        author0_.id=books1_.authorId 
    group by
        author0_.id

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.

Since JPA 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.

TypedQuery<Book> q = em.createQuery("SELECT b FROM Book b "
			    + "WHERE :double2 > function('calculate', b.price, :double1)"
			    , Book.class);

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.

07:15:43,305 DEBUG [org.hibernate.SQL] - 
    select
        book0_.id as id1_0_,
        book0_.price as price2_0_,
        book0_.publishingDate as publishi3_0_,
        book0_.title as title4_0_,
        book0_.version as version5_0_ 
    from
        Book book0_ 
    where
        ?>calculate(book0_.price, ?)

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 a native 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.

@NamedStoredProcedureQuery(
	name = "calculate", 
	procedureName = "calculate", 
	parameters = { 
		@StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "x"), 
		@StoredProcedureParameter(mode = ParameterMode.IN, type = Double.class, name = "y"), 
		@StoredProcedureParameter(mode = ParameterMode.OUT, type = Double.class, name = "sum")
	}
)

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.

StoredProcedureQuery query = this.em.createNamedStoredProcedureQuery("calculate");
query.setParameter("x", 1.23d);
query.setParameter("y", 4.56d);
query.execute();
Double sum = (Double) query.getOutputParameterValue("sum");

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.

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.

@Entity
public class Author {

	@Column
	@Generated(GenerationTime.ALWAYS)
	private LocalDateTime lastUpdate;

	…

}

10:33:49,612 DEBUG [org.hibernate.SQL] – insert into Author (firstName, lastName, version, id) values (?, ?, ?, ?)
10:33:49,620 DEBUG [org.hibernate.SQL] – select author_.lastUpdate as lastUpda4_0_ from Author author_ where author_.id=?
10:33:49,644 DEBUG [org.hibernate.SQL] – update Author set firstName=?, lastName=?, version=? where id=? and version=?
10:33:49,646 DEBUG [org.hibernate.SQL] – select author_.lastUpdate as lastUpda4_0_ from Author author_ where author_.id=?

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.

@Entity
public class Author {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	@Column(name = “id”, updatable = false, nullable = false)
	private Long id;
	
	@Column
	private LocalDate dateOfBirth;

	@Formula(value = “date_part(‘year’, age(dateOfBirth))”)
	private int age;
	…
	public int getAge() {
		return age;
	}
}

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.

05:35:15,762 DEBUG [org.hibernate.SQL] – select author0_.id as id1_0_, author0_.dateOfBirth as dateOfBi2_0_, author0_.firstName as firstNam3_0_, author0_.lastName as lastName4_0_, author0_.version as version5_0_, date_part(‘year’, age(author0_.dateOfBirth)) as formula0_ from Author author0_ where author0_.id=1

Sequences

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.

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

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.

@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;

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.

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

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.

@Override
public Object nullSafeGet(final ResultSet rs, final String[] names, final SessionImplementor session,
                          final Object owner) throws HibernateException, SQLException {
    final String cellContent = rs.getString(names[0]);
    if (cellContent == null) {
        return null;
    }
    try {
        final ObjectMapper mapper = new ObjectMapper();
        return mapper.readValue(cellContent.getBytes("UTF-8"), returnedClass());
    } catch (final Exception ex) {
        throw new RuntimeException("Failed to convert String to Invoice: " + ex.getMessage(), ex);
    }
}

@Override
public void nullSafeSet(final PreparedStatement ps, final Object value, final int idx,
                        final SessionImplementor session) throws HibernateException, SQLException {
    if (value == null) {
        ps.setNull(idx, Types.OTHER);
        return;
    }
    try {
        final ObjectMapper mapper = new ObjectMapper();
        final StringWriter w = new StringWriter();
        mapper.writeValue(w, value);
        w.flush();
        ps.setObject(idx, w.toString(), Types.OTHER);
    } catch (final Exception ex) {
        throw new RuntimeException("Failed to convert Invoice to String: " + ex.getMessage(), ex);
    }
}

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.

@org.hibernate.annotations.TypeDef(name = "MyJsonType", typeClass = MyJsonType.class)

package org.thoughts.on.java.model;

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.

public class MyPostgreSQL94Dialect extends PostgreSQL94Dialect {

	public MyPostgreSQL94Dialect() {
		this.registerColumnType(Types.JAVA_OBJECT, "jsonb");
	}
}

Summary

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.