Hi,

I'm using Hibernate deployed as a service in Jboss. And it seems the prepared statement cache does not work. When I examine the messages sent to DB server, I see the same SQL sent every time it's executed.

If I initiate Hibernate (load mapping files, build sessionfactory, etc.) the cache seems working. I see SQL once. This method uses the following hibernate.properties file:

hibernate.connection.driver_class = oracle.jdbc.OracleDriver
hibernate.connection.url = jdbc:oracle:oci:@tnsname
hibernate.connection.username = user
hibernate.connection.password = password
hibernate.dialect = net.sf.hibernate.dialect.Oracle9Dialect
hibernate.show_sql = false
hibernate.connection.pool_size = 10
hibernate.jdbc.batch_size = 100
hibernate.statement_cache.size = 100



Here is the jboss-service.xml:

<mbean code="net.sf.hibernate.jmx.HibernateService" name="jboss.jca:service=HibernateFactory,
name=HibernateFactory">
<depends>jboss.jca:service=RARDeployer</depends>
<depends>jboss.jca:service=LocalTxCM,name=OracleDS</depends>
<attribute name="MapResources">
kkdb/hnate/A.hbm.xml,
kkdb/hnate/B.hbm.xml
...
...
</attribute>
<attribute name="JndiName">java:/hibernate/HibernateFactory</attribute>
<attribute name="Datasource">java:/OracleDS</attribute>
<attribute name="Dialect">net.sf.hibernate.dialect.OracleDialect</attribute>
<attribute name="TransactionStrategy">net.sf.hibernate.transaction.JTATransactionFactory</attribute>
<attribute name="TransactionManagerLookupStrategy">net.sf.hibernate.transaction.JBossTransactionManagerLookup</attribute>
<attribute name="UseOuterJoin">false</attribute>
<attribute name="ShowSql">false</attribute>
<attribute name="UserTransactionName">java:/UserTransaction</attribute>

</mbean>


How can I define hibernate.statement_cache.size parameter in jboss-service.xml?

Regards.

Serkan Soykan

Ummm. Clearly, if Hibernate reports in the debug log that it is using a cached statement, then it is, in fact, using a cached statement....


....are you suggesting that the Hibernate log is intentionally lying to you?


Anyway, don't use the builtin connection pool / statement cache. This stuff is for "getting started" type usage only. Use the JBoss datasource.

Oh, in fact you are using the JBoss datasource




In which case all those other properties are quite irrelevant. And, I'm quite certain, Hibernate does not log any usage of its prepared statement cache!

No, of course I'm not saying that and yes I'm using Jboss datasource ;)

Here is what log says:

In the beginning of the log file it says, loaded properties with hibernate.statement_cache.size, etc.

...
2003-09-30 16:25:20,328 INFO [net.sf.hibernate.cfg.Environment] Hibernate 2.0.2
2003-09-30 16:25:20,343 INFO [net.sf.hibernate.cfg.Environment] loaded properties from resource hibernate.properties: {hibernate.connection.driver_class=oracle.jdbc.OracleDriver, hibernate.cglib.use_reflection_optimizer=true, hibernate.dialect=net.sf.hibernate.dialect.Oracle9Dialect, hibernate.jdbc.batch_size=100, hibernate.connection.username=user, hibernate.connection.url=jdbc:oracle:oci:@tnsname hibernate.show_sql=false, hibernate.connection.password=password, hibernate.statement_cache.size=100, hibernate.connection.pool_size=10}
2003-09-30 16:25:20,343 INFO [net.sf.hibernate.cfg.Environment] using CGLIB reflection optimizer
2003-09-30 16:25:20,343 INFO [net.sf.hibernate.cfg.Environment] JVM proxy support: true
...


And after this, following two query repeats about thousand times... Every time it's prepared. That's why I wrote the previous message.


2003-09-30 16:25:59,265 DEBUG [net.sf.hibernate.impl.BatcherImpl] about to open: 0 open PreparedStatements, 0 open ResultSets
2003-09-30 16:25:59,265 DEBUG [net.sf.hibernate.impl.SessionFactoryImpl] prepared statement get: <query 1>
2003-09-30 16:25:59,265 DEBUG [net.sf.hibernate.impl.SessionFactoryImpl] preparing statement
2003-09-30 16:25:59,312 DEBUG [net.sf.hibernate.type.LongType] binding '241' to parameter: 1
2003-09-30 16:25:59,359 DEBUG [net.sf.hibernate.loader.Loader] processing result set
2003-09-30 16:25:59,359 DEBUG [net.sf.hibernate.loader.Loader] result row: 241
...
...
2003-09-30 16:25:59,375 DEBUG [net.sf.hibernate.loader.Loader] done processing result set (1 rows)
2003-09-30 16:25:59,375 DEBUG [net.sf.hibernate.impl.BatcherImpl] done closing: 0 open PreparedStatements, 0 open ResultSets
2003-09-30 16:25:59,375 DEBUG [net.sf.hibernate.impl.SessionFactoryImpl] closing statement
2003-09-30 16:25:59,375 DEBUG [net.sf.hibernate.loader.Loader] total objects hydrated: 1
2003-09-30 16:25:59,375 DEBUG [net.sf.hibernate.impl.SessionImpl] resolving associations for [kkdb.hnate.terminals#241]
2003-09-30 16:25:59,375 DEBUG [net.sf.hibernate.impl.SessionImpl] loading [kkdb.hnate.merchants#42942]
2003-09-30 16:25:59,375 DEBUG [net.sf.hibernate.impl.SessionImpl] attempting to resolve [kkdb.hnate.merchants#42942]
2003-09-30 16:25:59,375 DEBUG [net.sf.hibernate.impl.SessionImpl] object not resolved in any cache [kkdb.hnate.merchants#42942]
2003-09-30 16:25:59,375 DEBUG [net.sf.hibernate.persister.EntityPersister] Materializing entity: kkdb.hnate.merchants#42942
2003-09-30 16:25:59,375 DEBUG [net.sf.hibernate.impl.BatcherImpl] about to open: 0 open PreparedStatements, 0 open ResultSets
2003-09-30 16:25:59,375 DEBUG [net.sf.hibernate.impl.SessionFactoryImpl] prepared statement get: <query 2>
2003-09-30 16:25:59,375 DEBUG [net.sf.hibernate.impl.SessionFactoryImpl] preparing statement
2003-09-30 16:25:59,375 DEBUG [net.sf.hibernate.type.LongType] binding '42942' to parameter: 1
2003-09-30 16:25:59,406 DEBUG [net.sf.hibernate.loader.Loader] processing result set
2003-09-30 16:25:59,406 DEBUG [net.sf.hibernate.loader.Loader] result row: 42942

Is there something I'm missing?


Regards...


Serkan.

It is the responsibility of the JBoss datasource to implement prepared statement caching. It would be quite illegal for Hibernate to cache statements under the management of the app server!

Ok, I see now that I'm trying to do two conflicting things, to use JBoss data source together with hibernate's statement cache...

Thanks for the information.


Regards...

Serkan.