how to over come toomanyconnection error when communicatingMYSQL

Discussions

Web tier: servlets, JSP, Web frameworks: how to over come toomanyconnection error when communicatingMYSQL

  1. hai all, i have one major issue in my project.. My project is developed using jsp,servlets in Tomcat5 with MySql as database.. i am using type1 driver My project is now used by end users in intranet.. When multiple users are accessing at the same time ,its throwing too many connections exception at sometimes . And at sometimes its dispaying error as "Operation not allowed after ResultSet is closed" i dont know how to rectify this .. Can anybody help me to overcome this.. Here is my bean file used for establing database connections... package com.pts.database; import java.io.*; import java.sql.*; import java.net.*; public class DBCon { private static final String DriverClass = "com.mysql.jdbc.Driver"; private static final String CONNECTION_STRING = "jdbc:mysql://"; private static final String PORT = "3306"; private static final String DATABASE = "opts"; private static final String user = "dav"; private static final String pwd=""; private static Connection con = null; private static Statement stmt = null; private static Statement stmt2 = null; private static PreparedStatement pst = null; private static ResultSet rs = null; private static int count = 0; public static void assignCon() { String CONNECTION_IP="100.100.100.6"; try{ Class.forName(DriverClass).newInstance(); } catch(ClassNotFoundException ce){System.out.println(ce);} catch(InstantiationException ie){} catch(IllegalAccessException ie1){} try{ con = DriverManager.getConnection(CONNECTION_STRING+CONNECTION_IP+":"+PORT+"/"+DATABASE,user,pwd); stmt = con.createStatement(); stmt2 = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); }catch(SQLException sqle){System.out.println("STMT:"+sqle);} } public static ResultSet retrieveFromDB(String sql) throws SQLException { return (stmt.executeQuery(sql)); } public static ResultSet retrieveScrollDB(String sql) throws SQLException { return (stmt2.executeQuery(sql)); } public static int updateDB(String sql) throws SQLException { return (stmt.executeUpdate(sql)); } public static PreparedStatement prepareStmt(String sql) throws SQLException{ pst=con.prepareStatement(sql); return pst; } public static ResultSet getProjectsList() throws SQLException{ rs=((PreparedStatement)con.prepareStatement("select projID,projName from proj_det where delFlag=0 || delFlag IS NULL")).executeQuery(); return rs; } public Connection getConn() { return con; } public Statement getStmt() { return stmt; } public static void CloseAll() throws SQLException { stmt.close(); stmt2.close(); con.close(); } } Hope Any of u can help me.. it would be very much helpful to me if any body suggest me a solution to this... Because of this issue my project is in pending.. Thanks in Advance
  2. Hi Prakash, I'm sorry to say that you are way off a working method to handle the db-specific parts in your application. First of all, Connection and all of its derived classes (Statement, ResultSet), *are NOT threadsafe*. You have assigned them all as static, which means that all threads in the application (or at least in the same classloader) share the same instances, creating random inconsitencies and errors. If you have not yet experienced such errors, you definitly will! Shortly, use one connection per thread (user request), and *be sure to close* all resultsets, preparedstatements and connections *always*. The "tooManyConnection" error is most certain due to the fact that you have not closed the resources correctly after certain requests. In short, you need to totally rewrite/refactor the way you handle db io, and make the resource handling waterproof. /Niklas