CODEWITHSUNDEEP
javajdbcconnection-pooling
AndrewBourgeois
AndrewBourgeois

Reputation: 2765

Share a JDBC connection pool between many classes

I have some c3p0 pool encapsulated in a class that I use to execute SQL statements. It's initialized this way:

public PooledQueryExecutor(String url, Properties connectionProperties) throws DALException {
        try {
            dataSource = new ComboPooledDataSource();
            dataSource.setDriverClass(DRIVER);
            dataSource.setJdbcUrl(url);
            dataSource.setProperties(connectionProperties);
        } catch (PropertyVetoException ve) {
            throw new DALException(ve);
        }
    }

Then - inside the same class - I use some methods to perform basic tasks:

public CachedRowSet executeSelect(String sql) throws DALException {
        // Get a connection, execute the SQL, return the rows that match, return resources to the pool
    }

The "question" is:
I have a lot of different classes that represent network packets that I receive. Most classes need to have this PooledQueryExecutor to perform DB operations, but some don't. Do I pass this PooledQueryExecutor to the constructor of the classes that need it (80% of the packets), or do I make the PooledQueryExecutor a singleton? Or maybe "something else"? I also though of using a ThreadLocal to avoid polluting my constructor, but I don't think that's a good idea, is it?

EDIT: It's not a web application, and no dependency injection framework is currently used.

Thank you for your time!

Upvotes: 2

Views: 955

Answers (4)

Tom Anderson
Tom Anderson

Reputation: 47223

The objects represent network packets? What are they using database connections for? This doesn't feel right to me. Would you like to talk a bit more about what you're trying to do, and what you architecture is?

I would lean towards making the packet objects a pure model of the domain - they can hold state, and they should define behaviour, but they shouldn't be in the business of grubbing around in databases. The database access in should be a different part of the system, a storage layer or subsystem. It's quite possible that key objects in that will be singletons (or, at least, that they will have a single instance, even if they're not formal singletons), and will be able to quite naturally contain a single global connection pool.

There is then the question of how the domain objects interact with the storage subsystem. Indeed, you have essentially the same question there as you asked about the connection pools. In general, i look to have some sort of controller objects orchestrating the program's work; they are well-placed to talk to objects in different layers, and to introduce them to one another.

Concrete examples would make this explanation easier. Tell us about your packets!

Upvotes: 0

Tomasz Nurkiewicz
Tomasz Nurkiewicz

Reputation: 340873

I assume you are not using any DI framework? If this is the case you have a few choices:

  • Pass PooledQueryExecutor to the constructor of classes that require it. This is actually pretty good from testing and architecture perspective.

  • let the classes requiring JDBC implement some simple interface like:

interface PooledQueryExecutorAware {

    void setPooledQueryExecutor(PooledQueryExecutor executor);

}

Then you can even iterate over classes and discover which implement this and inject PooledQueryExecutor where needed. You are one step to rediscovering DI here, but never mind.

  • Similar approach would be to create an abstract base class that would require PooledQueryExecutorAware as a dependency and have protected final field holding it.

  • Let every class be aware PooledQueryExecutor - not recommended, unnecessary coupling

  • Singleton is the worst what you can do, untestable and hard to understand code. Please, don't.

  • ThreadLocal? Forget about it. Remember, explicitness is king.

Also have a look at JdbcTemplate. It is part of the Spring, but you can only include spring-jdbc.jar and few other without using the whole framework. I think it can easily replace your PooledQueryExecutor.

Upvotes: 2

Thorbjørn Ravn Andersen
Thorbjørn Ravn Andersen

Reputation: 75406

A common way to do in e.g. web servers it is to have JNDI up and running and then get datasources from JNDI. Simpel JDNI implementations exist for stand alone applications.

If you use dependency injection in your program, then this is an excellent example of a resource that can be injected where needed. This either mean Java EE 6 or a Spring/Guice/CDI enabled application.

Upvotes: 0

Roger Lindsjö
Roger Lindsjö

Reputation: 11553

I prefer setting (injecting) the pool. That way you can decide if some instances should get different pools, you can wrap the pool(s) with loggers etc and it is clear which classes needs the pool.

I also think this makes the code easier to test.

Upvotes: 0

Related Questions