Get a DataSource/Connection from C3P0 connection pool

c3p0, connection-pooling, datasource, hibernate, java

Solution

If you are using hibernate and you want access to the c3p0 Connection pool it is already using , one easy approach would be to use the C3P0Registry class to find the DataSource, see here and here.

Probably you will find that getPooledDataSources() returns a Set containing single element, and that will be the DataSource hibernate has constructed. If you want to, you can also set the config param c3p0.dataSourceName (hibernate.c3p0.dataSourceName in hibernate config), and use C3P0Registry.pooledDataSourcesByName( dataSourceName ).

[If you will set your own name, it would probably be worth verifying that hibernate is not using the dataSourceName property itself. I don't think that it does, but I haven't checked. The easiest way to check would be to look at your logs for the pools config dump on init, and make sure that there's something like "dataSourceName -> z8kflt8uqkl8iymaxxkw|729f44" in it. If the name is a long random-ish String with a pipe in it, it's an instance-specific autogenerated identity token and you should feel free to set your own name. If you see a more sensible name, then hibernate has already set this property and may be expecting the name you see, so you should look up that name.]

Note that if you plan to use Connections from the DataSource directly, take care to ensure that all Connections get properly close()ed in finally blocks. If you "leak" Connections, i.e. if you check them out and fail to check them back in reliably, you'll eventually exhaust the pool and freeze your hibernate app.

Good luck!

Update: Example...

import java.util.Set;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.C3P0Registry;

// you probably want better Exception handling than this...
private DataSource findUniqueDataSource()
{
    Set set = C3P0Registry.getPooledDataSources();
    int sz = set.size();
    if ( sz == 1 ) // yay, just one DataSource
      return (DataSource) set.iterator().next();
    else 
      throw new RuntimeException("No unique c3p0 DataSource, found:" + sz);
}

// be sure you have configured a dataSourceName in your c3p0 or hibernate config
private DataSource findDataSourceByName( String dataSourceName )
{ return C3P0Registry.pooledDataSourceByName(dataSourceName); }

And no, you should not "leak" Connections and expect the pool to clean up after you. You can forget to close Statements and ResultSets, and the pool will take care of them when you close() the Connection, but the pool doesn't know when it's safe to snatch a Connection back from a client that has failed to close it. Some applications hold Connections open for a long time (although that's bad practice, if you are using a Connection pool).

You can force c3p0 to clean up leaked Connections after a period of time, see the config parameter unreturnedConnectionTimeout. But this is an icky strategy; I recommend if you have a leak to use this in concert with debugUnreturnedConnectionStackTraces only temporarily, to understand where you are leaking Connections and then fix the problem.

Problem

Original source