Newsletter

Hibernate Native SQL Query Example

Hibernate » on Jul 14, 2011 { 16 Comments } By Sivateja

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:

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:

//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

​​

You Might Also Like

  ::. About the Author .::

Java4s_Author
- Java/J2EE Full Stack Developer
Founder of Java4s - Get It Yourself, A popular Java/J2EE Programming Blog, Love Java and UI frameworks.
You can sign-up for the Email Newsletter for your daily dose of Java tutorials.

Comments

16 Responses to “Hibernate Native SQL Query Example”
  1. shrawan kumar says:

    Thaks Alot
    Its very easy to understand because you have written it in very simple way.
    Thanks

  2. Mahammad says:

    Hai Sir…

    I am new to hibernate, I am not able to do Native SQL Query Example, could you please give an example…

    I tried several ways but i didn’t…..

    please give an example for Native Sql Query

    except Native Sql Query concept I learned superbly.

    Thank you soooooo much…

  3. Anand says:

    Thank you Sir..for giving such a simple but very effective way to learn advance java technologies…kindly add JSP too ASAP….plzz

  4. Java4s says:

    @shrawan kumar, @Anand, @Mahammad

    Thanks for your response, and Anand we will definitely post Servlet/Jsp tutorials but will take little time, hope you can understand.

    Mahammad : Hope you got it now.

  5. saurabh says:

    Hi i am trying your way in my project but its showing a error on session
    *session can not be resolved what should i do?

  6. mahesh says:

    Hai,
    Iam using hql query to get the data from database.The database contains multiple records,but I am getting only one record multiple times.
    I want all records…..?

    please give the solution for my problem…..

  7. tom says:

    Hey, thanks, very simple and usefull example. This is it i was looking for.

  8. Raju says:

    Really , you explained this Native SQL concept in very simple way that’s why now i understood this concept.
    Sir , Thanks a lot !!!!!

  9. Deep says:

    I am working on big application with big HQL query , I want to see in simple native query. I am using Eclipse IDE . could you please help me how can we convert in simple query ?

    the sample below

    ” and p.futureStatementDate = p.futureStatementDate – :eftDays) or ”
    + ” (bt.subType = ‘PaymentDeclined’ and bt.modificationDate >= p.futureStatementDate – :eftDays ”
    + ” and ((exists (select bt2 from BillingTransaction bt2 where bt2.subType = ‘RecurringPayment’ ”
    + ” and bt2.account = ba and bt2.modificationDate >= p.futureStatementDate – :eftDays)) or ”
    + ” (exists (select bt3 from BillingTransaction bt3 where bt3.transactionNumber = bt.relatedTxNumber”
    + ” and bt3.subType in (‘RecurringPayment’,’DepositPayment’) and bt3.modificationDate >= p.futureStatementDate – :eftDays))))))”
    + ” and exists (select i.account from Invoice i “”

  10. Katyayani Adda says:

    I am very much thankful to this site.
    I am learning the topics very easily because the content is in that way, means self explanatory.

  11. Prasad says:

    When we can use addScalar() method. what is the difference between addScalar() and addEntity() ?

  12. Please upload the ruby on rails tutorials on your site.

  13. Manish Soni says:

    really!! very good explanation thank you so much..

  14. uday says:

    Its very easy and simple way to learn your concepts…

    Thanks
    uday

  15. uday says:

    Could you provide a CRUD Example using [hibernate(criteria + annotations) + Spring + Restful web services + Ajax] with explation

    Thanks
    Uday

Name*
Mail*
Website



By posting your answer, you agree to our comments policy.
Most Recent Posts from Top Categories
Spring Boot Hibernate Spring
Contact | About Us | Privacy Policy | Advertise With Us

© 2010 - 2018 Java4s - Get It Yourself.
The content is copyrighted to Sivateja Kandula and may not be reproduced on other websites.