bertrand wrote:
My two cents... (not tested ;) - why don't you just do:
Code:
query.uniqueResult()
Bertrand, you'll have to show me what you mean; I tried 2 or 3 variations of query.uniqueResult() and couldn't get it to work.
Simon,
This has been on my mind because I'm trying to get my head around what Hibernate is all about. Coming from a database world and learning Java, it's been great to be able to manipulate data with Java idioms, but I still want to revert to database and sql at times. I set up an Oracle8i table with basically a number ID and a varchar2 description.
First I wondered if doing the deletes from Hibernate using a session.delete(Object) would be waaaay slower than using sql. I inserted 10000 rows into the table and ran a sql delete. The delete consistantly takes about 10 seconds. Then I iterated over the objects and deleted them one at time and it took over a minute:
Code:
Transaction t = session.beginTransaction();
Iterator masterIterator = session.iterate("from MasterTable as m where m.masterId >= 5");
while (masterIterator.hasNext()) {
session.delete((MasterTable)masterIterator.next());
}
t.commit();
But here's the interesting part: I then created and deleted the rows in one session so that the objects would still be cached. The delete time dropped to about 14 seconds:
Code:
lgr.info("before create in makeThenDelete");
Session session = sessionFactory.openSession();
Transaction t = session.beginTransaction();
for (int i=5; i<=10005; i++) {
MasterTable m = new MasterTable();
m.setMasterId(new BigDecimal(i));
m.setMasterDescription("Master Record" + i);
session.save(m);
}
t.commit();
lgr.info("after create in makeThenDelete");
// now delete them
session.delete("from MasterTable as m where m.masterId >= 5");
t.commit();
session.close();
lgr.info("after delete in makeThenDelete");
Then I tried doing a session.delete(String) with a hard coded string, again in the same session. Now were down to 11 or 12 seconds:
Code:
lgr.info("before create in makeThenDeleteWithSql");
Session session = sessionFactory.openSession();
Transaction t = session.beginTransaction();
for (int i=5; i<=10005; i++) {
MasterTable m = new MasterTable();
m.setMasterId(new BigDecimal(i));
m.setMasterDescription("Master Record" + i);
session.save(m);
}
t.commit();
lgr.info("after create in makeThenDeleteWithSql");
Query q = session.createSQLQuery("delete from master_table {masterTable} where {masterTable}.master_id >=5", "masterTable", MasterTable.class );
q.uniqueResult();
// now delete them
t.commit();
lgr.info("after delete in makeThenDeleteWithSql");
session.close();
So, I guess what I'm saying that Hibernate would do a pretty good job unless you're talking about large batch type operations that DBA's or integrators encounter.
Back to your question. I can really see a use for a delete using a named query. I started playing around with it and well, I don't know if this is overkill, but what about the following:
First I wrote a method like this:
Code:
private Type [] toTypeArray(Object [] object) {
Type [] type = new Type[object.length];
for (int i=0; i<object.length; i++) {
type[i] = (Type)object[i];
}
return type;
}
With this handy method in hand I can then do something like:
Code:
List params = new ArrayList();
List types = new ArrayList();
Query q = session.createQuery("from MasterTable as m where m.masterId >= :masterId");
q.setParameter("masterId", new Integer(50), Hibernate.INTEGER);
// populate my lists when I set the parameters
params.add(new Integer(50));
types.add(Hibernate.INTEGER);
session.delete(q.getQueryString(), params.toArray(), toTypeArray(types.toArray()));
t.commit();
What do you think? Just for reference this method took 13-16 seconds to delete 10,000 rows.
Login
FAQ
Search
