Pastable version of Closing database connections properly

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>
 </div>
 </div>

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>
 </div>
 </div>

<div style="overflow:auto;border:solid 1px #ccc;background:#000;color:#F8F8F8">
 <div class="section">
 Context.xml
 <pre style="float:left;margin:0 10px;border-right:0;color:#666;">1
2
3
4
5
6
7
8
9
10
11
12
13
14</pre>
 <pre class="sunburst"> &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>
 </div>
 </div>

Refactor :my => 'code'

Recent

Popular

Pastable version of Closing database connections properly