Native SQL Queries

We can also express queries in the native SQL dialect of your database. This is useful if you want to 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.

Using a SQLQuery:

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 (values).

session.createSQLQuery("Select * FROM Emps").list();
session.createSQLQuery("Select ID, NAME, BIRTHDATE 
       FROM Emps").list();

These will return a List of Object arrays (Object[]) with scalar values for each column in the CATS table. Hibernate will use ResultSetMetadata to deduce the actual order and types of the returned scalar values.