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
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.
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?
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.
not sure whether work or not
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.
Hi,
I have written this query
hai i want to retreive 5th highest salary from the table through hibernate.how it is possible without native query?
Hi,
Could you in HQL query for displaying the all the tables in schema
Hi,
Could you help me in preparing HQL query for displaying all the tables in the schema.
|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.
Hi Mykong,
your blog is very impressive.
But you missed out to check the styles for About Us.
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
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!
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.
i think this is correct
“from ticket a where a.ticket:=48615 and a.id:=(from ticket b where b.ticket=a.ticket)”
thank you
easy to study HQL ..
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!
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.
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.
Thanks for your kind words :)
HI
we are using the postgres polygon data type how can i insert the polygon(which is belongs to map latitude ,longitude)through hibernate
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
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.
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.
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://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.htm – Oops! This link appears broken.
Link is updated, hibernate documentation changed too fast ~
that link is broken stil
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);
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
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.
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
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.
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.
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.
Dear! But i didn’t see hibernate.cfg.xml config to use hsql! Can you give it here?
When using hql with Oracle, don’t need config anything out of default!
hi Mkyong, can u illustrate more examples on HQL and native SQL.
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
Because this is HQL, the package you should be using is org.hibernate.Query, not javax.management.Query. Hope this helps.
Thanks Mailarus it helped :)
very easy and useful to start hql. thnx
thanks … useful as initiation to HQL
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?
Thanks for the suggestions, i will write those in future :)
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 ……