Connection is getting closed everytime when server is down. have added timeout 10000000.

Question

I have created connection pool using ContextListener and host this project to tomcat.my config part as below.

in tomcat confg context.xml i have defined resource as below.

<Resource name="jdbc/TEST_DS"
    auth="Container"
    type="javax.sql.DataSource"
    driverClassName="oracle.jdbc.driver.OracleDriver"
    url="jdbc:oracle:thin:@server:1521/db"
    username="uname"
    password="pwd"
    maxPoolSize="50"
    removeAbandoned="true"
    removeAbandonedTimeout="1000"
    logAbandoned="true"
/>

now use this resource in ContextListener as below.

public class ConnectionListener implements ServletContextListener {
    private DataSource dataSourceOracle = null;
    private Connection connectionOracle = null;
    private static final String ATTRIBUTE_NAME = "config";

    public void contextDestroyed(ServletContextEvent sce) {

        try {

            if(connectionOracle!=null && !connectionOracle.isClosed() ){
                            this.connectionOracle.close();
                            this.connectionOracle = null;
                    }
                ApplicationUtil.setServletContext(sce.getServletContext()); 

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
                e.printStackTrace();
                }
    }

    public void contextInitialized(ServletContextEvent event) {
            ServletContext servletContext = event.getServletContext();
            try {

                    String oracleDsName = servletContext.getInitParameter("oracle.ds.name");

                Context ctx = new InitialContext();
                        Context envContext = (Context) ctx.lookup("java:/comp/env");

                dataSourceOracle = (DataSource) envContext.lookup (oracleDsName); 
                connectionOracle =  dataSourceOracle.getConnection();
                        System.out.println("testing Oracle connection >> "+connectionOracle);

             ApplicationUtil.setServletContext(event.getServletContext()); 

            } catch (SQLException e) {
                        e.printStackTrace();
                }
                catch (NamingException e) {
                       e.printStackTrace();
               } catch (Exception e) {
                       e.printStackTrace();
               }

        servletContext.setAttribute(ATTRIBUTE_NAME, this);
    }

    public Connection getOracleConnection() throws SQLException, ClassNotFoundException {
       return this.connectionOracle;
        }

    public static ConnectionListener getInstance(ServletContext servletContext) {
            return (ConnectionListener) servletContext.getAttribute(ATTRIBUTE_NAME);
    }

}

now call this connection using method:

public class ApplicationUtil {
     private static ServletContext context;  
        /* Called by Listener */  
        public static void setServletContext(ServletContext context){  
            ApplicationUtil.context = context;  
        }  
        /* Use this method to access context from any location */  
        public static ServletContext getServletContext(){  
            return ApplicationUtil.context;  
        }  
}

public class DBAccess {
    ServletContext context = null;

    public DBAccess(ServletContext cnt) {
        context = cnt;
    }

    public Connection getOracleConnection() throws SQLException, ClassNotFoundException {
      return ConnectionListener.getInstance(context).getOracleConnection();
    }

    public List getLanguageList() {
      Connection cn = getOracleConnection();
      ...
   }

 }

these all are what i have created to make connection pool. now the issue is when the server is down connection will be closed . and i need to restart tomcat every time to make connection pool again.

Is there any permanent solution to resolve this ??

Any suggestions will be appreciate.

Thanks in adv.

Answer 1

A db connection cannot survive the db. When the DB shuts down, the connection is lost.
Don't get and store the connection object in the context initializing.
Ask the pool for a connection every time you need it and after working on it, release it. If sometimes the db is not startet, you get errors, that you must handle, but if the db restarts, you are able to get connections without restarting your tomat.