In previous section,we learnt to use the Hibernate Query Language (HQL) that focused on the business entities instead of the vendor dependent syntax. This does not mean that we are bound to use the HQL throughout the Hibernate application if we want to perform some database operations. You may express a query in SQL,using createSQLQuery() and let Hibernate take care of the mapping from result sets to objects. Note that you may at any time call session.connection() and use the JDBC Connection directly. If you chose to use the Hibernate API,you must enclose SQL aliases in braces.

Let's see how to use the SQL queries by adding the functionality to find a user by user id using native SQL in our UserManager. Add the following method in UserManager class.

public User getUserById(long userId)
{
    org.hibernate.SQLQuery query = session.createSQLQuery(""
       "SELECT u.user_id as {u.userId},u.first_name as {u.firstName},u.last_name as {u.lastName},u.age as {u.age},u.email as {u.email} "
       "FROM USERS {u} WHERE  {u}.user_id=" userId "");
    query.addEntity("u",User.class);
    java.util.List l = query.list();
    java.util.Iterator users = l.iterator();
    User user = null;
    if(users.hasNext())
        user = (User)users.next();
    return user;
}

In the above code,the result type is registered using query.addEntity("u",User.class) so that Hibernate knows how to translate the ResultSet obtained by executing the query. Also note that the aliases in the query are placed in braces and use the same prefix "u" as registered in query.addEntity(...). This way Hibernate knows  how to set attributes of the generated object. Also this way we can eliminate the confusion of attributes when more than one tables are used in query and both of them contain the same column name. query.list() actually executes the query and returns the ResulSet in the form of list of objects. We knew that this query is going to return only one object,so we returned only the first object if available.

Let's test this code by adding the following method in the TestClient.

public void testFindByNativeSQL(UserManager manager)

{

User user = manager.getUserById(2);

System.out.println("User found using native sql with ID=" user.getUserId() "\n"  

"\tName=" user.getLastName() "\n"  

"\tEmail=" user.getEmail()  

"");

java.util.Iterator numbers = user.getPhoneNumbers().iterator();

while(numbers.hasNext()) {

PhoneNumber phone = (PhoneNumber)numbers.next();

System.out.println("\t\tNumber Type:" phone.getNumberType() "\n"  

"\t\tPhone Number:" phone.getPhone());

}

}

Add the call to this method in main. To do this,add the following line in main.
client.testFindByNativeSQL(manager);

Be sure to pass a valid user id to this method that exists in the database with few valid phone numbers. This code will result in the following output.

User found using native sql with ID=2

Name=Elison

Email=john@visualbuilder.com

Number Type:Office

Phone Number:934757

Number Type:Home

Phone Number:934757

For more details on using native SQL in Hibernate,see Hibernate documentation.