Main Tutorials

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 Author

author image
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

Subscribe
Notify of
25 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Nick
11 years ago

Can we use the UNION using the criteria queries?

yogesh
9 years ago

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

govind
9 years ago

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

Tet
9 years ago

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
9 years ago

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

lulu
10 years ago

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

Naveen
10 years ago

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

kondalu
10 years ago

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

Diva
10 years ago

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

dev
10 years ago

Thanks Bhai. 🙂 Ram bhala kare tumara.

ddf
11 years ago

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

Sarbjit singh
11 years ago

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

sarvesh kumar
11 years ago

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

Saood
10 years ago
Reply to  sarvesh kumar

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
12 years ago

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
12 years ago

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
12 years ago
Reply to  Arby

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

Ant
14 years ago

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.

Arthur Carroll
10 years ago
Reply to  Ant

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
5 years ago

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
5 years ago
Reply to  Fox

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
8 years ago

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
11 years ago

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

ITS VERY VERY URGENT…….

thanks
Satish

satish
11 years ago

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