By the help of the express queries in the native SQL dialect of the database. In other words this is useful if we utilize database-specific features such as query hints or the CONNECT keyword in Oracle. It also provides a clean migration path from a direct SQL/JDBC based application to Hibernate. By the help of the Hibernate3 we can specify handwritten SQL, including stored procedures, for all create, update, delete, and load operations.

Returning non-managed entities in SQL

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

sess.createSQLQuery("SELECT NAME, BIRTHDATE FROM STUDENTS").setResultTransformer(Transformers.aliasToBean(StudentDTO.class))

This query specified:

the SQL query string
a result transformer

The above query will return a list of StudentDTO which has been instantiated and injected the values of NAME and BIRTHNAME into its corresponding properties or fields.

Using a SQLQuery

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

Handling inheritance in SQL:

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

Parameters in SQL:

Native SQL queries support positional as well as named parameters:

Query query = sess.createSQLQuery("SELECT * FROM STUDENTS WHERE NAME like ?").addEntity(Student.class);
List pusList = query.setString(0, "Pus%").list();
query = sess.createSQLQuery("SELECT * FROM STUDENTS WHERE NAME like :name").addEntity(Cat.class);
List pusList = query.setString("name", "Pus%").list();

Use of Alias and property references in SQL

In most cases the above alias injection is needed. For queries relating to more complex mappings, like composite properties, inheritance discriminators, collections etc., than we 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.

(追記) (追記ここまで)

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 the collection {[aliasname].*} {coll.*}

Use of Entity queries in SQL

All the above queries tells us specially scalar values, basically returning the "raw" values from the resultset. In the following code shows how to get entity objects from a native sql query via addEntity().

sess.createSQLQuery("SELECT * FROM STUDENT").addEntity(Student.class);
sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM STUDENT").addEntity(Student.class);

By the help of this query we found:

the SQL query string
the entity returned by the query

Assuming that Student is mapped as a class with the columns ID, NAME and BIRTHDATE the above queries will both return a List where each element is a Student entity. If the entity is mapped with a many-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. The additional columns will automatically be returned when using the * notation, but we prefer to be explicit as in the following example for a many-to-one to a Person:

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, PERSON_ID FROM PERSON").addEntity(Person.class);

Above code allow student.getPerson() to function properly.

Use of Handling associations and collections in SQL

We have already discussed is that in the hibernate every thing is possible which we want to do. So if It is possible to eagerly join in the Person to avoid the possible extra roundtrip for initializing the proxy. This is done via theaddJoin() method, which allows you to join in an association or collection.

sess.createSQLQuery("SELECT c.ID, NAME, BIRTHDATE, PERSON_ID, D_ID, D_NAME FROM STUDENTS c, PERSONS d WHERE c.PERSON_ID = d.D_ID").addEntity("student", Student.class).addJoin("student.person");

With the help of the above code of the example the returned Student's will have their dog property fully initialized without any extra roundtrip to the database. But we have to remember that added an alias name ("student") 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. if the Student had a one-to-many to Person instead.

sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE, D_ID, D_NAME, STUDENT_ID FROM STUDENTS c, PERSONS d WHERE c.ID = d.STUDENT_ID").addEntity("student", Student.class).addJoin("student.persons");

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

Previous Home Next

(追記) (追記ここまで)

R4R Topics