Pastable version of Closing database connections properly

<pre class='prettyprint' language='java'>/**
 * Get the article destined for the front page
 *
 * @return a linked list of the articles
 */
public LinkedList&lt;ArticleBean&gt; getFrontpageArticles() {
    DatabaseBean dbaBean = new DatabaseBean();
    LinkedList&lt;ArticleBean&gt; listOfBeans = new LinkedList&lt;ArticleBean&gt;();

try {
        dbaBean.setPrepStmt(dbaBean.getConn().prepareStatement(
                &quot;SELECT id, title, url, author, date_time, article_text &quot; +
                        &quot;FROM articles &quot; +
                        &quot;ORDER BY id desc &quot; +
                        &quot;LIMIT 5;&quot;));
    } catch (SQLException SQLEx) {
        logger.fatal(&quot;Problem with the SQL in the ArticleProcessBean.getFrontpageArticles() function&quot;);
        logger.fatal(SQLEx);
    }

ResultSet result = dbaBean.executeQuery();

try {
        while (result.next()) {
            // Make a new ArticleBeanTest that represents one article
            ArticleBean articleBean = new ArticleBean();
            // Set the properties of the bean
            articleBean.setArticleID(result.getString(&quot;id&quot;));
            articleBean.setAuthor(result.getString(&quot;author&quot;));
            articleBean.setTitle(result.getString(&quot;title&quot;));
            articleBean.setURL(result.getString(&quot;url&quot;));
            articleBean.setArticleText(result.getString(&quot;article_text&quot;));
            articleBean.setDateTime(result.getTimestamp(&quot;date_time&quot;));

// Process this articles tags
            try {
                TagProcessBean tagProcessBean = new TagProcessBean();
                tagProcessBean.setArticleID(&quot;&quot; + result.getInt(&quot;id&quot;));     // Set the user in the TagProcessBean *cast to String*
                articleBean.setTags(tagProcessBean.getArticleTags());      // Assign the results to the bean
            } catch (SQLException SQLEx) {
                logger.warn(SQLEx);
            }

listOfBeans.add(articleBean);                                  //Add the now populated bean to the list to be returned for display
        }
    } catch (SQLException SQLEx) {
        logger.warn(&quot;You had an error mapping objects in ArticleProcessBean.getFrontpageArticles()&quot;);
        logger.warn(SQLEx);
    }
    try {
        result.close();
        dbaBean.close();
    } catch (SQLException SQLEx) {
        logger.warn(&quot;There was an error closing the resultset and the database connection.&quot;);
        logger.warn(SQLEx);
    }
    return listOfBeans;
}</pre>
<pre class='prettyprint' language='java'>package com.mysite.utilities;

import org.apache.log4j.Logger;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.*;

/**
 * Contains a collection of database access related functions.
 * Including update and execute.
 */
public class DatabaseBean {

private static final Logger logger = Logger.getLogger(DatabaseBean.class);

private Connection conn;
    private PreparedStatement prepStmt;

/**
     * Zero argument constructor
     * Setup generic databse connection in here to avoid redundancy
     * The connection details are in /META-INF/context.xml
     */
    public DatabaseBean() {
        try {
            InitialContext initContext = new InitialContext();
            DataSource ds = (DataSource) initContext.lookup(&quot;java:/comp/env/jdbc/mysite&quot;);
            conn = ds.getConnection();
        }
        catch (SQLException SQLEx) {
            logger.fatal(&quot;There was a problem with the database connection.&quot;);
            logger.fatal(SQLEx);
            logger.fatal(SQLEx.getCause());
        }
        catch (NamingException nameEx) {
            logger.fatal(&quot;There was a naming exception&quot;);
            logger.fatal(nameEx);
            logger.fatal(nameEx.getCause());
        }
    }

/**
     * Execute a query. Do not use for statements (update delete insert etc).
     *
     * @return A ResultSet of the execute query. A set of size zero if no results were returned. It is never null.
     * @see #executeUpdate() for running update, insert delete etc.
     */

public ResultSet executeQuery() {

// This is the returned query from the database.
        ResultSet result = null;
        try {
            result = prepStmt.executeQuery();
            logger.debug(prepStmt.toString());
        }
        catch (SQLException SQLEx) {
            logger.fatal(&quot;There was an error running a query&quot;);
            logger.fatal(SQLEx);
        }
        // Return the result set to the caller and finish this database call
        return result;
    }

/**
     * Close the open database connection. This method is called from the process beans as the connections cant be closed before the resultset is returned&lt;br /&gt;
     * &lt;p/&gt;
     * This closes both the statment and the connection
     */
    public void close() {
        try {
            prepStmt.close();
            prepStmt = null;

conn.close();
            conn = null;
        } catch (SQLException SQLEx) {
            logger.warn(&quot;There was an error closing the database connection.&quot;);
        }
    }

public Connection getConn() {
        return conn;
    }

public PreparedStatement getPrepStmt() {
        return prepStmt;
    }

public void setPrepStmt(PreparedStatement prepStmt) {
        this.prepStmt = prepStmt;
    }
}</pre>
<pre class='prettyprint' language='java'>&lt;Resource name=&quot;jdbc/mysite&quot;
              auth=&quot;Container&quot;
              type=&quot;javax.sql.DataSource&quot;
              username=&quot;user&quot;
              password=&quot;pass&quot;
              driverClassName=&quot;com.mysql.jdbc.Driver&quot;
              url=&quot;jdbc:mysql://localhost:3306/mysite?autoReconnect=true&quot;
              maxActive=&quot;10&quot;
              maxIdle=&quot;0&quot;
              maxWait=&quot;5000&quot;
              removeAbandoned=&quot;true&quot;
              removeAbandonedTimeout=&quot;15&quot;
              validationQuery=&quot;SELECT 1;&quot;
            /&gt;</pre>
<a href="http://www.refactormycode.com/codes/409-clsing-database-connections-properly" style="color:#fff" title="As seen on RefactorMyCode.com"><img alt="Small_logo" src="http://www.refactormycode.com/images/small_logo.gif" style="border:0" /></a>

Refactor :my => 'code'

Recent

Popular

Pastable version of Closing database connections properly