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.

About the Author

author image
mkyong
Founder of Mkyong.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

Leave a Reply

avatar
newest oldest most voted
Nishanth
Guest
Nishanth

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 ;

yogesh
Guest
yogesh

how to write sum,between,and group by clause in criteria in struts hibernate

govind
Guest
govind

Thanks mr mkyong. It works for me and save a lot of time

Tet
Guest
Tet

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?

nitesh
Guest
nitesh

how to perform delete operation in one-to-many relationship ?

lulu
Guest
lulu

Why can’t you include the whole code? SO we know what is imported and how session is defined?

Naveen
Guest
Naveen

Can we call a teradata stored procedure with a “out” parameter using hibernate native sql ? please provide an example ?

kondalu
Guest
kondalu

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

Diva
Guest
Diva

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

dev
Guest
dev

Thanks Bhai. :) Ram bhala kare tumara.

satish
Guest
satish

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

ITS VERY VERY URGENT…….

thanks
Satish

satish
Guest
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

ddf
Guest
ddf

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

Nick
Guest
Nick

Can we use the UNION using the criteria queries?

Sarbjit singh
Guest
Sarbjit singh

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

sarvesh kumar
Guest
sarvesh kumar

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

Saood
Guest
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
Guest
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
Guest
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… Read more »
Jim
Guest
Jim

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

trackback
Hibernate Tutorials | Tutorials

[…] Hibernate native SQL queries examples A guide to show how to use native SQL in Hibernate. […]

trackback
How to embed Oracle hints in Hibernate query | Hibernate

[…] More Native SQL queries examples. […]

Ant
Guest
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… Read more »
Arthur Carroll
Guest
Arthur Carroll

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.