Avatar

I am making a blog for fun and learning. I have connecting and running queries fine, however I can not work out how to close the database connection properly. Using MySql Administrator, the connections sit in the connection window with a status of idle. New connections are made for each request that needs them until I run out. Then an error is thrown and the pool will kill off all the old connections and run fine until it runs out again.

The getFrontpageArticles is representative of how all the bean functions work.

Any help is greatly appreciated.

Function from the Articles bean.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
/**
 * Get the article destined for the front page
 *
 * @return a linked list of the articles
 */
public LinkedList<ArticleBean> getFrontpageArticles() {
    DatabaseBean dbaBean = new DatabaseBean();
    LinkedList<ArticleBean> listOfBeans = new LinkedList<ArticleBean>();

    try {
        dbaBean.setPrepStmt(dbaBean.getConn().prepareStatement(
                "SELECT id, title, url, author, date_time, article_text " +
                        "FROM articles " +
                        "ORDER BY id desc " +
                        "LIMIT 5;"));
    } catch (SQLException SQLEx) {
        logger.fatal("Problem with the SQL in the ArticleProcessBean.getFrontpageArticles() function");
        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("id"));
            articleBean.setAuthor(result.getString("author"));
            articleBean.setTitle(result.getString("title"));
            articleBean.setURL(result.getString("url"));
            articleBean.setArticleText(result.getString("article_text"));
            articleBean.setDateTime(result.getTimestamp("date_time"));

            // Process this articles tags
            try {
                TagProcessBean tagProcessBean = new TagProcessBean();
                tagProcessBean.setArticleID("" + result.getInt("id"));     // 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("You had an error mapping objects in ArticleProcessBean.getFrontpageArticles()");
        logger.warn(SQLEx);
    }
    try {
        result.close();
        dbaBean.close();
    } catch (SQLException SQLEx) {
        logger.warn("There was an error closing the resultset and the database connection.");
        logger.warn(SQLEx);
    }
    return listOfBeans;
}

The database bean

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
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("java:/comp/env/jdbc/mysite");
            conn = ds.getConnection();
        }
        catch (SQLException SQLEx) {
            logger.fatal("There was a problem with the database connection.");
            logger.fatal(SQLEx);
            logger.fatal(SQLEx.getCause());
        }
        catch (NamingException nameEx) {
            logger.fatal("There was a naming exception");
            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("There was an error running a query");
            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<br />
     * <p/>
     * 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("There was an error closing the database connection.");
        }
    }

    public Connection getConn() {
        return conn;
    }

    public PreparedStatement getPrepStmt() {
        return prepStmt;
    }

    public void setPrepStmt(PreparedStatement prepStmt) {
        this.prepStmt = prepStmt;
    }
}

Context.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
    <Resource name="jdbc/mysite"
              auth="Container"
              type="javax.sql.DataSource"
              username="user"
              password="pass"
              driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/mysite?autoReconnect=true"
              maxActive="10"
              maxIdle="0"
              maxWait="5000"
              removeAbandoned="true"
              removeAbandonedTimeout="15"
              validationQuery="SELECT 1;"
            />

Refactorings

No refactoring yet !

Fcd45b7de93cc8fa417fa43426973e06

JonM1827

July 31, 2008, July 31, 2008 04:28, permalink

No rating. Login to rate!

I have never really done anything with this before, but I just did a little bit of a google search... here is what I got http://tinyurl.com/5t262e (check out the very bottom of the page). I'm not sure if it applies to you or not but maybe try adding shutdown=true to the context file

Sorry if it has nothing to do with this, but hopefully that at least puts you in the right direction...

-Jon

B8d457d2c39911ea4c74ba7d66b9c3f7

Marco Valtas

July 31, 2008, July 31, 2008 19:06, permalink

No rating. Login to rate!

Hi, you should probably use a singleton pattern (http://en.wikipedia.org/wiki/Singleton_pattern) on your DatabaseBean to get the context and connection only once for each run, if you application get a connection in one class, then passes the result to another which gets connection too you´re probably consuming more resources then you need.

Way back in time, before Hibernate, the CementJ´s DatabaseUtility (http://tinyurl.com/6jqgxn) was a good choice to close result sets and connections, still I have in some projects.

Hope this helps.

50a73f68addb19a2738ff1962e366afb

CDH

August 15, 2008, August 15, 2008 12:43, permalink

No rating. Login to rate!

One common thing to do is put the close in a finally statement. That way no matter how you leave the method you will close the connection. You could also break up the closes into multiple trys that way an exception with the first close will not cause you to miss the second.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
/**
 * Get the article destined for the front page
 *
 * @return a linked list of the articles
 */
public LinkedList<ArticleBean> getFrontpageArticles() {
    DatabaseBean dbaBean = new DatabaseBean();
    LinkedList<ArticleBean> listOfBeans = new LinkedList<ArticleBean>();
    ResultSet result = null;
    try {

	    try {
	        dbaBean.setPrepStmt(dbaBean.getConn().prepareStatement(
	                "SELECT id, title, url, author, date_time, article_text " +
	                        "FROM articles " +
	                        "ORDER BY id desc " +
	                        "LIMIT 5;"));
	    } catch (SQLException SQLEx) {
	        logger.fatal("Problem with the SQL in the ArticleProcessBean.getFrontpageArticles() function");
	        logger.fatal(SQLEx);
	    }
	
	    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("id"));
	            articleBean.setAuthor(result.getString("author"));
	            articleBean.setTitle(result.getString("title"));
	            articleBean.setURL(result.getString("url"));
	            articleBean.setArticleText(result.getString("article_text"));
	            articleBean.setDateTime(result.getTimestamp("date_time"));
	
	            // Process this articles tags
	            try {
	                TagProcessBean tagProcessBean = new TagProcessBean();
	                tagProcessBean.setArticleID("" + result.getInt("id"));     // 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("You had an error mapping objects in ArticleProcessBean.getFrontpageArticles()");
	        logger.warn(SQLEx);
	    }
	    return listOfBeans;
    }
    finally {
    	try {
	    	if(result != null)
	    		result.close();
	    } catch (SQLException SQLEx) {
	        logger.warn("There was an error closing the resultset and the database connection.");
	        logger.warn(SQLEx);
	    }
	    try {
	        dbaBean.close();
	    } catch (SQLException SQLEx) {
	        logger.warn("There was an error closing the resultset and the database connection.");
	        logger.warn(SQLEx);
	    }
    }
}
50a73f68addb19a2738ff1962e366afb

CDH

August 15, 2008, August 15, 2008 12:44, permalink

No rating. Login to rate!

One common thing to do is put the close in a finally statement. That way no matter how you leave the method you will close the connection. You could also break up the closes into multiple trys that way an exception with the first close will not cause you to miss the second.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
/**
 * Get the article destined for the front page
 *
 * @return a linked list of the articles
 */
public LinkedList<ArticleBean> getFrontpageArticles() {
    DatabaseBean dbaBean = new DatabaseBean();
    LinkedList<ArticleBean> listOfBeans = new LinkedList<ArticleBean>();
    ResultSet result = null;
    try {

	    try {
	        dbaBean.setPrepStmt(dbaBean.getConn().prepareStatement(
	                "SELECT id, title, url, author, date_time, article_text " +
	                        "FROM articles " +
	                        "ORDER BY id desc " +
	                        "LIMIT 5;"));
	    } catch (SQLException SQLEx) {
	        logger.fatal("Problem with the SQL in the ArticleProcessBean.getFrontpageArticles() function");
	        logger.fatal(SQLEx);
	    }
	
	    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("id"));
	            articleBean.setAuthor(result.getString("author"));
	            articleBean.setTitle(result.getString("title"));
	            articleBean.setURL(result.getString("url"));
	            articleBean.setArticleText(result.getString("article_text"));
	            articleBean.setDateTime(result.getTimestamp("date_time"));
	
	            // Process this articles tags
	            try {
	                TagProcessBean tagProcessBean = new TagProcessBean();
	                tagProcessBean.setArticleID("" + result.getInt("id"));     // 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("You had an error mapping objects in ArticleProcessBean.getFrontpageArticles()");
	        logger.warn(SQLEx);
	    }
	    return listOfBeans;
    }
    finally {
    	try {
	    	if(result != null)
	    		result.close();
	    } catch (SQLException SQLEx) {
	        logger.warn("There was an error closing the resultset and the database connection.");
	        logger.warn(SQLEx);
	    }
	    try {
	        dbaBean.close();
	    } catch (SQLException SQLEx) {
	        logger.warn("There was an error closing the resultset and the database connection.");
	        logger.warn(SQLEx);
	    }
    }
}

Your refactoring





Format Copy from initial code

or Cancel