Link to home
Start Free TrialLog in
Avatar of Adwait Chitaley
Adwait ChitaleyFlag for India

asked on

Connection pooling

hi sorry to trouble u again.....i`m facing some problem  in connection pooling
dsource.getConnection() returns null, ..behavior is  inconsistence some time i get connection object some time null can u pls help me regarding asap

java.lang.NullPointerException

datasource connection return nulll



<data-sources>
        <data-source type="oracle.jdbc.pool.OracleDataSource">
            <set-property property="autoCommit" value="false"/>
            <set-property property="description" value="Configuration"/>
            <set-property property="driverClass" value="oracle.jdbc.OracleDriver"/>
            <set-property property="factory" value="org.apache.commons.dbcp.BasicDataSourceFactory" />
            <set-property property="URL" value="jdbc:oracle:thin:@128.127.50.122:1521:airsanchay"/>
            <set-property property="user" value="airuser"/>
            <set-property property="password" value="airsample"/>
            <set-property property="maxCount" value="20"/>
            <set-property property="minCount" value="2"/>          
        </data-source>
    </data-sources>
   
ServletContext application=servlet.getServletContext();
DataSource datasource = (DataSource) application.getAttribute(Globals.DATA_SOURCE_KEY);
ConnectionPool.dsource=datasource;
 return mapping.findForward(SUCCESS);





public static Connection openConnection() throws Exception{
        Connection con = null;
        con = dsource.getConnection();


        message = "Successful Connection.";
        System.out.println("-----conOpenedCount---->>"+(++conOpenedCount));
        return con;
Avatar of Mick Barry
Mick Barry
Flag of Australia image

could you be running out of connections?
make sure you are closing the connections once you are done with them

Avatar of Adwait Chitaley

ASKER

no not running out of connection and all connection close properly....
could it be b`coz of connection pooling configuration.. in struts-config.xml ...???

how do you know you aren't running out of connections?

i encounter out of connection exception once ..and its b`coz  connection not closed properly..
but this time around i`m closing all connection i used...so i guess its not  running out of connection...



this is my ConnectionPool.java  file where i open,close and execute query

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

package com.airsanchay.util;

import java.sql.CallableStatement;
import javax.sql.DataSource;
import java.util.HashMap;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *
 * @author santoshi
 */
public class ConnectionPool {

   public static String message = "Not Connected";
   public static DataSource dsource = null;  
   public static int conOpenedCount=0;
   public static int conClosedCount=0;
   public static final HashMap CONNECTIONS=new HashMap();

   public ConnectionPool(DataSource dsource){
       ConnectionPool.dsource = dsource;
   }  

   public static Connection openConnection() throws Exception{
        Connection con = null;
        con = dsource.getConnection();
        message = "Successful Connection.";
        System.out.println("-----conOpenedCount---->>"+(++conOpenedCount));
        return con;
    }  
   
    public static void closeConnection(Connection con,ResultSet rs){
        if(con==null){
            return;
        }
        try {
            rs.close();
            con.close();
            System.out.println("----connection closed---->>"+(++conClosedCount));
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static ResultSet executeQuery(String sqlQuery){
        if(sqlQuery ==null ){
            return null;
        }

        ResultSet rs=null;
        PreparedStatement stmt=null;
        Connection con=null;
        try{
             con=ConnectionPool.openConnection();

             stmt = con.prepareStatement(sqlQuery);
             rs = stmt.executeQuery();

             return rs;
        }catch(Exception e ){
             e.printStackTrace();
             ConnectionPool.closePreparedStatement(stmt);
             ConnectionPool.closeResultSet(rs);
             ConnectionPool.closeConnection(con);
             return rs;
        }finally{
            if(rs!=null){
                ConnectionPool.CONNECTIONS.put(rs.toString(), con);
            }          
        }
    }

    public static void closeConnection(Connection con){
        if(con==null){
            return;
        }
        try {
            con.close();
            System.out.println("----connection closed---->>"+(++conClosedCount));
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

/** close resultSet and connection *****/    

 public static void closeResultSet(ResultSet rs){
        if(rs==null){
            return;
        }
        try {
            rs.close();
            Connection con= (Connection) ConnectionPool.CONNECTIONS.get(rs.toString());
            ConnectionPool.CONNECTIONS.remove(rs.toString());
            //System.out.println("----connection closed---->>"+(++conClosedCount));
            ConnectionPool.closeConnection(con);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }


     public static void closePreparedStatement(PreparedStatement stmt){
        if(stmt==null){
            return;
        }
        try {
            stmt.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    public static void closeCollableStatement(CallableStatement  stmt){
        if(stmt==null){
            return;
        }
        try {
            stmt.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }  

}



this my login action class



package com.airsanchay.action;

import com.airsanchay.bean.LoginActionForm;
import com.airsanchay.model.LoginModel;

import com.airsanchay.util.AuditLog;
import java.net.InetAddress;
import javax.servlet.ServletContext;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.ActionForward;

/**
 *
 * @author santoshi
 */

public class LoginAction extends Action {    
   
    private final static int LOGIN_SUCCESSFUL=1;
    private final static int LOGIN_FAIL=2;
    private final static String VALID= "valid";    
    private final static String INVALID = "invalid";
   
    private ServletContext application=null;    
   
    /**
     * This is the action called from the Struts framework.
     * @param mapping The ActionMapping used to select this instance.
     * @param form The optional ActionForm bean for this request.
     * @param request The HTTP Request we are processing.
     * @param response The HTTP Response we are processing.
     * @throws java.lang.Exception
     * @return
     */
    @Override
    public ActionForward execute(ActionMapping mapping, ActionForm form,
            HttpServletRequest request, HttpServletResponse response)
            throws Exception {
           
            LoginActionForm loginForm= (LoginActionForm) form;
            HttpSession ssn = request.getSession();
           
            //check ServletContext application created  in SessionListener
            if(application==null){
                //initialize with ServletContex
                 this.application=this.getServlet().getServletContext();
            }
            int check = new LoginModel().authorizeUser(loginForm.getUsername(), loginForm.getPassword(),request,application);
            System.out.println("Login Returned: "+check);
            String userid = "";
            int userId = 0;            

            if(check==LOGIN_SUCCESSFUL){
                userid = ssn.getAttribute("UserId").toString();
                userId = Integer.parseInt(userid);
                System.out.println("User id: "+userId);
                new AuditLog().updateSystemAccessLog(InetAddress.getLocalHost().getHostAddress(),"Session Created","Successful Login",userId);
                new AuditLog().updateAuditSession(userId, ssn.getId());
                return mapping.findForward(VALID);                
            } else if(check==LOGIN_FAIL){
                if(userid==null){
                    userId=0;
                }
                System.out.println("User id: "+userId);
                System.out.println("IP address: "+InetAddress.getLocalHost().getHostAddress());
                new AuditLog().updateSystemAccessLog(InetAddress.getLocalHost().getHostAddress(),"Session Creation Failed","Error In Login",userId);                
            }
       return mapping.findForward(INVALID);
    }
}




this my loginmodel class


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

package com.airsanchay.model;

import com.airsanchay.util.ConnectionPool;
import java.sql.ResultSet;
import java.util.HashMap;
import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

/**
 *
 * @author santoshi
 */


public class LoginModel {
    HashMap LOGIN_USERS;
    private final static int LOGIN_SUCCESSFUL=1;
    private final static int LOGIN_FAIL=2;
    private final static int USER_ALREADY_LOGGED_IN=3;
    private ResultSet rs = null;
   
    /**
     *
     * @param username
     * @param password
     * @param req
     * @param application
     * @return
     * @throws java.lang.Exception
     * authorized  user login, check user existing status,username, password
     * check user  USER_ALREADY_LOGGED_IN
     */    
   
    public int authorizeUser(String username,String password,HttpServletRequest req, ServletContext application) throws Exception {
               
        int userId=0;
        String userFullName=null;
        HttpSession ses = req.getSession();
       
        try {
            String query = " SELECT user_id,user_full_name,user_login,Pkg_Pass_Enp_Dep.decrypt(USER_PASSWORD) " +
                           " FROM M55_USERS WHERE user_login = '"+username+"'" +
                           " and Pkg_Pass_Enp_Dep.decrypt(USER_PASSWORD) =" +
                           " '"+password+"' and      USER_ACTIVE_STATUS= '1' ";            
           
/********* this is where i execute quesry**********/    

        rs = ConnectionPool.executeQuery(query);
            System.out.println("Login query..."+query);
           
            if(rs.next()){
               
                userId=rs.getInt("user_id");
                userFullName=rs.getString("user_full_name");
               
            }
           
            if(userId!=0){
               
                // initialized LOGIN_USERS from application context set in ApplicattionListener
               
                LOGIN_USERS=(HashMap)application.getAttribute("aLoginUsers");
                 
                if(LOGIN_USERS.get(Integer.toString(userId)) == null){
                   
                    ses.setAttribute("UserId", Integer.toString(userId));
                    ses.setAttribute("fullname", userFullName);
                    LOGIN_USERS.put(Integer.toString(userId), ses);
                    application.setAttribute("aLoginUsers", LOGIN_USERS);
                    ses.removeAttribute("loginfail");
                    return LOGIN_SUCCESSFUL;
               
                }else{                    
                    ses.setAttribute("UserId", Integer.toString(userId));
                    return USER_ALREADY_LOGGED_IN;
                }
            }
           
        } catch (Exception e){
            e.printStackTrace();
        } finally {

/****this is where i close result set and Connection ****//
            ConnectionPool.closeResultSet(rs);
        }
        ses.setAttribute("loginfail","Login Fail Please Try Again...");
        return LOGIN_FAIL;
    }
}

this exception i get when i try to login

java.lang.NullPointerException
        at com.airsanchay.util.ConnectionPool.openConnection(ConnectionPool.java:34)
        at com.airsanchay.util.ConnectionPool.executeQuery(ConnectionPool.java:62)
        at com.airsanchay.model.LoginModel.authorizeUser(LoginModel.java:52)
        at com.airsanchay.action.LoginAction.execute(LoginAction.java:60)
        at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)
        at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)
        at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
        at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:710)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:390)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:263)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:584)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
        at java.lang.Thread.run(Thread.java:595)
That ConnectionPool class does not seem necessary, and not sure how reliable it is
you haven't said why you don't think you are running out of connections?

>         con = dsource.getConnection();

that exception suggests you are not even getting a datasource
Is the above line 34?

but when i restart netbean IDE  i get connection
you're probably incorrectly creating a ConnectionPool instance, that class does not serve any purpose that I can see. You shouldn't be trying to store connections thats what the connection pool is for.

for my convenience , i created the ConnectionPool class to track count of open and close connection,
also i dont want to declare Connection in my action or model class every time when i execute query..for that i declare executeQuery(string str) which manage open connection and i stored it in CONNECTION hash map with rs.toString() key


public static ResultSet executeQuery(String sqlQuery){
        if(sqlQuery ==null ){
            return null;
        }

        ResultSet rs=null;
        PreparedStatement stmt=null;
        Connection con=null;
        try{
             con=ConnectionPool.openConnection();

             stmt = con.prepareStatement(sqlQuery);
             rs = stmt.executeQuery();

             return rs;
        }catch(Exception e ){
             e.printStackTrace();
             ConnectionPool.closePreparedStatement(stmt);
             ConnectionPool.closeResultSet(rs);
             ConnectionPool.closeConnection(con);
             return rs;
        }finally{
            if(rs!=null){
                ConnectionPool.CONNECTIONS.put(rs.toString(), con);
            }          
        }

when i close connection using closeResultSet(ResultSet rs) , i use rs.toString() key to get  appropriate connection  from HashMap  to close and also remove connection from hash map CONNECTION


 public static void closeResultSet(ResultSet rs){
        if(rs==null){
            return;
        }
        try {
            rs.close();
            Connection con= (Connection) ConnectionPool.CONNECTIONS.get(rs.toString());
            ConnectionPool.CONNECTIONS.remove(rs.toString());
            //System.out.println("----connection closed---->>"+(++conClosedCount));
            ConnectionPool.closeConnection(con);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
>>for my convenience , i created the ConnectionPool class to track count of open and close connection,

You'd be better off doing that via logging in the connection pool rather than adding complexity that seems largely redundant
> for my convenience , i created the ConnectionPool class to track count of open and close connection,

thers no need, its already looked after for you.
And the numbers won't be correct anyway because the pool controls when the actual connection gets closed.

> when i close connection using closeResultSet(ResultSet rs) , i use rs.toString() key to get  appropriate connection  from HashMap  to close and also remove connection from hash map CONNECTION

that approach won't work. Instead just close the connection directly after you have finished with it.

can u please give me example for that
       Connection con = dataSource.getConnection();
        try {
              // do your sql
        } catch (Exception ex) {
             ex.printStackTrace();
        } finally {
            con.close();
        }

keep it simple :)

commons dbutil has classes to simplify things for you

ASKER CERTIFIED SOLUTION
Avatar of massmen
massmen

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks