Hibernate provides an option to execute 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.
Complete guide/tutorial to Hibernate 5 ORM at Hibernate ORM
Let's learn more about Hibernate native SQL queries with examples snippets.
Note that from Hibernate 5.2 createSQLQuery() method is deprecated. The Javadoc of the deprecated createSQLQuery(String) describes what to use instead:
@Deprecated
default NativeQuery createSQLQuery(java.lang.String queryString)
Deprecated. (since 5.2) use createNativeQuery(String) instead
Create a NativeQuery instance for the given SQL query string.
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.
Table of contents
- Scalar queries
- Entity queries
- Handling associations and collections
- Returning multiple entities
- Returning DTOs (Data Transfer Objects)
- Handling inheritance
- Parameters
1. Scalar queries
The most basic SQL query is to get a list of scalars (column) values.
Hibernate native query selecting all columns
List<Object[]> persons = session.createNativeQuery(
"SELECT * FROM Person" )
.list();
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():
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 array, 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.
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];
}
2. Entity queries
The above queries were all about returning scalar values, basically returning the raw values from the ResultSet. In this section, we will look into how to express Hibernate native query for selecting entities.
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.
JPA native query selecting entities with the explicit result set
List<Person> persons = entityManager.createNativeQuery(
"SELECT id, name, nickName, address, createdOn, version " +
"FROM Person", Person.class )
.getResultList();
Example 555. 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();
3. 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.
Hibernate native query selecting entities with a many-to-one association
The below example 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.
List<Phone> phones = session.createNativeQuery(
"SELECT id, phone_number, phone_type, person_id " +
"FROM Phone" )
.addEntity( Phone.class )
.list();
Hibernate native query selecting entities with a joined many-to-one association
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.
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() );
}
The SQL statements generated by the above code:
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.
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).
4. 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.
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:
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();
5. Returning DTOs (Data Transfer Objects)
It is possible to apply a ResultTransformer to native SQL queries, allowing it to return non-managed entities.
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();
6. 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.
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();
7. Parameters
Native SQL queries support positional as well as named parameters.
Hibernate native query with parameters
List<Person> persons = session.createNativeQuery(
"SELECT * " +
"FROM Person " +
"WHERE name like :name" )
.addEntity( Person.class )
.setParameter("name", "J%")
.list();
Complete guide/tutorial to Hibernate 5 ORM at Hibernate ORM
GitHub Repository
The complete source code of this article available on my GitHub Repository - https://github.com/RameshMF/Hibernate-ORM-Tutorials
Conclusion
In this article, we have learned Hibernate support for native SQL queries with examples snippets.
You can learn more about Hibernate ORM Framework at Hibernate Tutorial
Comments
Post a Comment
Leave Comment