JDBC Connection pooling using C3P0

Question

Following is my helper class to get DB connection:

I've used the C3P0 connection pooling as described here.

public class DBConnection {

    private static DataSource dataSource;
    private static final String DRIVER_NAME;
    private static final String URL;
    private static final String UNAME;
    private static final String PWD;

    static {

        final ResourceBundle config = ResourceBundle
                .getBundle("props.database");
        DRIVER_NAME = config.getString("driverName");
        URL = config.getString("url");
        UNAME = config.getString("uname");
        PWD = config.getString("pwd");

        dataSource = setupDataSource();
    }

    public static Connection getOracleConnection() throws SQLException {
        return dataSource.getConnection();
    }

    private static DataSource setupDataSource() {
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        try {
            cpds.setDriverClass(DRIVER_NAME);
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
        cpds.setJdbcUrl(URL);
        cpds.setUser(UNAME);
        cpds.setPassword(PWD);
        cpds.setMinPoolSize(5);
        cpds.setAcquireIncrement(5);
        cpds.setMaxPoolSize(20);
        return cpds;
    }
}

in the DAO i'll be writing something like this:

try {
            conn = DBConnection.getOracleConnection();

            ....


} finally {
    try {
        if (rs != null) {
            rs.close();
        }
        if (ps != null) {
            ps.close();
        }
        if (conn != null) {
            conn.close();
        }
    } catch (SQLException e) {
        logger
                .logError("Exception occured while closing cursors!", e);

    }

Now, my question is should I bother to do any other clean up other than closing the cursors(connection/statement/resultSet/preparedStatement) listed in the finally block.

What is this cleanup?? When and where should I do this?

Should you find anything wrong in the above code, please point out.

in JDK 7 you don't need to close preparedStatement, it implements AutoClosable. [due to this post](http://stackoverflow.com/questions/14862853/resource-leak-warning-in-eclipse) — Amin Sh, Apr 19 '14 at 13:47
@AminSh your comment is misleading, a class implementing `AutoClosable` doesn't mean that _you don't need to close_ it. It just gives you the option to use the objects of that class inside `try-with-resources` blocks. — Miss Chanandler Bong, Oct 28 '21 at 13:50

score 27 · Accepted Answer · answered Sep 22 '09 at 08:01

27

With a pooled data source, the connections in the pool are not actually closed, they just get returned to the pool. However, when the application is shut down, those connections to the database should be properly and actually closed, which is where the final cleanup comes in.

Incidentally, the c3p0 project is pretty much dead in the water, I recommend you use Apache Commons DBCP instead, it's still being maintained.

answered Sep 22 '09 at 08:01

skaffman

398,947
96
818
769

7

@skaffman: I was about to switch from DBCP to C3P0 after reading nearly the opposite to what you've said here: http://stackoverflow.com/questions/520585/connection-pooling-options-with-jdbc-dbcp-vs-c3p0. Now I'm not so sure - Seems like neither project has had much TLC recently. – Adamski Jan 28 '10 at 19:26
2

Use BoneCP, very active, excellent tool, good documentation, very fast. We use it at very high traffic site and have no problems. – Janning Vygen Mar 27 '12 at 13:32
2

As of this comment, rumours of c3p0's death seem greatly exaggerated: http://sourceforge.net/projects/c3p0/ (last repo update is 2014-04-04). – Josh Glover May 08 '14 at 09:30
7

A database connection pool does not need a staggering amount of features. Once it's done and its most prominent bugs are fixed, you should not expect too much activity, even if its maintainers do still care about it. – flup Jul 15 '14 at 08:20
1

I am still using c3p0 connection pool in 2018. It solved my problem which occurs due to dbcp. – Mr. Aniket Dec 25 '18 at 05:40

score 7 · Answer 2 · answered Sep 22 '09 at 09:53

DAOs should not be responsible for acquiring a connection to the database. They have no way to know when they're being used as part of a larger transaction. You should be passing the data source or connection instance into the DAO.

If any of the calls to close in your finally block throw an exception, none of the ones that follow will be called. Each one needs to be in its own try/catch block. I put them into a utility class as static methods.

score 5 · Answer 3 · answered Sep 22 '09 at 08:03

The code looks fine to me, but I would write a helper method that does the close operations or you'll get this verbose finally-block in every DAO or method. Maybe you should write three separate try-catch-blocks around the close Operations, to make sure the connection is closed no matter if the statement and resultset have thrown an exection. Also note that the javadoc says

When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

So you don't need to close the resultset in the above example, but you could.

The linked cleanup method is for closing the datasource, what isn't needed in most projects because the DS lives as long as your app is running.

score 0 · Answer 4 · answered Nov 24 '14 at 20:43

I use Play Framework and Scala, so the following example is in play project.

Step1. configuration

In build.sbt, if you use mysql/hive as database, you need to add these properties.

libraryDependencies ++ = Seq (
   jdbc,
  "mysql" % "mysql-connector-java" % "5.1.31",
  "org.apache.hive" % "hive-jdbc" % "0.12.0",
  "com.mchange" % "c3p0" % "0.9.2.1"
)

Step2. how to access it? you need to import c3p0 library.

import com.mchange.v2.c3p0.ComboPooledDataSource

Step3. and then you need to create instance.

val cpds = new ComboPooledDataSource()
cpds.setDriverClass(...)
cpds.setJdbcUrl(...)
cpds.setUser(...)
cpds.setPassword(...)

Step4. you get a connection

cpds.getConnection

JDBC Connection pooling using C3P0

4 Answers4

Linked