Steve Dyke

Tim Holloway wrote:

4. NEVER save a Connection. Especially across servlet process calls. Connections are Interfaces, not Objects, and they are not guaranteed serializable. Obtain a Connection from the pool, process the database functions and return the Connection to the pool AS SOON AS POSSIBLE. This allows more efficient use of Connection resources, especially when there are many concurrent web requests.

Could I please get a little more clarification here?

In my Servlet Context Listener I use setAttribute to set up application level data.
Would this be acceptable in lieu of a connection for each constructor.

I also have instances where I use this approach if I have embedded SQL calls. I will wrap one of these Connections around all of the SQL code.

public void contextInitialized(ServletContextEvent servletContextEvent) {                try{                 ServletContext ctx = servletContextEvent.getServletContext();         DataConnection dataSource = new DataConnection();        ctx.setAttribute("DataSourceObj", dataSource);                //System.out.println("StartUpParms: " + dataSource.getMyApplicationName());                Connection dc = dataSource.getAs400conn();        try{         new DeleteNextLongIndexRecords(dc);         QMSConfigurationData qmsConfigurationData = new QMSConfigurationData(dc);         servletContextEvent.getServletContext().setAttribute("QMSConfigDataObj", qmsConfigurationData);         QMSConfigurationParms qmsConfigurationParms = new QMSConfigurationParms(qmsConfigurationData);         servletContextEvent.getServletContext().setAttribute("QMSConfigParmsObj", qmsConfigurationParms);                 servletContextEvent.getServletContext().setAttribute("hitCountObj", new HitCount(qmsConfigurationParms, new Source(1), dataSource));                         servletContextEvent.getServletContext().setAttribute("qmsErrorMessageListObj", new QMSErrorMessageList(dc)); servletContextEvent.getServletContext().setAttribute("jobClassGroupListObj", new JobClassGroupList(dc)); servletContextEvent.getServletContext().setAttribute("jobClassListObj", new JobClassList(dc)); servletContextEvent.getServletContext().setAttribute("faiLocationOptionListObj", new FAILocationOptionList()); servletContextEvent.getServletContext().setAttribute("faiInspectionLocationLogLocationOptionListObj", new FAIInspectionLocationLogLocationOptionList(dc)); servletContextEvent.getServletContext().setAttribute("firstArticleCategoryListObj", new FirstArticleCategoryList(dc)); }                finally{                   dc.close();                }

Tim Holloway wrote:Definitely, please explain more clearly what you are actually hoping to do.

I got it figured out. Thanks for steering me in the right direction. Here is the code I used.

public class DistributeDrawingList { private List<DistributeDrawingPath> distributeDrawingList = null; private boolean succeeded = false; public DistributeDrawingList(HttpServletRequest request){ List temp = new LinkedList(); try{ final HttpSession session = request.getSession(); final VendorExtensionParms vendorExtensionParms = (VendorExtensionParms)session.getAttribute("vendorExtensionParmsObj"); final ServletContext context = request.getServletContext(); final QMSConfigurationParms qmsConfigurationParms = (QMSConfigurationParms)context.getAttribute("QMSConfigParmsObj"); final SupplierNotificationDrawingList supplierNotificationDrawingList = (SupplierNotificationDrawingList) context.getAttribute("supplierNotificationDrawingListObj"); if(supplierNotificationDrawingList != null){ if(supplierNotificationDrawingList.getSupplierNotificationDrawingList() != null){ for(SupplierNotificationDrawing supplierNotificationDrawing: supplierNotificationDrawingList.getSupplierNotificationDrawingList()){ if(supplierNotificationDrawing.getVendorcode().equals(vendorExtensionParms.getVendorcode())){ temp.add(new DistributeDrawingPath(supplierNotificationDrawing)); } } } } if(!temp.isEmpty()){ distributeDrawingList = temp;                                   NtlmPasswordAuthentication auth = null; try{ String nDirectory = qmsConfigurationParms.getAttachmentRoot_Directory() + "RequestQuotes/" + vendorExtensionParms.getVendorcode(); auth = new NtlmPasswordAuthentication(qmsConfigurationParms.getAttachmentRoot_DOMAIN(),qmsConfigurationParms.getAttachmentRoot_ID(),qmsConfigurationParms.getAttachmentRoot_PW()); new SmbFile(nDirectory, auth).mkdirs(); } catch(Exception e){ } for(DistributeDrawingPath distributeDrawingPath: distributeDrawingList){ String fileToGetPath = "smb://" + qmsConfigurationParms.getAttachmentRoot_DOMAIN() + ";" + qmsConfigurationParms.getAttachmentRoot_ID() + ":" + qmsConfigurationParms.getAttachmentRoot_PW() + "@" + distributeDrawingPath.getDistributeDrawingPath().replaceAll("http://sgai-fs02.corp.zodiac.lan/drawings/", "sgai-fs02/Drawings/Release PDF/"); SmbFile fileToGet = new SmbFile(fileToGetPath); String filename = qmsConfigurationParms.getAttachmentRoot_Directory() + "RequestQuotes/" + vendorExtensionParms.getVendorcode() + "/" + distributeDrawingPath.getDmdrawno() + ".pdf"; if(fileToGet.exists()){ InputStream in = null; OutputStream out = null; try{                SmbFile destinationFolder=new SmbFile(filename);                fileToGet.connect();                                  in = new BufferedInputStream(new SmbFileInputStream(fileToGet));                                    out = new BufferedOutputStream(new SmbFileOutputStream(destinationFolder));                  byte[] buffer = new byte[8192];                  int len = 0; //Read length                  while ((len = in.read(buffer, 0, buffer.length)) != -1) {                            out.write(buffer, 0, len);                  } } finally{ if(in != null){ in.close(); } if(out != null){ out.flush(); //The refresh buffer output stream out.close(); } }                }                else{                                 System.out.println("DistributeDrawingList(2)): File not found: ");                                } //http://sgai-fs02.corp.zodiac.lan/drawings/810000/817739P1.pdf } } succeeded = true; } catch(Exception e){ e.printStackTrace(); } } public boolean isSucceeded() { return succeeded; } }

I have a data file that contains file paths.

like:
http://sgai-fs02.corp.zodiac.lan/drawings/810000/817739P1.pdf

In java I would like to loop through this data and create this path result(pdf file) in a particular shared folder.

I will make the directory dynamically:

try{ String nDirectory = qmsConfigurationParms.getAttachmentRoot_Directory() + "RequestQuotes/" + vendorExtensionParms.getVendorcode(); NtlmPasswordAuthentication auth = new NtlmPasswordAuthentication(qmsConfigurationParms.getAttachmentRoot_DOMAIN(),qmsConfigurationParms.getAttachmentRoot_ID(),qmsConfigurationParms.getAttachmentRoot_PW()); new SmbFile(nDirectory, auth).mkdirs(); } catch(Exception e){ }

I am initializing several remote data list in my Servlet Context.

try{                 ServletContext ctx = servletContextEvent.getServletContext();         DataConnection dataSource = new DataConnection();        ctx.setAttribute("DataSourceObj", dataSource);                                Connection dc = dataSource.getAs400conn();        try{         new DeleteNextLongIndexRecords(dc);         QMSConfigurationData qmsConfigurationData = new QMSConfigurationData(dc);         servletContextEvent.getServletContext().setAttribute("QMSConfigDataObj", qmsConfigurationData);         QMSConfigurationParms qmsConfigurationParms = new QMSConfigurationParms(qmsConfigurationData);         servletContextEvent.getServletContext().setAttribute("QMSConfigParmsObj", qmsConfigurationParms);        }         finally{ dc.close(); }    }    catch(Exception e){         System.out.println("StartUpParms: " + e);     e.printStackTrace();        }

If within the app the remote data gets changed I do this:

context.removeAttribute("QMSConfigParmsObj"); QMSConfigurationParms rQMSConfigurationParms = new QMSConfigurationParms((QMSConfigurationData)request.getServletContext().getAttribute("QMSConfigDataObj")); request.getServletContext().setAttribute("QMSConfigParmsObj", rQMSConfigurationParms);

Is the removeAttribute necessary?
Will this process cause a negative affect on other users who are currently using the app?

Tim Holloway wrote:Yes, in answer to the first question.

I thank you for spending time to explain this to me and your sense that I need a more basic explanation than should be required.

This application is huge. I have had it deployed and work on it daily to improve it for the last 14 years. It has came a long way.
However, I have always felt the way I was connecting to the data was not working the best way. Now I know thanks to JavaRanch.

I have tried to break this line down into more statements but to no avail.

Connection dc = (Connection)request.getServletContext().getAttribute("DataConnectionObj"); //After the first break I cannot get any further Connection dc = (Connection)request;

Stephan van Hulst wrote:Your code would be a LOT simpler and robust if you used REST controllers and an IoC container. Then all you needed to do would be something like this:

public final class DataSourceProvider {  @Resource(name="java:comp/env/jdbc/DB2Weber")  private DataSource dataSource;  @Produces  public DataSource getDataSource() {    return dataSource;  } }

This certainly looks like cleaner code. However, I have not used annotation up to this point.
I tried to use this piece of code and even with reading several documents(maybe not the correct ones) I still do not understand the @Resource or @Produces. I get errors on these.
Do they need to be defined in my app? Are they a part of a library? Are they native?
Sorry to be unlearned in this respect. But I desire to learn and make my application better.

Tim Holloway wrote:Allowing for my incredible ability to miss things, that looks OK to me. Main difference is that I probably would break down the connection fetch into multiple statements, since if any of the steps fails, it's hard to tell which one failed from a stack trace. But of course it's not supposed to fail.

Are you referring to this?

Connection dc = (Connection)request.getServletContext().getAttribute("DataConnectionObj");

And just to make sure I need to use the following any where I fetch the connection fetch:

Connection dc = ... try{ { finally{    dc.close() }

One more question. Are the Connection dc instances scoped to the request?

Tim Holloway wrote:Whoah. Let's keep this simple and in keeping with accepted Best Practices.

Recapping what others have said:

1. Don't create a connection pool - or Connections! - yourself within the webapp. All JEE-compliant webapp servers have the ability to create and manage Connection Pools which can be attached to single webapps or shared between multiple webapps. That insulates the database particulars from the application code, leading to easier testing (just swap out databases in the server) and more portable coding.

The connection pool is created on the WebSphere Application Server.

I have this class:

public class DataConnection { private DataSource ds = null; private static boolean connectionSuccess = true; public DataConnection(){ try { InitialContext ctx = new InitialContext(); ds = (javax.sql.DataSource) ctx.lookup("java:comp/env/jdbc/DB2WeberDEV"); } catch (Exception e){ System.out.println("DataConnection: Naming exception: No JNDI." + e); e.printStackTrace(); connectionSuccess = false; } } public Connection getAs400conn() { try {            return ds.getConnection();                   } catch (Exception e){            System.out.println("Connection exception: " + e.getMessage());            connectionSuccess = false;            return null; } } public static boolean isConnectionSuccess() { return connectionSuccess; } }

Tim Holloway wrote:2. The server will publish Connection Pools that it creates for a webapp as JNDI resources. It's a good idea to set up a ServletContextListener that locates that pool(s) via JNDI lookup and caches the pool interface as an Application-Scope object. Not that JNDI is that much overhead, but it's less trouble to pull the pool from cache. However, DO NOT CACHE CONNECTIONS! More on this later. The Pool will exist before the app starts and continue to exist until after all apps that use it shut down, so caching the pool interface is OK.

My Servlet Context Listener:

public class StartUpParms implements ServletContextListener  {        @Override    public void contextInitialized(ServletContextEvent servletContextEvent) {                try{                        ServletContext ctx = servletContextEvent.getServletContext();            DataConnection dataConnection = new DataConnection();                      ctx.setAttribute("DataConnectionObj", dataConnection);                        //Other code to set several parms and lists                    }        catch(Exception e){                        System.out.println("StartUpParms: " + e);            e.printStackTrace();                    }            }    @Override    public void contextDestroyed(ServletContextEvent servletContextEvent) {                        }       }

Is this correct so far?

If so in my classes that need to get remote data I would??

Connection dc = (Connection)request.getServletContext().getAttribute("DataConnectionObj"); //Would I need this? try{   //Select/Update/Insert remote data } finally{ dc.close(); }

Stephan van Hulst wrote:So you are implementing a custom HttpServlet to handle requests? What do the requests look like, are they like REST requests or SOAP requests or some custom format?

Servlet Context Listener:

public class StartUpParms implements ServletContextListener  { @Override    public void contextInitialized(ServletContextEvent servletContextEvent) {                try{                 ServletContext ctx = servletContextEvent.getServletContext();         DataConnection dataConnection = new DataConnection();        ctx.setAttribute("DataConnectionObj", dataConnection);        //Other code to set several parms and lists        }        catch(Exception e){                 System.out.println("StartUpParms: " + e);         e.printStackTrace();                }            }    @Override    public void contextDestroyed(ServletContextEvent servletContextEvent) {             System.out.println("Shutting down!");        ServletContext ctx = servletContextEvent.getServletContext();        DataConnection dataConnection = (DataConnection)ctx.getAttribute("DataConnectionObj");        dataConnection.setAs400Close(dataConnection.getAs400conn());          }       }

Filter:

public class QMSFilterImproved implements Filter { private static final long SESSION_TIMEOUT = 1000 * 60 * 30; // 30 minutes private static final long WARNING_TIMEOUT = (long)(SESSION_TIMEOUT - (1000 * 60 * 3));//3 Minutes before invalidate private static final long LOGOFF_TIMEOUT = (long)(SESSION_TIMEOUT - (1000 * 60 * 1));//1 Minute before invalidate private static final String LAST_ACCESS_SESSION_ATTR = "lastAccessTime"; private static final String IGNORE_MESSAGE_URI = "/QMSWebApp/MessagingControllerServlet";//Do Not Update Access Timer private static final String IGNORE_INITIAL_URI = "/QMSWebApp/InitialControllerServlet"; private static final String IGNORE_LOGON_URI = "/QMSWebApp/LogonControllerServlet";    /**     * Default constructor.     */    public QMSFilterImproved() {        // TODO Auto-generated constructor stub    } /** * @see Filter#destroy() */ public void destroy() { // TODO Auto-generated method stub } /** * @see Filter#doFilter(ServletRequest, ServletResponse, FilterChain) */ public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException { // TODO Auto-generated method stub // place your code here DataConnection dataConnection = (DataConnection)request.getServletContext().getAttribute("DataConnectionObj"); MFGDateList mfgDateList = (MFGDateList)request.getServletContext().getAttribute("mfgDateListObj"); try{ HttpServletRequest httpRequest = (HttpServletRequest) request; HttpServletResponse httpResponse = (HttpServletResponse) response; Connection dc = dataConnection.getAs400conn(); try{ //System.out.println("QMSFilterImproved: Open Connection: "); httpRequest.getSession().setAttribute("dcObj", dc); if (!IGNORE_MESSAGE_URI.endsWith(httpRequest.getRequestURI()) && !IGNORE_INITIAL_URI.endsWith(httpRequest.getRequestURI()) && !IGNORE_LOGON_URI.endsWith(httpRequest.getRequestURI())) {            chain.doFilter(new SessionAccessAwareRequest(httpRequest), new SessionAccessAwareResponse(httpResponse));            return;                    } // Now we can handle the special case of non-tracked request        boolean expired = false;        boolean timeOutWarning = false;        HttpSession session = httpRequest.getSession(false);                  if (session == null) {            expired = true;        }        else {            Long lastAccessTime = (Long) session.getAttribute(LAST_ACCESS_SESSION_ATTR);                        if (IGNORE_MESSAGE_URI.endsWith(httpRequest.getRequestURI())){                        if (lastAccessTime == null || lastAccessTime + SESSION_TIMEOUT < System.currentTimeMillis()) {                                expired = true;            }            else if (lastAccessTime + LOGOFF_TIMEOUT < System.currentTimeMillis()) {                         if(session != null){             if(session.getAttribute("msgtimerObj") != null){             MessageTimer messagetimer = (MessageTimer)session.getAttribute("msgtimerObj");             messagetimer.setLogOffTimer(true);                        }             }            }            else if (lastAccessTime + WARNING_TIMEOUT < System.currentTimeMillis()) {                         if(session != null){             if(session.getAttribute("msgtimerObj") != null){             MessageTimer messagetimer = (MessageTimer)session.getAttribute("msgtimerObj");             messagetimer.setTimeoutwarning(true);            }             }            }                                    }                    }        // Handle error or process normally        if (expired) {            //httpResponse.sendError(HttpServletResponse.SC_BAD_REQUEST);                         if(session != null){         if(session.getAttribute("logonObj") != null){         LogOn logOn = (LogOn)session.getAttribute("logonObj");         QMSConfigurationParms qmsConfigurationParms = (QMSConfigurationParms)session.getAttribute("QMSConfigParmsObj");             DefaultPrams dft = (DefaultPrams)session.getAttribute("defaultpramsObj");         new FileCleanUp(dc, logOn, qmsConfigurationParms, dft, mfgDateList);         }         RequestDispatcher disp = session.getServletContext().getRequestDispatcher("/Layout/Layout-Misc/TimedOutDisplayForm.jsp"); disp.forward(request, response);         session.invalidate(); // Invalidate manually         }                    }        else {                    chain.doFilter(request, response);        } } finally{ //System.out.println("QMSFilterImproved: Close Connection: "); dc.close(); } } catch(IllegalStateException ise){ } catch(Exception e){ System.out.println("QMSFilterImproved: " + e); e.printStackTrace(); } // pass the request along the filter chain //chain.doFilter(request, response); } private static class SessionAccessAwareRequest extends HttpServletRequestWrapper { public SessionAccessAwareRequest(HttpServletRequest request) { super(request);        } @Override        public HttpSession getSession() {            return getSession(true);        }        @Override        public HttpSession getSession(boolean create) {            HttpSession session = super.getSession(create);                        //System.out.println("QMSFilterImproved: MaxInactiveInterval(1): " + SESSION_TIMEOUT);            if (session != null) {                         if(session.getAttribute("msgtimerObj") != null){             MessageTimer messagetimer = (MessageTimer)session.getAttribute("msgtimerObj");             messagetimer.setTimeoutwarning(false);             messagetimer.setLogOffTimer(false);             }                session.setAttribute(LAST_ACCESS_SESSION_ATTR, System.currentTimeMillis());            }            return session;        } } private static class SessionAccessAwareResponse extends HttpServletResponseWrapper { public SessionAccessAwareResponse(HttpServletResponse response) { super(response); response.setHeader( "Pragma", "no-cache" ); response.setHeader( "Cache-Control", "no-cache" ); response.setDateHeader( "Expires", 0 ); response.setCharacterEncoding("UTF-8");        } } /** * @see Filter#init(FilterConfig) */ public void init(FilterConfig fConfig) throws ServletException { // TODO Auto-generated method stub } }

Client side:

$('.rcmRecalculate').on("click", function(){ var answer = confirm("Are you sure you want to recalculate the Random Conformity Matrix."); if(answer){ $.post("/QMSWebApp/UserControllerServlet", {lifecycle:"recalculateRandomConformityMatrixList"},defaultDisplayResults, 'json'); } });

Server side:

public class UserControllerServlet extends HttpServlet { private static final long serialVersionUID = 1L;          /**     * @see HttpServlet#HttpServlet()     */    public UserControllerServlet() {        super();        // TODO Auto-generated constructor stub    } /** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */ protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { this.doPost(request, response); } /** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */ protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { DefaultPrams dft = (DefaultPrams)request.getSession().getAttribute("defaultpramsObj"); HitCount hitcount = (HitCount)request.getSession().getAttribute("hitcountObj"); ProgressBar progressbar = (ProgressBar)request.getSession().getAttribute("progressbarObj"); SubSourceList subSourceList = (SubSourceList)request.getServletContext().getAttribute("subSourceListObj"); QMSConfigurationParms qmsConfigurationParms = (QMSConfigurationParms)request.getServletContext().getAttribute("QMSConfigParmsObj"); LogOn logOn = (LogOn)request.getSession().getAttribute("logonObj"); EmployeeMasterList employeeMasterList = (EmployeeMasterList)request.getServletContext().getAttribute("employeeMasterListObj"); WareHouseList wareHouseList = (WareHouseList)request.getServletContext().getAttribute("wareHouseListObj"); DepartmentList departmentList = (DepartmentList)request.getServletContext().getAttribute("departmentListObj"); WorkCenterList workCenterList = (WorkCenterList)request.getSession().getServletContext().getAttribute("workCenterListObj"); MFGDateList mfgDateList = (MFGDateList)request.getServletContext().getAttribute("mfgDateListObj"); RandomConformityMatrix randomConformityMatrix = (RandomConformityMatrix)request.getServletContext().getAttribute("randomConformityMatrixObj"); String lifecycle = ""; try{lifecycle = (request.getParameter("lifecycle") != null?request.getParameter("lifecycle"):"");}catch(Exception e){} String jReturnString = "\"\""; RequestDispatcher disp = null; try{ if(request.getSession().getAttribute("defaultpramsObj") != null){ if(lifecycle.equals("")){ disp = getServletContext().getRequestDispatcher("/Layout/Layout-Misc/TimedOutDisplayForm.jsp"); disp.forward(request, response); } else if(lifecycle.equals("recalculateRandomConformityMatrixList")){ new RecalculateRandomConformityMatrix(request, dft, qmsConfigurationParms, subSourceList); jReturnString = "{\"msg\": \"\"}"; response.getWriter().write(jReturnString); } } else{ disp = getServletContext().getRequestDispatcher("/Layout/Layout-Misc/TimedOutDisplayForm.jsp"); disp.forward(request, response); } } catch(Exception e){ System.out.println("UserControllerServlet: " + e); e.printStackTrace();    Map<String, String[]> map = request.getParameterMap();    Set set = map.entrySet();        Iterator it = set.iterator();        while (it.hasNext()) {         Map.Entry<String, String[]> entry =                (Entry<String, String[]>) it.next();         System.out.println("UserControllerServlet: Parameter Name: " + entry.getKey() + " - " + entry.getValue()[0]);                            } } } }

Data layer:

public class RecalculateRandomConformityMatrix { public RecalculateRandomConformityMatrix(HttpServletRequest request, DefaultPrams dft, QMSConfigurationParms qmsConfigurationParms, SubSourceList subSourceList){ Connection dc = (Connection)request.getSession().getAttribute("dcObj"); Calendar cal = Calendar.getInstance(); cal.setTime(new Date()); int week = cal.get(Calendar.WEEK_OF_YEAR); int year = cal.get(Calendar.YEAR); String sql = ""; String sql2 = ""; try{ long recseqSeed = 0; sql = "Select recseq From " + AS400LibraryStringsV5.PRODUCTION + ".wqmsrncmr" + " Where year = " + year + " And week = " + week; if(dft.getQms_mode().equals("TEST")){System.out.println("RandomConformityMatrix: recalculateRandomConformityMatrix: sql: " + sql);} Statement stmt = dc.createStatement(); ResultSet rs = stmt.executeQuery(sql); if(rs.next()){ recseqSeed = rs.getLong("recseq"); } rs.close(); stmt.close(); sql2 = "Delete From " + AS400LibraryStringsV5.PRODUCTION + ".wqmsrncmr" + " Where recseq > " + recseqSeed; Statement stmt2 = dc.createStatement(); stmt2.executeUpdate(sql2); stmt2.close(); new RandomConformityMatrix(dc, qmsConfigurationParms, subSourceList); } catch(Exception e){ System.out.println("RecalculateRandomConformityMatrix: sql: " + sql); System.out.println("RecalculateRandomConformityMatrix: " + e); e.printStackTrace(); } } }

Stephan van Hulst wrote:Also, how are you handling client requests? Are you writing custom servlets? Are you using some MVC framework? Are you using enterprise beans?

My Java version is 1.7.
I am not using a formal framework but try to pattern the app using MVC.
I use JSP/jQuery on the client side. This makes requests to the servlets.
I use the sql Connection class(jt400.jar) to communicate with our AS400 data server.
I have connection pool set up on the WebSphere Application Server.
In my Deployment Descriptor there is a JNDI reference set up to the connection pool.

Stephan van Hulst wrote:Yeah. What you're doing is wrong. It shouldn't be up to your application to decide what database connection to use.

What web application framework are you using, and what web application container will you be running the application in?

Rational Application Development(Web)
WebSphere Application Server running on an iSeries machine.

Stephan van Hulst wrote:Yeah. What you're doing is wrong. It shouldn't be up to your application to decide what database connection to use.

I still have the question. Do I need to do a try-finally to close the dc on the following code?

Connection dc = (Connection)request.getSession().getAttribute("dcObj");

Stephan van Hulst wrote:That looks like a bad idea to me. Rogue code can just change the mode from a completely unrelated part of the code and you'd be none the wiser. Why do you set the mode in the code? What is the difference between DB2WeberCustom and DB2WeberDEV?

Two different servers. Production and Test.

Stephan van Hulst wrote:It's good to use a connection pool. It's bad to manage the connection pool yourself.

You need to configure a DataSource in your web container or application server (e.g. Tomcat, WildFly) and you need to configure your persistence framework (e.g. Spring Data JPA, Hibernate), to use the connection pool configured in your application server. All of this can be done through config files.

What application server and persistence framework are you using? Note that instead of a high level persistence framework you can also use JDBC to access the connection pool directly.

This is what I use for connection pooling:

import java.sql.Connection; import java.sql.DriverManager; import javax.naming.InitialContext; import javax.naming.NameNotFoundException; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpSession; import javax.sql.DataSource; import com.ibm.as400.access.AS400JDBCDataSource; import com.ibm.websphere.naming.CannotInstantiateObjectException; public class DataConnection { private static int DSMODE = 0; private Connection as400conn; private DataSource ds = null; private boolean connectionSuccess = true; private Connection qt9conn; public DataConnection(){ try { InitialContext ctx = new InitialContext(); if(DSMODE == 0){ ds = (javax.sql.DataSource) ctx.lookup("java:comp/env/jdbc/DB2WeberCustom"); } else{ ds = (javax.sql.DataSource) ctx.lookup("java:comp/env/jdbc/DB2WeberDEV"); } } catch (Exception e){ System.out.println("DataConnection: Naming exception: No JNDI." + e); e.printStackTrace(); connectionSuccess = false; } } public Connection getAs400conn() { try {            return ds.getConnection();                   } catch (Exception e){            System.out.println("Connection exception: " + e.getMessage());            connectionSuccess = false;            return null; } } public Connection getQt9conn() { return qt9conn; } public void closeQT9(Connection conn) { try { qt9conn.close(); } catch (Exception e){ System.out.println(e); connectionSuccess = false; } } public void setAs400Close(Connection conn) { try { conn.close(); } catch (Exception e){ System.out.println(e); connectionSuccess = false; } } public boolean isConnectionSuccess() { return connectionSuccess; } public static int getDSMODE() { return DSMODE; } public static void setDSMODE(int dSMODE) { DSMODE = dSMODE; } }

Stephan van Hulst wrote:This doesn't look right at all. Why are you opening a connection in a filter? And why are you ignoring certain URLs? And why are you wrapping the request/response in a custom wrapper?

I don't think a filter is the right place to set up connections. And even if you really want to do this, you're doing too much in a single filter. Filters should have a clear, single responsibility.

I found this in Stack Overflow:

In a Web app, each http request from a user runs in its own thread (or through a thread pool). Having a global connection object shared by all requests is not a good idea because only one thread at a time will be able to use it and it will become a bottleneck for your app.

The recommended setup is to use a connection pool (e.g. C3P0) that is initialized on server startup (either automatically through config or manually in your ServletContextListener). The connection pool will create and close the connections as needed. When you receive an http request (servlet's doPost or doGet), you then just have to get a connection from the pool and return it to the pool once you're done processing that request.

You can use a ServletFilter to automate that part. In your filter, before the call to chain.doFilter(), you get a connection from the pool and store it in a request attribute. After the call to doFilter(), you return it to the pool.