Re: How to cache prepared statements?

[Marc Prud'hommeaux]

Also note that these is an example of using OpenJPA with DBCP at:

  http://openjpa.apache.org/faq.html


On Jul 30, 2007, at 8:16 AM, Patrick Linskey wrote:

Hi,

The built-in OpenJPA DataSource implementation doesn't do any prepared
statement caching. If you plug in a DataSource (such as DBCP, for
example) that does handle prepared statement caching, OpenJPA should
integrate well with it.

-Patrick

On 7/30/07, Salminen Pasi <[EMAIL PROTECTED]> wrote:

Hi,

It seems like OpenJPA is closing the prepared statement after each
persist call. Is there any way to avoid this behaviour? For example,
this code snippet

TestEntity te;

for( int i = 0; i < 100; i++ )
{
        te = new TestEntity( );
        te.setId( ( short ) i );
        te.setName( "John Doe" );
        te.setNickName( "JoJo" );
        _em.persist( te );
}

seems to generate zillions of prepares and closes (in this case 100).

...

2795 [main] INFO jdbc.audit - 1. Connection.prepareStatement (INSERT INTO

PERSON (id, NAME, NICKNAME) VALUES (?, ?, ?)) returned
[EMAIL PROTECTED]
2795 [main] INFO jdbc.audit - 1. PreparedStatement.setShort(1, 98)
returned
2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(2, "John
Doe") returned

2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(3, "JoJo")

returned

2795 [main] INFO jdbc.sqlonly - INSERT INTO PERSON (id, NAME, NICKNAME)

VALUES (98, 'John Doe', 'JoJo')
2795 [main] INFO jdbc.sqltiming - INSERT INTO PERSON (id, NAME,
NICKNAME) VALUES (98, 'John Doe', 'JoJo')  {executed in 0 msec}
2795 [main] INFO jdbc.audit - 1. PreparedStatement.executeUpdate()
returned 1
2795 [main] INFO jdbc.audit - 1. PreparedStatement.close() returned

2795 [main] INFO jdbc.audit - 1. Connection.prepareStatement (INSERT INTO

PERSON (id, NAME, NICKNAME) VALUES (?, ?, ?)) returned
[EMAIL PROTECTED]
2795 [main] INFO jdbc.audit - 1. PreparedStatement.setShort(1, 99)
returned
2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(2, "John
Doe") returned

2795 [main] INFO jdbc.audit - 1. PreparedStatement.setString(3, "JoJo")

returned

2795 [main] INFO jdbc.sqlonly - INSERT INTO PERSON (id, NAME, NICKNAME)

VALUES (99, 'John Doe', 'JoJo')
2795 [main] INFO jdbc.sqltiming - INSERT INTO PERSON (id, NAME,
NICKNAME) VALUES (99, 'John Doe', 'JoJo')  {executed in 0 msec}
2795 [main] INFO jdbc.audit - 1. PreparedStatement.executeUpdate()
returned 1
2795 [main] INFO jdbc.audit - 1. PreparedStatement.close() returned

I think it's horrible waste of resources even with prepared statement
caches. It may work with transactional load (Java EE style) but with
batches it probably doesn't. Any ideas?

Regards,

Paci

--
Patrick Linskey
202 669 5907