Database connection pooling

[Ted Zeng]

Hi all,

I am new to Axis2 and database connection pooling.
I read the article "Exposing a Database as a Web Service" by Deepal Jayasinghe. 
Thanks to Deepal otherwise I don't know where to start.
Then I used the sample code there and modified for my use.
Everything works well except the database connection timeout.

Most articles about connection pool is using app. Server. But I use the stand 
alone Axis2.
So no help there. In fact, a question "DB connection pool in Standalone Axis2?" 
in stackoverflow.com never gets answered.

 After quite a bit of reading and searching Axis2 source, I realized that the 
following statement
configctx.setProperty(DB_CONNECTION, conn);
Did nothing more than store the connection object for later retrieval. For 
whatever reason, I kept thinking
The ServiceLifeCycle helps managing the database connection.

Then I looked up the DB connection pool and choose org.apache.tomcat.jdbc.pool 
to implement the pooling.
Here is what I did to implement the DB connection pool (pretty much the same as 
the sample code in tomcat jdbc pool website).
First, I set up the DataSource and store it instead of connection in 
ServiceLifeCycle.

              Class.forName("com.mysql.jdbc.Driver");
            // Creating the DB connection for our DB
                PoolProperties p = new PoolProperties();
                
p.setUrl("jdbc:mysql://localhost:3306/AndroidAuto?autoReconnect=true");
                p.setDriverClassName("com.mysql.jdbc.Driver");
                ..
                p.setJmxEnabled(true);
                p.setTestOnBorrow(true);
                p.setValidationQuery("SELECT 1");
                p.setValidationInterval(30000);
                p.setTimeBetweenEvictionRunsMillis(30000);
                p.setMaxActive(100);
                p.setInitialSize(10);
                ..
                
p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
                  "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
                DataSource datasource = new DataSource();
                datasource.setPoolProperties(p);
                //Storing the DB in the ConfigurationContext
                configctx.setProperty(DB_DATASOURCE, datasource);

Then the datasource is retrieved and connection is created from it. After DB 
operation,
The connection is closed and returned to the pool. I am not sure if this is the 
right way to implement
DB conn pool in Axis2.

        DataSource datasource = (DataSource) 
MessageContext.getCurrentMessageContext().getProperty(
                DBServiceLifeCycle.DB_DATASOURCE);
        Connection conn = null;
        try {
            conn = datasource.getConnection();
            String SQL = "SELECT xxx  FROM Devices";
            PreparedStatement statement = conn.prepareStatement(SQL);
            ResultSet result = statement.executeQuery();
            ... (get the result)
            result.close();
            statement.close();
            return (String[])list.toArray(new String[list.size()]);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
          if (conn!=null) try {conn.close();}catch (Exception ignore) {}
        }

I hope my approach is right. I am testing the code. Not sure why the first call 
take a couple of seconds. But
Fine after that. I need to wait to 6 hours (I don't know how to modify the 
mysql timeout yet) to test the Timeout condition.

Thanks,

Happy Holiday!

Ted Zeng
Adobe Systems