Hibernate - SPLessons

Chapter 17

Hibernate SQL Query

Hibernate SQL Query

Native Hibernate SQL Query is also one of the technique to perform an operation like Selection operations and non selection operations, Native SQL Query meanwhile selecting the data from the database, the execution will be done with Hibernate.

Hibernate application creates a Native Hibernate SQL Query uses the create SQLQuery () method. To execute the Native Hibernate SQL Query, object of SQLQuert needs to be created. Tables and columns will be directly given while executing the queries. Following are the mandatory points in Native SQL Query.

Following is the syntax to create an SQL Query object by calling the createSQLQuery () of Session interface.

SQLQuery query = session.createSQLQuery(”   SQL command  “);

Executing Select operations

While reading a full entity from Database by using Native SQL, Hibernate internally converts each row of ResultSet objects into objects [] and returns the list of objects. In SQL, there will be no POJO class name.

 SQLQuery query = session.createSQLQuery("  select * from IToolsInfo ");
                               List list=query.list();

To convert each row as POJO class object, then addEntity() has to be called. In addEntity() parameter is passed to the Pojo class object of the class.

 SQLQuery query=session.createSQLQuery("  select * from IToolsInfo ");
                                query.addEntity("IToolsInfo.class");
                               List list=query.list();

addEntity() present in query object.

To read the partial entity or more entities from a database, Hibernate internally stores a ResultSetMetaData.

 SQLQuery query=session.createSQLQuery("select employeeName,employeeSalary from IToolsInfo ");
                  query.addEntity("IToolsInfo.class");
                  List list=query.list();

This list object contains object[] array of ResultSetMetaData. Convert the specified columns of type, by calling the addScalar().

 SQLQuery query=session.createSQLQuery("select employeeName,employeeSalary from IToolsInfo ");
                 query.addSclar("employeeName", HIBERNATE.STRING); 
                 query.addSclar("employeeSalary", HIBERNATE.STRING);
                       List list=query.list();

Executing Non-Select operations

When executing a non-select Queries(curd operations) on a database, transaction object has to begin.

  SQLQuery query = session.createSQLQuery("update IToolsInfo set salary=8000 where deptno=1");
                  Transaction transaction=session.beginTransaction();
                           int i=query.executeUpadate();
                            transaction.commit();

Summary

  • Native SQL Query makes Hibernate as database dependent.
  • addEntity() method is used to build the query as entityQuery.
  • To get the instance of SQLQuery, createSQLQuert() method will be used.