Hibernate native SQL queries examples

In Hibernate, HQL or criteria queries should be able to let you 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.

Native SQL queries example

Hibernate provide a createSQLQuery method to let you call your native SQL statement directly.

1. In this example, you tell Hibernate to return you a Stock.class, all the select data (*) will match to your Stock.class properties automatically.

Query query = session.createSQLQuery(
"select * from stock s where s.stock_code = :stockCode")
.addEntity(Stock.class)
.setParameter("stockCode", "7277");
List result = query.list();

2. In this example, Hibernate will return you an Object array.

Query query = session.createSQLQuery(
"select s.stock_code from stock s where s.stock_code = :stockCode")
.setParameter("stockCode", "7277");
List result = query.list();

Alternative, you also can use the named query to call your native SQL statement. See Hibernate named query examples here.

Hibernate’s generated SQL statement is slow !?

I do not agreed on the statement “Hibernate’s generated SQL statement is slow”. Often times, i found out this is because of the developers do not understand the table relationship well, and did some wrong table mappings or misuse the fetch strategies. It will cause Hibernate generated some unnecessary SQL statements or join some unnecessary tables… And developers like to take this excuse and create their own SQL statement to quick fix the bug, and didn’t aware of the core problem will causing more bugs awaiting.

Conclusion

I admit sometime the native SQL statement is really more convenient and easy than HQL, but, do think carefully why you need a native SQL statement? Is this really Hibernate cant do it? If yes, then go ahead ~

P.S In Oracle database, i more prefer to use native SQL statement in many critical performance queries, because i need to put the “hint” to improve the Oracle query performance.

Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

  • kondalu

    hi……
    please explain it .
    In which scenario we are choosing NatvieSQLQyuereies over a HQLQuerie with example

  • http://javadomain.in Diva

    you can find some more details in the below link,”http://javadomain.in/hibernate-sql-query-sample-program/”

  • dev

    Thanks Bhai. :) Ram bhala kare tumara.

  • satish

    Hi mkyong,
    Can you plz answer My query………..

    ITS VERY VERY URGENT…….

    thanks
    Satish

  • satish

    I have a sql query as shown below:

    Select pb.partnerId, pb.brandId, count(*) as availableCoupons From partnerNbrand as pb join coupons as c on c.brandId=pb.brandId where c.languageId=1 and c.brandId=2 and c.couponIds in (1,2,3,4,6) group by pb.paetnerId, pb.brandId

    I’m very New to hibernate, can you plz provide an equivalent hibernate—criteria query.

    thanks in advance for any help.

    Thanks Satish

  • http://www.codref.com ddf

    Thanks a lot for your article, this function should be publicized much more! it saved me *a lot* if time!

  • Nick

    Can we use the UNION using the criteria queries?

  • Sarbjit singh

    mk what is the alternative sql or,and clause in hql

  • sarvesh kumar

    what does session.createSQLQuery() returns?
    object of Query or SQLQury?

    • Saood

      Query is an Interface. Parent of SQLQuery. session.createSQLQuery() returns object of SQLQuery, which we can also assign to Query.
      Thus Query query = session.createSQLQuery();
      and
      SQLQuery sqlQuery = session.createSQLQuery();

      are correct.

  • Muthu Krishnan.S

    Hi Mkyong:

    In Example you have mention like

    select * from stock s

    then you added “addEntity” property.Instead is possible like this

    select s.* from stock s

    so that we can avoid addEntity rite. Correct if i am wrong.

    Regards
    Muthu

  • Arby

    There is the Hello World. And there is Real World. Both parties – those who always want to let Hibernate generate SQL and those who always want to write their own SQL – have people still leaving in their Hello World. The Hello World created by the Books they reference.

    Those developers who are experts at SQL will never look at Hibernate. Those who don’t understand SQL suddenly start wielding their brand new weapon a.k.a. Hibernate like a WMD. One needs to use right tool for the job. If people want to use Hibernate simply to get it on their resume, they might be successful in the job market, but their project will fail.

    One needs to know when and how much to use from a framework such as Hibernate. Framework authors are not honest to admit drawbacks of framework. Fact of the matter, is frameworks do not need to have drawbacks if they don’t try to do too much. Why have Hibernate let one do stuff that is known to be inefficient? The best way to correct mistake is to prevent one from happening in the first place.

    • Jim

      Well said Arby. JDBC is still our friend … and yes, Hibernate is just a tool. Cheers!

  • Pingback: Hibernate Tutorials | Tutorials()

  • Pingback: How to embed Oracle hints in Hibernate query | Hibernate()

  • http://www.openscope.net Ant

    Hibernate is great at a lot of things, but its something that needs to be used appropriately. One of its strengths is its interior caching mechanism, it attempts to buffer the database pings from within. From what I understand when you end up going the native query route, you miss out on this feature. Not to mention you loose the cross database vendor portability. But then again, as you said – sometimes if you hit that wall where you need to squeeze as much juice as you can, then a native query might be the only way to go. I’m glad the Hibernate authors at least left the door open.

    • http://www.mkyong.com mkyong

      thanks for your invaluable comment, you did bring up a very strong feature in Hibernate – cache mechanism.