Hibernate and Native SQL

And though our main focus is always Hibernate, it is worth mentioning that you can indeed issue native SQL queries through the Hibernate Session using the Session's createSQLQuery method. You simply pass in a valid SQL String, and Hibernate will return the results in a java.util.List.

Now one thing to note about native SQL queries is that what gets returned in each element of the List is simply an Object array, containing the datatype to which the queried columns map, as defined by the JPA annotations of the class. Furthermore, with a SELECT * query, we would need to know the order of the columns in the database so we can cast the incoming data properly.

The following is an example of a native SQL query that goes against a User database table with id (Integer), email, name and password fields:

public static void main(String args[]) {
String sql = "SELECT * FROM USER";
Session session = HibernateUtil.beginTransaction();
SQLQuery query = session.createSQLQuery(sql);
List users = query.list();
for (int i = 0; i < users.size(); i++) {
Object[] o = (Object[]) users.get(i);
System.out.print(((Integer) o[0])); //id
System.out.print(((String) o[1])); //email
System.out.print(((String) o[2])); //name
System.out.println(((String) o[3]));//pass
}
}