15.2. Using native SQL queries

HQL, JPA QL, or criteria queries should be flexible enough to execute almost any query you like. They refer to the mapped object schema; hence, if your mapping works as expected, Hibernate's queries should give you the power you need to retrieve data any way you like. There are a few exceptions. If you want to include a native SQL hint to instruct the database management systems query optimizer, for example, you need to write the SQL yourself. HQL, JPA QL, and criteria queries don't have keywords for this.

On the other hand, instead of falling back to a manual SQL query, you can always try to extend the built-in query mechanisms and include support for your special operation. This is more difficult to do with HQL and JPA QL, because you have to modify the grammar of these string-based languages. It's easy to extend the Criteria API and add new methods or new Criterion classes. Look at the Hibernate source code in the org.hibernate.criterion package; it's well designed and documented.

When you can't extend the built-in query facilities or prevent nonportable manually written SQL, you should first consider using Hibernate's native SQL query options, which we now present. Keep in mind that you can always fall back to a plain JDBC Connection and prepare any SQL statement yourself. Hibernate's SQL options allow you to embed SQL statements in a Hibernate API and to benefit from extra services that make your life easier.

Most important, Hibernate can handle the resultset of your SQL query.

15.2.1. Automatic resultset handling

The biggest advantage of executing an SQL statement with the Hibernate API is automatic marshaling of the tabular resultset into business objects. The following SQL query returns a collection of Category objects:

List result = session.createSQLQuery("select * from CATEGORY")
                      .addEntity(Category.class)
                      .list();

Hibernate reads the resultset of the SQL query and tries to discover the column names and types as defined in your mapping metadata. If the column CATEGORY_NAME is returned, and it's mapped to the name property of the Category class, Hibernate knows how to populate that property and finally returns fully loaded business objects.

The * in the SQL query projects all selected columns in the resultset. The automatic discovery mechanism therefore works only for trivial queries; more complex queries need an explicit projection. The next query returns a collection of Item objects:

session.createSQLQuery("select {i.*} from ITEM i" +
                       " join USERS u on i.SELLER_ID = u.USER_ID" +
                       " where u.USERNAME = :uname")
        .addEntity("i", Item.class)
        .setParameter("uname", "johndoe");

The SQL SELECT clause includes a placeholder which names the table alias i and projects all columns of this table into the result. Any other table alias, such as the joined USERS table, which is only relevant for the restriction, isn't included in the resultset. You now tell Hibernate with addEntity() that the placeholder for alias i refers to all columns that are needed to populate the Item entity class. The column names and types are again automatically guessed by Hibernate during query execution and result marshaling.

You can even eagerly fetch associated objects and collections in a native SQL query:

session.createSQLQuery("select {i.*}, {u.*} from ITEM i" +
                       " join USERS u on i.SELLER_ID = u.USER_ID" +
                       " where u.USERNAME = :uname")
        .addEntity("i", Item.class)
        .addJoin("u", "i.seller")
        .setParameter("uname", "johndoe");

This SQL query projects two sets of columns from two table aliases, and you use two placeholders. The i placeholder again refers to the columns that populate the Item entity objects returned by this query. The addJoin() method tells Hibernate that the u alias refers to columns that can be used to immediately populate the associated seller of each Item.

Automatic marshaling of resultsets into business objects isn't the only benefit of the native SQL query feature in Hibernate. You can even use it if all you want to retrieve is a simple scalar value.

15.2.2. Retrieving scalar values

A scalar value may be any Hibernate value type. Most common are strings, numbers, or timestamps. The following SQL query returns item data:

List result = session.createSQLQuery("select * from ITEM").list();

The result of this query is a List of Object[]s, effectively a table. Each field in each array is of scalar type—that is, a string, a number, or a timestamp. Except for the wrapping in an Object[], the result is exactly the same as that of a similar plain JDBC query. This is obviously not too useful, but one benefit of the Hibernate API is that it throws unchecked exceptions so you don't have to wrap the query in try/catch block as you have to if you call the JDBC API.

If you aren't projecting everything with *, you need to tell Hibernate what scalar values you want to return from your result:

session.createSQLQuery("select u.FIRSTNAME as fname from USERS u")
         .addScalar("fname");

The addScalar() method tells Hibernate that your fname SQL alias should be returned as a scalar value and that the type should be automatically guessed. The query returns a collection of strings. This automatic type discovery works fine in most cases, but you may want to specify the type explicitly sometimes—for example, when you want to convert a value with a UserType:

Properties params = new Properties();
params.put("enumClassname", "auction.model.Rating");

session.createSQLQuery(
        "select c.RATING as rating from COMMENTS c" +
        " where c.FROM_USER_ID = :uid"
    )
    .addScalar("rating",
               Hibernate.custom(StringEnumUserType.class, params) )
    .setParameter("uid", new Long(123));

First, look at the SQL query. It selects the RATING column of the COMMENTS table and restricts the result to comments made by a particular user. Let's assume that this field in the database contains string values, such as EXCELLENT, OK, or BAD. Hence, the result of the SQL query is string values.

You'd naturally map this not as a simple string in Java but using an enumeration and probably a custom Hibernate UserType. We did this in chapter 5, section 5.3.7, "Mapping enumerations," and created a StringEnumUserType that can translate from strings in the SQL database to instances of any enumeration in Java. It must be parameterized with the enumClassname you want it to convert values to—auction.model.Rating in this example. By setting the prepared custom type with the addScalar() method on the query, you enable it as a converter that handles the result, and you get back a collection of Rating objects instead of simple strings.

Finally, you can mix scalar results and entity objects in the same native SQL query:

session.createSQLQuery(
        "select {i.*}, u.FIRSTNAME as fname from ITEM i" +
        " join USERS u on i.SELLER_ID = u.USER_ID" +
        " where u.USERNAME = :uname"
    )
    .addEntity("i", Item.class)
    .addScalar("fname")
    .setParameter("uname", "johndoe");

The result of this query is again a collection of Object[]s. Each array has two fields: an Item instance and a string.

You probably agree that native SQL queries are even harder to read than HQL or JPA QL statements and that it seems much more attractive to isolate and externalize them into mapping metadata. You did this in chapter 8, section 8.2.2, "Integrating stored procedures and functions," for stored procedure queries. We won't repeat this here, because the only difference between stored procedure queries and plain SQL queries is the syntax of the call or statement—the marshaling and resultset mapping options are the same.

Java Persistence standardizes JPA QL and also allows the fallback to native SQL.

15.2.3. Native SQL in Java Persistence

Java Persistence supports native SQL queries with the createNativeQuery() method on an EntityManager. A native SQL query may return entity instances, scalar values, or a mix of both. However, unlike Hibernate, the API in Java Persistence utilizes mapping metadata to define the resultset handling. Let's walk through some examples.

A simple SQL query doesn't need an explicit resultset mapping:

em.createNativeQuery("select * from CATEGORY", Category.class);

The resultset is automatically marshaled into a collection of Category instances. Note that the persistence engine expects all columns required to create an instance of Category to be returned by the query, including all property, component, and foreign key columns—otherwise an exception is thrown. Columns are searched in the resultset by name. You may have to use aliases in SQL to return the same column names as defined in your entity mapping metadata.

If your native SQL query returns multiple entity types or scalar types, you need to apply an explicit resultset mapping. For example, a query that returns a collection of Object[]s, where in each array index 0 is an Item instance and index 1 is a User instance, can be written as follows:

em.createNativeQuery("select " +
    "i.ITEM_ID, i.ITEM_PRICE, u.USERNAME, u.EMAIL " +
    "from ITEM i join USERS u where i.SELLER_ID = u.USER_ID",
    "ItemSellerResult");

The last argument, ItemSellerResult, is the name of a result mapping you define in metadata (at the class or global JPA XML level):

@SqlResultSetMappings({
    @SqlResultSetMapping(
        name = "ItemSellerResult",
        entities = {
            @EntityResult(entityClass = auction.model.Item.class),
            @EntityResult(entityClass = auction.model.User.class)
        }
    )
})

This resultset mapping likely doesn't work for the query we've shown—remember that for automatic mapping, all columns that are required to instantiate Item and User objects must be returned in the SQL query. It's unlikely that the four columns you return represent the only persistent properties. For the sake of the example, let's assume that they are and that your actual problem is the names of the columns in the resultset, which don't match the names of the mapped columns. First, add aliases to the SQL statement:

em.createNativeQuery("select " +
    "i.ITEM_ID as ITEM_ID, i.ITEM_PRICE as ITEM_PRICE, " +
    "u.USERNAME as USER_NAME, u.EMAIL as USER_EMAIL " +
    "from ITEM i join USERS u on i.SELLER_ID = u.USER_ID",
    "ItemSellerResult");

Next, use @FieldResult in the resultset mapping to map aliases to fields of the entity instances:

@SqlResultSetMapping(
name = "ItemSellerResult",
entities = {
    @EntityResult(
        entityClass = auction.model.Item.class,
        fields = {
          @FieldResult(name = "id", column = "ITEM_ID"),
          @FieldResult(name = "initialPrice", column = "ITEM_PRICE")
        }),
    @EntityResult(

        entityClass = auction.model.User.class,
        fields = {
          @FieldResult(name = "username", column = "USER_NAME"),
          @FieldResult(name = "email", column = "USER_EMAIL")
        })
})

You can also return scalar typed results. The following query returns auction item identifiers and the number of bids for each item:

em.createNativeQuery("select " +
    "i.ITEM_ID as ITEM_ID, count(b.*) as NUM_OF_BIDS " +
    "from ITEM i join BIDS b on i.ITEM_ID = b.ITEM_ID " +
    "group by ITEM_ID",
    "ItemBidResult");

The resultset mapping doesn't contain entity result mappings this time, only columns:

@SqlResultSetMapping(
name = "ItemBidResult",
columns = {
    @ColumnResult(name = "ITEM_ID"),
    @ColumnResult(name = "NUM_OF_BIDS")
})

The result of this query is a collection of Object[]s, with two fields, both of some numeric type (most likely long). If you want to mix entities and scalar types as a query result, combine the entities and columns attributes in a @SqlResultSetMapping.

Finally, note that the JPA specification doesn't require that named parameter binding is supported for native SQL queries. Hibernate supports this.

Next, we discuss another more exotic but convenient Hibernate feature (Java Persistence doesn't have an equivalent): collection filters.