PreparedStatement in Hibernate

maktech · **Joined:** Thu Dec 01, 2005 7:07 pm **Posts:** 21

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate Version: 3.0.5

Hibernate Config:
<?xml version="1.0"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>
<property name="connection.datasource">INGCoreConnection</property>
<property name="show_sql">true</property>
<property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>
<property name="hibernate.query.factory_class">org.hibernate.hql.ast.ASTQueryTranslatorFactory</property>
<property name="transaction.factory_class">org.hibernate.transaction.CMTTransactionFactory</property>
<property name="transaction.manager_lookup_class">org.hibernate.transaction.WebSphereExtendedJTATransactionLookup</property>
<property name="hibernate.transaction.flush_before_completion">true</property>
<property name="hibernate.transaction.auto_close_session">true</property>
<property name="hibernate.generate_statistics">true</property>
<property name="hibernate.cache.use_structured_entries">true</property>
<property name="jndi.class">com.ibm.websphere.naming.WsnInitialContextFactory</property>



<mapping resource="cl/ing/coreafp/pension/necessarycapital/properties/MortalityBean.hbm.xml" />
</session-factory>
</hibernate-configuration>
Mapping Information:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="cl.ing.coreafp.pension.necessarycapital.beans.MortalityBean" table="T_MORTALITYDATA">

<id name="id" type="int" column="MD_SUR_ID" unsaved-value="null" />

<property name="year" type="int" column="MD_YEAR_PE" not-null="true" length="6"></property>
<property name="age" type="int" column="MD_AGE_NN" not-null="true" length="3"></property>
<property name="numberOfPersonsAlive" type="double" column="MD_NOOFPRSNSALIVE_NN" not-null="false" length="11"></property>

</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
if(session==null || !session.isConnected()){
session = HibernateSessionFactory.currentSession();
qy = session.createQuery("from MortalityBean objMortalityBean where objMortalityBean.year = :year and objMortalityBean.age= :age");

MortalityBean objMortalityBean = (MortalityBean)qy.setInteger("year",objMortalityBeanPK.getYear())
.setInteger("age",objMortalityBeanPK.getAge()).uniqueResult();
}
Console Information:
[4/13/06 17:36:16:203 IST] 0000002c Environment I hibernate.properties not found
[4/13/06 17:36:16:219 IST] 0000002c Environment I using CGLIB reflection optimizer
[4/13/06 17:36:16:234 IST] 0000002c Environment I using JDK 1.4 java.sql.Timestamp handling
[4/13/06 17:36:16:733 IST] 0000002c Configuration I configuring from resource: /cl/ing/coreafp/common/utilities/hibernate.cfg.xml
[4/13/06 17:36:16:749 IST] 0000002c Configuration I Configuration resource: /cl/ing/coreafp/common/utilities/hibernate.cfg.xml
[4/13/06 17:36:19:133 IST] 0000002c Configuration I Mapping resource: cl/ing/coreafp/pension/necessarycapital/properties/MortalityBean.hbm.xml
[4/13/06 17:36:19:164 IST] 0000002c HbmBinder I Mapping class: cl.ing.coreafp.pension.necessarycapital.beans.MortalityBean -> T_MORTALITYDATA
[4/13/06 17:36:19:164 IST] 0000002c Configuration I Configured SessionFactory: null
[4/13/06 17:36:19:164 IST] 0000002c Configuration I processing extends queue
[4/13/06 17:36:19:211 IST] 0000002c Configuration I processing collection mappings
[4/13/06 17:36:19:211 IST] 0000002c Configuration I processing association property references
[4/13/06 17:36:19:227 IST] 0000002c Configuration I processing foreign key constraints
[4/13/06 17:36:19:336 IST] 0000002c NamingHelper I JNDI InitialContext properties:{java.naming.factory.initial=com.ibm.websphere.naming.WsnInitialContextFactory}
[4/13/06 17:36:19:647 IST] 0000002c ConnectionFac W J2CA0294W: Deprecated usage of direct JNDI lookup of resource INGCoreConnection. The following default values are used: [Resource-ref settings]

res-auth: 1 (APPLICATION)
res-isolation-level: 0 (TRANSACTION_NONE)
res-sharing-scope: true (SHAREABLE)
loginConfigurationName: null
loginConfigProperties: null
[Other attributes]

res-resolution-control: 999 (undefined)
isCMP1_x: false (not CMP1.x)
isJMS: false (not JMS)

[4/13/06 17:36:19:772 IST] 0000002c DatasourceCon I Using datasource: INGCoreConnection
[4/13/06 17:36:20:583 IST] 0000002c WSRdbDataSour I DSRA8203I: Database product name : Oracle
[4/13/06 17:36:20:598 IST] 0000002c WSRdbDataSour I DSRA8204I: Database product version : Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
[4/13/06 17:36:20:614 IST] 0000002c WSRdbDataSour I DSRA8205I: JDBC driver name : Oracle JDBC driver
[4/13/06 17:36:20:614 IST] 0000002c WSRdbDataSour I DSRA8206I: JDBC driver version : 9.2.0.1.0
[4/13/06 17:36:20:692 IST] 0000002c WSRdbDataSour I DSRA8208I: JDBC driver type : ""
[4/13/06 17:36:21:081 IST] 0000002c SettingsFacto I RDBMS: Oracle, version: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
[4/13/06 17:36:21:081 IST] 0000002c SettingsFacto I JDBC driver: Oracle JDBC driver, version: 9.2.0.1.0
[4/13/06 17:36:21:346 IST] 0000002c Dialect I Using dialect: org.hibernate.dialect.Oracle9Dialect
[4/13/06 17:36:21:378 IST] 0000002c TransactionFa I Transaction strategy: org.hibernate.transaction.CMTTransactionFactory
[4/13/06 17:36:21:424 IST] 0000002c TransactionMa I instantiating TransactionManagerLookup: org.hibernate.transaction.WebSphereExtendedJTATransactionLookup
[4/13/06 17:36:21:440 IST] 0000002c TransactionMa I instantiated TransactionManagerLookup
[4/13/06 17:36:21:440 IST] 0000002c SettingsFacto I Automatic flush during beforeCompletion(): enabled
[4/13/06 17:36:21:455 IST] 0000002c SettingsFacto I Automatic session close at end of transaction: enabled
[4/13/06 17:36:21:502 IST] 0000002c SettingsFacto I JDBC batch size: 15
[4/13/06 17:36:21:518 IST] 0000002c SettingsFacto I JDBC batch updates for versioned data: disabled
[4/13/06 17:36:21:518 IST] 0000002c SettingsFacto I Scrollable result sets: enabled
[4/13/06 17:36:21:565 IST] 0000002c SettingsFacto I JDBC3 getGeneratedKeys(): disabled
[4/13/06 17:36:21:580 IST] 0000002c SettingsFacto I Connection release mode: null
[4/13/06 17:36:21:580 IST] 0000002c SettingsFacto I Default batch fetch size: 1
[4/13/06 17:36:21:596 IST] 0000002c SettingsFacto I Generate SQL with comments: disabled
[4/13/06 17:36:21:611 IST] 0000002c SettingsFacto I Order SQL updates by primary key: disabled
[4/13/06 17:36:21:627 IST] 0000002c SettingsFacto I Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
[4/13/06 17:36:21:642 IST] 0000002c ASTQueryTrans I Using ASTQueryTranslatorFactory
[4/13/06 17:36:21:674 IST] 0000002c SettingsFacto I Query language substitutions: {}
[4/13/06 17:36:21:689 IST] 0000002c SettingsFacto I Second-level cache: enabled
[4/13/06 17:36:21:689 IST] 0000002c SettingsFacto I Query cache: disabled
[4/13/06 17:36:21:689 IST] 0000002c SettingsFacto I Cache provider: org.hibernate.cache.EhCacheProvider
[4/13/06 17:36:21:752 IST] 0000002c SettingsFacto I Optimize cache for minimal puts: disabled
[4/13/06 17:36:21:752 IST] 0000002c SettingsFacto I Structured second-level cache entries: enabled
[4/13/06 17:36:21:783 IST] 0000002c SettingsFacto I Echoing all SQL to stdout
[4/13/06 17:36:21:830 IST] 0000002c SettingsFacto I Statistics: enabled
[4/13/06 17:36:21:830 IST] 0000002c SettingsFacto I Deleted entity synthetic identifier rollback: disabled
[4/13/06 17:36:21:845 IST] 0000002c SettingsFacto I Default entity-mode: pojo
[4/13/06 17:36:22:188 IST] 0000002c SessionFactor I building session factory
[4/13/06 17:36:22:235 IST] 0000002c Configurator W No configuration found. Configuring ehcache from ehcache-failsafe.xml found in the classpath: wsjar:file:/C:/IWorkSpace/INGAFPCorePOC/lib/ehcache-1.1.jar!/ehcache-failsafe.xml
[4/13/06 17:36:25:103 IST] 0000002c SessionFactor I Not binding factory to JNDI, no JNDI name configured
[4/13/06 17:36:25:118 IST] 0000002c SessionFactor I Checking 0 named queries
[4/13/06 17:36:25:274 IST] 0000002c NamingHelper I JNDI InitialContext properties:{java.naming.factory.initial=com.ibm.websphere.naming.WsnInitialContextFactory}
[4/13/06 17:36:25:321 IST] 0000002c SystemOut O in dao of contracts module
[4/13/06 17:36:25:321 IST] 0000002c SystemOut O Affiliate RUT is 22222222
[4/13/06 17:36:25:399 IST] 0000002c ConnectionFac W J2CA0294W: Deprecated usage of direct JNDI lookup of resource INGCoreConnection. The following default values are used: [Resource-ref settings]

res-auth: 1 (APPLICATION)
res-isolation-level: 0 (TRANSACTION_NONE)
res-sharing-scope: true (SHAREABLE)
loginConfigurationName: null
loginConfigProperties: null
[Other attributes]

res-resolution-control: 999 (undefined)
isCMP1_x: false (not CMP1.x)
isJMS: false (not JMS)

Oracle 9.2:

The generated SQL (show_sql=true):
select mortalityb0_.MD_SUR_ID as MD1_, mortalityb0_.MD_YEAR_PE as MD2_13_,

mortalityb0_.MD_AGE_NN as MD3_13_, mortalityb0_.MD_NOOFPRSNSALIVE_NN as MD4_13_ from T_MORTALITYDATA mortalityb0_ where

mortalityb0_.MD_YEAR_PE=? and mortalityb0_.MD_AGE_NN=?

When looked up for, say, 5000 times, the query is parsed every time (Oracle trace revealed that).
The keys age, year vary. The table has a Composite unique index on age, year

The keys vary on every call and hence this is an ideal case to use JDBC Preparedstatement. When the application uses Java Preparedstatement,

the result is obtained in less than 3 secs. However, when using Hibernate, this runs for several minutes - sometimes times out.

Could someone help me understand how to use Preparedstatemnt effectively in Hibernate?

stewart.cambridge · **Joined:** Sat Nov 19, 2005 2:46 pm **Posts:** 69

Code:

Connection connection = session.connection();
PreparedStatement s = connection.prepareStatement( queryString );
ResultSet rs = s.executeQuery();

DWright · **Joined:** Wed Jul 07, 2004 2:00 pm **Posts:** 64

Hibernate does use prepared statements for the query.

maktech · **Joined:** Thu Dec 01, 2005 7:07 pm **Posts:** 21

/*
Connection connection = session.connection();
PreparedStatement s = connection.prepareStatement( queryString );
ResultSet rs = s.executeQuery();
*/
In the above mentioend code, is this Hibernate way of doing it or we just make use of the Session from Hibernate and go ahead with Preparedstatement. Pls explain

stewart.cambridge · **Joined:** Sat Nov 19, 2005 2:46 pm **Posts:** 69

maktech wrote:

Could someone help me understand how to use Preparedstatemnt effectively in Hibernate?

maktech wrote:

In the above mentioend code, is this Hibernate way of doing it or we just make use of the Session from Hibernate and go ahead with Preparedstatement. Pls explain

Well, to me, the word "PreparedStatement" means java.sql.PreparedStatement, which is part of the JDBC 3.0 API.

I looked in the Hibernate 3 API, and there is no PreparedStatement class that I could find.

What else could your question mean, except "how do I access the JDBC API from a hibernate session (in particular the PreparedStatement class) ?"

My code snippet gives an example of how to do this.

Unless, of course, there is something you know which I don't.

DWright · **Joined:** Wed Jul 07, 2004 2:00 pm **Posts:** 64

java.sql.PreparedStatement is in rt.jar from the JDK. I've used it for years, though I don't think it has always lived in that package (didn't there used to be a javax.sql?). Hibernate makes use of PreparedStatements internally. You can tell that a prepared stmt is being used in your query from the SQL trace, because the '?' are the parameters. There may be some case I'm not aware of when Hibernate doesn't use prep. stmts, but I traced through an example and found prep. stmts to be used in my case.

maktech · **Joined:** Thu Dec 01, 2005 7:07 pm **Posts:** 21

Hi Stewart & DWright,
Thanks for your time and response. You are correct - there is no API by name PreparedStatement in Hibernate and it belogs to JDBC. The problem scenario is that a particular statement like "select a1, a2, a3 from A1 where a4=?" is being issued repeatedly for 100 values, let us say. This is parsed so many times by Oracle ( i verified with SQL Trace) and that is quite a big overhead. JDBC's Preparedstatement would avoid this by caching the query - only once this will be parsed. In summary, please let me know, how could we ensure that such a query is parsed only once by Oracle. This should be okay because the query never changes except for teh value in the WHERE clause. Please share your thoughts.
Thanks,

stewart.cambridge · **Joined:** Sat Nov 19, 2005 2:46 pm **Posts:** 69

maktech wrote:

select a1, a2, a3 from A1 where a4=?

For multiple values of a4, maybe you could try

Code:

select a1, a2, a3 from A1 where a4 in (?,?, ..... ,?,?)

In hibernate 3 this would be done this way:

Code:

List A1List = session.createCriteria(A1.class).add( Restrictions.in("a4", Object[] values)).list();

DWright · **Joined:** Wed Jul 07, 2004 2:00 pm **Posts:** 64

Are all the 'a4' values known at the time that you want to run the query? Perhaps there is some human interaction between each one (eg, picking records to 'operate' on).

I am quite sure that Hibernate is using prepared statements for the query, but I have a question based on the following code:

void doStuff() {
Connection con = ....;
PreparedStatment stmt = con.prepareStatement("select a1,a2,a3 from Al where a4=?");
stmt.setInt(1,2);
ResultSet rs = stmt.executeQuery();
....
stmt.close();
con.close();
}

Does Oracle parse the prepared statement for every call to doStuff, or does it recognize from the text of the query that it is the same statement?

baliukas · **Posted:** Sat Apr 22, 2006 11:49 am

You can read this article http://asktom.oracle.com/pls/ask/f?p=49 ... 8723819082 it explains soft/hard parse. You can avoid parsing using prepared statement cache ( see your jdbc connection pool configuration), but soft parse must not be a performance problem.

DWright · **Joined:** Wed Jul 07, 2004 2:00 pm **Posts:** 64

Interesting. So I assume that maktech is saying that a hard parse is occuring for each call to his query. I see that WebSphere does have a statement cache, and it sounds like it is associated with the DataSource. I found this http://www-03.ibm.com/servers/eserver/i ... tcache.pdf on optimizing the cache.

maktech · **Joined:** Thu Dec 01, 2005 7:07 pm **Posts:** 21

Hi Baliukas, Could you please elaborate on how to use Preparedstatement cache in this context - with Hibernate and Websphere. The application runs on Websphere6.0 - however, currently in the dev environment, Rational Application Developer is being used. Thanks for your time.