× Home MicroServices WYSI!WYG Zen IDE AppServer JSP JDBC Pool EJB About Services
Backbutton Frameworks ©

Tomcat Website: properly written code to use a database 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 ...
   stmt = conn.createStatement("select ...");
   rs = stmt.executeQuery();
   ... iterate through the result set ...
   rs.close();
   rs = null;
   stmt.close();
   stmt = null;
   conn.close(); // Return to connection pool
   conn = null;  // Make sure we don't close it twice
 } catch (SQLException e) {
   ... deal with errors ...
 } finally {
   // Always make sure result sets and statements are closed,
   // and the connection is returned to the pool
   if (rs != null) {
     try { rs.close(); } catch (SQLException e) { ; }
     rs = null;
   }
   if (stmt != null) {
     try { stmt.close(); } catch (SQLException e) { ; }
     stmt = null;
   }
   if (conn != null) {
     try { conn.close(); } catch (SQLException e) { ; }
     conn = null;
   }
 }

Use this option if you wish to define a datasource specific to your application, not visible to other Tomcat applications. This method is less invasive to your Tomcat installation.

<Context>
  <Resource name="jdbc/postgres" auth="Container"
        type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
        url="jdbc:postgresql://127.0.0.1:5432/mydb"
        username="myuser" password="mypasswd" maxTotal="20" maxIdle="10"
  maxWaitMillis="-1"/>
  </Context>
web.xml configuration
<resource-ref>
       <description>postgreSQL Datasource example</description>
       <res-ref-name>jdbc/postgres</res-ref-name>
       <res-type>javax.sql.DataSource</res-type>
       <res-auth>Container</res-auth>
      </resource-ref>
Accessing the datasource

When accessing the datasource programmatically, remember to prepend java:/comp/env to your JNDI lookup, as in the following snippet of code. Note also that "jdbc/postgres" can be replaced with any value you prefer, provided you change it in the above resource definition file as well.

I would use this method to set some of the properties dynamically, because I wouldn't want to hard code database username and password in a configuration file.

  import java.sql.Connection;
        import java.sql.ResultSet;
        import java.sql.Statement;

        import org.apache.tomcat.jdbc.pool.DataSource;
        import org.apache.tomcat.jdbc.pool.PoolProperties;

      public class POJOExample {

      public static void main(String[] args) throws Exception {
          PoolProperties p = new PoolProperties();
          p.setUrl("jdbc:mysql://localhost:3306/mysql");
          p.setDriverClassName("com.mysql.jdbc.Driver");
          p.setUsername("root");
          p.setPassword("password");
          p.setJmxEnabled(true);
          p.setTestWhileIdle(false);
          p.setTestOnBorrow(true);
          p.setValidationQuery("SELECT 1");
          p.setTestOnReturn(false);
          p.setValidationInterval(30000);
          p.setTimeBetweenEvictionRunsMillis(30000);
          p.setMaxActive(100);
          p.setInitialSize(10);
          p.setMaxWait(10000);
          p.setRemoveAbandonedTimeout(60);
          p.setMinEvictableIdleTimeMillis(30000);
          p.setMinIdle(10);
          p.setLogAbandoned(true);
          p.setRemoveAbandoned(true);
          p.setJdbcInterceptors(
            "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
            "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
          DataSource datasource = new DataSource();
          datasource.setPoolProperties(p);

          Connection con = null;
          try {
            con = datasource.getConnection();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from user");
            int cnt = 1;
            while (rs.next()) {
                System.out.println((cnt++)+". Host:" +rs.getString("Host")+
                  " User:"+rs.getString("User")+" Password:"+
                  rs.getString("Password"));
            }
            rs.close();
            st.close();
          } finally {
            if (con!=null) try {con.close();}catch (Exception ignore) {}
          }
      }

        }