RE: [JBoss-user] A little BMP philosophy/understanding

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

ok, let's separate out two very different topics

1. Connection pooling.
Yes for JDBC 1.x JBoss is implementing the connection pool (see minerva)
This connection pool is a wrapper around any existing JDBC compliant driver.
when you call connection.close() minerva actually just returns the
connection to the pool.

2. Prepared statement caching.
I thought I was clear on this one, let me start all over.
In 1997 when JDBC driver developers implemented the prepared statements in
such away that the programmer held a reference to a statement object
directly connected to the database. In this case you as a programmer had to
cache the statements yourself.
These days, the programmer doesn't get hold of a direct reference.
When you call close on the prepared statement, the driver knows not to drop
the temporary procedure on the database server. In case you didn't know, a
prepared statement just gets compiled into a temporary stored procedure (or
something similar).
Minerva, (that I belive was built in the early days) still has code that
does some statement caching. with most drivers today that code is redundant,
and in some cases can even cause problems.
If you have any questions, please let me know.

Filip

JDBC code should always look like this (always close the connection inside
the same method)
otherwise you may experience problems debugging where your connections get
lost etc.

public void mymethod()
{
Connection con = null;
PreparedStatement pstmt = null;
try
{
	con = getConnection();
	pstmt = con.prepareStatement("my sql string");
	ResultSet rs = pstmt.executeQuery();
	...bla bla bla...
}
finally
{
	try
	{
		pstmt.close();
		con.close();
	}catch (Exception ignore() {}
}//finally
}

~
Namaste - I bow to the divine in you
~
Filip Hanik
Software Architect
fi...@fi...
www.filip.net

> -----Original Message-----
> From: jbo...@li...
> [mailto:jbo...@li...]On Behalf Of Mike Jau
> Sent: Thursday, March 22, 2001 2:30 PM
> To: 'jbo...@li...'
> Subject: RE: [JBoss-user] A little BMP philosophy/understanding
>
>
> It seems that the JBOSS EJB container do need to implement the connection
> pooling and preparedstatement caching just because the JDBC driver already
> support them. Does the JDBC2.x and above support the connection pooling?
>
> Is JDBC2.x specification mention about the Preparedstatement caching?
>
> - Mike
>
> -----Original Message-----
> From: Filip Hanik [mailto:ma...@fi...]
> Sent: Thursday, March 22, 2001 4:04 PM
> To: jbo...@li...
> Subject: RE: [JBoss-user] A little BMP philosophy/understanding
>
>
> > Doesn't this defeat the purpose of caching and prepared
> statements? If you
> > close the prepared statement then the db connection goes away
> > right? So why
> > used a prepared statmenet at all, beacuse it is never really
> prepared? It
> > seems to me the cache would need to keep at least one of each prepared
> > statement used to be of any value.
>
> no, you should not cache statements. If you are using prepared statements,
> it is on the level of the driver to make sure that they are cached.
>
> a prepared statement should give you the same performance even if
> you close
> them in your code, since it is handled by the driver, not by the
> programmer,
> or by jboss.
>
> caching statements goes back to 1997 when the drivers did not do this for
> you. now a days, almost all of them do.
>
> Filip
>
> ~
> Namaste - I bow to the divine in you
> ~
> Filip Hanik
> Software Architect
> fi...@fi...
> www.filip.net
>
> > -----Original Message-----
> > From: jbo...@li...
> > [mailto:jbo...@li...]On Behalf Of Hansen,
> > Richard
> > Sent: Thursday, March 22, 2001 1:53 PM
> > To: 'jbo...@li...'
> > Subject: RE: [JBoss-user] A little BMP philosophy/understanding
> >
> >
>
> >
> > Rick Hansen
> >
> > -----Original Message-----
> > From: ha...@ts... [mailto:ha...@ts...]
> > Sent: Thursday, March 22, 2001 1:25 PM
> > To: jbo...@li...
> > Subject: RE: [JBoss-user] A little BMP philosophy/understanding
> >
> >
> >
> > In the original JBoss 2.0 version the PreparedStatement cache was not
> > discarded after the connection was returned to the pool because
> more than
> > likely you might want to issue that one of these
> PreparedStatements again.
> > To make matters worse there wasn't an upper limit on the number of
> > PreparedStatement objects in the cache so things would continue
> to grow as
> > you prepared new SQL statements. If you happened to prepare the
> same exact
> > SQL statement then you received the previously cached PreparedStatement
> > object but otherwise you got a new PreparedStatement that was
> > also added to
> > the cache. This would continue until either a) the database
> > complained or b)
> > you ran out of memory which ever came first. On Oracle, for
> example, each
> > PreparedStatement takes memory on the database and once you hit
> 100 or so
> > the database throws an exception when you try to get another one.
> >
> > I patched the code by releasing the PreparedStatement cache when the
> > Connection was released and submitted that fix but I'm not sure it was
> > accepted. What really needs to happen is that the
> PreparedStatement cache
> > needs to be enhanced so that an upper bound can be established via a
> > configuration variable so that after x PreparedStatements have
> been cached
> > new PreparedStatements will push one of the old ones out of the cache.
> >
> > - Jon Harvie
> >
> >
> >
> > Mike Jau <MJau@eLaw.com>
> > Sent by: jbo...@li...
> > 03/22/2001 12:42 PM
> > Please respond to jboss-user
> >
> >         To:        "'jbo...@li...'"
> > <jbo...@li...>
> >         cc:
> >         Subject:        RE: [JBoss-user] A little BMP
> > philosophy/understanding
> >
> >
> >
> > Could you give me some background information about the Preparedstaement
> > caching on the EJB container side?
> >
> > Since the connection get from pool need to return to pool once the
> > transaction done. I assumed that the resouce associate to this
> connection
> > should be released and the released resoure include the
> preparedstatement.
> > Later on, the create preparedstatement will be invoked again from
> > different
> > connection. How the preparedstatement cached is my question?
> >
> >
> > - Mike
> >
> > -----Original Message-----
> > From: Bill Burke [mailto:wb...@co...]
> > Sent: Thursday, March 22, 2001 12:10 PM
> > To: jbo...@li...
> > Subject: Re: [JBoss-user] A little BMP philosophy/understanding
> >
> >
> >
> >
> > Dan Christopherson wrote:
> >
> > > On Fri, 23 Mar 2001, Peter Routtier-Wone wrote:
> > >
> > >>> Someone from this discussion group indicate that container
> might cache
> > the
> > >>> PreparedStatement.
> > >>
> > >> I can't speak with authority on this, but that rings true.
> I'm guessing
> > that
> > >> interception doesn't happen for the setEntityContext() method and
> > therefore
> > >> you actually create a PreparedStatement rather than
> receiving one from
> > the
> > >> pool.
> > >>
> > >>> Just for kicks, I gave it a try but transactions weren't
> completed and
> > >>> they'd just hang out there forever, blocking every other
> > persistence and
> > >>> finder method until they timed out.
> > >>
> > >> That would bollox lifecycle management, and the described behaviour
> > wouldn't
> > >> be at all surprising.
> > >
> > > This is also a common bean bug: 'close()' should be called on every
> > > resultset, statement, and connection in a finally clause so
> > that you know
> > > it happens every time.
> > >
> > >> On the other hand, I'd have thought that PreparedStatements
> > would be far
> > >> less costly to manufacture than Connections, and therefore not
> > worth the
> > >> overhead of managing a pool. I think I'll poke my nose into
> the source
> > and
> > >> see what's there.
> > >
> > > There's often communication with the database to create the
> > > PreparedStatement. That way it can pre-compile a query plan.
> There is a
> > > prepared statement cache in JBoss: in JBoss 2.0, it caused
> problems with
> > > Oracle's cursor limit (fixed in 2.1).
> >
> >
> > I'm re-writing the minerva PreparedStatement caching so it handles
> > cursor limit better.  I'll submit the code tomorrow after I test it.
> >
> > Bill
> >
> >
> >
> > _______________________________________________
> > JBoss-user mailing list
> > JBo...@li...
> > http://lists.sourceforge.net/lists/listinfo/jboss-user
> >
> > _______________________________________________
> > JBoss-user mailing list
> > JBo...@li...
> > http://lists.sourceforge.net/lists/listinfo/jboss-user
> >
> > _______________________________________________
> > JBoss-user mailing list
> > JBo...@li...
> > http://lists.sourceforge.net/lists/listinfo/jboss-user
> >
>
>
> _______________________________________________
> JBoss-user mailing list
> JBo...@li...
> http://lists.sourceforge.net/lists/listinfo/jboss-user
>
> _______________________________________________
> JBoss-user mailing list
> JBo...@li...
> http://lists.sourceforge.net/lists/listinfo/jboss-user
>