You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by "daad@libero.it" <da...@libero.it> on 2007/09/08 23:03:38 UTC
Connection pooling, is it the right way to do it?
Hi, i have done a server web application that runs on tomcat.
The server handle more clients and since i have to access to db, i tried to implement the connection pooling.
Since i had some problems with tomcat (crashes after some days telling thatthe connection pool was full, or something like it), i wish to be sure i'm doing the right things to use the connection pool.
Anyway, after reinstall of tomcat, it seems to work a lot better than before, but i still wish to know if the way i do it has sense.
The configuration of context.xml file is:
<Context path="/SomeApp" docBase="SomeApp" debug="5" reloadable="true" crossContext="true">
<Resource
name="jdbc/SomeAppDB"
auth="Container"
type="javax.sql.DataSource"
maxActive="300"
maxIdle="30"
maxWait="5000"
username="user"
password="pass"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://127.0.0.1:3306/db_db?autoReconnect=true"/>
</Context>
The setting in web.xml are:
...
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/SomeAppDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
...
then i have a class with all methods that access to db, and it's like:
class Database {
private Connection getConnection() throws Exception {
// get context: provides the starting point for resolution of names
Context ctx = new InitialContext();
if (ctx == null) {
throw new Exception("No Context");
}
// retrieve datasource
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/SomeAppDB");
if (ds == null) {
throw new Exception("No Datasource");
}
// return db connection
return ds.getConnection();
}
public Object getSomething() {
Connection con = null;
Statement stmt = null;
ResultSet rst = null;
try {
// get connection
con = getConnection();
if (con == null) {
throw new Exception("No Connection");
}
stmt = con.createStatement();
// perform query
rst = stmt.executeQuery("SELECT something FROM some_table WHERE id = '1'");
if (rst.next()) {
return rst.getString("something");
}
else {
throw new Exception("No results");
}
}
catch (Exception e1) {
return null;
}
// close resources
finally {
try {
rst.close();
rst = null;
}
catch (Exception e2) {
}
try {
stmt.close();
stmt = null;
}
catch (Exception e3) {
}
try {
con.close();
con = null;
}
catch (Exception e4) {
}
}
return null;
}
public Object getSomeOtherThing() {
Connection con = null;
Statement stmt = null;
ResultSet rst = null;
try {
// get connection
con = getConnection();
if (con == null) {
throw new Exception("No Connection");
}
...
}
...
>From all other classes of the application, i just use:
(new Database()).getSomething();
to call the method that access to db that i need.
Is it the correct way to use the connection pooling?
Database class should be done in a different way? For example, methods inside it, should be static so i don't need to create each time a Database object just to call one?
Any suggestion about it is very appreciated. Thanks
------------------------------------------------------
Leggi GRATIS le tue mail con il telefonino i-mode di Wind
http://i-mode.wind.it/
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org
Re: Connection pooling, is it the right way to do it?
Posted by David Smith <dn...@cornell.edu>.
Hi.
1. Your docBase and path attributes are at best optional. As long as
your context.xml is delivered in your webapp's META-INF folder, it
should be used automagically.
2. I would trade the autoReconnect=true parameter to the database url
with a validationQuery attribute in the <Resource .../> element. That
tells the db pool to do use the query to do a quick test of the
connection (and possibly recreate it) before you get it.
3. I see a lot of places where you catch and summarily eat the exception
with no logging or even passing it up to the method's caller. If you
don't pass it up and it's significant to the response, at least log it.
If you are throwing a new exception in response to an underlying
exception, pass the root cause along.
4. If the Database class is truly stateless (no class instance
variables) like what's listed, static methods are fine. However, I would
personally keep the DataSource object around after the first access in
some manner as a performance optimization for subsequent method calls.
Maybe something to the effect of:
db = new Database() ;
Object something = db.getSomething() ;
Object something2 = db.getSomeOtherThing() ;
In that case, store the DataSource object in the Database class and
don't make any methods static.
Otherwise it looks good. Have you tried the code?
--David
daad@libero.it wrote:
> Hi, i have done a server web application that runs on tomcat.
> The server handle more clients and since i have to access to db, i tried to implement the connection pooling.
> Since i had some problems with tomcat (crashes after some days telling thatthe connection pool was full, or something like it), i wish to be sure i'm doing the right things to use the connection pool.
> Anyway, after reinstall of tomcat, it seems to work a lot better than before, but i still wish to know if the way i do it has sense.
>
> The configuration of context.xml file is:
>
> <Context path="/SomeApp" docBase="SomeApp" debug="5" reloadable="true" crossContext="true">
> <Resource
> name="jdbc/SomeAppDB"
> auth="Container"
> type="javax.sql.DataSource"
> maxActive="300"
> maxIdle="30"
> maxWait="5000"
> username="user"
> password="pass"
> driverClassName="com.mysql.jdbc.Driver"
> url="jdbc:mysql://127.0.0.1:3306/db_db?autoReconnect=true"/>
> </Context>
>
>
> The setting in web.xml are:
>
> ...
> <resource-ref>
> <description>DB Connection</description>
> <res-ref-name>jdbc/SomeAppDB</res-ref-name>
> <res-type>javax.sql.DataSource</res-type>
> <res-auth>Container</res-auth>
> </resource-ref>
> ...
>
>
> then i have a class with all methods that access to db, and it's like:
>
> class Database {
>
> private Connection getConnection() throws Exception {
> // get context: provides the starting point for resolution of names
> Context ctx = new InitialContext();
> if (ctx == null) {
> throw new Exception("No Context");
> }
> // retrieve datasource
> DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/SomeAppDB");
> if (ds == null) {
> throw new Exception("No Datasource");
> }
> // return db connection
> return ds.getConnection();
> }
>
> public Object getSomething() {
> Connection con = null;
> Statement stmt = null;
> ResultSet rst = null;
> try {
> // get connection
> con = getConnection();
> if (con == null) {
> throw new Exception("No Connection");
> }
> stmt = con.createStatement();
> // perform query
> rst = stmt.executeQuery("SELECT something FROM some_table WHERE id = '1'");
> if (rst.next()) {
> return rst.getString("something");
> }
> else {
> throw new Exception("No results");
> }
> }
> catch (Exception e1) {
> return null;
> }
> // close resources
> finally {
> try {
> rst.close();
> rst = null;
> }
> catch (Exception e2) {
> }
> try {
> stmt.close();
> stmt = null;
> }
> catch (Exception e3) {
> }
> try {
> con.close();
> con = null;
> }
> catch (Exception e4) {
> }
> }
> return null;
> }
>
> public Object getSomeOtherThing() {
> Connection con = null;
> Statement stmt = null;
> ResultSet rst = null;
> try {
> // get connection
> con = getConnection();
> if (con == null) {
> throw new Exception("No Connection");
> }
> ...
> }
>
> ...
>
>
> From all other classes of the application, i just use:
>
> (new Database()).getSomething();
>
>
> to call the method that access to db that i need.
> Is it the correct way to use the connection pooling?
> Database class should be done in a different way? For example, methods inside it, should be static so i don't need to create each time a Database object just to call one?
> Any suggestion about it is very appreciated. Thanks
>
>
> ------------------------------------------------------
> Leggi GRATIS le tue mail con il telefonino i-mode™ di Wind
> http://i-mode.wind.it/
>
>
> ---------------------------------------------------------------------
> To start a new topic, e-mail: users@tomcat.apache.org
> To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
> For additional commands, e-mail: users-help@tomcat.apache.org
>
>
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org