?
Solved

Connection pooling

Posted on 2009-05-17
16
Medium Priority
?
334 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:Adwait Chitaley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Adwait Chitaley
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:Adwait Chitaley
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:Adwait Chitaley
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:Adwait Chitaley
ID: 24409439
but when i restart netbean IDE  i get connection
0
 
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:Adwait Chitaley
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:Adwait Chitaley
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 2000 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:Adwait Chitaley
ID: 31582489
Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Suggested Courses
Course of the Month13 days, 4 hours left to enroll

777 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