Re: JDBC Connection State Management with SQL Exceptions (esp Postgresql)

AV wrote:
>
> "Joseph Weinstein" <joe(at)bea(dot)com> wrote in message
> news:3B3277C6(dot)4C9BCA9(at)bea(dot)com(dot)(dot)(dot)
> >
> > John Moore wrote:
> .....
> > > I am doing transactional work, with multiple statements and then a
> commit().
> > > I am also doing my own connection pooling, so it is important that I be
> able
> > > to reliably re-use connections.
> >
> > Hi. There is a lot of state that can be left with a connection, and a good
> > pooling system should do a bunch of cleanup on the connection when it is
> > returned to the pool, so it will be ready for the next user. This would
> include
> > closing all statements and result sets that the previous user may have
> created
> > but not closed.
>
> What about PreparedConnection pooling?
> What is your oppinion on the following code
> [design] for such caching within a connection :
> ( getUsedPstmts() is imaginary method of imaginary
> MyConnection interface )
>
> public void returnConnection (Connection con) {
> Connection local_con = con;
> con = null;
> PreparedStatement [] used_pstmt = (MyConnection) local_con.getUsedPstmts()
> for (int i =0 ; i < used_con.length ; i++) {
> PreparedStatement new_pstmt = used_con[i];
> used_con[i] = null;
> cached_pstmt_HashMap.put( new_pstmt.getSql(), new_pstmt );
> }
> ... some other cleaning steps....
> ...set connection as available...
> }
>
> AlexV

Hi Alex. I think I understand this... The basis of caching/re-using a PreparedStatment
is via the SQL used to create it, but I see no actual statement-level cleanup here.
You should be clearing any warnings the statement may have accrued. Another example
is that you should do something to cover the possibility some user code called setMaxRows(1)
on the statement. You don't want this condition to remain and silently truncate the results
of any subsequent user... This code also doesn't allow for multiple statements with the
same SQL. There will be some 'utility' statements that might be used at several levels
in a user's stack, and you want to allow for caching multiple identical statements *and*
making sure that no two methods in the same caller stack get the *same* statement,
even if it is the same SQL.

Joe

>
> > This is crucial because you don't want retained references
> > to these objects to allow a 'previous user' to affect anything the next
> user
> > does. ......

--

PS: Folks: BEA WebLogic is expanding rapidly, with both entry and advanced positions
for people who want to work with Java, XML, SOAP and E-Commerce infrastructure products.
We have jobs at Nashua NH, Liberty Corner NJ, San Francisco and San Jose CA.
Send resumes to joe(at)bea(dot)com