I am using hibernate2.1 with Jboss3.2.1 in my project. I don't have any problems using HSQL. The only problem is bulk delete. I know there is no way to do bulk delete through the hibernate framework. So I used session.connection() to get direct JDBC connection and executed delete scripts. I checked MYSQL database, records had been deleted successfully. The trick thing is I still can get those deleted records using HSQL until I restart JBoss.

I heard someone said
"Beware that using direct JDBC can lead to all kinds of data staleness issues - I won't give you any guarantees on that. Better know what you are doing."

Does anybody know how to use direct JDBC connection correctly with hibernate?

Thanks a lot

DF

Do you use second-level cache (for objects and queries)?

But you know you can do session.delete( [query string] ) in Hibernate, don't you? What kinds of deletes do you want to do that are not possible through this?

Perhaps a better phrase would be "no way to do efficient bulk deletes" as session.delete does



Same goes for updates. Use JDBC for both if deleting/updating a lot of records on a regular basis.

Thank you for your reply.

I just want to do bulk delete in the best way.

This is my hibernate.cfg.xml

<session-factory>
<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hibernate.connection.url">jdbc:mysql:/localhost/test</property>
<property name="hibernate.connection.username">root</property>
<property name="hibernate.connection.password"></property>
<property name="dialect">net.sf.hibernate.dialect.MySQLDialect</property>
<property name="show_sql">true</property>
<property name="transaction.factory_class">
net.sf.hibernate.transaction.JDBCTransactionFactory
</property>
<property name="hibernate.hbm2ddl.auto">update</property>
<property name="cache.use_query_cache">false</property>


</session-factory>

I didn't turn on second-level cache option. But in logger file, I still can see
14:43:37,322 INFO [SettingsFactory] cache provider: net.sf.ehcache.hibernate.Provider
Is that the problem?
------------------------------------------------------------------------------
14:43:37,262 INFO [Configuration] Configured SessionFactory: null
14:43:37,262 INFO [Configuration] processing one-to-many association mappings
14:43:37,262 INFO [Configuration] processing one-to-one association property re
ferences
14:43:37,262 INFO [Configuration] processing foreign key constraints
14:43:37,272 INFO [Dialect] Using dialect: net.sf.hibernate.dialect.MySQLDialec
t
14:43:37,272 INFO [SettingsFactory] Maximim outer join fetch depth: 1
14:43:37,272 INFO [SettingsFactory] Use outer join fetching: false
14:43:37,272 INFO [DriverManagerConnectionProvider] Using Hibernate built-in co
nnection pool (not for production use!)
14:43:37,272 INFO [DriverManagerConnectionProvider] Hibernate connection pool s
ize: 1
14:43:37,272 INFO [DriverManagerConnectionProvider] using driver: com.mysql.jdb
c.Driver at URL: jdbc:mysql:/localhost/test
14:43:37,272 INFO [DriverManagerConnectionProvider] connection properties: {use
r=root, password=}
14:43:37,272 INFO [TransactionFactoryFactory] Transaction strategy: net.sf.hibe
rnate.transaction.JDBCTransactionFactory
14:43:37,272 INFO [TransactionManagerLookupFactory] No TransactionManagerLookup
configured (in JTA environment, use of process level read-write cache is not re
commended)
14:43:37,322 INFO [SettingsFactory] Use scrollable result sets: true
14:43:37,322 INFO [SettingsFactory] Use JDBC3 getGeneratedKeys(): true
14:43:37,322 INFO [SettingsFactory] Optimize cache for minimal puts: false
14:43:37,322 INFO [SettingsFactory] echoing all SQL to stdout
14:43:37,322 INFO [SettingsFactory] Query language substitutions: {no='N', true
=1, yes='Y', false=0}
14:43:37,322 INFO [SettingsFactory] cache provider: net.sf.ehcache.hibernate.Pr
ovider
14:43:37,322 INFO [Configuration] instantiating and configuring caches
14:43:37,322 INFO [SessionFactoryImpl] building session factory
14:43:37,432 INFO [SessionFactoryObjectFactory] no JNDI name configured
14:43:37,432 INFO [Dialect] Using dialect: net.sf.hibernate.dialect.MySQLDialec
t
14:43:37,432 INFO [DriverManagerConnectionProvider] Using Hibernate built-in co
nnection pool (not for production use!)
14:43:37,432 INFO [DriverManagerConnectionProvider] Hibernate connection pool s
ize: 1
14:43:37,432 INFO [DriverManagerConnectionProvider] using driver: com.mysql.jdb
c.Driver at URL: jdbc:mysql:/localhost/test
14:43:37,432 INFO [DriverManagerConnectionProvider] connection properties: {use
r=root, password=}
14:43:37,432 INFO [SchemaUpdate] Running hbm2ddl schema update
14:43:37,432 INFO [SchemaUpdate] fetching database metadata
14:43:37,482 INFO [SchemaUpdate] updating schema
14:43:37,482 INFO [Configuration] processing one-to-many association mappings
14:43:37,482 INFO [Configuration] processing one-to-one association property re
ferences
14:43:37,482 INFO [Configuration] processing foreign key constraints
14:43:37,522 INFO [SchemaUpdate] schema update complete
14:43:37,522 INFO [DriverManagerConnectionProvider] cleaning up connection pool
: jdbc:mysql:/localhost/test
-----------------------------------------------------------------------

Thank in advance

DF

Could you please post the code between sessionFactory.openSession() and session.close()?

AFAIK there's chance, that you have to tell the session to evict some data from the first level cache, or simply close the session and open a new one.

HTH
Ernst

My code is quite simple.
public void getItemList(int offset, int rows, String strSearch) {
try {
Session session = m_sessionFactory.openSession();
Query q = session.createQuery("select item.ITEM_NO from Item as item Where "
+ "item.ITEM_NO LIKE ? order by item.ITEM_NO");

q.setString(0, strSearch); q.setFirstResult(offset);
q.setMaxResults(rows);
List vc = q.list();
m_sessionFactory.close(session);
} catch (Exception e) {
System.out.println(e);
}

}

I also added <property name="hibernate.cache.enabled">false</property>
in hibernate.cfg.xml
It is still not working.

Thanks

Df

I can't find any bulkl deletions in your code. Did I miss something?

TQuery is just a connection manager. It has been used in many projects for many years.

try {
TQuery tquery = new TQuery();
tquery.setAutoCommit(false);
tquery.setSQL ("delete from ITEM where ITEM_NO >= '" + itemno + "'");
tquery.execute();
tquery.commit();
tquery.close();
} catch (Exception e) {
System.out.println(e);
}

private Connection m_conn;

public TQuery() throws Exception {
SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
Session session = sessionFactory.openSession();
m_conn = session.connection();
}

The interesting thing is If I delete a record using mysql command directly, I still can get the record using HSQL until I restart JBoss.

I think I probably misuse caching strategy of Hibernate. Appreciate for any help.

DF

try this before JDBC low level calls:

and only after Session.flush() and Session.clear() call Session.connection().

Maybe some delted objects are still present in first-level (Session) cache after JDBC deletes/updates and before transaction closing Session.flush() reinserts/reupdates changed data.

Thanks, shl.

I have solved the problem.
I did both session.clear() and sessionFactory.evict() . It is working very well now.

DF

If you don't use second-level cache, you don't need to call sessionFactory.evict(), it clears second-level cache. But I think it will not harm a lot if you call it.

To make sure from the above I think hibernate support opinion will be helpfull to make sure that above solution is the right way. Hibernate support, any comments ?!