Hi, I want to use prepared statements for queries and inserts because of the increase in performance. After looking in to how to use them in Hibernate my understanding is that Hibernate automatically turns your sql into prepared statements and then runs them on your database. To cache these statements (thus avoiding recreating them every time that you would like to reuse one) there are a couple of lines in Hibernate.properties which need to be uncommented (all the hibernate.dbcp.ps.* lines). If this is so, I have done this and I still do not see an increase in performance. To test it I just created a for loop which does 5000 inserts, and it takes the same amount of time whether I have the hibernate.dbcp.ps.* lines commented or uncommented. I think that it is not caching the statements for reuse because this is the output that I am getting in the terminal window(thousands of times and I assume it's once for every insert)

select @@identity
11:39:16,042 DEBUG BatcherImpl:241 - preparing statement
11:39:16,052 DEBUG EntityPersister:388 - Dehydrating entity: [net.sf.hibernate.e
xamples.catlovers.CatOwner#<null>]
11:39:16,052 DEBUG StringType:46 - binding 'Granny' to parameter: 1
11:39:16,092 DEBUG StringType:46 - binding 'Washington DC' to parameter: 2
11:39:16,092 DEBUG AbstractEntityPersister:1229 - Natively generated identity: 2
7102
11:39:16,092 DEBUG BatcherImpl:203 - done closing: 0 open PreparedStatements, 0
open ResultSets
11:39:16,092 DEBUG BatcherImpl:261 - closing statement
11:39:16,102 DEBUG SessionImpl:818 - saving [net.sf.hibernate.examples.catlovers
.CatOwner#<null>]
11:39:16,102 DEBUG SessionImpl:2298 - executing insertions
11:39:16,102 DEBUG EntityPersister:490 - Inserting entity: net.sf.hibernate.exam
ples.catlovers.CatOwner (native id)
11:39:16,102 DEBUG BatcherImpl:196 - about to open: 0 open PreparedStatements, 0
open ResultSets
11:39:16,102 DEBUG SQL:237 - insert into CAT_OWNERS (name, address) values (?, ?
)

In the excerpt above I see that it is preparing the statement and then closing the statement, and from what I have read closing the statement destroys it. Should it get cached? Why isn't it getting cached? How do I avoid recreating the same prepared statement over and over? Do I even understand how Hibernate uses prepared statements? If I don't could someone point me in the correct direction please.

I am using Hibernate 2.1, and 3CPO for my connection pool. If it helps the for loop that I am using is below.


Transaction tx= session.beginTransaction();

CatOwner oldLady ;
for(int i = 0 ; i < 5000 ; ++i)
{
oldLady = new CatOwner("Granny", "Washington DC") ;
session.save(oldLady);
}

tx.commit();

PreparedStatement caching does not necessarily mean a performance increase, this also depends on your database and driver.

You're using 3CP0 yet you state you're commenting/uncommenting DBCP, makes no sense to me. You need to set the c3p0.max_statements property.