Native SQL Queries

You may also express queries in the native SQL dialect of your database. This is useful if you want to utilize database specific features such as window functions, Common Table Expressions (CTE) or the CONNECT BY option in Oracle. It also provides a clean migration path from a direct SQL/JDBC based application to Hibernate/JPA. Hibernate also allows you to specify handwritten SQL (including stored procedures) for all create, update, delete, and retrieve operations.

Creating a native query using JPA

Execution of native SQL queries is controlled via the NativeQuery interface, which is obtained by calling Session.createNativeQuery(). The following sections describe how to use this API for querying.

Scalar queries

The most basic SQL query is to get a list of scalars (column) values.

Example 1. JPA native query selecting all columns
List<Object[]> persons = entityManager.createNativeQuery(
    "SELECT * FROM Person" )
.getResultList();
Example 2. JPA native query with a custom column selection
List<Object[]> persons = entityManager.createNativeQuery(
    "SELECT id, name FROM Person" )
.getResultList();

for(Object[] person : persons) {
    Number id = (Number) person[0];
    String name = (String) person[1];
}
Example 3. Hibernate native query selecting all columns
List<Object[]> persons = session.createNativeQuery(
    "SELECT * FROM Person" )
.list();
Example 4. Hibernate native query with a custom column selection
List<Object[]> persons = session.createNativeQuery(
    "SELECT id, name FROM Person" )
.list();

for(Object[] person : persons) {
    Number id = (Number) person[0];
    String name = (String) person[1];
}

These will return a List of Object arrays ( Object[] ) with scalar values for each column in the PERSON table. Hibernate will use java.sql.ResultSetMetadata to deduce the actual order and types of the returned scalar values.

To avoid the overhead of using ResultSetMetadata, or simply to be more explicit in what is returned, one can use addScalar():

Example 5. Hibernate native query with explicit result set selection
List<Object[]> persons = session.createNativeQuery(
    "SELECT * FROM Person" )
.addScalar( "id", LongType.INSTANCE )
.addScalar( "name", StringType.INSTANCE )
.list();

for(Object[] person : persons) {
    Long id = (Long) person[0];
    String name = (String) person[1];
}

Although it still returns an Object arrays, this query will not use the ResultSetMetadata anymore since it explicitly gets the id and name columns as respectively a BigInteger and a String from the underlying ResultSet. This also means that only these two columns will be returned, even though the query is still using * and the ResultSet contains more than the three listed columns.

It is possible to leave out the type information for all or some of the scalars.

Example 6. Hibernate native query with result set selection that’s a partially explicit
List<Object[]> persons = session.createNativeQuery(
    "SELECT * FROM Person" )
.addScalar( "id", LongType.INSTANCE )
.addScalar( "name" )
.list();

for(Object[] person : persons) {
    Long id = (Long) person[0];
    String name = (String) person[1];
}

This is essentially the same query as before, but now ResultSetMetaData is used to determine the type of name, where as the type of id is explicitly specified.

How the java.sql.Types returned from ResultSetMetaData is mapped to Hibernate types is controlled by the Dialect. If a specific type is not mapped, or does not result in the expected type, it is possible to customize it via calls to registerHibernateType in the Dialect.

Entity queries

The above queries were all about returning scalar values, basically returning the raw values from the ResultSet.

Example 7. JPA native query selecting entities
List<Person> persons = entityManager.createNativeQuery(
    "SELECT * FROM Person", Person.class )
.getResultList();
Example 8. Hibernate native query selecting entities
List<Person> persons = session.createNativeQuery(
    "SELECT * FROM Person" )
.addEntity( Person.class )
.list();

Assuming that Person is mapped as a class with the columns id, name, nickName, address, createdOn and version, the following query will also return a List where each element is a Person entity.

Example 9. JPA native query selecting entities with explicit result set
List<Person> persons = entityManager.createNativeQuery(
    "SELECT id, name, nickName, address, createdOn, version " +
    "FROM Person", Person.class )
.getResultList();
Example 10. Hibernate native query selecting entities with explicit result set
List<Person> persons = session.createNativeQuery(
    "SELECT id, name, nickName, address, createdOn, version " +
    "FROM Person" )
.addEntity( Person.class )
.list();

Handling associations and collections

If the entity is mapped with a many-to-one or a child-side one-to-one to another entity, it is required to also return this when performing the native query, otherwise a database specific column not found error will occur.

Example 11. JPA native query selecting entities with many-to-one association
List<Phone> phones = entityManager.createNativeQuery(
    "SELECT id, phone_number, phone_type, person_id " +
    "FROM Phone", Phone.class )
.getResultList();
Example 12. Hibernate native query selecting entities with many-to-one association
List<Phone> phones = session.createNativeQuery(
    "SELECT id, phone_number, phone_type, person_id " +
    "FROM Phone" )
.addEntity( Phone.class )
.list();

This will allow the Phone#person to function properly since the many-to-one or one-to-one association is going to use a proxy that will be initialized when being navigated for the first time.

It is possible to eagerly join the Phone and the Person entities to avoid the possible extra roundtrip for initializing the many-to-one association.

Example 13. Hibernate native query selecting entities with joined many-to-one association
List<Object[]> tuples = session.createNativeQuery(
    "SELECT * " +
    "FROM Phone ph " +
    "JOIN Person pr ON ph.person_id = pr.id" )
.addEntity("phone", Phone.class )
.addJoin( "pr", "phone.person")
.list();

for(Object[] tuple : tuples) {
    Phone phone = (Phone) tuple[0];
    Person person = (Person) tuple[1];
    assertNotNull( person.getName() );
}
SELECT
    *
FROM
    Phone ph
JOIN
    Person pr
ON  ph.person_id = pr.id

As seen in the associated SQL query, Hibernate manages to construct the entity hierarchy without requiring any extra database roundtrip.

By default, when using the addJoin() method, the result set will contain both entities that are joined. To construct the entity hierarchy, you need to use a ROOT_ENTITY or DISTINCT_ROOT_ENTITY ResultTransformer.

Example 14. Hibernate native query selecting entities with joined many-to-one association and ResultTransformer
List<Person> persons = session.createNativeQuery(
    "SELECT * " +
    "FROM Phone ph " +
    "JOIN Person pr ON ph.person_id = pr.id" )
.addEntity("phone", Phone.class )
.addJoin( "pr", "phone.person")
.setResultTransformer( Criteria.ROOT_ENTITY )
.list();

for(Person person : persons) {
    person.getPhones();
}

Because of the ROOT_ENTITY ResultTransformer, this query will return the parent-side as root entities.

Notice that you added an alias name pr to be able to specify the target property path of the join. It is possible to do the same eager joining for collections (e.g. the Phone#calls one-to-many association).

Example 15. JPA native query selecting entities with joined one-to-many association
List<Phone> phones = entityManager.createNativeQuery(
    "SELECT * " +
    "FROM Phone ph " +
    "JOIN phone_call c ON c.phone_id = ph.id", Phone.class )
.getResultList();

for(Phone phone : phones) {
    List<Call> calls = phone.getCalls();
}
SELECT *
FROM phone ph
JOIN call c ON c.phone_id = ph.id
Example 16. Hibernate native query selecting entities with joined one-to-many association
List<Object[]> tuples = session.createNativeQuery(
    "SELECT * " +
    "FROM Phone ph " +
    "JOIN phone_call c ON c.phone_id = ph.id" )
.addEntity("phone", Phone.class )
.addJoin( "c", "phone.calls")
.list();

for(Object[] tuple : tuples) {
    Phone phone = (Phone) tuple[0];
    Call call = (Call) tuple[1];
}
SELECT *
FROM phone ph
JOIN call c ON c.phone_id = ph.id

At this stage you are reaching the limits of what is possible with native queries, without starting to enhance the sql queries to make them usable in Hibernate. Problems can arise when returning multiple entities of the same type or when the default alias/column names are not enough.

Returning multiple entities

Until now, the result set column names are assumed to be the same as the column names specified in the mapping document. This can be problematic for SQL queries that join multiple tables since the same column names can appear in more than one table.

Column alias injection is needed in the following query which otherwise throws NonUniqueDiscoveredSqlAliasException.

Example 17. JPA native query selecting entities with the same column names
List<Object> entities = entityManager.createNativeQuery(
    "SELECT * " +
    "FROM Person pr, Partner pt " +
    "WHERE pr.name = pt.name" )
.getResultList();
Example 18. Hibernate native query selecting entities with the same column names
List<Object> entities = session.createNativeQuery(
        "SELECT * " +
                "FROM Person pr, Partner pt " +
                "WHERE pr.name = pt.name" )
        .list();

The query was intended to return all Person and Partner instances with the same name. The query fails because there is a conflict of names since the two entities are mapped to the same column names (e.g. id, name, version). Also, on some databases the returned column aliases will most likely be on the form pr.id, pr.name, etc. which are not equal to the columns specified in the mappings (id and name).

The following form is not vulnerable to column name duplication:

Example 19. Hibernate native query selecting entities with the same column names and aliases
List<Object> entities = session.createNativeQuery(
    "SELECT {pr.*}, {pt.*} " +
    "FROM Person pr, Partner pt " +
    "WHERE pr.name = pt.name" )
.addEntity( "pr", Person.class)
.addEntity( "pt", Partner.class)
.list();

There’s no such equivalent in JPA because the Query interface doesn’t define an addEntity method equivalent.

The {pr.} and {pt.} notation used above is shorthand for "all properties". Alternatively, you can list the columns explicitly, but even in this case Hibernate injects the SQL column aliases for each property. The placeholder for a column alias is just the property name qualified by the table alias.

Alias and property references

In most cases the above alias injection is needed. For queries relating to more complex mappings, like composite properties, inheritance discriminators, collections etc., you can use specific aliases that allow Hibernate to inject the proper aliases.

The following table shows the different ways you can use the alias injection. Please note that the alias names in the result are simply examples, each alias will have a unique and probably different name when used.

Table 1. Alias injection names
Description Syntax Example

A simple property

{[aliasname].[propertyname]

A_NAME as {item.name}

A composite property

{[aliasname].[componentname].[propertyname]}

CURRENCY as {item.amount.currency}, VALUE as {item.amount.value}

Discriminator of an entity

{[aliasname].class}

DISC as {item.class}

All properties of an entity

{[aliasname].*}

{item.*}

A collection key

{[aliasname].key}

ORGID as {coll.key}

The id of a collection

{[aliasname].id}

EMPID as {coll.id}

The element of a collection

{[aliasname].element}

XID as {coll.element}

property of the element in the collection

{[aliasname].element.[propertyname]}

NAME as {coll.element.name}

All properties of the element in the collection

{[aliasname].element.*}

{coll.element.*}

All properties of the collection

{[aliasname].*}

{coll.*}

Returning DTOs (Data Transfer Objects)

It is possible to apply a ResultTransformer to native SQL queries, allowing it to return non-managed entities.

Example 20. Hibernate native query selecting DTOs
public class PersonSummaryDTO {

    private Number id;

    private String name;

    public Number getId() {
        return id;
    }

    public void setId(Number id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

List<PersonSummaryDTO> dtos = session.createNativeQuery(
    "SELECT p.id as \"id\", p.name as \"name\" " +
    "FROM Person p")
.setResultTransformer( Transformers.aliasToBean( PersonSummaryDTO.class ) )
.list();

There’s no such equivalent in JPA because the Query interface doesn’t define a setResultTransformer method equivalent.

The above query will return a list of PersonSummaryDTO which has been instantiated and injected the values of id and name into its corresponding properties or fields.

Handling inheritance

Native SQL queries which query for entities that are mapped as part of an inheritance must include all properties for the base class and all its subclasses.

Example 21. Hibernate native query selecting subclasses
List<CreditCardPayment> payments = session.createNativeQuery(
    "SELECT * " +
    "FROM Payment p " +
    "JOIN CreditCardPayment cp on cp.id = p.id" )
.addEntity( CreditCardPayment.class )
.list();

There’s no such equivalent in JPA because the Query interface doesn’t define an addEntity method equivalent.

Parameters

Native SQL queries support positional as well as named parameters:

Example 22. JPA native query with parameters
List<Person> persons = entityManager.createNativeQuery(
    "SELECT * " +
    "FROM Person " +
    "WHERE name like :name", Person.class )
.setParameter("name", "J%")
.getResultList();
Example 23. Hibernate native query with parameters
List<Person> persons = session.createNativeQuery(
    "SELECT * " +
    "FROM Person " +
    "WHERE name like :name" )
.addEntity( Person.class )
.setParameter("name", "J%")
.list();

Named SQL queries

Named SQL queries can also be defined during mapping and called in exactly the same way as a named HQL query. In this case, you do not need to call addEntity() anymore.

JPA defines the javax.persistence.NamedNativeQuery annotation for this purpose, and the Hibernate org.hibernate.annotations.NamedNativeQuery annotation extends it and adds the following attributes:

flushMode()

The flush mode for the query. By default, it uses the current Persistence Context flush mode.

cacheable()

Whether the query (results) is cacheable or not. By default, queries are not cached.

cacheRegion()

If the query results are cacheable, name the query cache region to use.

fetchSize()

The number of rows fetched by the JDBC Driver per database trip. The default value is given by the JDBC driver.

timeout()

The query timeout (in seconds). By default, there’s no timeout.

callable()

Does the SQL query represent a call to a procedure/function? Default is false.

comment()

A comment added to the SQL query for tuning the execution plan.

cacheMode()

The cache mode used for this query. This refers to entities/collections returned by the query. The default value is CacheModeType.NORMAL.

readOnly()

Whether the results should be read-only. By default, queries are not read-only so entities are stored in the Persistence Context.

Named SQL queries selecting scalar values

To fetch a single column of given table, the named query looks as follows:

Example 24. Single scalar value NamedNativeQuery
@NamedNativeQuery(
    name = "find_person_name",
    query =
        "SELECT name " +
        "FROM Person "
),
Example 25. JPA named native query selecting a scalar value
List<String> names = entityManager.createNamedQuery(
    "find_person_name" )
.getResultList();
Example 26. Hibernate named native query selecting a scalar value
List<String> names = session.getNamedQuery(
    "find_person_name" )
.list();

Selecting multiple scalar values is done like this:

Example 27. Multiple scalar values NamedNativeQuery
@NamedNativeQuery(
    name = "find_person_name_and_nickName",
    query =
        "SELECT " +
        "   name, " +
        "   nickName " +
        "FROM Person "
),

Without specifying an explicit result type, Hibernate will assume an Object array:

Example 28. JPA named native query selecting multiple scalar values
List<Object[]> tuples = entityManager.createNamedQuery(
    "find_person_name_and_nickName" )
.getResultList();

for(Object[] tuple : tuples) {
    String name = (String) tuple[0];
    String nickName = (String) tuple[1];
}
Example 29. Hibernate named native query selecting multiple scalar values
List<Object[]> tuples = session.getNamedQuery(
    "find_person_name_and_nickName" )
.list();

for(Object[] tuple : tuples) {
    String name = (String) tuple[0];
    String nickName = (String) tuple[1];
}

It’s possible to use a DTO to store the resulting scalar values:

Example 30. DTO to store multiple scalar values
public class PersonNames {

    private final String name;

    private final String nickName;

    public PersonNames(String name, String nickName) {
        this.name = name;
        this.nickName = nickName;
    }

    public String getName() {
        return name;
    }

    public String getNickName() {
        return nickName;
    }
}
Example 31. Multiple scalar values NamedNativeQuery with ConstructorResult
@NamedNativeQuery(
    name = "find_person_name_and_nickName_dto",
    query =
        "SELECT " +
        "   name, " +
        "   nickName " +
        "FROM Person ",
    resultSetMapping = "name_and_nickName_dto"
),
@SqlResultSetMapping(
    name = "name_and_nickName_dto",
    classes = @ConstructorResult(
        targetClass = PersonNames.class,
        columns = {
            @ColumnResult(name = "name"),
            @ColumnResult(name = "nickName")
        }
    )
)
Example 32. JPA named native query selecting multiple scalar values into a DTO
List<PersonNames> personNames = entityManager.createNamedQuery(
    "find_person_name_and_nickName_dto" )
.getResultList();
Example 33. Hibernate named native query selecting multiple scalar values into a DTO
List<PersonNames> personNames = session.getNamedQuery(
    "find_person_name_and_nickName_dto" )
.list();

You can also use the @NamedNativeQuery Hibernate annotation to customize the named query using various configurations such as fetch mode, cacheability, time out interval.

Example 34. Multiple scalar values using ConstructorResult and Hibernate NamedNativeQuery
@NamedNativeQueries({
    @NamedNativeQuery(
        name = "get_person_phone_count",
        query = "SELECT pr.name AS name, count(*) AS phoneCount " +
                "FROM Phone p " +
                "JOIN Person pr ON pr.id = p.person_id " +
                "GROUP BY pr.name",
        resultSetMapping = "person_phone_count",
        timeout = 1,
        readOnly = true
    ),
})
@SqlResultSetMapping(
    name = "person_phone_count",
    classes = @ConstructorResult(
        targetClass = PersonPhoneCount.class,
        columns = {
            @ColumnResult(name = "name"),
            @ColumnResult(name = "phoneCount")
        }
    )
)
Example 35. Hibernate NamedNativeQuery named native query selecting multiple scalar values into a DTO
List<PersonPhoneCount> personNames = session.getNamedNativeQuery(
    "get_person_phone_count")
.getResultList();

Named SQL queries selecting entities

Considering the following named query:

Example 36. Single-entity NamedNativeQuery
@NamedNativeQuery(
    name = "find_person_by_name",
    query =
        "SELECT " +
        "   p.id AS \"id\", " +
        "   p.name AS \"name\", " +
        "   p.nickName AS \"nickName\", " +
        "   p.address AS \"address\", " +
        "   p.createdOn AS \"createdOn\", " +
        "   p.version AS \"version\" " +
        "FROM Person p " +
        "WHERE p.name LIKE :name",
    resultClass = Person.class
),

The result set mapping declares the entities retrieved by this native query. Each field of the entity is bound to an SQL alias (or column name). All fields of the entity including the ones of subclasses and the foreign key columns of related entities have to be present in the SQL query. Field definitions are optional provided that they map to the same column name as the one declared on the class property.

Executing this named native query can be done as follows:

Example 37. JPA named native entity query
List<Person> persons = entityManager.createNamedQuery(
    "find_person_by_name" )
.setParameter("name", "J%")
.getResultList();
Example 38. Hibernate named native entity query
List<Person> persons = session.getNamedQuery(
    "find_person_by_name" )
.setParameter("name", "J%")
.list();

To join multiple entities, you need to use a SqlResultSetMapping for each entity the SQL query is going to fetch.

Example 39. Joined-entities NamedNativeQuery
@NamedNativeQuery(
    name = "find_person_with_phones_by_name",
    query =
        "SELECT " +
        "   pr.id AS \"pr.id\", " +
        "   pr.name AS \"pr.name\", " +
        "   pr.nickName AS \"pr.nickName\", " +
        "   pr.address AS \"pr.address\", " +
        "   pr.createdOn AS \"pr.createdOn\", " +
        "   pr.version AS \"pr.version\", " +
        "   ph.id AS \"ph.id\", " +
        "   ph.person_id AS \"ph.person_id\", " +
        "   ph.phone_number AS \"ph.number\", " +
        "   ph.phone_type AS \"ph.type\" " +
        "FROM Person pr " +
        "JOIN Phone ph ON pr.id = ph.person_id " +
        "WHERE pr.name LIKE :name",
    resultSetMapping = "person_with_phones"
)
 @SqlResultSetMapping(
     name = "person_with_phones",
     entities = {
         @EntityResult(
             entityClass = Person.class,
             fields = {
                 @FieldResult( name = "id", column = "pr.id" ),
                 @FieldResult( name = "name", column = "pr.name" ),
                 @FieldResult( name = "nickName", column = "pr.nickName" ),
                 @FieldResult( name = "address", column = "pr.address" ),
                 @FieldResult( name = "createdOn", column = "pr.createdOn" ),
                 @FieldResult( name = "version", column = "pr.version" ),
             }
         ),
         @EntityResult(
             entityClass = Phone.class,
             fields = {
                 @FieldResult( name = "id", column = "ph.id" ),
                 @FieldResult( name = "person", column = "ph.person_id" ),
                 @FieldResult( name = "number", column = "ph.number" ),
                 @FieldResult( name = "type", column = "ph.type" ),
             }
         )
     }
 ),
Example 40. JPA named native entity query with joined associations
List<Object[]> tuples = entityManager.createNamedQuery(
    "find_person_with_phones_by_name" )
.setParameter("name", "J%")
.getResultList();

for(Object[] tuple : tuples) {
    Person person = (Person) tuple[0];
    Phone phone = (Phone) tuple[1];
}
Example 41. Hibernate named native entity query with joined associations
List<Object[]> tuples = session.getNamedQuery(
    "find_person_with_phones_by_name" )
.setParameter("name", "J%")
.list();

for(Object[] tuple : tuples) {
    Person person = (Person) tuple[0];
    Phone phone = (Phone) tuple[1];
}

Finally, if the association to a related entity involve a composite primary key, a @FieldResult element should be used for each foreign key column. The @FieldResult name is composed of the property name for the relationship, followed by a dot ("."), followed by the name or the field or property of the primary key. For this example, the following entities are going to be used:

Example 42. Entity associations with composite keys and named native queries
@Embeddable
public class Dimensions {

    private int length;

    private int width;

    public int getLength() {
        return length;
    }

    public void setLength(int length) {
        this.length = length;
    }

    public int getWidth() {
        return width;
    }

    public void setWidth(int width) {
        this.width = width;
    }
}

@Embeddable
public class Identity implements Serializable {

    private String firstname;

    private String lastname;

    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public boolean equals(Object o) {
        if ( this == o ) return true;
        if ( o == null || getClass() != o.getClass() ) return false;

        final Identity identity = (Identity) o;

        if ( !firstname.equals( identity.firstname ) ) return false;
        if ( !lastname.equals( identity.lastname ) ) return false;

        return true;
    }

    public int hashCode() {
        int result;
        result = firstname.hashCode();
        result = 29 * result + lastname.hashCode();
        return result;
    }
}

@Entity
public class Captain {

    @EmbeddedId
    private Identity id;

    public Identity getId() {
        return id;
    }

    public void setId(Identity id) {
        this.id = id;
    }
}

@Entity
@NamedNativeQueries({
    @NamedNativeQuery(name = "find_all_spaceships",
        query =
            "SELECT " +
            "   name as \"name\", " +
            "   model, " +
            "   speed, " +
            "   lname as lastn, " +
            "   fname as firstn, " +
            "   length, " +
            "   width, " +
            "   length * width as surface, " +
            "   length * width * 10 as volume " +
            "FROM SpaceShip",
        resultSetMapping = "spaceship"
    )
})
@SqlResultSetMapping(
    name = "spaceship",
    entities = @EntityResult(
        entityClass = SpaceShip.class,
        fields = {
            @FieldResult(name = "name", column = "name"),
            @FieldResult(name = "model", column = "model"),
            @FieldResult(name = "speed", column = "speed"),
            @FieldResult(name = "captain.lastname", column = "lastn"),
            @FieldResult(name = "captain.firstname", column = "firstn"),
            @FieldResult(name = "dimensions.length", column = "length"),
            @FieldResult(name = "dimensions.width", column = "width"),
        }
    ),
    columns = {
        @ColumnResult(name = "surface"),
        @ColumnResult(name = "volume")
    }
)
public class SpaceShip {

    @Id
    private String name;

    private String model;

    private double speed;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumns({
        @JoinColumn(name = "fname", referencedColumnName = "firstname"),
        @JoinColumn(name = "lname", referencedColumnName = "lastname")
    })
    private Captain captain;

    private Dimensions dimensions;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getModel() {
        return model;
    }

    public void setModel(String model) {
        this.model = model;
    }

    public double getSpeed() {
        return speed;
    }

    public void setSpeed(double speed) {
        this.speed = speed;
    }

    public Captain getCaptain() {
        return captain;
    }

    public void setCaptain(Captain captain) {
        this.captain = captain;
    }

    public Dimensions getDimensions() {
        return dimensions;
    }

    public void setDimensions(Dimensions dimensions) {
        this.dimensions = dimensions;
    }
}
Example 43. JPA named native entity query with joined associations and composite keys
List<Object[]> tuples = entityManager.createNamedQuery(
    "find_all_spaceships" )
.getResultList();

for(Object[] tuple : tuples) {
    SpaceShip spaceShip = (SpaceShip) tuple[0];
    Number surface = (Number) tuple[1];
    Number volume = (Number) tuple[2];
}
Example 44. Hibernate named native entity query with joined associations and composite keys
List<Object[]> tuples = session.getNamedQuery(
    "find_all_spaceships" )
.list();

for(Object[] tuple : tuples) {
    SpaceShip spaceShip = (SpaceShip) tuple[0];
    Number surface = (Number) tuple[1];
    Number volume = (Number) tuple[2];
}

Resolving global catalog and schema in native SQL queries

When using multiple database catalogs and schemas, Hibernate offers the possibility of setting a global catalog or schema so that you don’t have to declare it explicitly for every entity.

Example 45. Setting global catalog and schema
<property name="hibernate.default_catalog" value="crm"/>
<property name="hibernate.default_schema" value="analytics"/>

This way, we can imply the global crm catalog and analytics schema in every JPQL, HQL or Criteria API query.

However, for native queries, the SQL query is passed as is, therefore you need to explicitly set the global catalog and schema whenever you are referencing a database table. Fortunately, Hibernate allows you to resolve the current global catalog and schema using the following placeholders:

{h-catalog}

resolves the current hibernate.default_catalog configuration property value.

{h-schema}

resolves the current hibernate.default_schema configuration property value.

{h-domain}

resolves the current hibernate.default_catalog and hibernate.default_schema configuration property values (e.g. catalog.schema).

Withe these placeholders, you can imply the catalog, schema, or both catalog and schema for every native query.

So, when running the following native query:

@NamedNativeQuery(
    name = "last_30_days_hires",
    query =
        "select * " +
        "from {h-domain}person " +
        "where age(hired_on) < '30 days'",
    resultClass = Person.class
)

Hibernate is going to resolve the {h-domain} placeholder according to the values of the default catalog and schema:

SELECT *
FROM   crm.analytics.person
WHERE  age(hired_on) < '30 days'

Using stored procedures for querying

Hibernate provides support for queries via stored procedures and functions. A stored procedure arguments are declared using the IN parameter type, and the result can be either marked with an OUT parameter type, a REF_CURSOR or it could just return the result like a function.

Example 46. MySQL stored procedure with OUT parameter type
statement.executeUpdate(
    "CREATE PROCEDURE sp_count_phones (" +
    "   IN personId INT, " +
    "   OUT phoneCount INT " +
    ") " +
    "BEGIN " +
    "    SELECT COUNT(*) INTO phoneCount " +
    "    FROM Phone p " +
    "    WHERE p.person_id = personId; " +
    "END"
);

To use this stored procedure, you can execute the following JPA 2.1 query:

Example 47. Calling a MySQL stored procedure with OUT parameter type using JPA
StoredProcedureQuery query = entityManager.createStoredProcedureQuery( "sp_count_phones");
query.registerStoredProcedureParameter( "personId", Long.class, ParameterMode.IN);
query.registerStoredProcedureParameter( "phoneCount", Long.class, ParameterMode.OUT);

query.setParameter("personId", 1L);

query.execute();
Long phoneCount = (Long) query.getOutputParameterValue("phoneCount");
Example 48. Calling a MySQL stored procedure with OUT parameter type using Hibernate
Session session = entityManager.unwrap( Session.class );

ProcedureCall call = session.createStoredProcedureCall( "sp_count_phones" );
call.registerParameter( "personId", Long.class, ParameterMode.IN ).bindValue( 1L );
call.registerParameter( "phoneCount", Long.class, ParameterMode.OUT );

Long phoneCount = (Long) call.getOutputs().getOutputParameterValue( "phoneCount" );
assertEquals( Long.valueOf( 2 ), phoneCount );

If the stored procedure outputs the result directly without an OUT parameter type:

Example 49. MySQL stored procedure without an OUT parameter type
statement.executeUpdate(
    "CREATE PROCEDURE sp_phones(IN personId INT) " +
    "BEGIN " +
    "    SELECT *  " +
    "    FROM Phone   " +
    "    WHERE person_id = personId;  " +
    "END"
);

You can retrieve the results of the aforementioned MySQL stored procedure as follows:

Example 50. Calling a MySQL stored procedure and fetching the result set without an OUT parameter type using JPA
StoredProcedureQuery query = entityManager.createStoredProcedureQuery( "sp_phones");
query.registerStoredProcedureParameter( 1, Long.class, ParameterMode.IN);

query.setParameter(1, 1L);

List<Object[]> personComments = query.getResultList();
Example 51. Calling a MySQL stored procedure and fetching the result set without an OUT parameter type using Hibernate
Session session = entityManager.unwrap( Session.class );

ProcedureCall call = session.createStoredProcedureCall( "sp_phones" );
call.registerParameter( 1, Long.class, ParameterMode.IN ).bindValue( 1L );

Output output = call.getOutputs().getCurrent();

List<Object[]> personComments = ( (ResultSetOutput) output ).getResultList();

For a REF_CURSOR result sets, we’ll consider the following Oracle stored procedure:

Example 52. Oracle REF_CURSOR stored procedure
statement.executeUpdate(
    "CREATE OR REPLACE PROCEDURE sp_person_phones ( " +
    "   personId IN NUMBER, " +
    "   personPhones OUT SYS_REFCURSOR ) " +
    "AS  " +
    "BEGIN " +
    "    OPEN personPhones FOR " +
    "    SELECT *" +
    "    FROM phone " +
    "    WHERE person_id = personId; " +
    "END;"
);

REF_CURSOR result sets are only supported by Oracle and PostgreSQL because other database systems JDBC drivers don’t support this feature.

This function can be called using the standard Java Persistence API:

Example 53. Calling an Oracle REF_CURSOR stored procedure using JPA
StoredProcedureQuery query = entityManager.createStoredProcedureQuery( "sp_person_phones" );
query.registerStoredProcedureParameter( 1, Long.class, ParameterMode.IN );
query.registerStoredProcedureParameter( 2, Class.class, ParameterMode.REF_CURSOR );
query.setParameter( 1, 1L );

query.execute();
List<Object[]> postComments = query.getResultList();
Example 54. Calling an Oracle REF_CURSOR stored procedure using Hibernate
Session session = entityManager.unwrap(Session.class);

ProcedureCall call = session.createStoredProcedureCall( "sp_person_phones");
call.registerParameter(1, Long.class, ParameterMode.IN).bindValue(1L);
call.registerParameter(2, Class.class, ParameterMode.REF_CURSOR);

Output output = call.getOutputs().getCurrent();
List<Object[]> postComments = ( (ResultSetOutput) output ).getResultList();
assertEquals(2, postComments.size());

If the database defines an SQL function:

Example 55. MySQL function
statement.executeUpdate(
    "CREATE FUNCTION fn_count_phones(personId integer)  " +
    "RETURNS integer " +
    "DETERMINISTIC " +
    "READS SQL DATA " +
    "BEGIN " +
    "    DECLARE phoneCount integer; " +
    "    SELECT COUNT(*) INTO phoneCount " +
    "    FROM Phone p " +
    "    WHERE p.person_id = personId; " +
    "    RETURN phoneCount; " +
    "END"
);

Because the current StoredProcedureQuery implementation doesn’t yet support SQL functions, we need to use the JDBC syntax.

This limitation is acknowledged and will be addressed by the HHH-10530 issue.
Example 56. Calling a MySQL function
final AtomicReference<Integer> phoneCount = new AtomicReference<>();
Session session = entityManager.unwrap( Session.class );
session.doWork( connection -> {
    try (CallableStatement function = connection.prepareCall(
            "{ ? = call fn_count_phones(?) }" )) {
        function.registerOutParameter( 1, Types.INTEGER );
        function.setInt( 2, 1 );
        function.execute();
        phoneCount.set( function.getInt( 1 ) );
    }
} );

Stored procedure queries cannot be paged with setFirstResult()/setMaxResults().

Since these servers can return multiple result sets and update counts, Hibernate will iterate the results and take the first result that is a result set as its return value, so everything else will be discarded.

For SQL Server, if you can enable SET NOCOUNT ON in your procedure it will probably be more efficient, but this is not a requirement.

Using named queries to call stored procedures

Just like with SQL statements, you can also use named queries to call stored procedures. For this purpose, JPA defines the @NamedStoredProcedureQuery annotation.

Example 57. Oracle REF_CURSOR named query stored procedure
@NamedStoredProcedureQueries(
    @NamedStoredProcedureQuery(
        name = "sp_person_phones",
        procedureName = "sp_person_phones",
        parameters = {
            @StoredProcedureParameter(
                name = "personId",
                type = Long.class,
                mode = ParameterMode.IN
            ),
            @StoredProcedureParameter(
                name = "personPhones",
                type = Class.class,
                mode = ParameterMode.REF_CURSOR
            )
        }
    )
)

Calling this stored procedure is straightforward, as illustrated by the following example.

Example 58. Calling an Oracle REF_CURSOR stored procedure using a JPA named query
List<Object[]> postComments = entityManager
.createNamedStoredProcedureQuery( "sp_person_phones" )
.setParameter( "personId", 1L )
.getResultList();

Custom SQL for create, update, and delete

Hibernate can use custom SQL for create, update, and delete operations. The SQL can be overridden at the statement level or individual column level. This section describes statement overrides. For columns, see Column transformers: read and write expressions.

The following example shows how to define custom SQL operations using annotations. @SQLInsert, @SQLUpdate and @SQLDelete override the INSERT, UPDATE, DELETE statements of a given entity. For the SELECT clause, a @Loader must be defined along with a @NamedNativeQuery used for loading the underlying table record.

For collections, Hibernate allows defining a custom @SQLDeleteAll which is used for removing all child records associated with a given parent entity. To filter collections, the @Where annotation allows customizing the underlying SQL WHERE clause.

Example 59. Custom CRUD
@Entity(name = "Person")
@SQLInsert(
    sql = "INSERT INTO person (name, id, valid) VALUES (?, ?, true) ",
    check = ResultCheckStyle.COUNT
)
@SQLUpdate(
    sql = "UPDATE person SET name = ? where id = ? ")
@SQLDelete(
    sql = "UPDATE person SET valid = false WHERE id = ? ")
@Loader(namedQuery = "find_valid_person")
@NamedNativeQueries({
    @NamedNativeQuery(
        name = "find_valid_person",
        query = "SELECT id, name " +
                "FROM person " +
                "WHERE id = ? and valid = true",
        resultClass = Person.class
    )
})
public static class Person {

    @Id
    @GeneratedValue
    private Long id;

    private String name;

    @ElementCollection
    @SQLInsert(
        sql = "INSERT INTO person_phones (person_id, phones, valid) VALUES (?, ?, true) ")
    @SQLDeleteAll(
        sql = "UPDATE person_phones SET valid = false WHERE person_id = ?")
    @Where( clause = "valid = true" )
    private List<String> phones = new ArrayList<>();

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<String> getPhones() {
        return phones;
    }
}

In the example above, the entity is mapped so that entries are soft-deleted (the records are not removed from the database, but instead, a flag marks the row validity). The Person entity benefits from custom INSERT, UPDATE, and DELETE statements which update the valid column accordingly. The custom @Loader is used to retrieve only Person rows that are valid.

The same is done for the phones collection. The @SQLDeleteAll and the SQLInsert queries are used whenever the collection is modified.

You also call a store procedure using the custom CRUD statements; the only requirement is to set the callable attribute to true.

To check that the execution happens correctly, Hibernate allows you to define one of those three strategies:

  • none: no check is performed; the store procedure is expected to fail upon constraint violations

  • count: use of row-count returned by the executeUpdate() method call to check that the update was successful

  • param: like count but using a CallableStatement output parameter.

To define the result check style, use the check parameter.

The parameter order is important and is defined by the order Hibernate handles properties. You can see the expected order by enabling debug logging, so Hibernate can print out the static SQL that is used to create, update, delete etc. entities.

To see the expected sequence, remember to not include your custom SQL through annotations or mapping files as that will override the Hibernate generated static sql.

Overriding SQL statements for secondary tables is also possible using @org.hibernate.annotations.Table and the sqlInsert, sqlUpdate, sqlDelete attributes.

Example 60. Overriding SQL statements for secondary tables
@Entity(name = "Person")
@Table(name = "person")
@SQLInsert(
    sql = "INSERT INTO person (name, id, valid) VALUES (?, ?, true) "
)
@SQLDelete(
    sql = "UPDATE person SET valid = false WHERE id = ? "
)
@SecondaryTable(name = "person_details",
    pkJoinColumns = @PrimaryKeyJoinColumn(name = "person_id"))
@org.hibernate.annotations.Table(
    appliesTo = "person_details",
    sqlInsert = @SQLInsert(
        sql = "INSERT INTO person_details (image, person_id, valid) VALUES (?, ?, true) ",
        check = ResultCheckStyle.COUNT
    ),
    sqlDelete = @SQLDelete(
        sql = "UPDATE person_details SET valid = false WHERE person_id = ? "
    )
)
@Loader(namedQuery = "find_valid_person")
@NamedNativeQueries({
    @NamedNativeQuery(
        name = "find_valid_person",
        query = "select " +
                "    p.id, " +
                "    p.name, " +
                "    pd.image  " +
                "from person p  " +
                "left outer join person_details pd on p.id = pd.person_id  " +
                "where p.id = ? and p.valid = true and pd.valid = true",
        resultClass = Person.class
    )
})
public static class Person {

    @Id
    @GeneratedValue
    private Long id;

    private String name;

    @Column(name = "image", table = "person_details")
    private byte[] image;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public byte[] getImage() {
        return image;
    }

    public void setImage(byte[] image) {
        this.image = image;
    }
}

The SQL is directly executed in your database, so you can use any dialect you like. This will, however, reduce the portability of your mapping if you use database specific SQL.

You can also use stored procedures for customizing the CRUD statements.

Assuming the following stored procedure:

Example 61. Oracle stored procedure to soft-delete a given entity
statement.executeUpdate(
    "CREATE OR REPLACE PROCEDURE sp_delete_person ( " +
    "   personId IN NUMBER ) " +
    "AS  " +
    "BEGIN " +
    "    UPDATE person SET valid = 0 WHERE id = personId; " +
    "END;"
);}

The entity can use this stored procedure to soft-delete the entity in question:

Example 62. Customizing the entity delete statement to use the Oracle stored procedure= instead
@SQLDelete(
    sql =   "{ call sp_delete_person( ? ) } ",
    callable = true
)

You need to set the callable attribute when using a stored procedure instead of an SQL statement.