Hi Deepal,
I read the following line:
"When using a connection pool, closing the connection just returns it to the
pool for reuse by another request, it doesn't close the connection."
>From
>http://tomcat.apache.org/tomcat-7.0-doc/jndi-datasource-examples-howto.html
So returning the connection to the pool is the same as closing it?
I do see exception "org.apache.tomcat.jdbc.pool.ConnectionPool abandon" from
time to time when I try to get a connection. I need to look at this.
Extra words: I saw you are the creator of Axis2. I would like to express my
appreciation for your great work.
I used SOAP 10 years ago to create a test automation framework which has been
using since. I used Perl SOAP module.
It was very hard to use. It made me afraid to use SOAP. This time around, I
found Axis2 and installed and started to use it
In almost no time. And it works much better than I expected. It is hard to
describe the feeling when I saw my own module running in Axis2.
Thank you!
Best,
-ted
On 12/30/11 2:16 PM, "Deepal Jayasinghe" <dee...@opensource.lk> wrote:
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:
Database connection pooling 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
