Native SQL queries in Hibernate

Posted on 29-04-2013 13:38 by graham
This tutorial describes how to execute native SQL queries in Hibernate. This might be useful when Hibernate criteria queries don't meet our needs.

Returning raw Objects


Let's say we have a database table User with columns id, name and age. To retrieve records from such table using SQL, we use the following code:
SQLQuery query = session.createSQLQuery("SELECT id, name, age FROM users");
List records = query.list();

List<User> users = new ArrayList<User>();

// each element of the records list is an array of type object (Object[])
for (Object[] record : records)
{
// store the data into some object
User user = new User();
user.setId(record.get(0));
user.setName(record.get(1));
user.setAge(record.get(2));

users.add(user);
}


We created an SQLQuery object and executed the query by calling the list() method, which returned a list of object arrays.

Since Hibernate doesn't know anything about the contents of the query before running it, it must use ResultSetMetadata to figure out the number and types of returned columns before listing them. This might slow down the execution a little, so if you want to help Hibernate and make your code faster, you can (and should) explicitly indicate the number and data type of fields you are retrieving. Below is a description how this can be achieved.

Indicating the number and type of retrieved columns


To explicitly indicate the number and data type of fields you are retrieving, use the addScalar method on the query:
SQLQuery query = session.createSQLQuery("SELECT id, name, age FROM users");
query.addScalar("id", Hibernate.INTEGER);
query.addScalar("name", Hibernate.STRING);
query.addScalar("age", Hibernate.INTEGER);
List records = query.list();

List<User> users = new ArrayList<User>();

// each element of the records list is an array of type object (Object[])
for (Object[] record : records)
{
// store the data into some object
User user = new User();
user.setId(record.get(0));
user.setName(record.get(1));
user.setAge(record.get(2));

users.add(user);
}

Mapping results to objects


That's all nice but it requires us to manually parse the results. Hibernate can take care of this for us if we indicate what data type is being retrieved. To do this, use the addEntity() method:
SQLQuery query = session.createSQLQuery("SELECT id, name, age FROM users");
query.addEntity(User.class);
List records = query.list();

List<User> users = new ArrayList<User>();

// each element of the records list is an array of type object (Object[])
for (Object record : records)
{
users.add((User)record);
}

Adding anonymous parameters to the query


Of course, in most cases you will want to add some parameters to your query. You can do this either using the universal setParameter method or type-specific methods such as setInteger, setBoolean etc. The second method is preferred because it reduces the overhead Hibernate would need to infer the data types.

If you want to use data type specific methods, do something like this:
SQLQuery query = session.createSQLQuery("SELECT id, name, age FROM users WHERE id = ? AND name ILIKE ?");
query.addEntity(User.class);
query.setInteger(0, 3221);
query.setString(1, "john%");
List records = query.list();

To use the universal setParameter method:
SQLQuery query = session.createSQLQuery("SELECT id, name, age FROM users WHERE id = ? AND name ILIKE ?");
query.addEntity(User.class);
query.setParameter(0, 3221);
query.setParameter(1, "john%");
List records = query.list();

Adding named parameters


In the example above, parameters where anonymous because they had no names and where all identitied by the question mark. If we want to give them names for easier identification, we use a notation with colons:
SQLQuery query = session.createSQLQuery("SELECT id, name, age FROM users WHERE id = :id AND name ILIKE :name");
query.addEntity(User.class);
query.setInteger("id", 3221);
query.setString("name", "john%");
List records = query.list();

Then we can refer to the parameters names instead of indices while setting their values.

Summary


Native SQL queries is a backdoor if Criteria queries refuse to do what we want. They should not be overused because they are less readable and more bug prone than Criteria queries.
Comments

 

Add comment

Has this tutorial been helpful to you? Or do you see anything wrong? We appreciate your opinion!
Your comment:
Show formatting hints
HTML is disallowed, but in your text you can use the following markup
  • [code][/code] for a block of code
  • [tt][/tt] for inline code
  • [link]link href|link anchor[/link] for links
  • [b][/b] for bold text
Email:
+ Ask a question
If you have a technical question related to programming and computers, ask it here. Other users will help you solve it!
Unanswered questions
Share your knowledge by helping others solve their problems