[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Connection pooling

Posted on 2009-05-17
16
Medium Priority
?
337 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

I had a project requirement for a displaying a user workbench .This workbench would consist multiple data grids .In each grid the user will be able to see a large number of data. These data grids should allow the user to 1. Sort 2. Export the …
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
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…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Suggested Courses

649 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