MySQL, c3p0 and Apache Felix integration

Question

I integrate MySQL into Apache Felix. First, I use bndtools to generate MySQL bundle and c3p0 bundle. Then I add them all into my Apache Felix environment. I create a class for connection pool like below:

    public final class C3P0Manager {

        private static C3P0Manager instance;
        private DataSource pooled;

        private C3P0Manager() {
            // Of course, it is better to put all properties into a configuration file.
            // I just list them here for easy reading.

            ComboPooledDataSource cpds = new ComboPooledDataSource();
            cpds.setDriverClass("com.mysql.jdbc.Driver"));
            cpds.setJdbcUrl("jdbc:mysql://localhost/my-database?autoReconnect=true&characterSetResults=UTF-8&characterEncoding=UTF-8&useUnicode=yes");
            cpds.setUser("user");
            cpds.setPassword("password");
            cpds.setInitialPoolSize(3);
            cpds.setMaxPoolSize(15);
            cpds.setMaxIdleTime(1800);
            cpds.setAutoCommitOnClose(true);
            pooled(cpds);
        }

        public static C3P0Manager instance() throws Exception {
            if (instance == null) {
                instance = new C3P0Manager();
            }
            return instance;
        }

        public DataSource getPooled() throws SQLException {
            return pooled;
        }
    }

It works fine if I run JUnit test. But failed when running on my Apache Felix bundle with exception message. Usage in Activator class:

    Class.forName("com.mysql.jdbc.Driver");
    DataSource pooled = C3P0Manager.instance().getPooled();
    Connection con = pooled.getConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    int total;

    try {
        stmt = con.prepareStatement("SELECT count(*) FROM users", Statement.NO_GENERATED_KEYS);
        rs = stmt.executeQuery();
        if (rs.next()) {
            total = rs.getInt(1);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            rs.close();
            stmt.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    System.out.println("total = " + total);

Error messages:

    java.sql.SQLException: Connections could not be acquired from the underlying database!
        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:529)
        at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
    ...
    Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
        at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1319)
        at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
        at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
    ...

MySQL works if I just use (without c3p0):

    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost/my-database?autoReconnect=true&characterSetResults=UTF-8&characterEncoding=UTF-8&useUnicode=yes","user","password");
    Statement statement = connect.createStatement();
    ResultSet resultSet = statement.executeQuery("SELECT count(*) FROM users");

So I think the problem is due to c3p0. Please help me. Thanks.

Answer 1

Working like this (using Java EE patterns) in OSGi is setting you up for a lot of misery since many Java EE patterns are anti-modular, they require deep visibility off the class path. Since OSGi has strong modularity this does not work very well, requiring hacks like Context Classloader and other error prone things that in the end usually don't work. OSGi µservices were invented to handle this type of problems modular and much more elegantly.

The OSGi-JDBC specification (chapter 125) specifies how to use JDBC in a µservice world: The driver bundle is supposed to register a org.osgi.service.jdbc.DataSourceFactory service. With this service it is trivial to create your pooled Datasource. I know from experience that the H2 database does this out of the box (thanks H2 guys! Great product). Fortunately, Ops4j has a project here that provides an adapter for MySql. Since MySql is not standard available as an OSGi bundle you might look here

So after you install the MySql Driver and the PAX JDBC MySql Adaptor you can now use DataSource in a truly modular way:

@Component
public class DataSourceDemo {
   DataSource ds;

   @Activate
   void start() {
       Connection con = ds.getConnection();
       PreparedStatement stmt = con.prepareStatement("SELECT count(*) FROM users", Statement.NO_GENERATED_KEYS);
       ResultSet rs = stmt.executeQuery();
       if (rs.next()) {
        System.out.println(rs.getInt(1));
       }
   }

   @Reference
   void setDataSourceFactory( DataSourceFactory dsf ) throws Exception {
     ds = dsf.createDataSource();
   }
}

Notice how this also removes the MySql dependency from your code, singletons, statics (global variables) and dynamic class loading. Of course if you generate SQL that depends on MySQL you can make this explicit. You either use the @Reference like this:

 @Reference(target="(osgi.jdbc.driver.name=mysql)")

Or, better, you allow the deployer of your app to set this with Configuration Admin. Just set the DataSourceFactory.target property to "(osgi.jdbc.driver.name=mysql)", this allows you to dynamically wire the services.

Answer 2

I am lazy to modify c3p0 source and build new c3p0 bundle version again :). So I try Apache DBCP bundle instead:

org.apache.servicemix.bundles.commons-pool-1.5.4
org.apache.servicemix.bundles.commons-dbcp-1.4.0
(dbcp needs pool to work)

It is OK to CRUD MySQL database.

If anyone want to use those bundles, here they are:

http://mvnrepository.com/artifact/org.apache.servicemix.bundles/org.apache.servicemix.bundles.commons-pool
http://mvnrepository.com/artifact/org.apache.servicemix.bundles/org.apache.servicemix.bundles.commons-dbcp

When I have time, I will take a look at bonecp if someone already builds a bundle for it. Or modify c3p0 to use it.

Answer 3

Please check your logs. Prior to the Exception that you have shown above, c3p0 would have logged stack traces detailing at least one failed attempt to acquire a Connection from the database. If you set the logging level to FINE (if you want a specific logger, use com.mchange.v2.resourcepool.BasicResourcePool), you'll see each failed attempt logged. If you leave your log level at the conventional INFO (or WARN), you'll see only the final failed attempt in a "round" of failed attempts (by default 30 failed attempts with a 1 sec retry delay). If you've been logging at INFO, you should be able to find these Exceptions in your logs already.