Native SQL Queries
HQL or Criteria queries able to execute almost any SQL query you want. However, many developers are complaint about the Hibernate’s generated SQL statement is slow and more prefer to generated their own SQL (native SQL) statement.
Hibernate provide a createSQLQuery() method to let you call your native SQL statement directly. Your application will create a native SQL query from the session with the createSQLQuery() method on the Session interface.:
public SQLQuery createSQLQuery(String sqlString) throws HibernateException
-
By using Native SQL, we can perform both SELECT & NON- SELECT operations on the data
-
We can use the database specific keywords (commands), to get the data from the database
-
The main drawback of Native SQL is it makes the hibernate application as database dependent.
Working with Native SQL Queries
Even though we are selecting complete objects from the database we need to type cast into Object[] array only, not into our pojo class type, because we are giving direct table, column names in the Native SQL Query so it doesn’t know our class name
SQLQuery qry = session.createSQLQuery("select * from EMPLOYEE");
// Here EMPLOYEE is the table in the database...
List l = qry.list();
Iterator it = l.iterator();
while(it.hasNext())
{
Object row[] = (Object[])it.next();
--- -------
}
In the above code, we typecast into the object[] , if we want to type cast into our POJO class , then we need to go with entityQuery concept. to make the query as an entityQuery, we need to call addEntity() method
//We are letting hibernate to know our pojo class too
//We are letting hibernate to know our pojo class too
SQLQuery q=session.createSQLQuery("select *from EMPLOYEE").addEntity(EmployeeBo.class);
List l = q.list();
Iterator it = l.iterator();
while(it.hasNext())
{
EmployeeBo s = (EmployeeBo)it.next();
--- -------
}
Example
public class NativeSqlDemo {
public static void main(String[] args) {
// 1.Load Configuration
Configuration cfg = new Configuration();
cfg.configure("hibernate.cfg.xml");
// 2.Create Session
SessionFactory sf = cfg.buildSessionFactory();
Session session = sf.openSession();
System.out.println("1.Simple Native SQL\n ========================");
SQLQuery query1 = session.createSQLQuery("select *from EMPLOYEE");
List list1 = query1.list();
Iterator it1 = list1.iterator();
while (it1.hasNext()) {
Object[] ob = (Object[]) it1.next();
System.out.println(ob[0] + ", " + ob[1] + ", " + ob[2]);
}
System.out.println("2.Native SQL with entityQuery\n ==========");
SQLQuery query2 = session.createSQLQuery("select *from EMPLOYEE");
query2.addEntity(EmployeeBo.class);
List list2 = query2.list();
Iterator it2 = list2.iterator();
while (it2.hasNext()) {
EmployeeBo bo = (EmployeeBo) it2.next();
System.out.println(bo.getEid() + ", " + bo.getName() + ", " + bo.getAddress());
}
session.close();
sf.close();
}
}
1.Simple Native SQL
========================
1, SATYA, VIJAYAWADA
2, SURYA, HYDERABAD
2.Native SQL with entityQuery
========================
1, SATYA, VIJAYAWADA
2, SURYA, HYDERABAD