Hi Ted,
Managing database connections (or any other types of connection)
entirely up to the service author. He can write his service and manage
connection as he wish. However, since Axis2 has the notion of session
management, if you create and store your DB connections inside the
service impl class then depending on the session type (whether
transport, application, request etc) lifetime of a DB connection varies.
That is why we have introduced ServiceLifeCycle class, once you store
your DB connection inside the serviceLifeCycle it will be there until
you restart your application.
Regarding the connection pool, you do not need to close DB connection
every time when you use it, you can store them in the pool and put them
back to the pool after each usage.
On 12/30/2011 2:49 PM, Ted Zeng wrote:
> 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
--
Blog - http://blogs.deepal.org/
