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. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
22 Comment threads
5 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
24 Comment authors
rajasekharFoxNishanthyogeshgovind Recent comment authors
newest oldest most voted
Nick
Guest
Nick

Can we use the UNION using the criteria queries?

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.

ddf
Guest
ddf

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

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.

Fox
Guest
Fox

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.

rajasekhar
Guest
rajasekhar

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?

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 ;

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