It might look like this is turning into a Hibernate blog… maybe it’s just a phase I’m going through. This last week I ran into a place in some code where I wanted to write a query to delete a number of rows from the database. Deleting a single row using Hibernate is simple; get an instance of the persistent object you want to delete (ie: a Book, etc..) and an instance of the Session object and then called the delete(Object) method:
long bookid = 12;
Session sess = HibernateFactory.currentSession();
Object book = sess.load(Book.class, bookid);
sess.delete(book);
Easy. Now, if you wanted to delete all the computer books for some reason, you’d have to first query the database using Hibernate to get a Collection of books, then iterate over the books, calling delete(Object object) on each one.
Collection books = BookFactory.getComputerBooks();
Session sess = HibernateFactory.currentSession();
for (Iterator i = books.iterator(); i.hasNext();) {
Book book = (Book)i.next();
sess.delete(book);
}
It’s easier than that though. The Session object has a delete(String query) method that I originally thought took a standard SQL ‘delete from table where…’ statement. After fussing with it for a bit, I read the documentation which says: “Delete all objects returned by the query. Return the number of objects deleted.”. Aha! Instead of calling delete() on each one, you use a SQL (or a HQL) statement that fetches the objects and then pass the results directly to the delete method:
Session sess = HibernateFactory.currentSession();
sess.delete("select book FROM org.mycompany.Book as book where type = 'technical'");
Just another example of how Hibernate makes it easier to work with relational databases.
If you’re using Hibernate with Spring, there’s a slick HibernateTemplate.deleteAll(Collection) method.
Hibernate’s great and all, but I have a beef with their delete – that I have to materialize an object before I can remove it is rather repugnant. Why require 2 different queries when a single query will suffice? If Hibernate needs it to track IDs for the cache, then why not just list the IDs rather than materializing all of the objects? It’s a performance mess.
I’d like to see how easy you find it to delete an element from a collection association and get this persisted. E.g object P has list collection l. Load P out including a fetch of l and then detach. Now, the app removes a bunch of elements from the transient l collection and we want to synchronize those deletes with the database. Ideally save(p) would notice the l deletes. But no. In fact, I am still struggling!
In this case, Hibernate does not make any easier.
stmt.executeUpdate(“delete FROM book where type = ‘technical'”);
This statment results in a delete for each line… It’s very inefficiente if You have many lines to delete…
Thanks for this blog. I was looking (Googleing) for this exact answer and had not been able to find it.
session.load(Book.class, id) will only load the data that is in the session how about the ones in the database.
session.load will load the book if it is uncommited data
You don’t have to pull an object out of the db in order to remove it; and it can be done en masse.
http://www.hibernate.org/hib_docs/v3/reference/en/html/batch.html#batch-direct
True Dennis, but the the fact that one should be able to use the HibernateTemplate (esp. in Spring) to simply delete(Object), assuming Object is a mapped class w/ an ID cannot be overlooked. I also question all of the object “integrity” checking for the DELETE. Seems absolutely ridiculous. Anyone tyr to use the batch update? — The reference “documentation” doesn’t even match up w/ what one must do syntactically to make it work. I love hibernate, but I also really freakin’ hate it. It’s that evil 80% makes life simple, 20% makes life hell.
But session.delete() method won’t take a SQL query. Is there a way to batch delete using SQL ?
But session.delete() method won’t take a SQL query. Is there a way to delete multiple records using SQL ?
with hibernate3 and the support for oql delete all this pre-load pains are gone. except now the db must be designed very carefully with database level cascades in place. this is because hibernate no longer loads the entire object graph and walks it to determine where to trigger the cascade deletions.
Isn`t a `delete from …` more efficent? 🙂 Ok, I know that hibernate keeps up the cache consistency, but deleting them one by one is a pain imho.
But how can I detach an Object. If I try to delete a collection of objecs I always get a errormessage (two open session)
Collection col = tldDao.findByExample(tld);
Iterator it = col.iterator();
while( it.hasNext()) {
Topleveldomain tldTmp = (Topleveldomain) it.next();
tldDao.delete(tldTmp);
}
According to the hibernate v3 JavaDocs, the delete() function has been deprecated. “consider using HQL delete statements”
If u use HibernateFactory to delete,then how can i import HibernateFactory class.what lidrary file i have to use?
You can do this with Spring in one single line, by using HQL statements:
getHibernateTemplate().bulkUpdate(“delete from net.avantic.gesforc.model.items where type = ?”, type);
how do i pull this off in hibernate?
delete from EmployeeGroup.employees link where link.id = id
where “employees” is a collection mapping (a table (EMPLOYEE_GROUP_ID, EMPLOYEE_ID) but no orm class exists)
When I call session.delete(o), it will delete object o from database. But what happen to object o which in heap memory. Does hibernate make it available for GC or what?
Hi,
I want to delete the parent table record, subsequently the child table records also need to delete.(I have primary & foreign key relationship b/w these two tables.)
getHibernateTemplate().delete(countryObj);
I am trying to delete using the above. But its trying to delete the parent object without deleting the child first.
Parent:
@OneToMany(mappedBy=”countryCode”)
@Cascade org.hibernate.annotations.CascadeType.ALL, org.hibernate.annotations.CascadeType.DELETE_ORPHAN})
private Set tblStatesCollection;
Child:
@ManyToOne
@OnDelete(action=OnDeleteAction.CASCADE)
@JoinColumn(name=”COUNTRY_CODE”)
private TblCountries countryCode;
Can anyone please suggest how to achieve this auto deletion of child, in case of parent deletion?
I have the same issue, i have a simple many to many relation in the tables user and role storing the references in the
user_role table, and i want that when i delete a role get deleted the user_role reference automatically.
i try whit in the Role class mapping but i cannot get it to work properly
does anyone have a solution??
in the last post what i try is to use syncronize table user_role in the Role class mapping but i cannot get it to work properly
does anyone have a solution??
Isn’t your mapping the wrong way around? Have a look at the JPA spec for a good overview of how the “inverse” and “owning” sides relate to the foreign keys on the underlying table.
JPA spec = a very quick read and worth it 🙂
Maybe you could try replacing :
@Cascade org.hibernate.annotations.CascadeType.ALL, org.hibernate.annotations.CascadeType.DELETE_ORPHAN})
by
@Cascade ({org.hibernate.annotations.CascadeType.ALL, org.hibernate.annotations.CascadeType.DELETE_ORPHAN})
?
hello,
i have one situation where i want delete rows from one table and for this i have written query,
‘DELETE FROM dist_details WHERE id = (SELECT a.details_id FROM dist_mapping a WHERE a.dist_id=11 AND a.dist_id NOT IN (SELECT dist_id FROM cpn_dist_mapping)
AND a.details_id NOT IN (SELECT a.details_id FROM dist_mapping a INNER JOIN dist_mapping b ON a.details_id = b.details_id WHERE a.dist_id b.dist_id)) ‘
it works fine in mysql. but now i want to convert this to HQL. I’m working with HQL for the first time.. i tried but it shows exception.. please help. sorry for my bad english.