Skip site navigation (1) Skip section navigation (2)

PreparedStatement cache and dynamic queries

From: Ken Johanson <pg-user(at)kensystem(dot)com>
To: pgsql-jdbc(at)postgreSQL(dot)org
Subject: PreparedStatement cache and dynamic queries
Date: 2008-03-01 16:00:32
Message-ID: 47C97DA0.2090908@kensystem.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
I'm trying to assess how a large in-house app will behave with PG. The 
obvious and most correct way to handle the situation is to re-code their 
apps to /not/ send dynamic queries to Connection.prepareStatement() at 
all :-) I'm curious though, about how PG and/or the JDBC driver 
generally handles prepared statements (now, and future/ideally):

1) Are statements cached server side or driver?

2) If client side, are statements cached per-connection, or pooled in a 
classloader (or even jvm classloader)?

	(their connection pool impl allows creation of prepared statements 
bound to pooled Connections -- caching only at the connection level * 50 
connections with prepareStatement(dynamicSql) has implications since the 
list can be several thousand items long and in random order)

3) Can we set a maximum time-to-live so that only frequently reused 
statements stay cached, so that if there is dynamic sql sent to 
Con.prepareStatement, will not be a memory leak (see next)?

4) They want parameter escaping but in the context of lists:
	WHERE foo IN ('a','b','c',...dynamic list).
Is this possible in a database neutral way?

String[] ar = new String[]{"a","b"};
"WHERE foo IN ?",
ps.setObject(1,ar,Types.ARRAY);

(I've never tried this snippet but presume it won't work due to the 
zero-len case which should failfast according to sql, I believe)

Thanks,
Ken



Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2008-03-01 17:49:28
Subject: Re: PreparedStatement cache and dynamic queries
Previous:From: dmpDate: 2008-03-01 04:47:18
Subject: Re: Summer of Code Java?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group