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.
As for speed, we’ve found in general, PreparedStatements run much slower then Statement. When using parameters in hibernate criteria and hql, hibernate will use PreparedStatements which generally slows down our application. If you want more performance, use hql without parameters, or native queries without parameters. Just be very mindful of SQL injection attacks which the prepared statements help protect against.
In general, If we use PreparedStatement we will get good performance from precompiled query cached. But here you are telling don’t use prepareStatement .. What is true?
Please help me to create hql from this oracle query – SELECT item, supply, count(0) cnt,sum(case when item =’food’ then 1 else 0 end)itemcnt, sum(case when supply=’ABC’ then 1 else 0 end)supcnt from table1 group by item, supply ;
how to write sum,between,and group by clause in criteria in struts hibernate
Thanks mr mkyong. It works for me and save a lot of time
My query is like this:
EntityManager em = HibernateUtil.getEntityManager();
Query query = em.createQuery(“select r.hiredAgency, count(assessment), sum(r.assessment) ,sum(r.assessment)/(count(assessment)) from TRequest r where r.hiredAgency=: agencyID group by r.hiredAgency”);
query.setParameter(“agencyID”, “1”);
List list = query.getResultList();
Is it right to save the result in List?
how to perform delete operation in one-to-many relationship ?
Why can’t you include the whole code? SO we know what is imported and how session is defined?
Can we call a teradata stored procedure with a “out” parameter using hibernate native sql ? please provide an example ?
hi……
please explain it .
In which scenario we are choosing NatvieSQLQyuereies over a HQLQuerie with example
you can find some more details in the below link,”http://javadomain.in/hibernate-sql-query-sample-program/”
Thanks Bhai. 🙂 Ram bhala kare tumara.
Hi mkyong,
Can you plz answer My query………..
ITS VERY VERY URGENT…….
thanks
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
Thanks a lot for your article, this function should be publicized much more! it saved me *a lot* if time!
Can we use the UNION using the criteria queries?
mk what is the alternative sql or,and clause in hql
what does session.createSQLQuery() returns?
object of Query or SQLQury?
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.
Hi Mkyong:
In Example you have mention like
then you added “addEntity” property.Instead is possible like this
so that we can avoid addEntity rite. Correct if i am wrong.
Regards
Muthu
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.
Well said Arby. JDBC is still our friend … and yes, Hibernate is just a tool. Cheers!
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.
thanks for your invaluable comment, you did bring up a very strong feature in Hibernate – cache mechanism.
I agree… the other exception besides performance in maintainability. Sometimes is just easier implement and clearer for the next guy if you write it as a query.