how to return connection to Connection Pool &to overcome TooManyConnections
Hi all,
My project is done in Tomcat 5.0 with Mysql 5 as backend.
Through tomcat admin console i have configured datasource named "mine" with all the attributes needed to connect to MySql like url, username,password, Max connections etc.
I have written a function named getConn() in one plain java file namedConClass for fetching connection from datasource.
The ConClass code is as follows.
package com.pts.beans;
import java.io.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
publicclass ConClass
{
publicstatic Connection getConn()
{
DataSource ds=null;
Connection con=null;
try
{
Context envCtx = (Context)new InitialContext().lookup("java:comp/env");
System.out.println("got up environment for naming context"+envCtx);
// Look up data source
ds = (DataSource) envCtx.lookup("mine");
con=ds.getConnection();
}
catch(NamingException e)
{
e.printStackTrace();
}
catch (SQLException e)
{
System.out.println("Exception inside ConClass" +e);
}
return con;
}
}
In all my jsp and servlet files,
i am fetching a connection using
Connection con = ConClass.getConn();
Using this object i am creating statements by means of this con object and then fetching query with the resultset after wards.
In finally block of all JSPs and Servlets
i am using code like this
if (con !=null)
{
con.close();
con=null;
}
I just want to know whether after execution of this code , the fetched connection "con " will be returned back to Pool Or not...
I am gettigToo Many Connections: Datasource Rejected Establishment of Conneciton Exception.
Need to restart Mysql to make the application running well..
Please its very urgent . Expecting your valuable suggestions.
# 6
[nobr]This is one of the jsp files in my project. AddUser.jsp
<%@ page import="java.io.*,java.sql.*,java.net.*,java.util.*,javax.sql.*,com.pts.beans.ConClass" %>
<%
Connection con=null;
Statement stmt1=null;
ResultSet rs=null;
ResultSet rs1,rs2,rs3,rs4;
try
{
con=ConClass.getConn();
System.out.println("after static connection is " + con);
stmt1=con.createStatement();
String amsg=(String)session.getAttribute("amsg");
session.removeAttribute("amsg");
HashMap deptNames = new HashMap();
HashMap deptIDS = new HashMap();
HashMap desigNames = new HashMap();
HashMap desigIDS = new HashMap();
String dno = request.getParameter("dno");
rs = stmt1.executeQuery("select distinct deptID,deptname from dept_det");
for(int i=0; rs.next(); i++){
deptIDS.put(""+i,""+rs.getInt("deptID"));
deptNames.put(""+i,rs.getString("deptName"));
}
if(dno != null)
{
rs = stmt1.executeQuery("select deptID,deptName,desigName from dept_det where deptID="+dno);
for(int i=0; rs.next(); i++){
//desigIDS.put(""+i,rs.getString("desigID"));
desigNames.put(""+i,rs.getString("desigName"));
}
}
%>
<html>
<head>
<title>Add User</title>
<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>
<LINK href="../script/myStyle.css" type="text/css" rel=stylesheet>
<script language='javascript'>
function f1(){
location.href = '../../servlets/mainPage';
}
function check(){
var illegalChars = /\W/;
if(form1.deptName.options[form1.deptName.selectedIndex].value=='Select'){
alert('Select department');
form1.deptName.focus();
}else if(form1.desig.options[form1.desig.selectedIndex].value=='Select'){
alert('Select Designation');
form1.desig.focus();
}else if(form1.username.value==''){
alert('Enter username');form1.username.focus();
}else if(form1.password.value==''){
alert('Enter password');form1.password.focus();
}else if(form1.retypepwd.value==''){
alert('Enter confirm Password');
form1.retypepwd.focus();
}else if(form1.password.value.length>10){
alert('password should be maximum of 10 characters');
form1.password.value='';form1.retypepwd.value='';form1.password.focus();
}else if(form1.password.value != form1.retypepwd.value){
alert('password and retype password does not match');
form1.password.value='';form1.retypepwd.value='';form1.password.focus();
}
else if (illegalChars.test(form1.username.value)) {
form1.username.style.background = 'Yellow';
alert("The username contains illegal characters.\n");
}
else{
form1.action='../../servlets/AddUser1'
form1.submit();
}
}
function doOnChange(select){
if(select.selectedIndex !=0){
form1.indept.value = select.selectedIndex;
var dno = select.options[select.selectedIndex].value;
form1.action='AddUser.jsp?dno='+dno;
form1.submit();
}
}
function setDefault(){
if(form1.setpassword.checked){
form1.password.value='diacritech';
form1.retypepwd.value='diacritech';
}else{
form1.password.value='';
form1.retypepwd.value='';
form1.password.focus();
}
}
</script>
</head>
<BODY bgColor=#EBEBEB leftMargin=0 topMargin=0 link="#0000CC" vlink="#0000CC" alink="#0000CC">
<%@ include file="../script/head.jsp" %>
<form name='form1' method='post'>
<br>
<br>
<div align="center">
<center>
<table border="1" width="60%">
<tr><td width="100%" bgcolor="#BFCCE6" style="font-family: Arial; font-size: 10pt; color: '#0000CC'; font-weight: bold"><%if(amsg!=null) out.println(amsg);%></td></tr>
<tr>
<td width="100%">
<table border='0' align='center' width="100%">
<tr><td height='30' align='left' valign='top'><font face=Verdana size=2>Department </font></td>
<td height='30'>
<select name='deptName' onChange='doOnChange(this);'>
<option value='Select'>Select</option>
<%for(int i = 0; i < deptNames.size(); i++) {
// System.out.println("deptid " + deptIDS.get(""+i) );
out.println("<option value='"+deptIDS.get(""+i)+"'>" + deptNames.get(""+i));
}%>
</tr>
<tr><td height='30' align='left'><font face=Verdana size=2>Designation</font></td>
<td height='30'>
<select name='desig'>
<option value='Select'>Select</option>
<%for(int i = 0; i < desigNames.size(); i++) {
out.println("<option value='"+desigNames.get(""+i)+"'>" + desigNames.get(""+i));
}%>
</select></td>
</tr>
<tr>
<td align='left'><font face=Verdana size=2>Login ID</font></td>
<td><input type='text' name='username'></td>
<td><input type=checkbox name=setpassword onclick="return setDefault();">
<font face=verdana size=2 color='#FF0000'>Set Default Password</font></td>
</tr>
<tr><td align='left'><font face=Verdana size=2>Password</font></td>
<td><input type='password' name='password'></td>
<td align=left><font face=verdana size=2 color='#FF0000'>(maximum limit: 10 characters)</font></td>
</tr>
<tr><td align='left'><font face=Verdana size=2>Re-type Password</font></td>
<td><input type='password' name='retypepwd'></td>
</tr>
<tr><td> </td></tr>
<tr><td></td>
<td align=left height='24'><input type='button' name='Submit' value='OK' onClick='check();'>
<input type='button' name='cancel' value='Cancel' onClick='f1();'></td>
</tr>
</table>
</td>
</tr>
</table>
</center>
</div>
<br>
<br>
<br>
<input type=hidden name=indept>
</form>
<script language=javascript>
<%if(request.getParameter("indept")!=null)
out.println("form1.deptName.selectedIndex = "+request.getParameter("indept"));
%>
form1.deptName.focus();
</script>
<br>
<br>
</body>
</html>
<%
deptNames.clear(); deptIDS.clear();desigNames .clear();desigIDS.clear(); deptNames=null;
deptIDS=null;desigNames=null;desigIDS=null;amsg=null;dno=null;System.gc();
}
catch(Exception e)
{
System.out.println("exception in viewstatus report" + e.getMessage());
}//try
finally
{
try
{
System.out.println("inside finally");
if (rs != null)
{
rs.close();
rs=null;
}
if (stmt1 != null)
{
stmt1.close();
stmt1=null;
}
if (con != null)
{
con.close();
con=null;
}
System.out.println("final");
} catch (SQLException e){System.out.println("exception in closing " + e.getMessage());
}
}
%>
All my JSP pages will follow the same way as this AddUser.jsp file.
Please advice me now what to do...?[/nobr]
