Solved

Connection pooling

Posted on 2009-05-17
16
324 Views
Last Modified: 2013-11-24
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
Comment
Question by:achitaley
16 Comments
 
LVL 92

Expert Comment

by:objects
ID: 24409226
could you be running out of connections?
make sure you are closing the connections once you are done with them

0
 

Author Comment

by:achitaley
ID: 24409280
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
 
LVL 92

Expert Comment

by:objects
ID: 24409297
how do you know you aren't running out of connections?

0
 

Author Comment

by:achitaley
ID: 24409378
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
 

Author Comment

by:achitaley
ID: 24409397
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
 
LVL 92

Expert Comment

by:objects
ID: 24409402
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
 
LVL 92

Expert Comment

by:objects
ID: 24409409
>         con = dsource.getConnection();

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

0
 

Author Comment

by:achitaley
ID: 24409439
but when i restart netbean IDE  i get connection
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 92

Expert Comment

by:objects
ID: 24409451
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
 

Author Comment

by:achitaley
ID: 24409567
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 24409757
>>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
 
LVL 92

Expert Comment

by:objects
ID: 24409887
> 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
 

Author Comment

by:achitaley
ID: 24409957
can u please give me example for that
0
 
LVL 92

Expert Comment

by:objects
ID: 24410322
       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
 
LVL 1

Accepted Solution

by:
massmen earned 500 total points
ID: 24428457
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
 

Author Closing Comment

by:achitaley
ID: 31582489
Thanks
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now