[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

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;
0
Adwait Chitaley
Asked:
Adwait Chitaley
1 Solution
 
objectsCommented:
could you be running out of connections?
make sure you are closing the connections once you are done with them

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

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

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Adwait ChitaleyAuthor Commented:
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;
    }
}

0
 
Adwait ChitaleyAuthor Commented:
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)
0
 
objectsCommented:
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?

0
 
objectsCommented:
>         con = dsource.getConnection();

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

0
 
Adwait ChitaleyAuthor Commented:
but when i restart netbean IDE  i get connection
0
 
objectsCommented:
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.

0
 
Adwait ChitaleyAuthor Commented:
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();
        }
    }
0
 
CEHJCommented:
>>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
0
 
objectsCommented:
> 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.

0
 
Adwait ChitaleyAuthor Commented:
can u please give me example for that
0
 
objectsCommented:
       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

0
 
massmenCommented:
hi
struts.xml
<data-sources>
        <data-source type="org.apache.commons.dbcp.BasicDataSource" key="massman">
          <set-property property="driverClassName"
                          value="com.mysql.jdbc.Driver"/>
             <set-property property="url"
                           value="jdbc:mysql://aton:3306/murali"/>
             <set-property property="username"
                           value="trg"/>
             <set-property property="password"
                           value="trg@456"/>
        </data-source>
    </data-sources>

in your action class

 ds=getDataSource(request,"massman");
            con=ds.getConnection();

you have to pass the request getting the connection and key name
0
 
Adwait ChitaleyAuthor Commented:
Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now