Hibernate Native SQL Query Example

Welcome to the Hibernate Native SQL Query example tutorial. We looked into Hibernate Query Language and Hibernate Criteria in earlier articles, today we will look into Hibernate Native SQL query with examples.

Hibernate SQL Query

Hibernate provide option to execute native SQL queries through the use of SQLQuery object. Hibernate SQL Query is very handy when we have to execute database vendor specific queries that are not supported by Hibernate API. For example query hints or the CONNECT keyword in Oracle Database.

For normal scenarios, Hibernate SQL query is not the recommended approach because we loose benefits related to hibernate association and hibernate first level cache.

I will use MySQL database and same tables and data setup as used in HQL example, so you should check out that first to understand the tables and corresponding model classes mapping.

Hibernate Native SQL Example

For Hibernate Native SQL Query, we use Session.createSQLQuery(String query) to create the SQLQuery object and execute it. For example, if you want to read all the records from Employee table, we can do it through below code.

When we execute above code for the data setup we have, it produces following output.

Notice that list() method returns the List of Object array, we need to explicitly parse them to double, long etc. Our Employee and Address classes have following toString() method implementations.

Notice that our query is not returning Address data, whereas if we use HQL query "from Employee", it returns the associated table data too.

Hibernate SQL Query addScalar

Hibernate uses ResultSetMetadata to deduce the type of the columns returned by the query, from performance point of view we can use addScalar() method to define the data type of the column. However we would still get the data in form of Object array.

The output generated will be same, however we will see slight performance improvement when the data is huge.

Hibernate Native SQL Multiple Tables

If we would like to get data from both Employee and Address tables, we can simply write the SQL query for that and parse the result set.

For above code, the output produced will be like below.

Hibernate Native SQL Entity and Join

We can also use addEntity() and addJoin() methods to fetch the data from associated table using tables join. For example, above data can also be retrieved as below.

{[aliasname].*} is used to return all properties of an entity. When we use addEntity() and addJoin() with join queries like above it returns both the objects, as shown above.

Output produced by above code is like below.

You can run both the queries in the mysql client and notice that the output produced is same.

Hibernate Native SQL Query with Parameters

We can also pass parameters to the Hibernate SQL queries, just like JDBC PreparedStatement. The parameters can be set using the name as well as index, as shown in below example.

Output produced by above code would be:

That’s all for a brief introduction of Hibernate SQL Query, you should avoid using it unless you want to execute any database specific queries.

By admin

Leave a Reply

%d bloggers like this: