Problem with Connection pool in MYSQL
Hi Adrian
.I am using JBoss 3.2.1 and Tomcat 4.2.1.24 . Also I am using mysql-connector-java-3.0.8-stable-bin.. After a few hours of load the jboss spits out an error " makeConnection org.jboss.util.NestedSQLException: No ManagedConnections Available!"
I have checked a lot of times if we are missing out on closing connections.
I use the following utilility class to create connections. I get the connection object , create a statement, execute the selectFrom Table() which returs me a result set.Use the result set and then close the result set. Close the Statement . I also get a message closing statement Please do your own house Kepping,but infact I am closing resultsets as well as statements and then closing connection in every part of my code
I am also not sure if this is the problem with my MYSQLConnect class or the MYSQL Connection
I have also pasted my mysql-ds.xml
/*********************************************************/
****/
import java.io.*;
import javax.naming.*;
import javax.sql.*;
import java.sql.*;
import java.util.*;
public class MYSQLConnect {
Connection conn = null;
Context ctx =null;
int rs_count=0;
int stmt_count=0;
public void makeConnection(){
try{
ctx = new InitialContext();
if(ctx == null ){
System.out.println("Initiall Context Not Set");
}
System.out.println("Initial Context Set");
DataSource ds = (DataSource)ctx.lookup("java:/jdbc/expressionsDS");
if (ds != null) {
System.out.println("Got DataSource");
conn = ds.getConnection();
if(conn != null) {
System.out.println("Got Connection");
}
else{
System.out.println("No Connection");
}
}
else{
System.out.println("No DataSource");
}
}
catch(Exception e){
System.out.println("Error In MYSQLConnect:makeConnection "+ e.toString());
}
}
public void showCounts(){
System.out.println("The no od Result set remained open are"+rs_count
+ " and the no of Statements="+stmt_count);
}
public void disconnectConnection(){
try{
conn.close();
}
catch(Exception e){
System.out.println("Error in MYSQLConnect:disconnectConnection "+ e.toString());
}
}
public void closeResultSet(ResultSet rs){
try{
if(rs!=null){
System.out.println("Closing ResultSet");
rs.close();
}
}
catch(Exception e){
System.out.println("Error in MYSQLConnect:disconnectConnection "+ e.toString());
}
}
public void closeStatement(Statement stmt){
try{
if(stmt!=null){
System.out.println("Closing Statement");
stmt.close();
}
}
catch(Exception e){
System.out.println("Error in MYSQLConnect:disconnectConnection "+ e.toString());
}
}
public Statement createMYSQLStatement(){
try {
return conn.createStatement();
}
catch(Exception exp){
return null;
}
}
public ResultSet selectFromTable(String query,Statement stmt){
ResultSet rs=null;
try{
stmt = conn.createStatement();
stmt_count++;
stmt.execute(query);
rs = stmt.getResultSet();
}
catch(Exception e){
try{
if(stmt!=null){
stmt.close();
}
}
catch(Exception exp){
System.out.println("{Query="+ query+"}");
System.out.println("Error in MYSQLConnect:selectFromTable Close Statement"+ exp.toString());
}
System.out.println("{Query="+ query+"}");
System.out.println("Error in MYSQLConnect:selectFromTable "+ e.toString());
}
return rs;
}
public int insertIntoTable(String query){
Statement stmt_ins=null;
try{
stmt_ins = conn.createStatement();
stmt_ins.execute(query);
}
catch(Exception e){
try{
System.out.println("Caught Exception in insertIntoTable");
if(stmt_ins!=null){
stmt_ins.close();
}
}
catch(Exception exp){
System.out.println("{Query="+ query+"}");
System.out.println("Error in MYSQLConnect:insertIntoTable Close Statement"+ exp.toString());
}
System.out.println("{Query="+ query+"}");
System.out.println("Error in MYSQLConnect:insertIntoTable "+ e.toString());
return 1;
}
finally{
try{
if(stmt_ins!=null){
stmt_ins.close();
}
}
catch(Exception exp1){
System.out.println("Could Not Close Statement after Insert");
}
}
return 0;
}
public int deleteFromTable(String query){
Statement stmt_del=null;
try{
stmt_del = conn.createStatement();
stmt_del.execute(query);
}
catch(Exception e){
try{
System.out.println("Caught Exception in deleteFromTable");
if(stmt_del!=null){
stmt_del.close();
}
}
catch(Exception exp){
System.out.println("{Query="+ query+"}");
System.out.println("Error in MYSQLConnect:deleteFromTable Close Statement"+ exp.toString());
}
System.out.println("{Query="+ query+"}");
System.out.println("Error in MYSQLConnect:deleteFromTable "+ e.toString());
return 1;
}
finally{
try{
if(stmt_del!=null){
stmt_del.close();
}
}
catch(Exception exp1){
System.out.println("Could Not Close stmt after Delete");
}
}
return 0;
}
}
/**************************************************/
This is my mysql-ds.xml
/**************************************************/
<?xml version="1.0" encoding="UTF-8"?>
<local-tx-datasource>
<jndi-name>jdbc/expressionsDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/expressions</connection-url>
<driver-class>org.gjt.mm.mysql.Driver</driver-class>
<user-name>expressions</user-name>
expressions
<min-pool-size>5</min-pool-size>
<max-pool-size>50</max-pool-size>
<idle-timeout-millis>100</idle-timeout-millis>
</local-tx-datasource>
/****************************************************/
