Hi Martin,
thanks for the help. I wrote my reply to Enrique's mail just before I read
yours.
Glad to see that I was on the right track. Just one little question. Why do
you have
two finally blocks in the DAO - can't you close both the statement and the
result set
in the same finally block?
cheers,
Brian
----- Original Message -----
From: "Martin Vilcans" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, June 12, 2003 12:01 PM
Subject: RE: [JBoss-user] Closing database connections
> This code is no good. It doesn't close the connection and it doesn't close
> the PreparedStatement. Depending on what toPage does, it might not close
the
> ResultSet either, and even if it does, it's still somewhat ugly to
allocate
> a resource in one place and close it somewhere else.
>
> I haven't looked at XPetstore, and if this is the standard of the code in
> that project, I think I won't.
>
> Here's more like how I would have done it:
>
>
> //------------- session facade methods-----------------------------
>
> /**
> * @ejb.interface-method
> * @ejb.transaction
> * type="NotSupported"
> */
> public Page searchProducts( String key, int start, int count )
> {
> Connection connection;
> try
> {
> connection = getConnection();
> // constructor doesn't take a connection parameter:
> ProductDAO dao = new ProductDAO();
> // findByKey does instead:
> return dao.findByKey(connection, "%" + key + "%", start,
> count );
> }
> catch( SQLException sql )
> {
> throw new EJBException( sql );
> }
> finally {
> connection.close();
> }
> }
>
>
>
> private Connection getConnection()
> {
> try
> {
> InitialContext ic = new InitialContext();
> DataSource ds = ( DataSource )ic.lookup(
> JNDINames.DATASOURCE );
> return ds.getConnection();
> }
> catch( Exception e )
> {
> // changed from throw new EJBException();
> // this aids debugging more
> throw new EJBException("Failed to get connection", e);
> }
> }
>
> //------------- DAO method -----------------------------
>
> // removed constructor
>
> // Connection as parameter instead:
> public Page findByKey(Connection connection, String key, int start,
int
> count )
> throws SQLException
> {
> PreparedStatement stmt = connection.prepareStatement (
> SQL_FIND_BY_KEY,
> ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_READ_ONLY );
> try {
>
> stmt.setString ( 1, key );
> stmt.setString ( 2, key );
> stmt.setString ( 3, key );
>
> ResultSet rs = stmt.executeQuery ( );
> try {
> return toPage ( rs, start, count );
> }
> finally {
> rs.close();
> }
> }
> finally {
> stmt.close();
> }
> }
>
> I haven't compiled or tested this, but I hope you catch my drift.
>
> Martin
>
>
>
> -------------------------------------------------------
> This SF.NET email is sponsored by: eBay
> Great deals on office technology -- on eBay now! Click here:
> http://adfarm.mediaplex.com/ad/ck/711-11697-6916-5
> _______________________________________________
> JBoss-user mailing list
> [EMAIL PROTECTED]
> https://lists.sourceforge.net/lists/listinfo/jboss-user
>
-------------------------------------------------------
This SF.NET email is sponsored by: eBay
Great deals on office technology -- on eBay now! Click here:
http://adfarm.mediaplex.com/ad/ck/711-11697-6916-5
_______________________________________________
JBoss-user mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-user
