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