How to configure Bonecp in java project without breaking connection when more than connection

[Rd Dari]

Hi all Ranchers,

I am new in BoneCP Connection pooling I using it from last 5 days in my existing project but in the more than one connection it throw an exception that is

Too many connections


My Configuration code is :

package src;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;
public class ConnectionManager
{
private static BoneCP connectionPool = null;

public static void configureConnPool()
{
    try
    {
        Class.forName("com.mysql.jdbc.Driver"); //also you need the MySQL driver
        BoneCPConfig config = new BoneCPConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/facility");
        config.setUsername("root");
        config.setPassword("adnig");
        config.setMinConnectionsPerPartition(5); //if you say 5 here, there will be 10 connection available
        //config.setMaxConnectionsPerPartition(10);
        config.setPartitionCount(2); //2*5 = 10 connection will be available
       // config.setLazyInit(true); //depends on the application usage you should chose lazy or not
        //setting Lazy true means BoneCP won't open any connections before you request a one from it.
        connectionPool = new BoneCP(config); // setup the connection pool
        System.out.println("contextInitialized.....Connection Pooling is configured");
        System.out.println("Total connections ==> " + connectionPool.getTotalCreatedConnections());
        ConnectionManager.setConnectionPool(connectionPool);
    }
    catch (Exception e)
    {
        e.printStackTrace(); //you should use exception wrapping on real-production code
    }
}

public static void shutdownConnPool()
{
    try
    {
        BoneCP connectionPool = ConnectionManager.getConnectionPool();
        System.out.println("contextDestroyed....");
        if (connectionPool != null)
        {
             connectionPool.shutdown(); //this method must be called only once when the application stops.
            //you don't need to call it every time when you get a connection from the Connection Pool
            System.out.println("contextDestroyed.....Connection Pooling shut downed!");
        }
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
}
public synchronized  Connection getConnection()
{
    Connection conn = null;
    try
    {
         conn = getConnectionPool().getConnection();
        //will get a thread-safe connection from the BoneCP connection pool.
        //synchronization of the method will be done inside BoneCP source
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
return conn;
}

public static void close(Statement stmt)
{
    try
    {
        if (stmt != null)
        stmt.close();
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
}
public static void close(PreparedStatement pstmt)
{
    try
    {
        if (pstmt != null)
        pstmt.close();
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
}
public static void close(ResultSet rSet)
{
    try
    {
        if (rSet != null)
        rSet.close();
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
}

public static void close(Connection conn)
{
    try
    {
        if (conn != null)
        conn.close(); //release the connection - the name is tricky but connection is not closed it is released
       //and it will stay in pool
    }
    catch (SQLException e)
    {
        e.printStackTrace();
    }
}

public static BoneCP getConnectionPool()
{
    return connectionPool;
}

public static void setConnectionPool(BoneCP connectionPool)
{
    ConnectionManager.connectionPool = connectionPool;
}
}

Context Listner is:

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package src;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

/**
 * Web application lifecycle listener.
 * @author Ading
 */
public class ContextListener implements ServletContextListener {

    public void contextInitialized(ServletContextEvent sce) {
        //throw new UnsupportedOperationException("Not supported yet.");
       ConnectionManager.configureConnPool();
       
      
    }

    public void contextDestroyed(ServletContextEvent sce) {
        //throw new UnsupportedOperationException("Not supported yet.");
        ConnectionManager.shutdownConnPool();
    }
}

here is code.....

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package src;

import com.oreilly.servlet.MultipartRequest;
import java.io.*;
import java.util.Enumeration;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.io.File;
import java.io.IOException;
import javax.net.ssl.SSLSocket.*;
import java.sql.Connection;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;


/**
 *
 * @author user2
 */
public class Add_OwnerProfile extends HttpServlet
{
    String username ;
    String password ;
    String society_name;
    String flat_extensionno;
    String appartment_name ;
    String appartment_no ;
    String tower_no ;
    String type_of_apparttment ;
    String floor_area ;
    String power_backup ;
    String electricity_meterno ;
    String water_meterno ;
    String gas_meterno ;
    String name_of_owner ;
    String owner_brithday ;
    String owner_anniversary ;
    String mobileno_ofmainperson ;
    String emailid_ofmainperson;
    String arrear_amount;
    String maintenaceArrears;
    String last_grid_unit;
    String last_dg_unit;
    String late_payment_charge_status;
    String late_payment_charge;
    String resident_satatus ;
    String tenent_name ;
    String tenant_phoneno ;
    String tenant_email ;
    String police_verification_status ;
    String dth_connection;
    String dth_connection_no;
    String dth_company;
    
    String maintainance_type;
    String flat_types;
    String flat_amount;
    String maintainance_id;
    
    String pocessession ;
    String payment_cycle;
    String plan_name;
    

    String[] nationality ;
    String[] name ;
    String[] age ;
    String[] qualification ;
    String[] profession ;
    String[] email_id ;
    String[] office_address ;

    String[] type ;
    String[] make ;
    String[] brand ;
    String[] registration_no ;
    
    String[] pet_type ;
    String[] pet_name ;
    String[] pet_vaccination ;

    String[] helper_type;
    String[] helper_age;
    String[] helper_photo;
    String[] helper_prev_address;
    String[] helper_agnt_details;
    String[] helper_police_verification;

    HttpSession session;
    ConnectionManager jdbc;
    Connection con=null;
    Statement st=null;
    PreparedStatement pst=null;
    PreparedStatement pst6=null;
    String appartmentid=null;
    String passwd="adnig";
    PreparedStatement pst1 = null;
    PreparedStatement pst2 = null;
    int count=0;
    
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException, SQLException {
         response.setContentType("text/html;charset=UTF-8");
         PrintWriter out = response.getWriter();
         session = request.getSession(true);
       //  String radio=request.getParameter("pocessession");
        // System.out.println("radio value is=== "+radio);
        try {

            
           // if("Postpocessession".equals(radio))
          //  {
              id_generation fac_id = new id_generation();
              appartmentid= fac_id.appartment_id();
              
              String path="na";
              String filename = "na";
              String rtempfile = File.createTempFile("temp","1").getParent();
              MultipartRequest multi = new MultipartRequest(request, rtempfile, 500000 * 1024);
              helper_type=multi.getParameterValues("helper_type");

                 File rnewfile=null;
                 File dir =new File(getServletContext().getRealPath("/")+"OwnerProfileImages\\"+ appartmentid.toString()+File.separator);
                 String[] children = dir.list();
                  if (children == null) {
                  } else {
                      for (int i = 0; i < children.length; i++) {
                          // Get filename of file or directory
                          if ("Thumbs.db".equalsIgnoreCase(children[i])) {
                          } else {
                              filename = children[i];
                              String[] imag_id = filename.split("_");
                              String na = imag_id[0];//.substring(0, r1);
                               if (na.equalsIgnoreCase(appartmentid)) {
                                  System.out.println("image id is:"+na+" image id is:::"+appartmentid);
                                  File dir1 = new File(getServletContext().getRealPath("/") + "OwnerProfileImages\\"+appartmentid);
                                  String[] children1 = dir1.list();
                                  if(children1==null){}else{
                                  for (int j = 0; j < children1.length; j++) {
                                      File dir11 = new File(getServletContext().getRealPath("/") + "OwnerProfileImages\\"+appartmentid+"\\"+children1[j]);
                                      dir11.delete();
                                     }
                                  }
                                   dir1.delete();
                              }
                          }
                      }
                  }
                int i=0;
                Enumeration e=multi.getFileNames();
                while(e.hasMoreElements()){
               // if(e.nextElement()!=null)
               // {
                    int len =helper_type.length;
                   String maid1=e.nextElement().toString();
                   // if(maid1!=null)
                // {
                 File  f=multi.getFile(maid1);
                 if(f!=null)
                 {
                    // path="NA";
                //}
                 long tt=f.length();
                 
                   rnewfile = new File(getServletContext().getRealPath("/")+"OwnerProfileImages\\"+ appartmentid.toString()+"\\");
                   if(rnewfile.exists())
                        {}
                    else
                      {rnewfile.mkdirs();}
                    String n=f.getName();
                    if(appartmentid!=null&&1<len)
                    {n=appartmentid+"_"+helper_type[i]+"_"+n+".jpg";}
                    int fr=n.indexOf(".");
                    String fr1=n.substring(0, fr);
                           FileInputStream fin =new FileInputStream(f);
                           RandomAccessFile r = new RandomAccessFile(rnewfile+File.separator+n,"rw");
                            byte[] sizefile = new byte[(int)tt];

                          // byte[] sizefile =f.getClass().getName().getBytes();
                           fin.read(sizefile);
                           r.write(sizefile);
                           r.close();
                           fin.close();
                         //  path=getServletContext().getRealPath("/")+"OwnerProfileImages\\"+File.separator+n;
                           path ="OwnerProfileImages/"+appartmentid+"/" + fr1+".jpg";
                      i++;
                 }
                }

            username = (String)session.getAttribute("user");
            password = (String)session.getAttribute("password");
            pocessession= multi.getParameter("pocessession");
            maintainance_type=multi.getParameter("maintainance_type");
            flat_types=multi.getParameter("flat_type");
            flat_amount=multi.getParameter("flat_amount");
            maintainance_id=multi.getParameter("maintainance_id");
            plan_name = multi.getParameter("plan_name");
            payment_cycle = multi.getParameter("payment_cycle");

            society_name = multi.getParameter("society_name");
            flat_extensionno = multi.getParameter("flat_extensionno");
            appartment_name = multi.getParameter("appartment_name");
            appartment_no = multi.getParameter("appartment_no");
            tower_no = multi.getParameter("tower_no");
            type_of_apparttment = multi.getParameter("type_of_apparttment");
            floor_area = multi.getParameter("floor_area");
            power_backup = multi.getParameter("power_backup");
            electricity_meterno = multi.getParameter("electricity_meterno");
            water_meterno = multi.getParameter("water_meterno");
            gas_meterno = multi.getParameter("gas_meterno");
            name_of_owner = multi.getParameter("name_of_owner");
            owner_brithday = multi.getParameter("owner_brithday");
            owner_anniversary = multi.getParameter("owner_anniversary");
            mobileno_ofmainperson = multi.getParameter("mobileno_ofmainperson");
            emailid_ofmainperson = multi.getParameter("emailid_ofmainperson");
            arrear_amount=multi.getParameter("arreas");
            maintenaceArrears=multi.getParameter("mainArreas");
            last_grid_unit=multi.getParameter("last_grid_units");
            last_dg_unit=multi.getParameter("last_dg_units");
            late_payment_charge_status=multi.getParameter("late_payment_status");
            late_payment_charge=multi.getParameter("late_payment");
            resident_satatus = multi.getParameter("resident_satatus");
            tenent_name = multi.getParameter("tenent_name");
            tenant_phoneno = multi.getParameter("tenant_phoneno");
            tenant_email= multi.getParameter("tenant_email");
            police_verification_status = multi.getParameter("police_verification_status");
            dth_connection = multi.getParameter("dth_connection");
            dth_connection_no = multi.getParameter("dth_connection_no");
            dth_company = multi.getParameter("dth_company");

            nationality = multi.getParameterValues("nationality");
            name = multi.getParameterValues("name");
            age = multi.getParameterValues("age");
            qualification = multi.getParameterValues("qualification");
            profession = multi.getParameterValues("profession");
            email_id = multi.getParameterValues("email_id");
            office_address = multi.getParameterValues("office_address");

            type = multi.getParameterValues("type");
            make = multi.getParameterValues("make");
            brand = multi.getParameterValues("brand");
            registration_no = multi.getParameterValues("registration_no");

            pet_type = multi.getParameterValues("pet_type");
            pet_name = multi.getParameterValues("pet_name");
            pet_vaccination=multi.getParameterValues("pet_vaccination");

            helper_type=multi.getParameterValues("helper_type");
            helper_age=multi.getParameterValues("helper_age");
            helper_photo=multi.getParameterValues("helper_photo");
            helper_prev_address=multi.getParameterValues("helper_prev_address");
            helper_agnt_details=multi.getParameterValues("helper_agnt_details");
            helper_police_verification=multi.getParameterValues("helper_police_verification");
          //  if(path==null || path.equals(""))
           // {
           //     path="OwnerProfileImages/APR000010/login_button.jpg";
           // }
            if(flat_extensionno==null || "".equals(flat_extensionno))
            {
                flat_extensionno="NA";
            }
            if(plan_name==null || "".equals(plan_name))
            {
                plan_name="NA";
            }

            if(dth_connection_no==null ||"".equalsIgnoreCase(dth_connection_no)){
               dth_connection_no="NA";
            }
            if(dth_company==null||"".equalsIgnoreCase(dth_company)){
                dth_company="NA";
            }
            if(tenent_name==null ||"".equalsIgnoreCase(tenent_name)){
               tenent_name="NA";
            }
           if(tenant_phoneno==null||"".equalsIgnoreCase(tenant_phoneno)){
                tenant_phoneno="NA";
            }
           if(police_verification_status==null||"".equalsIgnoreCase(police_verification_status)){
                police_verification_status="NA";
            }
            if(tenant_email==null||"".equalsIgnoreCase(tenant_email)){
                tenant_email="NA";
            }
            if(late_payment_charge==null ||"".equalsIgnoreCase(late_payment_charge)){
                late_payment_charge="NA";
            }
            if(arrear_amount==null || "".equalsIgnoreCase(arrear_amount))
            {
                arrear_amount="0";
            }
           if(last_grid_unit==null || "".equalsIgnoreCase(last_grid_unit))
            {
                last_grid_unit="0";
            }
            if(last_dg_unit==null || "".equalsIgnoreCase(last_dg_unit))
            {
                last_dg_unit="0";
            }
      //////////////////
            

               try
               {
                jdbc = new ConnectionManager();
                con = jdbc.getConnection();
                String sql="insert into appartment values (0,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate())";
                pst = con.prepareStatement(sql);
                pst.setString(1, appartmentid.trim());
                pst.setString(2, society_name.trim());
                if(flat_extensionno==null || flat_extensionno.equals(""))
                {
                    flat_extensionno="NA";
                }
                pst.setString(3, flat_extensionno.trim());
                pst.setString(4, appartment_name.trim());
                pst.setString(5, appartment_no.trim());
                pst.setString(6, tower_no.trim());
                pst.setString(7, type_of_apparttment.trim());
                pst.setString(8, floor_area.trim());
                pst.setString(9, power_backup.trim());
                pst.setString(10, electricity_meterno.trim());
                if(water_meterno==null || water_meterno.equals(""))
                {
                    water_meterno="NA";
                }
                pst.setString(11, water_meterno.trim());
                if(gas_meterno==null || gas_meterno.equals(""))
                {
                    gas_meterno="NA";
                }
                pst.setString(12, gas_meterno.trim());
                pst.setString(13, name_of_owner.trim());
                pst.setString(14, owner_brithday.trim());
                pst.setString(15, owner_anniversary.trim());
                pst.setString(16, mobileno_ofmainperson.trim());
                pst.setString(17, emailid_ofmainperson.trim());
                pst.setString(18, arrear_amount.trim());
                if(last_grid_unit==null || last_grid_unit.equals(""))
                {
                    last_grid_unit="NA";
                }
                pst.setString(19,maintenaceArrears.trim());
                pst.setString(20, last_grid_unit.trim());
                if(last_dg_unit==null || last_dg_unit.equals(""))
                {
                    last_dg_unit="NA";
                }
                pst.setString(21, last_dg_unit.trim());
                if(resident_satatus==null || resident_satatus.equals(""))
                {
                    resident_satatus="NA";
                }
                pst.setString(22, resident_satatus.trim());
                if(tenent_name==null || tenent_name.equals(""))
                {
                    tenent_name="NA";
                }
                pst.setString(23, tenent_name.trim());
                pst.setString(24, tenant_phoneno.trim());
                pst.setString(25, tenant_email.trim());
                pst.setString(26, police_verification_status.trim());
                if(dth_connection==null || dth_connection.equals(""))
                {
                    dth_connection="NA";
                }
                pst.setString(27, dth_connection.trim());
                pst.setString(28, dth_connection_no.trim());
                pst.setString(29, dth_company.trim());
                pst.setString(30, path.trim());
                pst.setString(31, pocessession.trim());
                pst.setString(32, username.trim());
                pst.setString(33, password.trim());
                pst.setString(34, flat_types.trim());
                if(plan_name==null || plan_name.equals(""))
                {
                    plan_name="NA";
                }
                pst.setString(35,plan_name.trim());
                pst.executeUpdate();
                System.out.println("Data inserted in appartment table for postpoecesseion.... " + pst);
               }
               catch(Exception ex)
               {
                   ex.printStackTrace();
               }
            finally
            {
                ConnectionManager.close(pst);
                ConnectionManager.close(con);
            }
try
{
               String sql1="insert into appartment_ref1 values (0,?,?,?,?,?,?,?,?,sysdate())";
                for(int j=0;j<nationality.length && j<name.length && j<age.length && j<qualification.length
                        && j<profession.length && j<email_id.length && j<office_address.length;j++)
                {
                con = jdbc.getConnection();    
                pst1 = con.prepareStatement(sql1);
                pst1.setString(1, appartmentid.trim());
                pst1.setString(2, nationality[j].trim());
                pst1.setString(3, name[j].trim());
                pst1.setString(4, age[j].trim());
                pst1.setString(5, qualification[j].trim());
                pst1.setString(6, profession[j].trim());
                pst1.setString(7, email_id[j].trim());
                pst1.setString(8, office_address[j].trim());
                pst1.executeUpdate();
                 }System.out.println("Data inserted in appartment_ref1 table.... " + pst1);

               }
               catch(Exception ex)
               {
                   ex.printStackTrace();
               }
            finally
            {
                ConnectionManager.close(pst1);
                ConnectionManager.close(con);
            }
            try
            {
                String sql2="insert into appartment_ref2 values (0,?,?,?,?,?,sysdate())";
                for(int k=0;k<type.length && k<make.length && k<brand.length && k<registration_no.length;k++)
                {
                con = jdbc.getConnection();
                pst2 = con.prepareStatement(sql2);
                pst2.setString(1, appartmentid.trim());
                pst2.setString(2, type[k].trim());
                pst2.setString(3, make[k].trim());
                pst2.setString(4, brand[k].trim());
                pst2.setString(5, registration_no[k].trim());
                pst2.executeUpdate();
                 }System.out.println("Data inserted in appartment_ref2 table.... " + pst2);
              }
               catch(Exception ex)
               {
                   ex.printStackTrace();
               }
            finally
            {
                ConnectionManager.close(pst2);
                ConnectionManager.close(con);
           }
            try
            {
                String sql3="insert into appartment_pet_ref values (0,?,?,?,?,?,sysdate())";
                for(int l=0;l<pet_type.length && l<pet_name.length && l<pet_vaccination.length; l++)
                {
                String pet_id = fac_id.appartment_pet_id();
                con = jdbc.getConnection();
                pst2 = con.prepareStatement(sql3);
                pst2.setString(1, appartmentid);
                pst2.setString(2, pet_id.trim());
                pst2.setString(3, pet_type[l].trim());
                pst2.setString(4, pet_name[l].trim());
                pst2.setString(5, pet_vaccination[l].trim());
                pst2.executeUpdate();
                 }System.out.println("Data inserted in appartment_pet_ref table.... " + pst2);
              }
               catch(Exception ex)
               {
                   ex.printStackTrace();
               }
            finally
            {
                ConnectionManager.close(pst2);
                ConnectionManager.close(con);
            }
            try
            {
                String sql4="insert into helper_informaion_ref values (0,?,?,?,?,?,?,?,sysdate())";
                for(int o =0;o<helper_type.length && o<helper_age.length && o<helper_prev_address.length
                        && o<helper_agnt_details.length && o<helper_police_verification.length;o++)
                {
                String helper_id = fac_id.app_helper_id();
                con = jdbc.getConnection();
                pst2 = con.prepareStatement(sql4);
                pst2.setString(1, appartmentid);
                pst2.setString(2, helper_id.trim());
                pst2.setString(3, helper_type[o].trim());
                pst2.setString(4, helper_age[o].trim());
                pst2.setString(5, helper_prev_address[o].trim());
                pst2.setString(6, helper_agnt_details[o].trim());
                pst2.setString(7, helper_police_verification[o].trim());
                pst2.executeUpdate();
                 }
              }
               catch(Exception ex)
               {
                   Logger.getLogger(Add_OwnerProfile.class.getName()).log(Level.SEVERE, null, ex);
               }
            finally
            {
                ConnectionManager.close(pst2);
                ConnectionManager.close(con);
           }
                count++;
            System.out.println("count is==== "+count);
            if(count>0)
                {
                    System.out.append("AddOwnerProfile Mailing kklklkl  "+count);
                    SMS_ForCustomer sms = new SMS_ForCustomer();
                    sms.send_sms(mobileno_ofmainperson, passwd,emailid_ofmainperson);
                    String pass[] = name_of_owner.split(" ");
                    String pass123 = pass[0].trim();
                    passwd=pass123+"123";
                    MAIL ownermail = new MAIL();
                    ownermail.Mail_forOwner(emailid_ofmainperson,passwd);
                    if(count>0){
                    System.out.println("the count is----"+count);
                    session.setAttribute("emailid_ofmainperson", emailid_ofmainperson);
                    session.setAttribute("pass123", passwd);
                    session.setAttribute("society_name", society_name);
                    session.setAttribute("appartment_id", appartmentid);

                    RequestDispatcher rd = request.getRequestDispatcher("add_appartment.jsp");
                    rd.forward(request, response);
                    }
                    else{
                       RequestDispatcher rd = request.getRequestDispatcher("add_appartment1.jsp");
                       rd.forward(request, response);
                        }
                   }
                   else
                   {
                       RequestDispatcher rd = request.getRequestDispatcher("add_appartment1.jsp");
                       rd.forward(request, response);
                   }
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
         finally{
         ConnectionManager.close(con);
         }
          
}

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
   /* @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (SQLException ex) {
            Logger.getLogger(Add_OwnerProfile.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        try {
            processRequest(request, response);
        } catch (SQLException ex) {
            Logger.getLogger(Add_OwnerProfile.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
   @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

}

this type code i am using for connection but it could run proper but With Jdbc connection its running good
Tell me the solution for this problem