17. 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.

17.1. 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.

17.2. Scalar queries

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

Example 570. JPA native query selecting all columns

List<Object[]> persons = entityManager.createNativeQuery(
    "SELECT * FROM Person" )
.getResultList();

Example 571. 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 572. Hibernate native query selecting all columns

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

Example 573. 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 574. 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 575. 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, whereas 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.

17.3. Entity queries

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

Example 576. JPA native query selecting entities

List<Person> persons = entityManager.createNativeQuery(
    "SELECT * FROM Person", Person.class )
.getResultList();

Example 577. 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 578. 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 579. 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();

17.4. 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 580. 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 581. 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 582. 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

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 583. 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();
}

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 584. 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 585. 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.

17.5. 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 586. 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 587. 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 588. 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();

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.

17.6. 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.

17.7. Returning DTOs (Data Transfer Objects)

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

Example 589. Hibernate native query selecting DTOs

public class PersonSummaryDTO {
    private Number id;
    private String name;
    //Getters and setters are omitted for brevity
    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();

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.

17.8. 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 590. 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();

17.9. Parameters

Native SQL queries support positional as well as named parameters:

Example 591. JPA native query with parameters

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

Example 592. Hibernate native query with parameters

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

17.10. 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? The 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.

17.10.1. Named SQL queries selecting scalar values

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

Example 593. Single scalar value NamedNativeQuery

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

Example 594. JPA named native query selecting a scalar value

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

Example 595. 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 596. 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 597. 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 598. 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 599. 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 600. 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 601. JPA named native query selecting multiple scalar values into a DTO

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

Example 602. 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 603. 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 604. Hibernate NamedNativeQuery named native query selecting multiple scalar values into a DTO

List<PersonPhoneCount> personNames = session.getNamedNativeQuery(
    "get_person_phone_count")
.getResultList();

17.10.2. Named SQL queries selecting entities

Considering the following named query:

Example 605. 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 606. JPA named native entity query

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

Example 607. 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 608. 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 609. 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 610. 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 involves 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 611. Entity associations with composite keys and named native queries

@Embeddable
public class Dimensions {
    private int length;
    private int width;
    //Getters and setters are omitted for brevity
}
@Embeddable
public class Identity implements Serializable {
    private String firstname;
    private String lastname;
    //Getters and setters are omitted for brevity
    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;
    //Getters and setters are omitted for brevity
}
@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;
    //Getters and setters are omitted for brevity
}

Example 612. 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 613. 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];
}

17.11. 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 614. 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).

With 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'

17.12. 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 615. 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 616. 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 617. 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 618. 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 619. 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 620. 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 621. 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;"
);

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

Example 622. 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 623. 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 624. 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.

Example 625. 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 ) );
    }
} );

17.13. 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 626. 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 627. Calling an Oracle REF_CURSOR stored procedure using a JPA named query

List<Object[]> postComments = entityManager
.createNamedStoredProcedureQuery( "sp_person_phones" )
.setParameter( "personId", 1L )
.getResultList();

17.14. Custom SQL for CRUD (Create, Read, Update and Delete)

Hibernate can use custom SQL for CRUD 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 628. 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<>();
    //Getters and setters are omitted for brevity
}

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.

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.

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

Example 629. 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;
    //Getters and setters are omitted for brevity
}

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

Assuming the following stored procedure:

Example 630. 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 631. Customizing the entity delete statement to use the Oracle stored procedure= instead

@SQLDelete(
    sql =   "{ call sp_delete_person( ? ) } ",
    callable = true
)