Iterator i = session.createQuery(
"select item.id, item.description, bid.amount " +
"from Item item join item.bids bid " +
"where bid.amount > 100"
)
.list()
.iterator();
while ( i.hasNext() ) {
Object[] row = (Object[]) i.next();
Long id = (Long) row[0];
String description = (String) row[1];
BigDecimal amount = (BigDecimal) row[2];
// ... show values in a report screen
}

from Hiberntate in Action Chapter 7

7.5.4 Native SQL queries
We can think of two good examples why you might use native SQL queries in Hibernate:
HQL provides no mechanism for specifying SQL query hints, and it also
doesn’t support hierarchical queries (such as the Oracle CONNECT BY clause). We
suppose that you’ll stumble on other examples.
In these (relatively rare) cases, you’re free to resort to using the JDBC API
directly. However, doing so means writing the tedious code by hand to transform a
JDBC ResultSet to an object graph. You can avoid all this work by using Hibernate’s
built-in support for native SQL queries.
You only need to learn one trick. An SQL query result may return the state of
multiple entity instances in each row and even the state of multiple instances of the
same entity. You need a way to distinguish between the different entities. Hibernate
uses a naming scheme for the result column aliases to correctly map column values
284 CHAPTER 7
Retrieving objects efficiently
to the properties of particular instances. You wouldn’t want the details of this naming
scheme to be exposed to the user; instead, native SQL queries are specified with
placeholders for the column aliases.
The following native SQL query shows what these placeholders—the names
enclosed in braces—look like:
String sql = "select u.USER_ID as {uzer.id},"
+ " u.FIRSTNAME as {uzer.firstname},"
+ " u.LASTNAME as {uzer.lastname} from USERS u";
Each placeholder specifies an HQL-style property name. When we call this query in
code, we must provide the entity class that is referred to by uzer in the placeholders.