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
Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.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

  • http://javadomain.in Diva

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

  • 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

    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

    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 Source)
    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 Source)
    Hibernate: select hostelleav0_.Id as col_0_0_ from HostelLeaveMaster hostelleav0_ where hostelleav0_.SID=110000001
    Hibernate: select hostelleav0_.Id as Id0_, hostelleav0_.SID as SID814_0_, hostelleav0_.tName as tName814_0_, hostelleav0_.tClass as tClass814_0_, hostelleav0_.RoomNo as RoomNo814_0_, hostelleav0_.nFloorNo as nFloorNo814_0_, hostelleav0_.tBuildingName as tBuildin7_814_0_, hostelleav0_.LeaveFrom as LeaveFrom814_0_, hostelleav0_.LeaveTo as LeaveTo814_0_, hostelleav0_.LeaveReason as LeaveRe10_814_0_, hostelleav0_.LeavePlace as LeavePlace814_0_, hostelleav0_.nschoolId as nschoolId814_0_, hostelleav0_.dtentry as dtentry814_0_, hostelleav0_.dmodify as dmodify814_0_, hostelleav0_.tdeleteStatus as tdelete15_814_0_, hostelleav0_.tseen as tseen814_0_ from HostelLeaveMaster hostelleav0_ where hostelleav0_.Id=?
    Hibernate: select hostelleav0_.Id as col_0_0_ from HostelLeaveMaster hostelleav0_ where hostelleav0_.tseen like ‘N’
    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.iterate(SessionImpl.java:916)
    at org.hibernate.impl.QueryImpl.iterate(QueryImpl.java:41)
    at org.apache.jsp.HostelLeaveStudentInfo_jsp._jspService(HostelLeaveStudentInfo_jsp.java:107)
    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 Source)

  • http://liquoroutpost.tumblr.com/ 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

    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?

  • Pingback: HQL Query Examples | F@tih Durmus's WebBlog()

  • http://www.renganathan.co.nr 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

      not sure whether work or not

  • http://None 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

    Hi,

    I have written this query

  • Rahul

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

  • Jacob

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

    • Jacob

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

  • 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

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

  • 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

  • http://decaptcher.org/author/admin/ 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

    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.

    • http://google 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

    thank you
    easy to study HQL ..

  • http://is.gd/kdaAts 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!

  • http://community.atom.com/Post/voip/03EFBFFFF02431179000801585E21?ref_title=rickylloyds820&ref_url=http%3a%2f%2fcommunity.atom.com%2fprofile%2f07911430202431179000102431179%2fContent%2fPost%2fMyPo 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.

  • http://www.blogabond.com/CommentView.aspx?CommentID=406423 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.

    • http://www.mkyong.com mkyong

      Thanks for your kind words :)

  • Mohanakannan

    HI
    we are using the postgres polygon data type how can i insert the polygon(which is belongs to map latitude ,longitude)through hibernate

  • fouding

    Dear all,

    I have a question: how to get the id of the new record(if the id is auto-incremented) when using createQuery(“insert into table values()”).executeUpdate()?

    From your code:

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

    If the Stock table is sequence-generated, how to get the new reocrd’s id?

    Thanks,
    Fouding

    • http://www.mkyong.com mkyong

      Hibernate’s Query is let developer execute native SQL (pure SQL statement) in database. To get back the affected record id, you need to issue another find query.

      Normally, people use session.save() to save an object, as it can get back the new record id automatically.

      • Naeem

        What if another find/select query return many results and in multi threaded environment you can’t select MAX record from the list.

        However session.save() can be used when we have a proper Hibernate Entity.

      • Ehsan

        Hi Mkyong
        I am very impresed from mkyong.i have one ques :Hhow to rolleback in hibernate.Example:Like i am inserting any record in database after insertion want to rollback that record.
        Thanks
        Ehsan Khan9711295282

  • http://devlab.pl devlab
    • http://www.mkyong.com mkyong

      Link is updated, hibernate documentation changed too fast ~

      • kjkgyu

        that link is broken stil

  • chary1112004

    I read reference about hibernate but still didn’t know how to map between field which reference to object with property in POJO.

    In Oracle, I have Event and Location. In type of Event has location_ref field which reference to object of Location. Matter is how config in hibernate-mapping and property in POJO to use hql or default function of hibernate. If you know, let say here. Thanks Mkyong!

    //Type Location
    CREATE TYPE Location_objtyp AS OBJECT
    (
    id INTEGER,
    name VARCHAR2(255)
    );

    //Table Location
    CREATE TABLE Location_objtab OF Location_objtyp
    (
    id PRIMARY KEY
    ) OBJECT IDENTIFIER IS PRIMARY KEY;

    //Type Event
    CREATE TYPE Event_objtyp AS OBJECT
    (
    id INTEGER,
    location_ref REF Location_objtyp,
    name NVARCHAR2(100)
    );

    //Table Event
    CREATE TABLE Event_objtab OF Event_objtyp
    (
    id PRIMARY KEY
    ) OBJECT IDENTIFIER IS PRIMARY KEY;

    ALTER TABLE Event_objtab ADD (SCOPE FOR(location_ref) IS Location_objtab);

  • salmon

    Hi Yong,
    I have a requirement, where i need to save 5000 records at a time. currently for saving 1000 records it is saving more than 1 minute. in such a case for saving 5000 records it may take around 5 minutes. i am adding the code what i have written. please suggest how can i reduce save time. i am using merge() for saving..if i dont use merge, if i use saveOrUpdate() or save() i am getting nonuniqueobject exception. please suggest me….

    for (LoadGsCmpTape loadGsCmpTape : cmpTapeList) {
    if(loadGsCmpTape.getCmpSetId()== null || loadGsCmpTape.getCmpSetId()== 0L){
    loadGsCmpTape.setCmpSetId(currentSetId);
    }
    this.getHibernateTemplate().merge(loadGsCmpTape);

    }

    Thanks,
    Salmon

    • http://www.mkyong.com mkyong

      May i know where are these 5k records come from? adhoc or frequent request? Some ideas flash in my mind…

      1. Try set auto commit after 100, 200.. bulk inserted, until you find the optimal value.
      2. If dump from one db to another db, issue a SQL command like “insert into….select”.
      3. Upgrade hardware :)

      After all, 5k records have to take some time. Hope help.

      • salmon

        Hi Yong,
        we fetch 5k records from one table and we need to save to another table. plz can u share code to optimize saving time.

        Thanks,
        Salmon

        • http://www.mkyong.com mkyong

          In this case, pick the idea #2, shoot a “insert into…select” SQL command instead of using hibernate. If you still insist want to do it in code, try idea #1, send a db commit every 200, 500 or xxx records inserted to free your server and database resources, tune it to a optimal point that suits your hardware limit.

          • salmon

            Hi Yong,
            The problem is the table has 32 columns.individually we cannot specify…the reason why i used merge() is it can do both insert/update.some times i need to update also.

          • http://www.mkyong.com mkyong

            Then try tune the db commit for every xxx records inserted or updated, the performance should be increase..a bit.

            Sometime, the code is not really the root caused, the use case or the tool you choose are. If this is a data migration task or once time ad-hoc request, really not worth to create a complex Java app to cater it, just use normal SQL or store procedure can done within minutes.

  • chary1112004

    Dear! But i didn’t see hibernate.cfg.xml config to use hsql! Can you give it here?

    • chary1112004

      When using hql with Oracle, don’t need config anything out of default!

  • Deepak Lalchandani

    hi Mkyong, can u illustrate more examples on HQL and native SQL.

  • Yonathan

    hai, i’m trying to use HQL Update Query Example but get this error :

    cannot find symbol
    symbol:method setParameter(java.lang.String,java.lang.String)
    location:class javax.management.Query

    what is correct package i should import.
    i’m using import javax.management.Query;

    thank u for your answer

    • Mailarus

      Because this is HQL, the package you should be using is org.hibernate.Query, not javax.management.Query. Hope this helps.

      • sara

        Thanks Mailarus it helped :)

  • Pingback: Hibernate Tutorials()

  • Naveed

    very easy and useful to start hql. thnx

  • JuserNt

    thanks … useful as initiation to HQL

  • Amit

    Awesome ! Your articles are very easy to understand and explains the concept very well. I wonder if you have come through inheritance strategies and hibernate caching?

    • http://www.mkyong.com mkyong

      Thanks for the suggestions, i will write those in future :)

      • DSI

        Please mk yong help me at this :
        I wont to delete a row from table Which have a Many to Many relation with other table the Consol show this error ” cannot delete or update a parent row a foreign key constraint fails ” Give me an exemple to delete in same situation …
        Thinks ……