aspose file tools*
The moose likes Servlets and the fly likes Connection Pooling Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "Connection Pooling" Watch "Connection Pooling" New topic
Author

Connection Pooling

Gaurav Chikara
Ranch Hand

Joined: Jun 09, 2000
Posts: 410
I have made a connection pool class and using it in each of my servlet with jdbc-odbc driver
Now whenver i invoke another servlet from my current servlet
thru click of submit button
It works finely for first two times but on third time
it gives internal server error which says could not create connection pool class
can anyone suggest where the problem lies
this is the source code of connection pool class

(added UBB code tags to make more readable, FC)

I'm using connection pool class in my servlet in this way
in output of this servlet i am thrwoing an html
which calls another servlet and i use connection pooling in that servlet also
in the similar way
this is the code
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class CltNameQry extends HttpServlet
{
private ConnectionPool pool;
int i=0;
String sessionId = null;
int rslen,numcols;
String str1[];
String newLocation = "servlet/CltAllNames";
public void init(ServletConfig config) throws ServletException
{ super.init(config);
try
{
pool = new ConnectionPool("jdbc dbc:hra","sa","","sun.jdbc.odbc.JdbcOdbcDriver",5,5);
} //try
catch (Exception e)
{
throw new UnavailableException(this,"Couldn't create connection pool");
}// catch
//
}//init
public void doGet(HttpServletRequest req,HttpServletResponse res)
throws ServletException,IOException
{
Connection con = null;
Statement stmt = null;
ResultSet rs1 = null;
ResultSet rs2 = null;
String Result2 = null;
res.setContentType("text/html");
PrintWriter out = res.getWriter();
try {
con = pool.getConnection();
con.setAutoCommit(false);
HttpSession mySession = req.getSession(false);
String User = (String)mySession.getValue("UserName");
// Check if it's a valid session or not and check for user
if(req.isRequestedSessionIdValid())
{
sessionId = req.getRequestedSessionId();
String sql1 = "select count(distinct CltCompanyName) from DBCLTMST";
stmt = con.createStatement();
rs1 = stmt.executeQuery(sql1);
ResultSetMetaData rsmd = rs1.getMetaData();
int numcols = rsmd.getColumnCount();
boolean b = rs1.next();
if(b==true)
{
Result2 = rs1.getString(numcols);
}
String sql2 = "select distinct CltCompanyName from DBCLTMST";
//stmt = con.createStatement();
rs2 = stmt.executeQuery(sql2);
int len = Integer.parseInt(Result2);
str1 = new String[len];

while(rs2.next())
{
str1[i]=rs2.getString("CltCompanyName");
i++;
}
///////////////////////////////////////////////////////////////
out.println("<html>");
out.println("<head>");
out.println("<title>Vendor Name based Query</title>");
out.println("</head>");
out.println("<body BACKGROUND=\"/IllumBackgound.gif\" LINK=\"#00CCFF\" VLINK=\"#0033CC\" TEXT=\"#000000\"");
out.println("TOPMARGIN=\"0\" LEFTMARGIN=\"0\" MARGINWIDTH=\"0\" MARGINHEIGHT=\"0\">");
out.println("<FORM name = \"resusrqry\" action = /servlet/CltNameSubQry method = get>");
out.println("<table BORDER=\"0\" CELLSPACING=\"0\" CELLPADDING=\"0\" WIDTH=\"700\">");
out.println("<input type = \"hidden\" name=\"sessionid\" value="+ sessionId + ">");
out.println("<input type = \"hidden\" name=\"User\" value="+ User + ">");
out.println(" <tr VALIGN=\"top\" ALIGN=\"left\">");
out.println(" <td WIDTH=\"15\" HEIGHT=\"56\"><img SRC=\"/clearpixel.gif\" WIDTH=\"15\" HEIGHT=\"1\" BORDER=\"0\"></td>");
out.println(" <td WIDTH=\"100\" ROWSPAN=\"2\" ALIGN=\"left\" VALIGN=\"top\"><img id=\"Picture5\" SRC=\"/anim.gif\"");
out.println(" BORDER=\"0\" ALT=\"anim\" WIDTH=\"100\" HEIGHT=\"59\"></td>");
out.println(" <td WIDTH=\"3\"><img SRC=\"/clearpixel.gif\" WIDTH=\"3\" HEIGHT=\"1\" BORDER=\"0\"></td>");
out.println(" <td WIDTH=\"582\" ALIGN=\"left\" VALIGN=\"top\"><img id=\"Picture3\" SRC=\"/hra1.gif\" BORDER=\"0\"");
out.println(" ALT=\"hra1\" WIDTH=\"582\" HEIGHT=\"56\"></td>");
out.println(" </tr>");
out.println(" <tr VALIGN=\"top\" ALIGN=\"left\">");
out.println(" <td HEIGHT=\"3\"></td>");
out.println(" <td COLSPAN=\"2\"><p align=\"center\"><font color=\"#FF0000\" face=\"Arial\"><big><big>Query");
out.println(" through Client Name</big></big></font></td>");
out.println(" </tr>");
out.println("</table>");
out.println("<table CELLPADDING=\"0\" CELLSPACING=\"0\" BORDER=\"0\" WIDTH=\"455\">");
out.println(" <tr VALIGN=\"top\" ALIGN=\"left\">");
out.println(" <td><table BORDER=\"0\" CELLSPACING=\"0\" CELLPADDING=\"0\" WIDTH=\"124\" height=\"499\">");
out.println(" <tr VALIGN=\"top\" ALIGN=\"left\">");
out.println(" <td COLSPAN=\"2\" height=\"21\"></td>");
out.println(" <td WIDTH=\"105\" COLSPAN=\"3\" height=\"21\"><font COLOR=\"#80FF42\" SIZE=\"-1\"");
out.println(" FACE=\"Arial,Helvetica,Univers,Zurich BT\"></font></td>");
out.println(" <td height=\"21\"></td>");
out.println(" </tr>");
out.println(" <tr VALIGN=\"top\" ALIGN=\"left\">");
out.println(" <td COLSPAN=\"6\" HEIGHT=\"53\"></td>");
out.println(" </tr>");
out.println(" <tr VALIGN=\"top\" ALIGN=\"left\">");
out.println(" <td HEIGHT=\"132\"></td>");
out.println(" <td WIDTH=\"114\" COLSPAN=\"5\" height=\"132\"><table id=\"VerticalNavigationBar2\" BORDER=\"0\"");
out.println(" CELLSPACING=\"0\" CELLPADDING=\"7\" WIDTH=\"114\">");
out.println("<tr VALIGN=\"top\" ALIGN=\"left\">");
out.println("<td WIDTH=\"100\" HEIGHT=\"30\">");
out.println("<a HREF=\"/Site_Map.html\">");
out.println("<img SRC=\"/Sitemap_illumbutton.gif\" BORDER=\"0\" ALT=\" SITEMAP \" WIDTH=\"99\" HEIGHT=\"31\">");
out.println("</a>");
out.println("</td>");
out.println("</tr>");
out.println("<tr VALIGN=\"top\" ALIGN=\"left\">");
out.println("<td WIDTH=\"100\" HEIGHT=\"30\">");
out.println("<a HREF=\"/servlet/LogoutUser\">");
out.println("<img SRC=\"/Logout_illumbutton.gif\" BORDER=\"0\" ALT=\" LOG OUT \" WIDTH=\"99\" HEIGHT=\"31\">");
out.println("</a>");
out.println("</td>");
out.println("</tr>");
out.println(" </table>");
out.println(" </td>");
out.println(" </tr>");
out.println(" <tr VALIGN=\"top\" ALIGN=\"left\">");
out.println(" <td COLSPAN=\"6\" HEIGHT=\"242\"></td>");
out.println(" </tr>");
out.println(" <tr VALIGN=\"top\" ALIGN=\"left\">");
out.println(" <td COLSPAN=\"3\" HEIGHT=\"29\"></td>");
out.println(" <td WIDTH=\"70\" ALIGN=\"left\" VALIGN=\"top\" height=\"29\"></td>");
out.println(" <td COLSPAN=\"2\" height=\"29\"></td>");
out.println(" </tr>");
out.println(" </table>");
out.println(" </td>");
out.println(" <td><table BORDER=\"0\" CELLSPACING=\"0\" CELLPADDING=\"0\" width=\"556\">");
out.println(" <tr VALIGN=\"top\" ALIGN=\"left\">");
out.println(" <td WIDTH=\"22\" HEIGHT=\"467\"><img SRC=\"/clearpixel.gif\" WIDTH=\"20\" HEIGHT=\"1\" BORDER=\"0\"></td>");
out.println(" <td width=\"534\"><p align=\"left\"> </p>");
out.println(" <p align=\"left\"> </p>");
out.println(" <p align=\"center\"><font face=\"Arial\" color=\"#000000\">               ");
out.println("   </font><select name=\"UserType\" size=\"1\">");
for(int i = 0; i < str1.length;i++)
{
out.println(" <option value="+str1[i]+">"+str1[i]+"</option>");
}
out.println(" </select></p>");
out.println(" <p align=\"center\"><input type=\"submit\" value=\"Submit\"></p>");
out.println(" <p align=\"center\"> </td>");
out.println(" </tr>");
out.println(" <tr VALIGN=\"top\" ALIGN=\"left\">");
out.println(" <td width=\"22\"></td>");
out.println(" <td NOWRAP WIDTH=\"534\"><table id=\"NavigationBar1\" BORDER=\"0\" CELLSPACING=\"0\"");
out.println(" CELLPADDING=\"1\" WIDTH=\"528\">");
out.println("<tr VALIGN=\"top\" ALIGN=\"left\">");
out.println("<td WIDTH=\"100\">[");
out.println("<a HREF=\"/Site_Map.html\">SITE MAP");
out.println("</a>]");
out.println("</td>");
out.println("<td WIDTH=\"252\">[");
out.println("<a HREF=\"/servlet/LogoutUser\">LOG OUT");
out.println("</a>]");
out.println("</td>");
out.println("</tr>");
out.println(" </table>");
out.println(" </td>");
out.println(" </tr>");
out.println(" </table>");
out.println(" </td>");
out.println(" </tr>");
out.println("</table>");
out.println("</form>");
out.println("</body>");
out.println("</html>");
/////////
res.setHeader("Refresh","30;URL = http://manoj:8080/"+newLocation);
}
else
{
out.println("<html>");
out.println("<head>");
out.println("<title>Invalid Access</title>");
out.println("</head>");
out.println("<body>");
out.println("Your account information not found");
out.println("</body>");
out.println("</html>");
res.sendRedirect(res.encodeRedirectURL("http://manoj:8080/HomePage.html"));
}
}//try
catch(SQLException e)
{ String reason = "";
String SQLState = "" ;
SQLException f = new SQLException(reason,SQLState);
out.println("<html>");
out.println("<TITLE>Vendor Tracking System of SQL Star Internatiomnal!</TITLE>");
out.println("Constraint violated:"+e.getMessage()+" "+f.getSQLState());
out.println("</html>");
}
catch(NullPointerException e)
{
out.println("<html>");
out.println("<TITLE>Vendor Tracking System of SQL Star Internatiomnal!</TITLE>");
out.println("Null Pointer:"+e.getMessage());
out.println("</html>");
}//catch
catch(ArrayIndexOutOfBoundsException e)
{
out.println("<html>");
out.println("<TITLE>Vendor Tracking System of SQL Star Internatiomnal!</TITLE>");
out.println("Array Exception"+e.getMessage());
out.println("</html>");
}//catch
}//doGet
}//class

[This message has been edited by Frank Carver (edited June 09, 2000).]
[This message has been edited by Gaurav Chikara (edited June 09, 2000).]


SCJP,SCWCD,SCBCD<br />If Opportunity doesn't knock then build the door
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
I'd have to see the code which uses this pool. Are you creating a new pool for each GET/POST request? It might be that the DB has some sort of limit on open connections.
Creating a connection pool is a great thing to put in the servlet "init" method.


Read about me at frankcarver.me ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Connection Pooling