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 SQLQuery interface, which is obtained by calling Session.createSQLQuery(). 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) {
    BigInteger id = (BigInteger) person[0];
    String name = (String) person[1];
}

Example 3. Hibernate native query selecting all columns

List<Object[]> persons = session.createSQLQuery(
    "SELECT * FROM person" )
.list();

Example 4. Hibernate native query with a custom column selection

List<Object[]> persons = session.createSQLQuery(
    "SELECT id, name FROM person" )
.list();

for(Object[] person : persons) {
    BigInteger id = (BigInteger) 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.createSQLQuery(
    "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.createSQLQuery(
    "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.createSQLQuery(
    "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.createSQLQuery(
    "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, number, type, person_id " +
    "FROM phone", Phone.class )
.getResultList();

Example 12. Hibernate native query selecting entities with many-to-one association

List<Phone> phones = session.createSQLQuery(
    "SELECT id, number, type, person_id " +
    "FROM phone" )
.addEntity( Phone.class )
.list();

This will allow the Phone#person to function properly.

The additional columns will automatically be returned when using the * notation.

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. JPA native query selecting entities with joined many-to-one association

List<Phone> phones = entityManager.createNativeQuery(
    "SELECT * " +
    "FROM phone ph " +
    "JOIN person pr ON ph.person_id = pr.id", Phone.class )
.getResultList();

for(Phone phone : phones) {
    Person person = phone.getPerson();
}

SELECT id ,
       number ,
       type ,
       person_id
FROM   phone

Example 14. Hibernate native query selecting entities with joined many-to-one association

List<Object[]> tuples = session.createSQLQuery(
    "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];
}

SELECT id ,
       number ,
       type ,
       person_id
FROM   phone

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 15. Hibernate native query selecting entities with joined many-to-one association and ResultTransformer

List<Person> persons = session.createSQLQuery(
    "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 16. JPA native query selecting entities with joined one-to-many association

List<Phone> phones = entityManager.createNativeQuery(
    "SELECT * " +
    "FROM phone ph " +
    "JOIN 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 17. Hibernate native query selecting entities with joined one-to-many association

List<Object[]> tuples = session.createSQLQuery(
    "SELECT * " +
    "FROM phone ph " +
    "JOIN 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 18. 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 19. Hibernate native query selecting entities with the same column names

List<Object> entities = session.createSQLQuery(
    "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 20. Hibernate native query selecting entities with the same column names and aliases

List<Object> entities = session.createSQLQuery(
    "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 a 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 an collection	`{[aliasname].id}`	`EMPID as {coll.id}`
The element of an 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 21. Hibernate native query selecting DTOs

public class PersonSummaryDTO {

    private BigInteger id;

    private String name;

    public BigInteger getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

List<PersonSummaryDTO> dtos = session.createSQLQuery(
    "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 22. Hibernate native query selecting subclasses

List<CreditCardPayment> payments = session.createSQLQuery(
    "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 23. JPA native query with parameters

List<Person> persons = entityManager.createNativeQuery(
    "SELECT * " +
    "FROM person " +
    "WHERE name like :name", Person.class )
.setParameter("name", "J%")
.getResultList();

Example 24. Hibernate native query with parameters

List<Person> persons = session.createSQLQuery(
    "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 25. Single scalar value NamedNativeQuery

@NamedNativeQuery(
    name = "find_person_name",
    query =
        "SELECT name " +
        "FROM person "
),

Example 26. JPA named native query selecting a scalar value

List<String> names = entityManager.createNamedQuery(
    "find_person_name" )
.getResultList();

Example 27. 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 28. 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 29. 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 30. 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 31. 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 32. 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 33. JPA named native query selecting multiple scalar values into a DTO

List<PersonNames> personNames = entityManager.createNamedQuery(
    "find_person_name_and_nickName_dto" )
.getResultList();

Example 34. Hibernate named native query selecting multiple scalar values into a DTO

List<PersonNames> personNames = session.getNamedQuery(
    "find_person_name_and_nickName_dto" )
.list();

Named SQL queries selecting entities

Considering the following named query:

Example 35. 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 36. JPA named native entity query

List<Person> persons = entityManager.createNamedQuery(
    "find_person_by_name" )
.setParameter("name", "J%")
.getResultList();

Example 37. 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 38. 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.number AS \"ph.number\", " +
        "   ph.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 39. 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 40. 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 41. 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 42. 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];
    Integer surface = (Integer) tuple[1];
    Integer volume = (Integer) tuple[2];
}

Example 43. 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];
    Integer surface = (Integer) tuple[1];
    Integer volume = (Integer) tuple[2];
}

Using stored procedures for querying

Hibernate provides support for queries via stored procedures and functions. Most of the following documentation is equivalent for both. The stored procedure/function must return a resultset as the first out-parameter to be able to work with Hibernate. An example of such a stored function in Oracle 9 and higher is as follows:

CREATE OR REPLACE FUNCTION selectAllEmployments
    RETURN SYS_REFCURSOR
AS
    st_cursor SYS_REFCURSOR;
BEGIN
    OPEN st_cursor FOR
        SELECT EMPLOYEE, EMPLOYER,
        STARTDATE, ENDDATE,
        REGIONCODE, EID, VALUE, CURRENCY
        FROM EMPLOYMENT;
    RETURN  st_cursor;
END;

To use this query in Hibernate you need to map it via a named query.

<sql-query name = "selectAllEmployees_SP" callable = "true">
    <return alias="emp" class="Employment">
        <return-property name = "employee" column = "EMPLOYEE"/>
        <return-property name = "employer" column = "EMPLOYER"/>
        <return-property name = "startDate" column = "STARTDATE"/>
        <return-property name = "endDate" column = "ENDDATE"/>
        <return-property name = "regionCode" column = "REGIONCODE"/>
        <return-property name = "id" column = "EID"/>
        <return-property name = "salary">
            <return-column name = "VALUE"/>
            <return-column name = "CURRENCY"/>
        </return-property>
    </return>
    { ? = call selectAllEmployments() }
</sql-query>

Stored procedures currently only return scalars and entities. <return-join> and <load-collection> are not supported.

Rules/limitations for using stored procedures

You cannot use stored procedures with Hibernate unless you follow some procedure/function rules. If they do not follow those rules they are not usable with Hibernate. If you still want to use these procedures you have to execute them via session.doWork().

The rules are different for each database, since database vendors have different stored procedure semantics/syntax.

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

The recommended call form is standard SQL92: { ? = call functionName(<parameters>) } or { ? = call procedureName(<parameters>}. Native call syntax is not supported.

For Oracle the following rules apply:

A function must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9 or 10. In Oracle you need to define a REF CURSOR type. See Oracle literature for further information.

For Sybase or MS SQL server the following rules apply:

The procedure must return a result set. Note that 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. Everything else will be discarded.
If you can enable SET NOCOUNT ON in your procedure it will probably be more efficient, but this is not a requirement.

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.

Example 44. Custom CRUD via annotations

@Entity
@Table(name = "CHAOS")
@SQLInsert( sql = "INSERT INTO CHAOS(size, name, nickname, id) VALUES(?,upper(?),?,?)")
@SQLUpdate( sql = "UPDATE CHAOS SET size = ?, name = upper(?), nickname = ? WHERE id = ?")
@SQLDelete( sql = "DELETE CHAOS WHERE id = ?")
@SQLDeleteAll( sql = "DELETE CHAOS")
@Loader(namedQuery = "chaos")
@NamedNativeQuery(name = "chaos", query="select id, size, name, lower( nickname ) as nickname from CHAOS where id= ?", resultClass = Chaos.class)
public class Chaos {
    @Id
    private Long id;
    private Long size;
    private String name;
    private String nickname;

@SQLInsert, @SQLUpdate, @SQLDelete, @SQLDeleteAll respectively override the INSERT, UPDATE, DELETE, and DELETE all statement. The same can be achieved using Hibernate mapping files and the <sql-insert>, <sql-update> and <sql-delete> nodes.

Example 45. Custom CRUD XML

<class name = "Person">
    <id name = "id">
        <generator class = "increment"/>
    </id>
    <property name = "name" not-null = "true"/>
    <sql-insert>INSERT INTO PERSON (NAME, ID) VALUES ( UPPER(?), ? )</sql-insert>
    <sql-update>UPDATE PERSON SET NAME=UPPER(?) WHERE ID=?</sql-update>
    <sql-delete>DELETE FROM PERSON WHERE ID=?</sql-delete>
</class>

If you expect to call a store procedure, be sure to set the callable attribute to true, in annotations as well as in xml.

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 issues
count: use of rowcount to check that the update is successful
param: like COUNT but using an output parameter rather that the standard mechanism

To define the result check style, use the check parameter which is again available in annotations as well as in xml.

You can use the exact same set of annotations respectively xml nodes to override the collection related statements, as you can see in the following example.

Example 46. Overriding SQL statements for collections using annotations

@OneToMany
@JoinColumn(name = "chaos_fk")
@SQLInsert( sql = "UPDATE CASIMIR_PARTICULE SET chaos_fk = ? where id = ?")
@SQLDelete( sql = "UPDATE CASIMIR_PARTICULE SET chaos_fk = null where id = ?")
private Set<CasimirParticle> particles = new HashSet<CasimirParticle>();

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 either (or all) attributes sqlInsert, sqlUpdate, sqlDelete:

Example 47. Overriding SQL statements for secondary tables

@Entity
@SecondaryTables({
    @SecondaryTable(name = "`Cat nbr1`"),
    @SecondaryTable(name = "Cat2"})
@org.hibernate.annotations.Tables( {
    @Table(
        appliesTo = "Cat",
        comment = "My cat table"
    ),
    @Table(
        appliesTo = "Cat2",
        foreignKey = @ForeignKey(name = "FK_CAT2_CAT"), fetch = FetchMode.SELECT,
        sqlInsert = @SQLInsert(
            sql = "insert into Cat2(storyPart2, id) values(upper(?), ?)"
        )
    )
} )
public class Cat implements Serializable {

The previous example also shows that you can give a comment to a given table (primary or secondary): This comment will be used for DDL generation.

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.

Last but not least, stored procedures are in most cases required to return the number of rows inserted, updated and deleted. Hibernate always registers the first statement parameter as a numeric output parameter for the CUD operations:

Example 48. Stored procedures and their return value

CREATE OR REPLACE FUNCTION updatePerson (uid IN NUMBER, uname IN VARCHAR2)
    RETURN NUMBER IS
BEGIN

    update PERSON
    set
        NAME = uname,
    where
        ID = uid;

    return SQL%ROWCOUNT;

END updatePerson;

Custom SQL for loading

You can also declare your own SQL (or HQL) queries for entity loading. As with inserts, updates, and deletes, this can be done at the individual column level as described in For columns, see Column transformers: read and write expressions or at the statement level. Here is an example of a statement level override:

<sql-query name = "person">
    <return alias = "pers" class = "Person" lock-mod e= "upgrade"/>
    SELECT NAME AS {pers.name}, ID AS {pers.id}
    FROM PERSON
    WHERE ID=?
    FOR UPDATE
</sql-query>

This is just a named query declaration, as discussed earlier. You can reference this named query in a class mapping:

<class name = "Person">
    <id name = "id">
        <generator class = "increment"/>
    </id>
    <property name = "name" not-null = "true"/>
    <loader query-ref = "person"/>
</class>

This even works with stored procedures.

You can even define a query for collection loading:

<set name = "employments" inverse = "true">
    <key/>
    <one-to-many class = "Employment"/>
    <loader query-ref = "employments"/>
</set>

<sql-query name = "employments">
    <load-collection alias = "emp" role = "Person.employments"/>
    SELECT {emp.*}
    FROM EMPLOYMENT emp
    WHERE EMPLOYER = :id
    ORDER BY STARTDATE ASC, EMPLOYEE ASC
</sql-query>

You can also define an entity loader that loads a collection by join fetching:

<sql-query name = "person">
    <return alias = "pers" class = "Person"/>
    <return-join alias = "emp" property = "pers.employments"/>
    SELECT NAME AS {pers.*}, {emp.*}
    FROM PERSON pers
    LEFT OUTER JOIN EMPLOYMENT emp
        ON pers.ID = emp.PERSON_ID
    WHERE ID=?
</sql-query>

The annotation equivalent <loader> is the @Loader annotation as seen in Custom CRUD via annotations.