0

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.

cuongvm
  • 11
  • 5
  • I also try DataSource but still failed. DataSource unpooled = DataSources.unpooledDataSource("jdbc:mysql://localhost/my-database?autoReconnect=true&characterSetResults=UTF-8&characterEncoding=UTF-8&useUnicode=yes", "user", "password"); DataSource pooled = DataSources.pooledDataSource( unpooled ); – cuongvm Jul 27 '12 at 16:59

3 Answers3

1

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.

cuongvm
  • 11
  • 5
  • If you found a solution yourself, don't hesitate to [accept your own answer](http://meta.stackexchange.com/questions/16930/). – Björn Pollex Jul 30 '12 at 08:01
0

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.

Steve Waldman
  • 13,689
  • 1
  • 35
  • 45
  • Thanks, Steve Waldman. I turn on c3p0 logger. It says: Could not load driverClass com.mysql.jdbc.Driverjava.lang.ClassNotFoundException: com.mysql.jdbc.Driver not found by c3p0-0.9.1.2 [22] at org.apache.felix.framework.BundleWiringImpl.findClassOrResourceByDelegation(BundleWiringImpl.java:1460) at org.apache.felix.framework.BundleWiringImpl.access$400(BundleWiringImpl.java:72) at org.apache.felix.framework.BundleWiringImpl$BundleClassLoader.loadClass(BundleWiringImpl.java:1843) at java.lang.ClassLoader.loadClass(Unknown Source) – cuongvm Jul 27 '12 at 18:03
  • It seems there is a problem with class loader to find MySQL. I don't know how to fix it because I'm new to Apache Felix world. – cuongvm Jul 27 '12 at 18:05
  • According to [link]http://stackoverflow.com/questions/2198928/better-handling-of-thread-context-classloader-in-osgi[link] and [link]http://stackoverflow.com/questions/9911587/loading-resources-with-the-context-loader-fails-with-a-nullpointerexception[link], when c3p0 creates a new thread for db connection, that thread does not share the same class loader context. It causes MySQL class not found exception. How can I solve it? – cuongvm Jul 27 '12 at 18:17
  • I don't know very much about Apache Felix, but one straightforward thing to try is to place the jar file containing the mysql driver in the same place as the c3p0 jar file(s). [c3p0-0.9.2pre4, the latest version, needs two jar files.] alternatively, you might place the mysql jar file where it will be found by some "root" class loader, like in the lib/ext directory [ http://docs.oracle.com/javase/tutorial/ext/basics/install.html ], which most threads should be able to see. – Steve Waldman Jul 28 '12 at 00:04
  • I combine c3p0 and MySQL into a bundle but the problem is still there. The root cause is when c3p0 creates a new thread, this thread accesses to different class loader context other than OSGi bundle context and it does not find class path to MySQL. I use dbcp instead and it works. See my below message. Thanks, Steve! – cuongvm Jul 28 '12 at 03:26
0

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.

Arjan Tijms
  • 37,782
  • 12
  • 108
  • 140
Peter Kriens
  • 15,196
  • 1
  • 37
  • 55
  • Could you take a look at my question please http://stackoverflow.com/questions/42566102/how-to-use-ops4j-pax-jdbc-sqlite-driver-adapter – Marc Mar 02 '17 at 21:31