Hibernate Query examples (HQL)

Hibernate created a new language named Hibernate Query Language (HQL), the syntax is quite similar to database SQL language. The main difference between is HQL uses class name instead of table name, and property names instead of column name.

HQL is extremely simple to learn and use, and the code is always self-explanatory.

1. HQL Select Query Example

Retrieve a stock data where stock code is “7277”.


Query query = session.createQuery("from Stock where stockCode = :code ");
query.setParameter("code", "7277");
List list = query.list();

Query query = session.createQuery("from Stock where stockCode = '7277' ");
List list = query.list();

2. HQL Update Query Example

Update a stock name to “DIALOG1” where stock code is “7277”.


Query query = session.createQuery("update Stock set stockName = :stockName" +
    				" where stockCode = :stockCode");
query.setParameter("stockName", "DIALOG1");
query.setParameter("stockCode", "7277");
int result = query.executeUpdate();

Query query = session.createQuery("update Stock set stockName = 'DIALOG2'" +
    				" where stockCode = '7277'");
int result = query.executeUpdate();

3. HQL Delete Query Example

Delete a stock where stock code is “7277”.


Query query = session.createQuery("delete Stock where stockCode = :stockCode");
query.setParameter("stockCode", "7277");
int result = query.executeUpdate();

Query query = session.createQuery("delete Stock where stockCode = '7277'");
int result = query.executeUpdate();

4. HQL Insert Query Example

In HQL, only the INSERT INTO … SELECT … is supported; there is no INSERT INTO … VALUES. HQL only support insert from another table. For example


"insert into Object (id, name) select oo.id, oo.name from OtherObject oo"; 

Insert a stock record from another backup_stock table. This can also called bulk-insert statement.


Query query = session.createQuery("insert into Stock(stock_code, stock_name)" +
    			"select stock_code, stock_name from backup_stock");
int result = query.executeUpdate();

The query.executeUpdate() will return how many number of record has been inserted, updated or deleted.

Reference

  1. Hibernate 3.3.2 query documentation

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
Luís
Guest
Luís

The Select example produces this warning: “List is a raw type. References to generic type List should be parameterized”

coolbeans
Guest
coolbeans

The meaning is you should parameterize the class or POJO object the list is for. For example, if you have an ‘Employee’ table and an ‘Employee’ entity, make sure to import that class and then reconfigure your List object as “List”.

coolbeans
Guest
coolbeans

sorry,the site recognized my code as HTML tags. Should look like this: List with no spaces.

coolbeans
Guest
coolbeans

List<Employee>

Daniel Quintos López
Guest
Daniel Quintos López

Hi all… somebody knows if after the “query.executeUpdate” or “query.list”, we have to close the hibernate session?? or Hibernate does it automatically?? :).

Tr?n Hi?p
Guest
Tr?n Hi?p

we must close the hibernate session, it’s necessary.

Tr?n Hi?p
Guest
Tr?n Hi?p
Alessandra P.
Guest
Alessandra P.
Hi! I have a problem with my code because I have a many-to-many association and I’m trying to figure out a problem that I also explained here http://stackoverflow.com/questions/26164603/hibernate-many-to-many-bidiractional-association-save-only-id-in-join-table . I am now trying to use a bulk-insert statement which is perfectly explained in your post, but my problem is that I’m working with a collection: I need to assign the id of a row from another table (called Role) to one of the collection’s rows (the other table is called User and it has a set of roles). The association between the two tables is made possible by a third… Read more »
bandoj
Guest
bandoj
karthik
Guest
karthik

Hi Kong,
Just i want to update a single column for inactive a record i want to update the table so which is recommended way i am using Spring JPA hibernate.

chandu
Guest
chandu

List workBeanList = (List) hibernateTemplate.find(“SELECT employeeId , TIMEDIFF( MAX( ?) , MIN( ? ) ) FROM WorkBean GROUP BY employeeId “,);

how to give timestamp values to the above query ,
and if the result came how to show the result value to the front-end Jsp

Sandeep K
Guest
Sandeep K

Hi,

I have two threads in Java executing org.hibernate.Query.executeUpdate() method at the same time on same table. Is there any chance that one will be overwritten by another thread value. DB I am using is Microsoft SQL Server.

From my analysis first thread to call exceuteUpdate was taking lock until it is finished. But I am facing with a problem where both threads have finished successfully(no exception in logs) but only one threads value has been updated to table.

Pravesh Gupta
Guest
Pravesh Gupta
I have made many to many demo as suggested in the article http://www.mkyong.com/hibernate/hibernate-many-to-many-relationship-example/ . Now i am implementing hql to update stockName of Stock table using query.executeUpdate. It does not give me error or any exception however in case of only single table it reflects the changes. My code is this:- private static void updateQueryDemo(Session session) { session.beginTransaction(); Query query = session.createQuery(“update Stock set stockName=:name where stockCode=:code”); System.out.println(query.getQueryString()); query.setParameter(“code”, “”); query.setParameter(“name”, “mystockName”); int status = query.executeUpdate(); System.out.println(status); session.getTransaction().commit(); } My Hibernate Trace is :- Hibernate: alter table stock_category drop foreign key FK97929007DE707CAF Hibernate: alter table stock_category drop foreign key FK97929007FA55A265… Read more »
DIEK
Guest
DIEK

Mortal Kombat Yong IS MAH HERO

Harish Govindarajan
Guest
Harish Govindarajan

Mine too :D

María Alejandra Gómez Casani
Guest
María Alejandra Gómez Casani

:D

Diva
Guest
Diva

you can find some more details in the below link,”http://javadomain.in/hibernate-select-query-example-in-java/”

Raju
Guest
Raju

Query: I want a query that should return results with serial_number. SO here is my query in mysql.
Select @a:=@a+1 as serial_number,linkNeId as linkNeId from (select @a:=0) initvars, LinkNe.
But I am facing problem when I execute with hibernate session even though it works fine in mysql console. Is there any way to do it in hibernate?

Hibernate exception – line 1:47: unexpected char: ‘@’ — QueryTranslatorImpl.java — hibernate-core.3.6.7

Thanks for you time.
Raju.

Feri
Guest
Feri

I would have one more question – namely how is it possible in Hibernate to write a query using LIKE keyword in a similar manner to the one you presented in the beginning of this lesson? I mean in fact how can you protect yourself against HQL injection in such a case?

Akshay Patil
Guest
Akshay Patil
I have an error while executing update query /**************************/ String st=request.getParameter(“id”); Query q = session1.createQuery(“Update HostelLeaveForm set tseen =’Y’ where SID =”+st); int result= q.executeUpdate(); /*******************************/ Error is java.lang.IndexOutOfBoundsException: Index: 0, Size: 0 java.lang.IndexOutOfBoundsException: Index: 0, Size: 0 at java.util.ArrayList.rangeCheck(Unknown Source) at java.util.ArrayList.get(Unknown Source) at org.hibernate.hql.ast.HqlSqlWalker.postProcessUpdate(HqlSqlWalker.java:390) at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:164) at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:189) at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:130) at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83) at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:425) at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:880) at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:861) at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89) at org.apache.jsp.HostelLeaveStudentInfo_jsp._jspService(HostelLeaveStudentInfo_jsp.java:100) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:388) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:313) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:260) at javax.servlet.http.HttpServlet.service(HttpServlet.java:717) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489) at java.lang.Thread.run(Unknown… Read more »
Jay
Guest
Jay

your error is not in this code. i thought that is in another code.

rod
Guest
rod

i think you should query.setParameter(“st”, st);

and the query should be

session1.createQuery(“Update HostelLeaveForm set tseen =’Y’ where SID = :st);

visit the site
Guest
visit the site

I’ve been surfing the web more than 3 hours today, and this is the best article I’ve come across. I’m a article fiend so I’ve actually seen a lot already.Personally I think, if all website owners and bloggers made as good content as youhave, the internet would bea lot more useful than ever before.

Pankaj Kumar Mishra
Guest
Pankaj Kumar Mishra

Dear Mkyong,
I am implementing hibernate in a APP where I get frequent request from DB.
This request is retrieved using on condition
ex. ISPICK=1 and When Data is taken into bean I issue merge() method to update the same record in DB by ID

Question is that whether its is good to use HQL and issue executeUpdate() or use merge() to just update single column?

Which one is fast in this case?

trackback
HQL Query Examples | F@tih Durmus's WebBlog
Renganathan.P
Guest
Renganathan.P

Dear mkyong,
I want HQL or MySQL query for following criteria,
we have three textboxes in form. 1st one is zipcode. 2nd one is city. Last one is state .
when we are enter the zipcode, automatically appear the city and state in its textbox from datbases.

User
Guest
User

not sure whether work or not

Mak
Guest
Mak

Hello YOng i want to fire one conditional Hibernate Query. That means if suppose the sum of something is equal to count(which will also come from Hibernate Query) then only the next query must gets fired.
Please help me in this.

Suraj Prakash
Guest
Suraj Prakash

Hi,

I have written this query

Rahul
Guest
Rahul

hai i want to retreive 5th highest salary from the table through hibernate.how it is possible without native query?

Jacob
Guest
Jacob

Hi,
Could you in HQL query for displaying the all the tables in schema

Jacob
Guest
Jacob

Hi,
Could you help me in preparing HQL query for displaying all the tables in the schema.

Chuck Fuhrman
Guest
Chuck Fuhrman

|I don’t generally interrupt amazing conversations like this with my own personal issues, but I really need the help of whoever in a position to lend me a hand. I’m conducting some research employing http://lawncaremaintenance.net/ and I was wondering if anyone here has employed them in the past. I am interested in both the positive and negative components of their business. Please get back to me as fast as possible for this is essential.Thank you.

Nagarjun
Guest
Nagarjun

Hi Mykong,
your blog is very impressive.
But you missed out to check the styles for About Us.

Arpit
Guest
Arpit

Hello,

I have problem in defining alias in HQL., it looks like using the “as” in the SELECT clause of a HQL query cause the “, expected in SELECT” exception, no matter you use a join or not.
plz help me.
Thanks in advance

decaptchers
Guest
decaptchers

My brother suggested I would possibly like this website. He used to be totally right. This post truly made my day. You cann’t believe just how a lot time I had spent for this info! Thank you!

Madristaa
Guest
Madristaa

SELECT * FROM TICKET A WHERE A.TICKET = 48615
AND A.ID = ( SELECT MAX(ID) FROM TICKET B WHERE B.TICKET = A.TICKET)

How to convert above query to hibernate?

Can you plz help for the same.

simham
Guest
simham

i think this is correct

“from ticket a where a.ticket:=48615 and a.id:=(from ticket b where b.ticket=a.ticket)”

minah
Guest
minah

thank you
easy to study HQL ..

Portland Insulation
Guest
Portland Insulation

Awesome read. I just passed this onto a buddy who was doing some research on that. He just bought me lunch as I found it for him! So let me rephrase: Thanx for lunch!

callshop software
Guest
callshop software

Purely to follow up on the up-date of this theme on your site and would want to let you know simply how much I liked the time you took to write this beneficial post. Inside the post, you actually spoke regarding how to definitely handle this issue with all comfort. It would be my personal pleasure to get some more ideas from your web-site and come as much as offer others what I have benefited from you. I appreciate your usual wonderful effort.

Ebay Wholesale
Guest
Ebay Wholesale

I wish to get across my affection for your generosity supporting individuals who really want help on this content. Your real commitment to passing the solution all through has been unbelievably functional and has in every case made associates much like me to arrive at their objectives. Your own useful guideline denotes a lot a person like me and additionally to my office workers. Thanks a lot; from everyone of us.