Hibernate Native SQL Queries

We can use native QL queries in Hibernate to use any database specific feature.

SQLQuery:

Scalar queries:

String sqlstmt = "SELECT name, salary FROM EMPLOYEE";
SQLQuery query = session.createSQLQuery(sqlstmt);
List objList = query.list();

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

We can avoid the overhead of using ResultSetMetadata.

session.createSQLQuery("SELECT name FROM EMPLOYEE")
 .addScalar("NAME", Hibernate.STRING) 
 //Specified columns return type

Entity queries:

We can specify entity, so that entity object will get returned instead of Object.

String sql = "SELECT name FROM EMPLOYEE";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Employee.class);
List emps = query.list();

Returning multiple entities:

sess.createSQLQuery("SELECT e.*, he.*  FROM EMPLOYEE e, 
              HEADEMPLOYEE he WHERE e.EMP_ID = he.ID")
 .addEntity("e", EMPLOYEE.class)
 .addEntity("he", HEADEMPLOYEE.class)

Parameters: We can create parameterized queries:

String sqlstmt = "SELECT name, salary FROM EMPLOYEE where name = ?";
SQLQuery query = session.createSQLQuery(sqlstmt);
query.addEntity(Employee.class);
 //passing parameter and executing query
List objList = query.setString(0, "Ankit").list();

Instead of positional parameter we can have named parameter:

String sqlstmt = 
"SELECT name, salary FROM EMPLOYEE where name = :emp_name";
SQLQuery query = session.createSQLQuery(sqlstmt);
query.addEntity(Employee.class);
 //passing parameter and executing query
List objList = query.setString(emp_name, "Ankit").list();