Get all updates of Java4s
Follow Us: Email Newsletter

Hibernate Native SQL Query Example

Hibernate » On Jul 14, 2011 { 12 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:

  • 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:

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 Author ♦

Java4s_Author
sivateja@java4s.com
Full Stack( Java/J2EE & UI/UX ) Developer and founder of Java4s - Get It Yourself, A popular Java/J2EE Programming Blog. Sign-up for the Email Newsletter for your daily dose of Java tutorials.

Comments

12 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. Anil Mahadik says:

    Please upload the ruby on rails tutorials on your site.

Name*
Mail*
Website



By posting your answer, you agree to our comments policy.
What would you like to learn today?
Browse these popular technologies
Hibernate spring struts Servlets ajax Log4j jQuery Json Core Java Web Services AngularJs
Most Recent Tutorials
Hibernate_Logo Hibernate Recent Posts Spring_Logo Spring Recent Posts Struts_Logo Struts Recent Posts
Useful Links Current & UpComing Tutorials
Upcoming
  Web Services [ SOAP ], JSP
Contact | About Us | Privacy Policy | Advertise With Us

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