hibernate by 71 comments

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

71 comments on “Hibernate Query examples (HQL)”

  3. query = ” select TIMESTAMP ?1 AT TIME ZONE ‘UTC’ AT TIME ZONE ?2″
    how i convert to hql. it shows error

    error = “org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: TIMESTAMP near line 1, column 9 [ select TIMESTAMP ?1 AT TIME ZONE ‘UTC’ AT TIME ZONE ?2 ]”

    Reply

    1. 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”.

      Reply

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

    Reply

  6. 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 table called USERS_ROLES.
    So how should I write my insert-bulk statement since I have this collection?
    Thanks in advance.

    Reply

  8. 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.

    Reply

  9. 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

    Reply

  10. 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.

    Reply

  11. I have made many to many demo as suggested in the article https://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
    Hibernate:
    drop table if exists mkyong_hibernate_demo.category
    Hibernate:
    drop table if exists mkyong_hibernate_demo.stock
    Hibernate:
    drop table if exists stock_category
    Hibernate:
    create table mkyong_hibernate_demo.category (
    CATEGORY_ID integer not null auto_increment,
    NAME varchar(10) not null,
    `DESC` varchar(255) not null,
    primary key (CATEGORY_ID)
    )
    Hibernate:
    create table mkyong_hibernate_demo.stock (
    STOCK_ID integer not null auto_increment,
    STOCK_CODE varchar(10) not null unique,
    STOCK_NAME varchar(20) not null unique,
    primary key (STOCK_ID)
    )
    Hibernate:
    create table stock_category (
    CATEGORY_ID integer not null,
    STOCK_ID integer not null,
    primary key (STOCK_ID, CATEGORY_ID)
    )
    Hibernate:
    alter table stock_category
    add index FK97929007DE707CAF (CATEGORY_ID),
    add constraint FK97929007DE707CAF
    foreign key (CATEGORY_ID)
    references mkyong_hibernate_demo.category (CATEGORY_ID)
    Hibernate:
    alter table stock_category
    add index FK97929007FA55A265 (STOCK_ID),
    add constraint FK97929007FA55A265
    foreign key (STOCK_ID)
    references mkyong_hibernate_demo.stock (STOCK_ID)
    11:30:17,557 INFO SchemaExport:406 – HHH000230: Schema export complete
    11:30:17,580 WARN ConfigContext:250 – HSEARCH000075: Configuration setting hibernate.search.lucene_version was not specified, using LUCENE_CURRENT.
    Hibernate many to many (XML Mapping)
    Hibernate:
    insert
    into
    mkyong_hibernate_demo.stock
    (STOCK_CODE, STOCK_NAME)
    values
    (?, ?)
    11:30:17,692 TRACE BasicBinder:83 – binding parameter [1] as [VARCHAR] – 7052
    11:30:17,693 TRACE BasicBinder:83 – binding parameter [2] as [VARCHAR] – PADINI
    Hibernate:
    insert
    into
    mkyong_hibernate_demo.category
    (NAME, `DESC`)
    values
    (?, ?)
    11:30:17,723 TRACE BasicBinder:83 – binding parameter [1] as [VARCHAR] – INVESTMENT
    11:30:17,723 TRACE BasicBinder:83 – binding parameter [2] as [VARCHAR] – INVESTMENT COMPANY
    Hibernate:
    insert
    into
    mkyong_hibernate_demo.category
    (NAME, `DESC`)
    values
    (?, ?)
    11:30:17,725 TRACE BasicBinder:83 – binding parameter [1] as [VARCHAR] – CONSUMER
    11:30:17,725 TRACE BasicBinder:83 – binding parameter [2] as [VARCHAR] – CONSUMER COMPANY
    Hibernate:
    insert
    into
    stock_category
    (STOCK_ID, CATEGORY_ID)
    values
    (?, ?)
    11:30:17,742 TRACE BasicBinder:83 – binding parameter [1] as [INTEGER] – 1
    11:30:17,743 TRACE BasicBinder:83 – binding parameter [2] as [INTEGER] – 1
    Hibernate:
    insert
    into
    stock_category
    (STOCK_ID, CATEGORY_ID)
    values
    (?, ?)
    11:30:17,788 TRACE BasicBinder:83 – binding parameter [1] as [INTEGER] – 1
    11:30:17,788 TRACE BasicBinder:83 – binding parameter [2] as [INTEGER] – 2
    Done
    update Stock set stockName=:name where stockCode=:code
    Hibernate:
    update
    mkyong_hibernate_demo.stock
    set
    STOCK_NAME=?
    where
    STOCK_CODE=?
    11:30:17,898 TRACE BasicBinder:83 – binding parameter [1] as [VARCHAR] – mystockName
    11:30:17,899 TRACE BasicBinder:83 – binding parameter [2] as [VARCHAR] –
    0
    from Stock where STOCK_CODE=:code
    from Stock where STOCK_CODE=:code
    Hibernate:
    select
    stock0_.STOCK_ID as STOCK1_2_,
    stock0_.STOCK_CODE as STOCK2_2_,
    stock0_.STOCK_NAME as STOCK3_2_
    from
    mkyong_hibernate_demo.stock stock0_
    where
    STOCK_CODE=?
    11:30:17,911 TRACE BasicBinder:83 – binding parameter [1] as [VARCHAR] – 7052
    11:30:17,914 TRACE BasicExtractor:72 – Found [1] as column [STOCK1_2_]
    11:30:17,915 TRACE BasicExtractor:72 – Found [7052] as column [STOCK2_2_]
    11:30:17,915 TRACE BasicExtractor:72 – Found [PADINI] as column [STOCK3_2_]
    PADINI

    Please guide me why doesn’t query.executeUpdate does not work in case of many-to-many mapping.
    Thanks in advance.

    Reply

  14. 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.

    Reply

  15. 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?

    Reply

  16. 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)

    Reply

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

      and the query should be

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

      Reply

  17. 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.

    Reply

  18. 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?

    Reply

  19. 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.

    Reply

  20. 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.

    Reply

  24. |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.

    Reply

  26. 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

    Reply

  27. 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!

    Reply

  28. 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.

    Reply

  31. 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.

    Reply

  32. 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.

    Reply

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

    Reply

  34. 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

    Reply

    1. 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.

      Reply

      1. 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.

        Reply

      2. 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

        Reply

  36. 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);

    Reply

  37. 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

    Reply

    1. 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.

      Reply

      1. 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

        Reply

        1. 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.

          Reply

          1. 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.

          2. 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.

  40. 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

    Reply

  43. 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?

    Reply

      1. 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 ……

        Reply

Leave a Comment

Your email address will not be published. Required fields are marked *