Hibernate Native SQL Query Example

Native SQL is another technique of performing bulk operations on the data using hibernate

• By using Native SQL, we can perform both select, non-select operations on the data
• In face Native SQL means using the direct SQL command specific to the particular (current using) database and executing it with using hibernate

Advantages and Disadvantages of Native SQL

• We can use the database specific keywords (commands), to get the data from the database
• While migrating a JDBC program into hibernate, the task becomes very simple because JDBC uses direct SQL commands and hibernate also supports the same commands by using this Native SQL
• The main draw back of Native SQL is, some times it makes the hibernate application as database dependent one

If we want to execute Native SQL Queries on the database then, we need to construct an object of SQLQuery, actually this SQLQuery is an interface extended from Query and it is given in ” org.hibernate package ”

In order to get an object of SQLQuery, we need to use a method createSQLQuery() given by session interface.

While executing native sql queries on the database, we use directly tables, column names directly in our command.

Remember, while executing Native SQL Queries, even though we are selecting complete objects from teh 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 Querie so it does’nt know our class name

If we execute the command, always first it will put’s data in ResultSet and from there List

Usage:

123456789

SQLQuery qry = session.createSQLQuery("select * from PRODUCTS");

// Here PRODUCTS is the table in the database...

List l = qry.list();

Iterator it = l.iterator();

while(it.hasNext())

{

Object row[] = (Object[])it.next();

--- ------- }

• while selecting data from the table, even though you are selecting the complete object from the table, in while loop still we type cast into object array only right
• See the above code, we typecast into the object[] arrays right..,  in case if we want to type cast into our POJO class (i mean to get POJO class obj), then we need to go with entityQuery concept
• In order to inform the hibernate that convert each row of ResultSet into an object of the POJO class back, we need to make the query as an entityQuery
• to make the query as an entityQuery, we need to call addEntity() method

Usage:

123456789//We are letting hibernate to know our pojo class too

SQLQuery qry = session.createSQLQuery("select * from PRODUCTs").addEntity(Product.class);

List l = qry.list();

Iterator it = l.iterator();

while(it.hasNext())

{

Product p = (Product)it.next();

--- ------- }

Notes:

• See line number 2, i have been added addEntity(Product.class) at the end, which will let the hibernate to know about our POJO class, so now we can typecast into our POJO class type like what i have done at  line number 7
• And that’s it, this is the total concept on this Native SQL, am not going to give any example on this separately hope you understood the concept
•