Stored Procedures with Hibernate

1. Overview

Stored Procedures are sets of compiled SQL statements residing in the database. They are used to encapsulate and share logic with other programs, and benefit from database-specific features like index hints or specific keywords.

This article demonstrates how to use Hibernate to call a stored procedure in a MySQL database.

2. Stored Procedures in MySQL

Before we discuss how to call a stored procedure from Hibernate, we need to create it.

For this quick MySQL example, we’ll create a stored procedure to get all the records from a foo table.

To create a stored procedure, we make use of the CREATE PROCEDURE statement:

DELIMITER //
    CREATE PROCEDURE GetAllFoos()
        LANGUAGE SQL
        DETERMINISTIC
        SQL SECURITY DEFINER
        BEGIN
            SELECT * FROM foo;
        END //
DELIMITER;

Before the BEGIN statement, we can define optional statements. You can drill down into the details of these statements by following the official MySQL documentation link.

We can use the CALL statement to make sure that our procedure behave in the desired way:

CALL GetAllFoos();

Now that we have our stored procedure up and running let’s jump straight to how to call it from Hibernate.

3. Call a Stored Procedure With Hibernate

Starting from Hibernate 3, we have the possibility to use raw SQL statement including stored procedures to query a database.

In this section, we are going to walk through a seemingly basic example that will illustrate how to call the GetAllFoos() procedure using Hibernate.

3.1. Configuration

Before we start writing code that can run, we need to have configured Hibernate in our project.

And of course for all of that – the Maven dependencies, MySQL configuration, Hibernate configuration and SessionFactory instantiation – you can check out the Hibernate article.

3.2. Call a Stored Procedure Using the CreateNativeSQL Method

Hibernate allows to express queries in native SQL format directly. Therefore, we can straightforwardly create a native SQL query, and use the CALL statement to call the getAllFoos() stored procedure:

Query<Foo> query = session.createNativeQuery("CALL GetAllFoos()").addEntity(Foo.class);
List<Foo> allFoos = query.list();

The above query returns a list where each element is a Foo object.

We use the addEntity() method to get entity objects from the native SQL query, otherwise, a ClassCastException will be thrown whenever a stored procedure returns a non-raw value.

3.3. Call a Stored Procedure Using @NamedNativeQueries

Another way to call a stored procedure is to use the @NamedNativeQueries annotation.

@NamedNativeQueries is used to specify an array of native SQL named queries scoped to the persistence unit:

@NamedNativeQueries({ 
  @NamedNativeQuery(
    name = "callGetAllFoos", 
    query = "CALL GetAllFoos()", 
    resultClass = Foo.class) 
})
@Entity
public class Foo implements Serializable {
    // Model definition
}

Each named query has obviously a name attribute, the actual SQL query, and the resultClass which refers to the Foo mapped entity.

Query<Foo> query = session.getNamedQuery("callGetAllFoos", Foo.class);
List<Foo> allFoos = query.list();

The resultClass attribute plays the same role as the addEntity() method in our previous example.

Both of these approaches can be used interchangeably, as there are no real differences between the two when it comes to performance or productivity.

3.4. Call a Stored Procedure Using @NamedStoredProcedureQuery

If you are using JPA 2.1 and the Hibernate implementation of the EntityManagerFactory and EntityManager.

The @NamedStoredProcedureQuery annotation can be used to declare a stored procedure:

@NamedStoredProcedureQuery(
  name="GetAllFoos",
  procedureName="GetAllFoos",
  resultClasses = { Foo.class }
)
@Entity
public class Foo implements Serializable {
    // Model Definition 
}

To call our named stored procedure query, we need to have instantiated an EntityManager, and then call the createNamedStoredProcedureQuery() method to create the procedure:

StoredProcedureQuery spQuery = 
  entityManager.createNamedStoredProcedureQuery("getAllFoos");

We can directly get the list of Foo entities by calling the execute() method on StoredProcedureQuery object.

4. Stored Procedures With Parameters

Almost all our stored procedures will require parameters. In this section, we are going to show how to call a stored procedure with parameters from Hibernate.

Let’s create a getFoosByName() stored procedure in MySQL.

This procedure returns a list of Foo objects where the name attribute matches the fooName parameter:

DELIMITER //
    CREATE PROCEDURE GetFoosByName(IN fooName VARCHAR(255))
        LANGUAGE SQL
        DETERMINISTIC
        SQL SECURITY DEFINER
        BEGIN
            SELECT * FROM foo WHERE name = fooName;
        END //
DELIMITER;

To call the GetFoosByName() procedure we will use named parameters:

Query query = session.createSQLQuery("CALL GetFoosByName(:fooName)")
  .addEntity(Foo.class)
  .setParameter("fooName","New Foo");

Similarly, the named parameter :fooName can be used with the @NamedNativeQuery annotation:

@NamedNativeQuery(
  name = "callGetFoosByName", 
  query = "CALL GetFoosByName(:fooName)", 
  resultClass = Foo.class
)

The named query would be called as follows:

Query query = session.getNamedQuery("callGetFoosByName")
  .setParameter("fooName","New Foo");

When using the @NamedStoredProcedureQuery annotation, we can specify parameters using the @StoredProcedureParameter annotation:

@NamedStoredProcedureQuery(
  name="GetFoosByName",
  procedureName="GetFoosByName",
  resultClasses = { Foo.class },
  parameters={
    @StoredProcedureParameter(name="fooName", type=String.class, mode=ParameterMode.IN)
  }
)

We can make use of the setParameter() method to call our stored procedure with the fooName parameter:

StoredProcedureQuery spQuery = entityManager.createNamedStoredProcedureQuery("GetFoosByName")
  .setParameter("fooName", "NewFooName");

5. Conclusion

This article demonstrated how to use Hibernate to call a stored procedure in a MySQL database using different approaches.

It is worth mentioning that not all RDBMS support stored procedures.