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 2008/03/13 15:53:59 UTC
Connection pooling
Hi,
i'm using tomcat 5.5.9 for a web server application where many clients connect and do stuffs.
each time they login to the server, for example, a database query is performed. Application works, but reading http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html#Common%20Problems some doubt come to my mind. I just want to be sure that i'm using connection pooling correctly.
In that article there is written:
Here is an example of properly written code to use a db connection obtained from a connection pool:
Connection conn = null;
Statement stmt = null; // Or PreparedStatement if needed
ResultSet rs = null;
try {
conn = ... get connection from connection pool ...
...
But what's the best way to "get connection from connection pool"?
Here is what i've done:
My context.xml file:
<Context debug="5" reloadable="true" crossContext="true">
<Resource
name="jdbc/someDB"
auth="Container"
type="javax.sql.DataSource"
maxActive="300"
maxIdle="30"
maxWait="15000"
username="someUser"
password="somePass"
driverClassName="com.mysql.jdbc.Driver"
validationQuery="SELECT 1"
url="jdbc:mysql://1.1.1.1:3306/somePath"/>
</Context>
My web.xml file:
<web-app xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd" version="2.4">
<display-name>Some name</display-name>
<listener>
<listener-class>somePackage.ApplicationWatch</listener-class>
</listener>
<servlet>
<servlet-name>htmlcontent</servlet-name>
<servlet-class>somePackage.HtmlContentServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>htmlcontent</servlet-name>
<url-pattern>/htmlcontent.view</url-pattern>
</servlet-mapping>
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/someDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
Then i have Database.java class where i have the method:
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/someDB");
if (ds == null) {
throw new Exception("No Datasource");
}
// return db connection
return ds.getConnection();
}
that get connection for all of the 20-30 methods in Database.java that do a query each. An example of method in Database.java that use it is:
// get user info
public UserData getUserData(UserData res, String un, String pw) {
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("some sql syntax");
if (rst.next()) {
.....
}
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 res;
}
At the moment, methods are not static, so each time a client thread need to login, for example, i do (new Database()).getUserData(res, un, pw).
First question is if that solution is correct or create each time a new Database class is useless/stupid. If it's stupid, should i make the methods static? or should i create a static database object each time server is started?
Second, is that getConnection() method a correct way for what the article (http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html#Common%20Problems) meant about "get connection from connection pool"?
Thanks for any suggestion.
---------------------------------------------------------------------
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
Posted by Christopher Schultz <ch...@christopherschultz.net>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Daad,
daad@libero.it wrote:
| i'm using tomcat 5.5.9 for a web server application where many
| clients connect and do stuffs. each time they login to the server,
| for example, a database query is performed. Application works, but
| reading
|
http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html#Common%20Problems
| some doubt come to my mind.
It's interesting that non-native English speakers often use the word
"doubt" rather than the more usual "question". Just FYI (and any other
non-native English speakers who happen to be reading), the word "doubt"
typically has a connotation closer to "suspicion", rather than an
interrogative. The better term in English is "question".
In Italiano, la parola "doubt" e piu similare a "uno dubbio", "una
domanda" non. Me dispiace per mio Italiano malo... seguo solo due
semestri d'Italiano. ;)
| [W]hat's the best way to "get connection from connection pool"?
|
| Here is what i've done:
Your context.xml looks just fine, as does web.xml.
| Then i have Database.java class where i have the method:
|
| private Connection getConnection() throws Exception {
This one also looks good. I would recommend that you use an
application-specific exception that any thrown exceptions, or that you
throw if any objects are null, but that's just my own personal feeling.
| that get connection for all of the 20-30 methods in Database.java
| that do a query each. An example of method in Database.java that use
it is:
|
| // get user info
| public UserData getUserData(UserData res, String un, String pw) {
| Connection con = null;
| Statement stmt = null;
| ResultSet rst = null;
| try {
[snip]
| }
| catch (Exception e1) {
| return null;
| }
You should not do this: exceptions should be (wrapped and) re-thrown or
logged (or both!). Never swallow an exception. Note that you will
swallow your own Exception that you throw in the body of the try/catch
block, thereby wasting the entire "throw" in the first place.
| // close resources
| finally {
| try {
| rst.close();
| rst = null;
Don't forget to check for null. This is an NPE just waiting to happen.
I would recommend a method in Database.java like this:
protected void close(Connection conn, Statement s, ResultSet r)
{
~ if(null != r)
~ try {
~ r.close();
~ } catch (SQLException sqle) {
~ logger.warn("Cannot close result", sqle);
~ }
~ if(null != s)
~ try {
~ s.close();
~ } catch (SQLException sqle) {
~ logger.warn("Cannot close statement", sqle);
~ }
~ if(null != conn)
~ try {
~ conn.close();
~ } catch (SQLException sqle) {
~ logger.warn("Cannot close connection", sqle);
~ }
}
...and use that, rather than repeating the code all over the place.
| At the moment, methods are not static, so each time a client thread
| need to login, for example, i do (new Database()).getUserData(res, un,
pw).
|
| First question is if that solution is correct or create each time a
| new Database class is useless/stupid.
This is a matter of taste, in my opinion. I would prefer to write an
interface called Database and then write an implementation of that
interface. Then, have a dependency-injection framework such as Spring
call setDatabase(Database) on the object that needs to access the
database (such as a servlet or whatever) and then use it that way. It
also makes it much easier to unit-test your code because you can "mock"
the Database class if you want to.
| If it's stupid, should i make the
| methods static? or should i create a static database object each time
| server is started?
Object allocation is cheap, so i wouldn't worry too much about it. Your
Database class does not contain any members, so it doesn't take up much
memory, etc.
| Second, is that getConnection() method a correct way for what the
| article
|
(http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.html#Common%20Problems)
| meant about "get connection from connection pool"?
Yes.
- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.8 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkfZcfcACgkQ9CaO5/Lv0PD85gCggizSgcs3NrvN1yCXIhjlvP8z
kFUAn1nTN+T96Z3G3p6RkQzJUYswcjl7
=zRYV
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
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