Connection between Web Application and DB

[Anjib Mulepati <an...@hotmail.com>]

Hi All,

I have my DB and web application running in different machine. And 
whenever my DB machine restart my application fail since it can't 
connect to the DB. I have to restart my application every time my DB start.
I am using Struts Plug-in  to load connection information from web.xml 
file and creating the DAO factory from the information. DAO factory is 
stored in servlet context. Now this daoFactory is used by each DAO class 
to create the connection.

This problem exist in my system for long time and trying to fix it. I 
have put my most code here to make things clear. I am using tomcat 7.0.26

1. Context parameter in web.xml

<context-param>
<description>Type of DB.{ 1 = Oracle }</description>
<param-name>dbType</param-name>
<param-value>1</param-value>
</context-param>
<context-param>
<description>Resource reference for the database.</description>
<param-name>resRef</param-name>
<param-value>jdbc/mydb</param-value>
</context-param>

2. Plug-in configuration in struts-config.xml

<plug-in className="com.anjib.plugin.DBConfigPlugin">

3. My DBConfigPlugin class

     public class DBConfigPlugin implements PlugIn {

         private static Log log = 
LogFactory.getLog(DBConfigPlugin.class.getName());

         private DBConfig dbConfig;

         public void destroy() {
             //Nothing to do here
         }

         public void init(ActionServlet servlet, ModuleConfig config) 
throws ServletException {
             ServletContext servletContext = servlet.getServletContext();

             /* Create factory for database */
             String resRef = servletContext.getInitParameter("resRef");
             int dbType = 0;
             if (resRef == null || resRef.trim().length() == 0) {
                 log.fatal("Parameter resRef is null or invalid");
             }
             try {
                 dbType = 
Integer.parseInt(servletContext.getInitParameter("dbType"));
             } catch (NumberFormatException nfe) {
                 log.fatal(nfe);
                 log.error("Parameter dbType is not numeric");
             }
             if (dbType != DBConfig.ORACLE) {
                 log.fatal("Parameter dbType has to be 1 (Oracle)");
             }
             DBConfig = new DBConfig(resRef, dbType);
             DAOFactory daoFactory = DAOFactory.getDAOFactory(DBConfig);
             servletContext.setAttribute("daoFactory", daoFactory);
         }

         public DBConfig getDBConfig() {
             return DBConfig;
         }

         public void setDBConfig(DBConfig DBConfig) {
             this.DBConfig = DBConfig;
         }
     }

4. Create connection in DAOFactory class
         public abstract class DAOFactory {

             private DBConfig dbConfig;

             public static DAOFactory getDAOFactory(DBConfig dbConfig) {
                 switch (dbConfig.getDbType()) {
                     case DBConfig.ORACLE:
                         return new OracleDAOFactory(dbConfig);
                     default:
                         return null;
                 }
             }

             public DAOFactory(DBConfig dbConfig) {
                 this.dbConfig = dbConfig;
             }

            public Connection createConnection() throws DAOException {
                 try {
                     String str = "java:comp/env/" + dbConfig.getResRef();
                     DataSource dataSource = (DataSource) 
ServiceLocator.getInstance().getDataSource(str);
                     return dataSource.getConnection();
                 } catch (SQLException se) {
                     throw new DAOException(se.getMessage(), se);
                 } catch (ServiceLocatorException tase) {
                     throw new DAOException(tase.getMessage(), tase);
                 }
            }
     }

5. In my com.anjib.actions.BaseAction class

     public abstract class BaseAction extends 
org.apache.struts.action.Action {

         private static Log log = 
LogFactory.getLog(BaseAction.class.getName());

         protected DAOFactory daoFactory = null;

         private String message = null;

         private UserInfo userInfo = null;

         @Override
         public ActionForward execute(ActionMapping mapping, ActionForm 
form,
                 HttpServletRequest request, HttpServletResponse response)
                 throws IOException, ServletException {
             userInfo = new UserInfo();
             setUserInfo((UserInfo) 
request.getSession().getAttribute("userInfo"));
             ActionErrors errors = new ActionErrors();
             if (request.getSession().getAttribute("loginStatus") == 
null) {
                 errors.add("error", new 
ActionMessage("error.notloggedin"));
                 this.saveErrors(request, errors);
                 return mapping.findForward("sessionEnded");
             }
             ServletContext servletContext = servlet.getServletContext();
             try {
                 daoFactory = (DAOFactory) 
servletContext.getAttribute("daoFactory");
             } catch (NullPointerException npex) {
                 log.debug(npex);
                 log.error("Couldn't find the valid factory class.");
                 message = "Factory value is NULL.";
                 errors.add("label", new 
ActionMessage("error.null.factory"));
                 saveErrors(request, errors);
                 request.setAttribute("MYEXCEPTION", new 
DAOException(message, npex));
             }
         }

         protected abstract ActionForward executeAction(ActionMapping 
mapping,
             ActionForm form, HttpServletRequest request,
             HttpServletResponse response) throws IOException, 
ServletException;


         public UserInfo getUserInfo() {
             return userInfo;
         }

        public void setUserInfo(UserInfo userInfo) {
            this.userInfo = userInfo;
         }
}

6. Context.xml

<Resource auth="Container"
             driverClassName="oracle.jdbc.driver.OracleDriver"
             maxActive="20"
             maxIdle="10"
             maxWait="2000"
             name="jdbc/mydb"
             password="passw0rd"
             testOnBorrow="true"
             type="javax.sql.DataSource"
             url="jdbc:oracle:thin:@//localhost:5000/TESTDB"
             username="scott"
             validationQuery="SELECT 1 FROM DUAL"
              />

Re: Connection between Web Application and DB

[Christopher Schultz <ch...@christopherschultz.net>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Anjib,

On 4/23/12 10:53 AM, Anjib Mulepati wrote:
> I have my DB and web application running in different machine. And 
> whenever my DB machine restart my application fail since it can't 
> connect to the DB.

That sounds like something that's fixable. :)

(Since we're here already, might I ask if you are using SSL or some
kind of secure VPN or something to connect your app server to your db
server? If not, you should seriously consider it.)

> I am using Struts Plug-in  to load connection information from
> web.xml file and creating the DAO factory from the information. DAO
> factory is stored in servlet context. Now this daoFactory is used
> by each DAO class to create the connection.

Okay.

> This problem exist in my system for long time and trying to fix it.
> I have put my most code here to make things clear. I am using
> tomcat 7.0.26
> 
> 1. Context parameter in web.xml

If your db works at all, then this is set up correctly.

> [...]
> 
> public Connection createConnection() throws DAOException { try { 
> String str = "java:comp/env/" + dbConfig.getResRef(); DataSource
> dataSource = (DataSource) 
> ServiceLocator.getInstance().getDataSource(str); return
> dataSource.getConnection();

Good: you always get a connection from the JNDI DataSource.

> <Resource auth="Container" 
> driverClassName="oracle.jdbc.driver.OracleDriver" maxActive="20" 
> maxIdle="10" maxWait="2000" name="jdbc/mydb" password="passw0rd" 
> testOnBorrow="true" type="javax.sql.DataSource" 
> url="jdbc:oracle:thin:@//localhost:5000/TESTDB" username="scott" 
> validationQuery="SELECT 1 FROM DUAL" />

I would expect that the above configuration would recover from severed
database connections: you have a validationQuery and testOnBorrow is
true by default (though it doesn't really hurt to specify it, here).

Are you sure Tomcat is using the configuration you have posted here?
If you've been playing with it a lot lately and haven't properly
re-deployed then Tomcat may be using an old configuration. Look in
CATALINA_BASE/conf/[engine]/[hostname]/ for a .xml file matching the
name of your webapp. That's the one Tomcat is using if it's there.

If everything looks good with the context.xml file, then the next
question is whether there is some other code in your webapp that might
be getting a database connection in some other way. Does your stack
trace (should occur when the database restarts) show that the error is
happening in code where the connection definitely came through your
DAOFactory class(es)?

Finally, you might be able to check to see if something funny is going
on by setting your validationQuery to something syntactically invalid
(like "SELECT this is broken") just to see if you can get the
validation query itself to fail. You might learn something from that
exercise.

- -chris
-----BEGIN PGP SIGNATURE-----
Version: GnuPG/MacGPG2 v2.0.17 (Darwin)
Comment: GPGTools - http://gpgtools.org
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk+YUuAACgkQ9CaO5/Lv0PCRsgCfQdw8OmMqbou8YyULz9GaP0vl
3CYAn1UPhwrXWVbcY8PxdLbEMSJzHIhQ
=ObFm
-----END PGP SIGNATURE-----

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org