Article:  |
Database Connection Pooling with Tomcat | |
| Subject: | Easier said than done: connection pooling | |
| Date: | 2006-10-08 16:53:44 | |
| From: | jpower | |
|
Response to: More pools for Tomcat
|
||
|
I specified maxActive=100 and maxIdle=20 for my webapp. But as the app runs, the connections just ramped up. After a while, even if no one was using the app, I saw ~ 30 idle connections there, much bigger than the value of maxIdle. Unless I shutdown Tomcat, the number doesn't get smaller. This is giving me huge headache since I know the app is going to blow up as user keeps using it. I take special care in closing connections in finally block: fnally {
|
||
Showing messages 1 through 3 of 3.
-
Easier said than done: connection pooling
2006-10-08 16:53:35 jpower [View]
-
Easier said than done: connection pooling
2008-05-21 03:42:49 lekanb [View]
When i run the servlet now i get this error bcos i have started using the connection pooling:
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class ' ' for connect URL 'null'
and this is my code 4 d servlet.
package com.lekan.dbcp;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
public class MobileServlet extends HttpServlet
{
private DataSource datasource = null;
public void init() throws ServletException {
try {
//Create a datasource for pooled connections.
datasource = (DataSource) getServletContext().getAttribute("DBCPool");
//Register the driver for non-pooled connections.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
}
catch (Exception e) {
throw new ServletException(e.getMessage());
}
}
private synchronized Connection getConnection() throws SQLException {
//if (pooledConnection) {
//pooledCount++;
return datasource.getConnection(); // Allocate and use a connection from the pool
//}else {
//nonPooledCount++;
//Connection con = DriverManager.getConnection("jdbc:odbc:Addbook");
//return con; //retun a newly created object
//}
}
public void doPost ( HttpServletRequest req, HttpServletResponse res )
throws IOException, ServletException
{
doGet( req, res );
}
public void doGet ( HttpServletRequest req, HttpServletResponse res )
throws IOException, ServletException
{
res.setContentType("text/plain");
PrintWriter out = res.getWriter();
//BufferedReader br = req.getReader( );
//String buf = br.readLine( );
Connection conn = null;
Statement stmt = null;
ResultSet rest = null;
try{
//String uid = buf.substring(buf.indexOf('*')+1,buf.indexOf(','));
//String pas = buf.substring(buf.indexOf(',')+1,buf.indexOf('>'));
String sql = "SELECT * FROM tblOwner WHERE OwnerID = 'lekan' AND Password = 'golden'";
conn = getConnection();
stmt = conn.createStatement();
rest = stmt.executeQuery(sql);
if(rest.next()){
out.print("User name and password valid!!");
System.out.print("User name and password valid!!");
}
else{
out.print("User name and password invalid!!");
System.out.print("User name and password valid!!");
}
conn.close();
}catch(SQLException se){
out.print(se);
se.printStackTrace();
}
finally {
try {if (rest != null) rest.close();} catch (SQLException e) {}
try {if (stmt != null) stmt.close();} catch (SQLException e) {}
try {if (conn != null) conn.close();} catch (SQLException e) {}
}
out.close();
}
}
and this is the listener
package com.lekan.dbcp;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
public class DBCPoolingListener implements ServletContextListener{
public void contextInitialized(ServletContextEvent sce){
try {
// Obtain our environment naming context
Context envCtx = (Context) new InitialContext().lookup("java:comp/env");
// Look up our data source
DataSource ds = (DataSource) envCtx.lookup("jdbc/Addbook");
sce.getServletContext().setAttribute("DBCPool", ds);
}catch(NamingException e){
e.printStackTrace();
}
}
public void contextDestroyed(ServletContextEvent sce){}
}
This is the context in the server.xml
<Context path="/dbcp" docBase="dbcp" debug="5" reloadable="true" crossContext="true">
+ <Resource name="jdbc/Addbook" auth="Container"+
+ type="javax.sql.DataSource" removeAbandoned="true"+
+ removeAbandonedTimeout="30" maxActive="0"+
+ maxIdle="-1" maxWait="30000" username=""+
+ password=""+
+ driverClassName="sun.jdbc.odbc.JdbcOdbcDriver"+
+ url="jdbc:odbc:Addbook"/>+
+ </Context>+
This is my web.xml
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN" "http://java.sun.com/j2ee/dtds/web-app_2_2.dtd">
<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>
Servlet for mobile apps
</display-name>
+<description> +
+ Servlet that handles mobile request+
</description>
<listener>
<listener-class>com.lekan.dbcp.DBCPoolingListener</listener-class>
</listener>
<!-- This component has a dependency on an external resource-->
<resource-ref>
<description>DB Connection Pooling</description>
<res-ref-name>jdbc/Addbook</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
<servlet>
+ <servlet-name>myServlet</servlet-name>+
+ <servlet-class>com.lekan.dbcp.MobileServlet</servlet-class>+
<load-on-startup>0</load-on-startup>
</servlet>
<servlet-mapping>
+ <servlet-name>myServlet</servlet-name>+
+ <url-pattern>/mobile1</url-pattern>+
</servlet-mapping>
</web-app>
Please need help,
Thank you -
Easier said than done: connection pooling
2009-06-09 03:53:33 Gerhard Greyvenstein [View]
The problem is not with your java code, but rather the context of the datasource.
This is the resource I found that has the best example: http://wiki.metawerx.net/wiki/Context.xml
I take special care in closing connections in the finally block:
fnally {
if (con != null) con.close;
}
However, I didn't try to close PreparedStatement in finally block because I assume if I close the connection, PreparedStatement will be automaticall closed.
Thanks.