Chapter 17. Customizing SQL

In this chapter

• Falling back to JDBC
• Mapping SQL query results
• Customizing CRUD operations
• Calling stored procedures

In this chapter, we cover customizing and embedding SQL in a Hibernate application. SQL was created in the 1970s, but ANSI didn’t standardized it until 1986. Although each update of the SQL standard has seen new (and many controversial) features, every DBMS product that supports SQL does so in its own unique way. The burden of portability is again on the database application developers. This is where Hibernate helps: its built-in query languages produce SQL that depends on the configured database dialect. Dialects also help produce all other automatically generated SQL (for example, when Hibernate has to retrieve a collection on demand). With a simple dialect switch, you can run your application on a different DBMS. Hibernate generates all SQL statements for you, for all create, read, update, and delete (CRUD) operations.

Sometimes, though, you need more control than Hibernate and the Java Persistence API provide: you need to work at a lower level of abstraction. With Hibernate, you can write your own SQL statements:

• Fall back to the JDBC API, and work directly with the Connection, PreparedStatement, and ResultSet interfaces. Hibernate provides the Connection, so you don’t have to maintain a separate connection pool, and your SQL statements execute within the same (current) transaction.
• Write plain SQL SELECT statements, and either embed them within your Java code or externalize them (in XML files or annotations) as named queries. You execute these SQL queries with the Java Persistence API, just like a regular JPQL query. Hibernate can then transform the query result according to your mapping. This also works with stored procedure calls.
• Replace SQL statements generated by Hibernate with your own hand-written SQL. For example, when Hibernate loads an entity instance with em.find() or loads a collection on-demand, your own SQL query can perform the load. You can also write your own Data Manipulation Language (DML) statements, such as UPDATE, INSERT, and DELETE. You might even call a stored procedure to perform a CRUD operation. You can replace all SQL statements automatically generated by Hibernate with custom statements.

We start with JDBC fallback usage and then discuss Hibernate’s automatic result-mapping capabilities. Then, we show you how to override queries and DML statements in Hibernate. Last, we discuss integration with stored database procedures.

17.1. Falling back to JDBC

UserTransaction tx = TM.getUserTransaction();
tx.begin();
EntityManager em = JPA.createEntityManager();
<enter/>
Session session = em.unwrap(Session.class);
session.doWork(new QueryItemWork(ITEM_ID));
<enter/>
tx.commit();
em.close();

17.2. Mapping SQL query results

When you execute an SQL SELECT query with the JDBC API or execute a stored procedure that returns a ResultSet, you iterate through each row of this result set and retrieve the data you need. This is a labor-intensive task, and you end up duplicating the same lines of code repeatedly.

Hibernate offers an alternative: execute the native SQL query or stored procedure call with the Hibernate/Java Persistence API and, instead of a ResultSet, get a List of instances of your choice. You can map the ResultSet to any class you like, and Hibernate will perform the transformation for you.

Note

In this section, we only talk about native SELECT SQL queries. You can also write UPDATE and INSERT statements with the same API, which we cover in section 20.1.

17.2.1. Projection with SQL queries

The following query returns a List of Object[], each array representing a tuple (row) of the SQL projection:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/NativeQueries.java

Query query = em.createNativeQuery(
    "select NAME, AUCTIONEND from {h-schema}ITEM"
);
List<Object[]> result = query.getResultList();
<enter/>
for (Object[] tuple : result) {
    assertTrue(tuple[0] instanceof String);
    assertTrue(tuple[1] instanceof Date);
}

Path: /examples/src/test/java/org/jpwh/test/querying/sql/HibernateSQLQueries.java

org.hibernate.SQLQuery query = session.createSQLQuery(
    "select NAME, AUCTIONEND from {h-schema}ITEM"
);
List<Object[]> result = query.list();
<enter/>
for (Object[] tuple : result) {
    assertTrue(tuple[0] instanceof String);
    assertTrue(tuple[1] instanceof Date);
}

The em.createNativeQuery() and session.createSQLQuery() methods accept a plain SQL query string.

The query retrieves the NAME and AUCTIONEND column values of the ITEM table, and Hibernate automatically maps them to String and java.util.Date values. Hibernate reads the java.sql.ResultSetMetaData to determine the type of each projection element, and it knows that a VARCHAR column type maps to a String and a TIMESTAMP to a java.util.Date (as explained in section 5.3).

Hibernate’s SQL query engine supports several handy placeholders, like {h-schema} in the previous examples. Hibernate replaces this placeholder with the default schema of your persistence unit (the hibernate.default_schema configuration property). The other supported placeholders are {h-catalog} for the default SQL catalog and {h-domain}, which combines the catalog and schema values.

The biggest advantage of executing an SQL statement within Hibernate is automatic marshaling of the result set into instances of your domain model (entity) classes.

17.2.2. Mapping to an entity class

This SQL query returns a List of Item entity instances:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/NativeQueries.java

Query query = em.createNativeQuery(
    "select * from ITEM",
    Item.class
);
<enter/>
List<Item> result = query.getResultList();

Path: /examples/src/test/java/org/jpwh/test/querying/sql/HibernateSQLQueries.java

org.hibernate.SQLQuery query = session.createSQLQuery(
    "select * from ITEM"
);
query.addEntity(Item.class);
<enter/>
List<Item> result = query.list();

The returned Item instances are in persistent state, managed by the current persistence context. The result is therefore the same as with the JPQL query select i from Item i.

For this transformation, Hibernate reads the result set of the SQL query and tries to discover the column names and types as defined in your entity mapping metadata. If the column AUCTIONEND is returned, and it’s mapped to the Item#auctionEnd property, Hibernate knows how to populate that property and returns fully loaded entity instances.

Note that Hibernate expects the query to return all columns required to create an instance of Item, including all properties, embedded components, and foreign key columns. If Hibernate can’t find a mapped column (by name) in the result set, an exception is thrown. You may have to use aliases in SQL to return the same column names as defined in your entity mapping metadata.

The interfaces javax.persistence.Query and org.hibernate.SQLQuery both support parameter binding. The following query returns only a single Item entity instance:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/NativeQueries.java

Path: /examples/src/test/java/org/jpwh/test/querying/sql/HibernateSQLQueries.java

For historical reasons, Hibernate counts positional parameters starting at zero, whereas JPA starts at one. Named parameter binding is usually more robust:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/NativeQueries.java

Query query = em.createNativeQuery(
    "select * from ITEM where ID = :id",
    Item.class
);
query.setParameter("id", ITEM_ID);
<enter/>
List<Item> result = query.getResultList();

Path: /examples/src/test/java/org/jpwh/test/querying/sql/HibernateSQLQueries.java

org.hibernate.SQLQuery query = session.createSQLQuery(
    "select * from ITEM where ID = :id"
);
query.addEntity(Item.class);
query.setParameter("id", ITEM_ID);
<enter/>
List<Item> result = query.list();

Although available in Hibernate for both APIs, the JPA specification doesn’t consider named parameter binding for native queries portable. Therefore, some JPA providers may not support named parameters for native queries.

If your SQL query doesn’t return the columns as mapped in your Java entity class, and you can’t rewrite the query with aliases to rename columns in the result, you must create a result-set mapping.

17.2.3. Customizing result mappings

The following query returns a List of managed Item entity instances. All columns of the ITEM table are included in the SQL projection, as required for the construction of an Item instance. But the query renames the NAME column to EXTENDED_NAME with an alias in the projection:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/NativeQueries.java

Query query = em.createNativeQuery(
    "select " +
        "i.ID, " +
        "'Auction: ' || i.NAME as EXTENDED_NAME, " +
        "i.CREATEDON, " +
        "i.AUCTIONEND, " +
        "i.AUCTIONTYPE, " +
        "i.APPROVED, " +
        "i.BUYNOWPRICE, " +
        "i.SELLER_ID " +
        "from ITEM i",
    "ItemResult"
);
<enter/>
List<Item> result = query.getResultList();

Hibernate can no longer automatically match the result set fields to Item properties: the NAME column is missing from the result set. You therefore specify a “result mapping” with the second parameter ofcreateNativeQuery(), here ItemResult.

Mapping result fields to entity properties

You can declare this mapping with annotations, for example, on the Item class:

Path: /model/src/main/java/org/jpwh/model/querying/Item.java

@SqlResultSetMappings({
    @SqlResultSetMapping(
        name = "ItemResult",
        entities =
        @EntityResult(
            entityClass = Item.class,
            fields = {
                @FieldResult(name = "id", column = "ID"),
                @FieldResult(name = "name", column = "EXTENDED_NAME"),
                @FieldResult(name = "createdOn", column = "CREATEDON"),
                @FieldResult(name = "auctionEnd", column = "AUCTIONEND"),
                @FieldResult(name = "auctionType", column = "AUCTIONTYPE"),
                @FieldResult(name = "approved", column = "APPROVED"),
                @FieldResult(name = "buyNowPrice", column = "BUYNOWPRICE"),
                @FieldResult(name = "seller", column = "SELLER_ID")
            }
        )
    )
})
@Entity
public class Item {
    // ...
}

You map all fields of the result set to properties of the entity class. Even if only one field/column doesn’t match the already mapped column name (here EXTENDED_NAME), all other columns and properties have to be mapped as well.

SQL result mappings in annotations are difficult to read and as usual with JPA annotations, they only work when declared on a class, not in a package-info.java metadata file. We prefer externalizing such mappings into XML files. The following provides the same mapping:

Path: /model/src/main/resources/querying/NativeQueries.xml

<sql-result-set-mapping name="ExternalizedItemResult">
    <entity-result entity-class="org.jpwh.model.querying.Item">
        <field-result name="id" column="ID"/>
        <field-result name="name" column="EXTENDED_NAME"/>
        <field-result name="createdOn" column="CREATEDON"/>
        <field-result name="auctionEnd" column="AUCTIONEND"/>
        <field-result name="auctionType" column="AUCTIONTYPE"/>
        <field-result name="approved" column="APPROVED"/>
        <field-result name="buyNowPrice" column="BUYNOWPRICE"/>
        <field-result name="seller" column="SELLER_ID"/>
    </entity-result>
</sql-result-set-mapping>

If both result-set mappings have the same name, the mapping declared in XML overrides the one defined with annotations.

You can also externalize the actual SQL query with @NamedNativeQuery or <named-native-query>, as shown in section 14.4. In all following examples, we keep the SQL statement embedded in the Java code, because this will make it easier for you to understand what the code does. But most of the time, you’ll see result-set mappings in the more succinct XML syntax.

Let’s first repeat the last query with the proprietary Hibernate API:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/HibernateSQLQueries.java

org.hibernate.SQLQuery query = session.createSQLQuery(
    "select " +
        "i.ID as {i.id}, " +
        "'Auction: ' || i.NAME as {i.name}, " +
        "i.CREATEDON as {i.createdOn}, " +
        "i.AUCTIONEND as {i.auctionEnd}, " +
        "i.AUCTIONTYPE as {i.auctionType}, " +
        "i.APPROVED as {i.approved}, " +
        "i.BUYNOWPRICE as {i.buyNowPrice}, " +
        "i.SELLER_ID as {i.seller} " +
        "from ITEM i"
);
query.addEntity("i", Item.class);
<enter/>
List<Item> result = query.list();

With the Hibernate API, you can perform the result-set mapping directly within the query through alias placeholders. When calling addEntity(), you provide an alias, here i. In the SQL string, you then let Hibernate generate the actual aliases in the projection with placeholders such as {i.name} and {i.auctionEnd}, which refer to properties of the Item entity. No additional result-set mapping declaration is necessary; Hibernate generates the aliases in the SQL string and knows how to read the property values from the query ResultSet. This is much more convenient than the JPA result-set mapping option.

Or, if you can’t or don’t want to modify the SQL statement, use addRoot() and addProperty() on the org.hibernate.SQLQuery to perform the mapping:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/HibernateSQLQueries.java

org.hibernate.SQLQuery query = session.createSQLQuery(
    "select " +
        "i.ID, " +
        "'Auction: ' || i.NAME as EXTENDED_NAME, " +
        "i.CREATEDON, " +
        "i.AUCTIONEND, " +
        "i.AUCTIONTYPE, " +
        "i.APPROVED, " +
        "i.BUYNOWPRICE, " +
        "i.SELLER_ID " +
        "from ITEM i"
);
query.addRoot("i", Item.class)
    .addProperty("id", "ID")
    .addProperty("name", "EXTENDED_NAME")
    .addProperty("createdOn", "CREATEDON")
    .addProperty("auctionEnd", "AUCTIONEND")
    .addProperty("auctionType", "AUCTIONTYPE")
    .addProperty("approved", "APPROVED")
    .addProperty("buyNowPrice", "BUYNOWPRICE")
    .addProperty("seller", "SELLER_ID");
<enter/>
List<Item> result = query.list();

As with the standard API, you can also rely on an existing result-set mapping by name with the Hibernate API:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/HibernateSQLQueries.java

org.hibernate.SQLQuery query = session.createSQLQuery(
    "select " +
        "i.ID, " +
        "'Auction: ' || i.NAME as EXTENDED_NAME, " +
        "i.CREATEDON, " +
        "i.AUCTIONEND, " +
        "i.AUCTIONTYPE, " +
        "i.APPROVED, " +
        "i.BUYNOWPRICE, " +
        "i.SELLER_ID " +
        "from ITEM i"
);
query.setResultSetMapping("ItemResult");
<enter/>
List<Item> result = query.list();

Another case where you need custom result-set mapping are duplicate column names in the result set of your SQL query.

Mapping duplicate fields

The following query loads the seller of each Item in a single statement, joining ITEM and USERS tables:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/NativeQueries.java

Query query = em.createNativeQuery(
    "select " +
        "i.ID as ITEM_ID, " +
        "i.NAME, " +
        "i.CREATEDON, " +
        "i.AUCTIONEND, " +
        "i.AUCTIONTYPE, " +
        "i.APPROVED, " +
        "i.BUYNOWPRICE, " +
        "i.SELLER_ID, " +
        "u.ID as USER_ID, " +
        "u.USERNAME, " +
        "u.FIRSTNAME, " +
        "u.LASTNAME, " +
        "u.ACTIVATED, " +
        "u.STREET, " +
        "u.ZIPCODE, " +
        "u.CITY " +
        "from ITEM i join USERS u on u.ID = i.SELLER_ID",
    "ItemSellerResult"
);
List<Object[]> result = query.getResultList();
<enter/>
for (Object[] tuple : result) {
    assertTrue(tuple[0] instanceof Item);
    assertTrue(tuple[1] instanceof User);
    Item item = (Item) tuple[0];
    assertTrue(Persistence.getPersistenceUtil().isLoaded(item, "seller"));
    assertEquals(item.getSeller(), tuple[1]);
}

This is effectively an eager fetch of the association Item#seller. Hibernate knows that each row contains the fields for an Item and a User entity instance, linked by the SELLER_ID.

The duplicate columns in the result set would be i.ID and u.ID, which both have the same name. You’ve renamed them with an alias to ITEM_ID and USER_ID, so you have to map how the result set is to be transformed:

Path: /model/src/main/resources/querying/NativeQueries.xml

<sql-result-set-mapping name="ItemSellerResult">
    <entity-result entity-class="org.jpwh.model.querying.Item">
        <field-result name="id" column="ITEM_ID"/>
        <field-result name="name" column="NAME"/>
        <field-result name="createdOn" column="CREATEDON"/>
        <field-result name="auctionEnd" column="AUCTIONEND"/>
        <field-result name="auctionType" column="AUCTIONTYPE"/>
        <field-result name="approved" column="APPROVED"/>
        <field-result name="buyNowPrice" column="BUYNOWPRICE"/>
        <field-result name="seller" column="SELLER_ID"/>
    </entity-result>
    <entity-result entity-class="org.jpwh.model.querying.User">
        <field-result name="id" column="USER_ID"/>
        <field-result name="name" column="NAME"/>
        <field-result name="username" column="USERNAME"/>
        <field-result name="firstname" column="FIRSTNAME"/>
        <field-result name="lastname" column="LASTNAME"/>
        <field-result name="activated" column="ACTIVATED"/>
        <field-result name="homeAddress.street" column="STREET"/>
        <field-result name="homeAddress.zipcode" column="ZIPCODE"/>
        <field-result name="homeAddress.city" column="CITY"/>
    </entity-result>
</sql-result-set-mapping>

As before, you have to map all fields of each entity result to column names, even if only two have different names as the original entity mapping.

This query is much easier to map with the Hibernate API:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/HibernateSQLQueries.java

org.hibernate.SQLQuery query = session.createSQLQuery(
    "select " +
        "{i.*}, {u.*} " +
        "from ITEM i join USERS u on u.ID = i.SELLER_ID"
);
query.addEntity("i", Item.class);
query.addEntity("u", User.class);
<enter/>
List<Object[]> result = query.list();

Hibernate will add auto-generated unique aliases to the SQL statement for the {i.*} and {u.*} placeholders, so the query won’t return duplicate column names.

You may have noticed the dot syntax in the previous JPA result mapping for the homeAddress embedded component in a User. Let’s look at this special case again.

Mapping fields to component properties

The User has a homeAddress, an embedded instance of the Address class. The following query loads all User instances:

<enter/>

Path: /examples/src/test/java/org/jpwh/test/querying/sql/NativeQueries.java

Query query = em.createNativeQuery(
    "select " +
        "u.ID, " +
        "u.USERNAME, " +
        "u.FIRSTNAME, " +
        "u.LASTNAME, " +
        "u.ACTIVATED, " +
        "u.STREET as USER_STREET, " +
        "u.ZIPCODE as USER_ZIPCODE, " +
        "u.CITY as USER_CITY " +
        "from USERS u",
    "UserResult"
);
<enter/>
List<User> result = query.getResultList();

In this query, you rename the STREET, ZIPCODE, and CITY columns, so you have to map them manually to the embedded component properties:

Path: /model/src/main/resources/querying/NativeQueries.xml

<sql-result-set-mapping name="UserResult">
    <entity-result entity-class="org.jpwh.model.querying.User">
        <field-result name="id" column="ID"/>
        <field-result name="name" column="NAME"/>
        <field-result name="username" column="USERNAME"/>
        <field-result name="firstname" column="FIRSTNAME"/>
        <field-result name="lastname" column="LASTNAME"/>
        <field-result name="activated" column="ACTIVATED"/>
        <field-result name="homeAddress.street" column="USER_STREET"/>
        <field-result name="homeAddress.zipcode" column="USER_ZIPCODE"/>
        <field-result name="homeAddress.city" column="USER_CITY"/>
    </entity-result>
</sql-result-set-mapping>

We’ve shown this dot syntax several times before when discussing embedded components: you reference the street property of homeAddress with homeAddress.street. For nested embedded components, you can write homeAddress.city.name if City isn’t just a string but another embeddable class.

Hibernate’s SQL query API also supports the dot syntax in alias placeholders for component properties. Here are the same query and result-set mapping:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/HibernateSQLQueries.java

org.hibernate.SQLQuery query = session.createSQLQuery(
    "select " +
        "u.ID as {u.id}, " +
        "u.USERNAME as {u.username}, " +
        "u.FIRSTNAME as {u.firstname}, " +
        "u.LASTNAME as {u.lastname}, " +
        "u.ACTIVATED as {u.activated}, " +
        "u.STREET as {u.homeAddress.street}, " +
        "u.ZIPCODE as {u.homeAddress.zipcode}, " +
        "u.CITY as {u.homeAddress.city} " +
        "from USERS u"
);
query.addEntity("u", User.class);
<enter/>
List<User> result = query.list();

Eager fetching of collections with an SQL query is only available with the Hibernate API.

Eager-fetching collections

org.hibernate.SQLQuery query = session.createSQLQuery(
    "select " +
        "{i.*}, " +
        "{b.*} " +
        "from ITEM i left outer join BID b on i.ID = b.ITEM_ID"
);
query.addEntity("i", Item.class);
query.addFetch("b", "i", "bids");
<enter/>
List<Object[]> result = query.list();

So far, you’ve seen SQL queries returning managed entity instances. You can also return transient instances of any class with the right constructor.

Mapping the result to a constructor

We covered dynamic instantiation in a query with JPQL and criteria examples in section 15.3.2. JPA supports the same feature with native queries. The following query returns a List of ItemSummary instances:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/NativeQueries.java

Query query = em.createNativeQuery(
    "select ID, NAME, AUCTIONEND from ITEM",
    "ItemSummaryResult"
);
List<ItemSummary> result = query.getResultList();

The ItemSummaryResult mapping transforms each column of the query result into a parameter for the ItemSummary constructor:

Path: /model/src/main/resources/querying/NativeQueries.xml

<sql-result-set-mapping name="ItemSummaryResult">
    <constructor-result target-class="org.jpwh.model.querying.ItemSummary">
        <column name="ID" class="java.lang.Long"/>
        <column name="NAME"/>
        <column name="AUCTIONEND"/>
    </constructor-result>
</sql-result-set-mapping>

The returned column types have to match the constructor parameter types; Hibernate would default to BigInteger for the ID column, so you map it to a Long with the class attribute.

The Hibernate API gives you a choice. You can either use an existing result mapping for the query by name, or apply a result transformer, as you saw for JPQL queries in section 16.1:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/HibernateSQLQueries.java

1. You can use an existing result mapping.
2. Alternatively, you can map the fields returned by the query as scalar values. Without a result transformer, you’d get an Object[] for each result row.
3. Apply a built-in result transformer to turn the Object[] into instances of Item-Summary.

As explained in section 15.3.2, Hibernate can use any class constructor with such a mapping. Instead of ItemSummary, you can construct Item instances. They will be in either transient or detached state, depending on whether you return and map an identifier value in your query.

You can also mix different kinds of result mappings or return scalar values directly.

Scalar and mixed result mappings

The next query returns a List of Object[], where the first element is an Item entity instance and the second element is a scalar reflecting the number of bids for that item:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/NativeQueries.java

Query query = em.createNativeQuery(
    "select " +
        "i.*, " +
        "count(b.ID) as NUM_OF_BIDS " +
        "from ITEM i left join BID b on b.ITEM_ID = i.ID " +
        "group by i.ID, i.NAME, i.CREATEDON, i.AUCTIONEND, " +
        "i.AUCTIONTYPE, i.APPROVED, i.BUYNOWPRICE, i.SELLER_ID",
    "ItemBidResult"
);
<enter/>
List<Object[]> result = query.getResultList();
<enter/>
for (Object[] tuple : result) {
    assertTrue(tuple[0] instanceof Item);
    assertTrue(tuple[1] instanceof Number);
}

The result mapping is simple, because the projection contains no duplicate columns:

Path: /model/src/main/resources/querying/NativeQueries.xml

<sql-result-set-mapping name="ItemBidResult">
    <entity-result entity-class="org.jpwh.model.querying.Item"/>
    <column-result name="NUM_OF_BIDS"/>
</sql-result-set-mapping>

With the Hibernate API, the additional scalar result is mapped with addScalar():

Path: /examples/src/test/java/org/jpwh/test/querying/sql/HibernateSQLQueries.java

org.hibernate.SQLQuery query = session.createSQLQuery(
    "select " +
        "i.*, " +
        "count(b.ID) as NUM_OF_BIDS " +
        "from ITEM i left join BID b on b.ITEM_ID = i.ID " +
        "group by i.ID, i.NAME, i.CREATEDON, i.AUCTIONEND, " +
        "i.AUCTIONTYPE, i.APPROVED, i.BUYNOWPRICE, i.SELLER_ID"
);
query.addEntity(Item.class);
query.addScalar("NUM_OF_BIDS");
<enter/>
List<Object[]> result = query.list();
<enter/>
for (Object[] tuple : result) {
    assertTrue(tuple[0] instanceof Item);
    assertTrue(tuple[1] instanceof Number);
}

Finally, in a single result mapping, you can combine entity, constructor, and scalar column results. The following query returns a persistent and managed User entity instance that is the seller of the also-returned ItemSummary. You also get the number of bids for each item:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/NativeQueries.java

The result mapping of this query is as follows:

Path: /model/src/main/resources/querying/NativeQueries.xml

<sql-result-set-mapping name="SellerItemSummaryResult">
<enter/>
    <entity-result entity-class="org.jpwh.model.querying.User"/>
<enter/>
    <constructor-result target-class="org.jpwh.model.querying.ItemSummary">
        <column name="ID" class="java.lang.Long"/>
        <column name="ITEM_NAME"/>
        <column name="ITEM_AUCTIONEND"/>
    </constructor-result>
<enter/>
    <column-result name="NUM_OF_BIDS"/>
</sql-result-set-mapping>

The JPA specification guarantees that in mixed result mappings, the produced Object[] of each tuple will contain elements in the following order: first all <entity-result> data, then <constructor-result> data, and then <column-result> data. The JPA XML schema enforces this order in the result mapping declaration; but even if you map elements in a different order with annotations (which can’t enforce the order in which you declare mappings), the query result will have the standard order. Be aware that, as shown in the code example, Hibernate at the time of writing didn’t return the right order.

Note that with the Hibernate query API, you can use the same result mapping by name, as we’ve shown previously. If you need more programmatic control over the result marshalling, you must write your own result transformer, because there is no built-in transformer to map such a mixed query result automatically.

Last, you’ll see a more complex example with an SQL query declared in an XML file.

17.2.4. Externalizing native queries

We now show you how to declare an SQL query in an XML file, instead of embedding the string in the code. In real applications with long SQL statements, reading concatenated strings in Java code is unpleasant, so you’ll almost always prefer having SQL statements in an XML file. This also simplifies ad hoc testing, because you can copy and paste SQL statements between an XML file and your SQL database console.

You’ve probably noticed that all the SQL examples in the previous sections were trivial. In fact, none of the examples required a query written in SQL—we could have used JPQL in each case. To make the next example more interesting, we write a query that can’t be expressed in JPQL, only in SQL.

The tree of categories

Consider the Category class and its self-referencing many-to-one association, as shown in figure 17.1.

Figure 17.1. The Category has a self-referencing many-to-one association.

This is the mapping of the association—a regular @ManyToOne of the PARENT_ID foreign key column:

Path: /model/src/main/java/org/jpwh/model/querying/Category.java

Categories form a tree. The root of the tree is a Category node without a parent. The database data for the example tree is in figure 17.2.

Figure 17.2. Database table and sample data for a tree of categories

You can also represent this data as a tree diagram, as shown in figure 17.3. Alternatively, you can use a sequence of paths and the level of each node:

/One, 0
/One/Two, 1
/One/Three, 1
/One/Two/Four, 2

Figure 17.3. A tree of categories

Now, consider how your application loads Category instances. You may want to find the root Category of the tree. This is a trivial JPQL query:

select c from Category c where c.parent is null

You can easily query for the categories in a particular level of the tree, such as all children of the root:

select c from Category c, Category r where r.parent is null and c.parent = r

This query will only return direct children of the root: here, categories Two and Three.

How can you load the entire tree (or a subtree) in one query? This isn’t possible with JPQL, because it would require recursion: “Load categories at this level, then all the children on the next level, then all the children of those, and so on.” In SQL, you can write such a query, using a common table expression (CTE), a feature also known as subquery factoring.

Loading the tree

The following SQL query loads the entire tree of Category instances, declared in a JPA XML file:

Path: /model/src/main/resources/querying/NativeQueries.xml

<named-native-query name="findAllCategories"
                    result-set-mapping="CategoryResult">
    <query>
        with CATEGORY_LINK(ID, NAME, PARENT_ID, PATH, LEVEL) as (
            select
                ID,
                NAME,
                PARENT_ID,
                '/' || NAME,
                0
            from CATEGORY where PARENT_ID is null
            union all
            select
                c.ID,
                c.NAME,
                c.PARENT_ID,
                cl.PATH || '/' || c.NAME,
                cl.LEVEL + 1
            from CATEGORY_LINK cl
            join CATEGORY c on cl.ID = c.PARENT_ID
        )
        select
            ID,
            NAME as CAT_NAME,
            PARENT_ID,
            PATH,
            LEVEL
        from CATEGORY_LINK
        order by ID
    </query>
</named-native-query>

It’s a complex query, and we won’t spend too much time on it here. To understand it, read the last SELECT, querying the CATEGORY_LINK view. Each row in that view represents a node in the tree. The view is declared here in the WITH() AS operation. The CATEGORY_LINK view is a combined (union) result of two other SELECTs. You add additional information to the view during recursion, such as the PATH and the LEVEL of each node.

Let’s map the result of this query:

Path: /model/src/main/resources/querying/NativeQueries.xml

<sql-result-set-mapping name="CategoryResult">
    <entity-result entity-class="org.jpwh.model.querying.Category">
        <field-result name="id" column="ID"/>
        <field-result name="name" column="CAT_NAME"/>
        <field-result name="parent" column="PARENT_ID"/>
    </entity-result>
    <column-result name="PATH"/>
    <column-result name="LEVEL" class="java.lang.Integer"/>
</sql-result-set-mapping>

The XML maps the ID, CAT_NAME, and PARENT_ID fields to properties of the Category entity. The mapping returns the PATH and LEVEL as additional scalar values.

To execute the named SQL query and access the result, write the following:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/NativeQueries.java

Query query = em.createNamedQuery("findAllCategories");
List<Object[]> result = query.getResultList();
<enter/>
for (Object[] tuple : result) {
    Category category = (Category) tuple[0];
    String path = (String) tuple[1];
    Integer level = (Integer) tuple[2];
    // ...
}

Each tuple contains a managed, persistent Category instance; its path in the tree as a string (such as /One, /One/Two, and so on); and the tree level of the node.

Alternatively, you can declare and map an SQL query in a Hibernate XML metadata file:

Path: /model/src/main/resources/querying/SQLQueries.hbm.xml

<sql-query name="findAllCategoriesHibernate">
    <return class="org.jpwh.model.querying.Category">
        <return-property name="id" column="ID"/>
        <return-property name="name" column="CAT_NAME"/>
        <return-property name="parent" column="PARENT_ID"/>
    </return>
    <return-scalar column="PATH"/>
    <return-scalar column="LEVEL" type="integer"/>
    ...

</sql-query>

We left out the SQL query in this snippet; it’s the same as the SQL statement shown earlier in the JPA example.

As mentioned in section 14.4, with regard to the execution in Java code, it doesn’t matter which syntax you declare your named queries in: XML file or annotations. Even the language doesn’t matter—it can be JPQL or SQL. Both Hibernate and JPA query interfaces have methods to “get a named query” and execute it independently from how you defined it.

This concludes our discussion of SQL result mapping for queries. The next subject is customization of SQL statements for CRUD operations, replacing the SQL automatically generated by Hibernate for creating, reading, updating, and deleting data in the database.

17.3. Customizing CRUD operations

17.3.1. Enabling custom loaders

Hibernate has two requirements when you override an SQL query to load an entity instance:

• Write a named query that retrieves the entity instance. We show an example in SQL, but as always, you can also write named queries in JPQL. For an SQL query, you may need a custom result mapping, as shown earlier in this chapter.
• Activate the query on an entity class with @org.hibernate.annotations .Loader. This enables the query as the replacement for the Hibernate-generated query.

Let’s override how Hibernate loads an instance of the User entity, as shown in the following listing.

Listing 17.2. Loading a User instance with a custom query

Path: /model/src/main/java/org/jpwh/model/customsql/User.java

1. Annotations declare the query to load an instance of User; you can also declare it in an XML file (JPA or Hibernate metadata). You can call this named query directly in your data-access code when needed.
2. The query must have exactly one parameter placeholder, which Hibernate sets as the identifier value of the instance to load. Here it’s a positional parameter, but a named parameter would also work.
3. For this trivial query, you don’t need a custom result-set mapping. The User class maps all fields returned by the query. Hibernate can automatically transform the result.
4. Setting the loader for an entity class to a named query enables the query for all operations that retrieve an instance of User from the database. There’s no indication of the query language or where you declared it; this is independent of the loader declaration.

In a named loader query for an entity, you have to SELECT (that is, perform a projection for) the following properties of the entity class:

• The value of the identifier property or properties, if a composite primary key is used.
• All scalar properties of basic type.
• All properties of embedded components.
• An entity identifier value for each @JoinColumn of each mapped entity association such as @ManyToOne owned by the loaded entity class.
• All scalar properties, embedded component properties, and association join references that are inside a @SecondaryTable annotation.
• If you enable lazy loading for some properties, through interception and bytecode instrumentation, you don’t need to load the lazy properties (see section 12.1.3).

Hibernate always calls the enabled loader query when a User has to be retrieved from the database by identifier. When you call em.find(User.class, USER_ID), your custom query will execute. When you call someItem.getSeller().getUsername(), and the Item#seller proxy has to be initialized, your custom query will load the data.

You may also want to customize how Hibernate creates, updates, and deletes an instance of User in the database.

17.3.2. Customizing creation, updates, and deletion

Hibernate usually generates CRUD SQL statements at startup. It caches the SQL statements internally for future use, thus avoiding any runtime costs of SQL generation for the most common operations. You’ve seen how you can override the R in CRUD, so now, let’s do the same for CUD. For each entity, you can define custom CUD SQL statements with the Hibernate annotations @SQLInsert, @SQLUpdate, and @SQLDelete, respectively.

Listing 17.3. Custom DML for the User entity

Path: /model/src/main/java/org/jpwh/model/customsql/User.java

@org.hibernate.annotations.SQLInsert(
    sql = "insert into USERS " +
          "(ACTIVATED, USERNAME, ID) values (?, ?, ?)"
)
@org.hibernate.annotations.SQLUpdate(
    sql = "update USERS set " +
          "ACTIVATED = ?, " +
          "USERNAME = ? " +
          "where ID = ?"
)
@org.hibernate.annotations.SQLDelete(
    sql = "delete from USERS where ID = ?"
)
@Entity
@Table(name = "USERS")
public class User {
    // ...
}

You need to be careful with the binding of arguments for SQL statements and parameter placeholders (the ? in the statements). For CUD customization, Hibernate only supports positional parameters.

What is the right order for the parameters? There’s an internal order to how Hibernate binds arguments to SQL parameters for CUD statements. The easiest way to figure out the right SQL statement and parameter order is to let Hibernate generate an example for you. Without any custom SQL statements, enable DEBUG logging for the org.hibernate.persister.entity category, and search the Hibernate startup output for lines like the following:

Static SQL for entity: org.jpwh.model.customsql.User
 Insert 0: insert into USERS (activated, username, id) values (?, ?, ?)

 Update 0: update USERS set activated=?, username=? where id=?
 Delete 0: delete from USERS where id=?

These automatically generated SQL statements show the right parameter order, and Hibernate always binds the values in that order. Copy the SQL statements you want to customize into annotations, and make the necessary changes.

A special case is properties of an entity class mapped to another table with @-SecondaryTable. The CUD customization statements we’ve shown so far only refer to the columns of the main entity table. Hibernate still executes automatically generated SQL statements for insertion, updates, and deletion of rows in the secondary table(s). You can customize this SQL by adding the @org.hibernate.annotations.Table annotation to your entity class and setting its sqlInsert, sqlUpdate, and sqlDelete attributes.

If you prefer to have your CUD SQL statements in XML, your only choice is to map the entire entity in a Hibernate XML metadata file. The elements in this proprietary mapping format for custom CUD statements are <sql-insert>, <sql-update>, and <sql-delete>. Fortunately, CUD statements are usually much more trivial than queries, so annotations are fine in most applications.

You’ve now added custom SQL statements for CRUD operations of an entity instance. Next, we show how to override SQL statements for loading and modifying a collection.

17.3.3. Customizing collection operations

Let’s override the SQL statement Hibernate uses when loading the Item#images collection. This is a collection of embeddable components mapped with @Element-Collection; the procedure is the same for collections of basic types or many-valued entity associations (@OneToMany or @ManyToMany).

Listing 17.4. Loading a collection with a custom query

Path: /model/src/main/java/org/jpwh/model/customsql/Item.java

@Entity
public class Item {
<enter/>
    @ElementCollection
    @org.hibernate.annotations.Loader(namedQuery = "loadImagesForItem")
    protected Set<Image> images = new HashSet<Image>();
<enter/>
    // ...
}

As before, you declare that a named query will load the collection. This time, however, you must declare and map the result of the query in a Hibernate XML metadata file, which is the only facility that supports mapping of query results to collection properties:

Path: /model/src/main/resources/customsql/ItemQueries.hbm.xml

<sql-query name="loadImagesForItem">
    <load-collection alias="img" role="Item.images"/>
    select
        ITEM_ID, FILENAME, WIDTH, HEIGHT
    from
        ITEM_IMAGES
    where
        ITEM_ID = ?

</sql-query>

The query has to have one (positional or named) parameter. Hibernate sets its value to the entity identifier that owns the collection. Whenever Hibernate need to initialize the Item#images collection, Hibernate now executes your custom SQL query.

Sometimes you don’t have to override the entire SQL statement for loading a collection: for example, if you only want to add a restriction to the generated SQL statement. Let’s say the Category entity has a collection of Item references, and the Item has an activation flag. If the property Item#active is false, you don’t want to load it when accessing the Category#items collection. You can append this restriction to the SQL statement with the Hibernate @Where annotation on the collection mapping, as a plain SQL fragment:

Path: /model/src/main/java/org/jpwh/model/customsql/Category.java

@Entity
public class Category {
<enter/>
    @OneToMany(mappedBy = "category")
    @org.hibernate.annotations.Where(clause = "ACTIVE = 'true'")
    protected Set<Item> items = new HashSet<Item>();
<enter/>
    // ...
}

You can also write custom insertion and deletion SQL statements for collection elements, as shown next.

Listing 17.5. Custom CUD statements for collection modification

Path: /model/src/main/java/org/jpwh/model/customsql/Item.java

@Entity
public class Item {
<enter/>
    @ElementCollection
    @org.hibernate.annotations.SQLInsert(
        sql = "insert into ITEM_IMAGES " +
              "(ITEM_ID, FILENAME, HEIGHT, WIDTH) " +
              "values (?, ?, ?, ?)"
    )
    @org.hibernate.annotations.SQLDelete(
        sql = "delete from ITEM_IMAGES " +
              "where ITEM_ID = ? and FILENAME = ? and HEIGHT = ? and WIDTH = ?"
    )
    @org.hibernate.annotations.SQLDeleteAll(
        sql = "delete from ITEM_IMAGES where ITEM_ID = ?"
    )
    protected Set<Image> images = new HashSet<Image>();
<enter/>
    // ...
}

To find the right parameter order, enable DEBUG logging for the org.hibernate.persister.collection category and search the Hibernate startup output for the generated SQL statements for this collection, before you add your custom SQL annotations.

A new annotation here is @SQLDeleteAll, which only applies to collections of basic or embeddable types. Hibernate executes this SQL statement when the entire collection has to be removed from the database: for example, when you call someItem.getImages().clear() or someItem.setImages(new HashSet()).

No @SQLUpdate statement is necessary for this collection, because Hibernate doesn’t update rows for this collection of embeddable type. When an Image property value changes, Hibernate detects this as a new Image in the collection (recall that images are compared “by value” of all their properties). Hibernate will DELETE the old row and INSERT a new row to persist this change.

Instead of lazily loading collection elements, you can eagerly fetch them when the owning entity is loaded. You can also override this query with a custom SQL statement.

17.3.4. Eager fetching in custom loaders

Let’s consider the Item#bids collection and how it’s loaded. Hibernate enables lazy loading by default because you mapped with @OneToMany, so it’s only when you begin iterating through the collection’s elements that Hibernate will execute a query and retrieve the data. Hence, when loading the Item entity, you don’t have to load any collection data.

If instead you want to fetch the Item#bids collection eagerly when the Item is loaded, first enable a custom loader query on the Item class:

Path: /model/src/main/java/org/jpwh/model/customsql/Item.java

@org.hibernate.annotations.Loader(
    namedQuery = "findItemByIdFetchBids"
)
@Entity
public class Item {
<enter/>
    @OneToMany(mappedBy = "item")
    protected Set<Bid> bids = new HashSet<>();
<enter/>
    // ...
}

As in the previous section, you must declare this named query in a Hibernate XML metadata file; no annotations are available to fetch collections with named queries. Here is the SQL statement to load an Item and its bids collection in a single OUTER JOIN:

Path: /model/src/main/resources/customsql/ItemQueries.hbm.xml

<sql-query name="findItemByIdFetchBids">
    <return alias="i" class="Item"/>
    <return-join alias="b" property="i.bids"/>
    select
        {i.*}, {b.*}
    from
        ITEM i
    left outer join BID b
        on i.ID = b.ITEM_ID
    where
        i.ID = ?

</sql-query>

You saw this query and result mapping in Java code earlier in this chapter in the section “Eager-fetching collections.” Here, you apply an additional restriction to only one row of ITE, with the given primary key value.

You can also eagerly load single-valued entity associations such as a @ManyToOne with a custom SQL statement. Let’s say you want to eagerly load the bidder when a Bid entity is retrieved from the database. First, enable a named query as the entity loader:

Path: /model/src/main/java/org/jpwh/model/customsql/Bid.java

@org.hibernate.annotations.Loader(
    namedQuery = "findBidByIdFetchBidder"
)
@Entity
public class Bid {
<enter/>
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    protected User bidder;
<enter/>
    // ...
}

Unlike custom queries used to load collections, you can declare this named query with standard annotations (of course, you can also have it in an XML metadata file, in either the JPA or Hibernate syntax):

Path: /model/src/main/java/org/jpwh/model/customsql/Bid.java

@NamedNativeQueries({
    @NamedNativeQuery(
        name = "findBidByIdFetchBidder",
        query =
            "select " +
                "b.ID as BID_ID, b.AMOUNT, b.ITEM_ID, b.BIDDER_ID, " +
                "u.ID as USER_ID, u.USERNAME, u.ACTIVATED " +
                "from BID b join USERS u on b.BIDDER_ID = u.ID " +
                "where b.ID = ?",
        resultSetMapping = "BidBidderResult"
    )
})
@Entity
public class Bid {
    // ...
}

An INNER JOIN is appropriate for this SQL query, because a Bid always has a bidder and the BIDDER_ID foreign key column is never NULL. You rename duplicate ID columns in the query, and because you rename them to BID_ID and USER_ID, a custom result mapping is necessary:

Path: /model/src/main/java/org/jpwh/model/customsql/Bid.java

@SqlResultSetMappings({
    @SqlResultSetMapping(
        name = "BidBidderResult",
        entities = {
            @EntityResult(
                entityClass = Bid.class,
                fields = {
                    @FieldResult(name = "id", column = "BID_ID"),
                    @FieldResult(name = "amount", column = "AMOUNT"),
                    @FieldResult(name = "item", column = "ITEM_ID"),
                    @FieldResult(name = "bidder", column = "BIDDER_ID")
                }
            ),
            @EntityResult(
                entityClass = User.class,
                fields = {
                    @FieldResult(name = "id", column = "USER_ID"),
                    @FieldResult(name = "username", column = "USERNAME"),
                    @FieldResult(name = "activated", column = "ACTIVATED")
                }
            )
        }
    )
})
@Entity
public class Bid {
    // ...
}

Hibernate executes this custom SQL query and maps the result when loading an instance of the Bid class, either through em.find(Bid.class, BID_ID) or when it has to initialize a Bid proxy. Hibernate loads the Bid#bidder right away and overrides the FetchType.LAZY setting on the association.

You’ve now customized Hibernate operations with your own SQL statements. Let’s continue with stored procedures and explore the options for integrating them into your Hibernate application.

17.4. Calling stored procedures

Stored procedures are common in database application development. Moving code closer to the data and executing it inside the database has distinct advantages. You end up not duplicating functionality and logic in each program that accesses the data. A different point of view is that a lot of business logic shouldn’t be duplicated, so it can be applied all the time. This includes procedures that guarantee the integrity of the data: for example, constraints that are too complex to implement declaratively. You’ll usually also find triggers in a database that contain procedural integrity rules.

Stored procedures have advantages for all processing on large amounts of data, such as reporting and statistical analysis. You should always try to avoid moving large data sets on your network and between your database and application servers, so a stored procedure is the natural choice for mass data operations.

There are of course (legacy) systems that implement even the most basic CRUD operations with stored procedures. In a variation on this theme, some systems don’t allow any direct use of SQL INSERT, UPDATE, or DELETE, but only stored procedure calls; these systems also had (and sometimes still have) their place.

In some DBMSs, you can declare user-defined functions, in addition to, or instead of, stored procedures. The summary in table 17.1 shows some of the differences between procedures and functions.

Table 17.1. Comparing database procedures and functions

It’s difficult to generalize and compare procedures and functions beyond these obvious differences. This is one area where DBMS support differs widely; some DBMSs don’t support stored procedures or user-defined functions, whereas others roll both into one (for example, PostgreSQL has only user-defined functions). Programming languages for stored procedures are usually proprietary. Some databases even support stored procedures written in Java. Standardizing Java stored procedures was part of the SQLJ effort, which unfortunately hasn’t been successful.

In this section, we show you how to integrate Hibernate with MySQL stored procedures and PostgreSQL user-defined functions. First, we look at defining and calling stored procedures with the standardized Java Persistence API and the native Hibernate API. Then, we customize and replace Hibernate CRUD operations with procedure calls. It’s important that you read the previous sections before this one, because the integration of stored procedures relies on the same mapping options as other SQL customization in Hibernate.

As before in this chapter, the actual SQL stored procedures we cover in the examples are trivial so you can focus on the more important parts—how to call the procedures and use the API in your application.

When calling a stored procedure, you typically want to provide input and receive the output of the procedure. You can distinguish between procedures that

• Return a result set
• Return multiple result sets
• Update data and return the count of updated rows
• Accept input and/or output parameters
• Return a cursor, referencing a result in the database

Let’s start with the simplest case: a stored procedure that doesn’t have any parameters and only returns data in a result set.

17.4.1. Returning a result set

You can create the following procedure in MySQL. It returns a result set containing all rows of the ITEM table:

Path: /model/src/main/resources/querying/StoredProcedures.hbm.xml

create procedure FIND_ITEMS()
begin
    select * from ITEM;
end

Using an EntityManager, build a StoredProcedureQuery and execute it:

Path:/examples/src/test/java/org/jpwh/test/querying/sql/CallStoredProcedures.java

As you’ve previously seen in this chapter, Hibernate automatically maps the columns returned in the result set to properties of the Item class. The Item instances returned by this query will be managed and in persistent state. To customize the mapping of returned columns, provide the name of a result-set mapping instead of the Item.class parameter.

org.hibernate.procedure.ProcedureCall call =
    session.createStoredProcedureCall("FIND_ITEMS", Item.class);
<enter/>
org.hibernate.result.ResultSetOutput resultSetOutput =
    (org.hibernate.result.ResultSetOutput) call.getOutputs().getCurrent();
<enter/>
List<Item> result = resultSetOutput.getResultList();

17.4.2. Returning multiple results and update counts

The following MySQL procedure returns all rows of the ITEM table that weren’t approved and all rows that were already approved, and also sets the APPROVED flag for these rows:

Path: /model/src/main/resources/querying/StoredProcedures.hbm.xml

create procedure APPROVE_ITEMS()
begin
    select * from ITEM where APPROVED = 0;
    select * from ITEM where APPROVED = 1;
    update ITEM set APPROVED = 1 where APPROVED = 0;
end

In the application, you get two result sets and an update count. Accessing and processing the results of a procedure call is a bit more involved, but JPA is closely aligned to plain JDBC, so this kind of code should be familiar if you’ve worked with stored procedures:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/CallStoredProcedures.java

1. Execute the procedure call with execute(). This method returns true if the first result of the call is a result set and false if the first result is an update count.
2. Process all results of the call in a loop. Stop looping when no more results are available, which is always indicated by hasMoreResults() returning false and getUpdateCount() returning -1.
3. If the current result is a result set, read and process it. Hibernate maps the columns in each result set to managed instances of the Item class. Alternatively, provide a result-set mapping name applicable to all result sets returned by the call.
4. If the current result is an update count, getUpdateCount() returns a value greater than -1.
5. hasMoreResults() advances to the next result and indicates the type of that result.

17.4.3. Setting input and output parameters

The following MySQL procedure returns a row for the given identifier from the ITEM table, as well as the total number of items:

Path: /model/src/main/resources/querying/StoredProcedures.hbm.xml

create procedure FIND_ITEM_TOTAL(in PARAM_ITEM_ID bigint,
                                 out PARAM_TOTAL bigint)
begin
    select count(*) into PARAM_TOTAL from ITEM;
    select * from ITEM where ID = PARAM_ITEM_ID;
end

This next procedure returns a result set with the data of the ITEM row. Additionally, the output parameter PARAM_TOTAL is set. To call this procedure in JPA, you must first register all parameters:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/CallStoredProcedures.java

1. Register all parameters by position (starting at 1) and their type.
2. Bind values to the input parameters.
3. Retrieve the result set returned by the procedure.
4. After you’ve retrieved the result sets, you can access the output parameter values.

You can also register and use named parameters, but you can’t mix named and positional parameters in a particular call. Also, note that any parameter names you choose in your Java code don’t have to match the names of the parameters in your stored procedure declaration. Ultimately, you must register the parameters in the same order as declared in the signature of the procedure.

create procedure UPDATE_ITEM(in PARAM_ITEM_ID bigint,
                             in PARAM_NAME varchar(255))
begin
    update ITEM set NAME = PARAM_NAME where ID = PARAM_ITEM_ID;
end

org.hibernate.procedure.ProcedureCall call =
    session.createStoredProcedureCall("UPDATE_ITEM");
<enter/>
call.registerParameter(1, Long.class, ParameterMode.IN)
    .bindValue(ITEM_ID);
<enter/>
call.registerParameter(2, String.class, ParameterMode.IN)
    .bindValue("New Item Name");
<enter/>
org.hibernate.result.UpdateCountOutput updateCountOutput =
    (org.hibernate.result.UpdateCountOutput) call.getOutputs().getCurrent();
<enter/>
assertEquals(updateCountOutput.getUpdateCount(), 1);

Next, instead of returning a result set, we see procedures that return a cursor reference.

17.4.4. Returning a cursor

MySQL doesn’t support returning cursors from stored procedures. The following example only works on PostgreSQL. This stored procedure (or, because this is the same in PostgreSQL, this user-defined function) returns a cursor to all rows of the ITEM table:

Path: /model/src/main/resources/querying/StoredProcedures.hbm.xml

create function FIND_ITEMS() returns refcursor as $$
    declare someCursor refcursor;
    begin
        open someCursor for select * from ITEM;
        return someCursor;
    end;
$$ language plpgsql;

JPA always registers cursor results as parameters, using the special ParameterMode .REF_CURSOR:

Path: /examples/src/test/java/org/jpwh/test/querying/sql/CallStoredProcedures.java

StoredProcedureQuery query = em.createStoredProcedureQuery(
    "FIND_ITEMS",
    Item.class
);
<enter/>
query.registerStoredProcedureParameter(
    1,
    void.class,
    ParameterMode.REF_CURSOR
);
<enter/>
List<Item> result = query.getResultList();
for (Item item : result) {
    // ...
}

The type of the parameter is void, because its only purpose is to prepare the call internally for reading data with the cursor. When you call getResultList(), Hibernate knows how to get the desired output.

org.hibernate.procedure.ProcedureCall call =
    session.createStoredProcedureCall("FIND_ITEMS", Item.class);
<enter/>
call.registerParameter(1, void.class, ParameterMode.REF_CURSOR);
<enter/>
org.hibernate.result.ResultSetOutput resultSetOutput =
    (org.hibernate.result.ResultSetOutput) call.getOutputs().getCurrent();
<enter/>
List<Item> result = resultSetOutput.getResultList();
for (Item item : result) {
    // ...
}

Supporting database cursors across DBMS dialects is difficult, and Hibernate has some limitations. For example, with PostgreSQL, a cursor parameter must always be the first registered parameter, and (because it’s a function) only one cursor should be returned by the database. With the PostgreSQL dialect, Hibernate doesn’t support named parameter binding if a cursor return is involved: you must use positional parameter binding. Consult your Hibernate SQL dialect for more information; the relevant methods are Dialect#getResultSet(CallableStatement) and so on.

This completes our discussion of APIs for direct stored procedure calls. Next, you can also use stored procedures to override the statements generated by Hibernate when it loads or stores data.

17.5. Using stored procedures for CRUD

17.5.1. Custom loader with a procedure

First, write a named query that calls a stored procedure—for example, in annotations on the User class:

Path: /model/src/main/java/org/jpwh/model/customsql/procedures/User.java

@NamedNativeQueries({
    @NamedNativeQuery(
        name = "findUserById",
        query = "{call FIND_USER_BY_ID(?)}",
        resultClass = User.class
    )
})
@org.hibernate.annotations.Loader(
    namedQuery = "findUserById"
)
@Entity
@Table(name = "USERS")
public class User {
    // ...
}

Compare this with the previous customization in section 17.3.1: the declaration of the loader is still the same, and it relies on a defined named query in any supported language. You’ve only changed the named query, which you can also move into an XML metadata file to further isolate and separate this concern.

JPA doesn’t standardize what goes into a @NamedNativeQuery, you’re free to write any SQL statement. With the JDBC escape syntax in curly braces, you’re saying, “Let the JDBC driver figure out what to do here.” If your JDBC driver and DBMS understand stored procedures, you can invoke a procedure with {call PROCEDURE}. Hibernate expects that the procedure will return a result set, and the first row in the result set is expected to have the columns necessary to construct a User instance. We listed the required columns and properties earlier, in section 17.3.1. Also remember that you can always apply a result-set mapping if the column (names) returned by your procedure aren’t quite right or you can’t change the procedure code.

The stored procedure must have a signature matching the call with a single argument. Hibernate sets this identifier argument when loading an instance of User. Here’s an example of a stored procedure on MySQL with such a signature:

Path: /model/src/main/resources/customsql/CRUDProcedures.hbm.xml

create procedure FIND_USER_BY_ID(in PARAM_USER_ID bigint)
begin
    select * from USERS where ID = PARAM_USER_ID;
end

Next, we map creating, updating, and deleting a User to a stored procedure.

17.5.2. Procedures for CUD

You use the Hibernate annotations @SQLInsert, @SQLUpdate, and @SQLDelete to customize how Hibernate creates, updates, and deletes an entity instance in the database. Instead of a custom SQL statement, you can call a stored procedure to perform the operation:

Path: /model/src/main/java/org/jpwh/model/customsql/procedures/User.java

@org.hibernate.annotations.SQLInsert(
    sql = "{call INSERT_USER(?, ?, ?)}",
    callable = true
)
@org.hibernate.annotations.SQLUpdate(
    sql = "{call UPDATE_USER(?, ?, ?)}",
    callable = true,
    check = ResultCheckStyle.NONE
)
@org.hibernate.annotations.SQLDelete(
    sql = "{call DELETE_USER(?)}",
    callable = true
)
@Entity
@Table(name = "USERS")
public class User {
    // ...
}

You have to indicate that Hibernate must execute an operation with a JDBC Callable-Statement instead of a PreparedStatement; hence set the callable=true option.

As explained in section 17.3.2, argument binding for procedure calls is only possible with positional parameters, and you must declare them in the order Hibernate expects. Your stored procedures must have a matching signature. Here are some procedure examples for MySQL that insert, update, and delete a row in the USERS table:

Path: /model/src/main/resources/customsql/CRUDProcedures.hbm.xml

create procedure INSERT_USER(in PARAM_ACTIVATED bit,
                             in PARAM_USERNAME varchar(255),
                             in PARAM_ID bigint)
begin
    insert into USERS (ACTIVATED, USERNAME, ID)
        values (PARAM_ACTIVATED, PARAM_USERNAME, PARAM_ID);
end

Path: /model/src/main/resources/customsql/CRUDProcedures.hbm.xml

create procedure UPDATE_USER(in PARAM_ACTIVATED bit,
                             in PARAM_USERNAME varchar(255),
                             in PARAM_ID bigint)
begin
    update USERS set
        ACTIVATED = PARAM_ACTIVATED,
        USERNAME = PARAM_USERNAME
    where ID = PARAM_ID;
end

Path: /model/src/main/resources/customsql/CRUDProcedures.hbm.xml

create procedure DELETE_USER(in PARAM_ID bigint)
begin
    delete from USERS where ID = PARAM_ID;
end

When a stored procedure inserts, updates, or deletes an instance of User, Hibernate has to know whether the call was successful. Usually, for dynamically generated SQL, Hibernate looks at the number of updated rows returned from an operation. If you enabled versioning (see section 11.2.2), and the operation didn’t or couldn’t update any rows, an optimistic locking failure occurs. If you write your own SQL, you can customize this behavior as well. It’s up to the stored procedure to perform the version check against the database state when updating or deleting rows. With the check option in your annotations, you can let Hibernate know how the procedure will implement this requirement.

The default is ResultCheckStyle.NONE, and the following settings are available:

• NONE—The procedure will throw an exception if the operation fails. Hibernate doesn’t perform any explicit checks but instead relies on the procedure code to do the right thing. If you enable versioning, your procedure must compare/increment versions and throw an exception if it detects a version mismatch.
• COUNT—The procedure will perform any required version increments and checks and return the number of updated rows to Hibernate as an update count. Hibernate uses CallableStatement#getUpdateCount() to access the result.
• PARAM—The procedure will perform any required version increments and checks and return the number of updated rows to Hibernate in its first output parameter. For this check style, you need to add an additional question mark to your call and, in your stored procedure, return the row count of your DML operation in this (first) output parameter. Hibernate automatically registers the parameter and reads its value when the call completes.

Finally, remember that stored procedures and functions sometimes can’t be mapped in Hibernate. In such cases, you have to fall back to plain JDBC. Sometimes you can wrap a legacy stored procedure with another stored procedure that has the parameter interface Hibernate expects.

17.6. Summary

• You saw how to fall back to the JDBC API when necessary. Even for custom SQL queries, Hibernate can do the heavy lifting and transform the ResultSet into instances of your domain model classes, with flexible mapping options, including customizing result mapping. You can also externalize native queries for a cleaner setup.
• We discussed how to override and provide your own SQL statements for regular create, read, update, and delete (CRUD) operations, as well as for collection operations.
• You can enable custom loaders and use eager fetching in such loaders.
• You learned how to call database stored procedures directly and integrate them into Hibernate. You explored processing a single result set as well as multiple result sets and update counts. You set stored procedure (input and output) parameters and learned how to return a database cursor. You also saw how to use stored procedures for CRUD.