Java multithreading and connection pooling with PreparedStatement

Question

I currently have a Database class, with PreparedStatement member variables that are initialized in the constructor. Something like this:

public class Database
{
    private Connection connection;
    private PreparedStatement statement1, statement2, ...;

    public Database(String url, String user, String pass)
    {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        connection = DriverManager.getConnection(url, user, pass);

        statement1 = connection.prepareStatement("sql stuff");
        statement2 = connection.prepareStatement("sql stuff");
        // etc
    }

    public User getUser(int userId)
    {
        // execute getUser statement
    }
    // and other similar methods
}

The application will be multithreaded and I would like to use c3p0 for connection pooling. But I have no idea how to go about it.

Let's say I create a Database object for every thread, and the constructor now gets a connection from the pool. Each thread is supposed to only call one of the methods (with max 5 queries), then end. Will I have to initialize all the prepared statements every time? If yes, wouldn't it take too long?

Is there a better way I could do this?

Answer 1

One of the advantages of a connection pool is that it re-uses existing connections, something which your current implementation does not do. So the question "wouldn't it take too long to initialize all the prepared statements each time?" is not really relevant since creating a new database connection each time will most likely take much longer than initializing the prepared statements each time. Even if the prepared statements are initialized each time and never re-used, I doubt you will notice any performance difference because executing the database statements takes a much longer time than initializing prepared statements.

That being said, most JDBC drivers will have an option to cache prepared statements (i.e. this is not strictly up to the connection pool). See for example the MySQL configuration options here (cachePrepStmts, prepStmtCacheSize and prepStmtCacheSqlLimit). But please keep in mind that these optimizations are "nice to have", first and foremost make sure your program works correctly in the multi-threaded scenario (e.g. ensure you always return a connection borrowed from the pool to the pool, even when (runtime) exceptions occur) and is maintainable.