Re: Connection Pooling

Ok,
I've done some reading and stuff on how to handle database records and I 
think I got it now, but I want someone to verify my thoughts.
Instead of accessing my result sets and record processing through the 
servlets, because they are multithreaded, I'm better off creating a DAO 
class for each table and using instantiated classes of that because 
instantiated classes are thread safe yes?  My code follows for a simple 
servlet that saves a new softwareName.  Thus I shouldn't get two different 
threads changing the variables in each one as it goes thru.  Thanks,

package com.csj.hardwaremaintenance;

import java.io.IOException;
import java.sql.DatabaseMetaData;
import java.util.Hashtable;

import javax.naming.InitialContext;
import javax.servlet.ServletConfig;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.sql.DataSource;

/**
 * @version     1.0
 * @author
 */
public class SaveSoftwareName extends HttpServlet {

        private String dsname;
        private static DataSource ds = null;
        private DatabaseMetaData dmd;
        private ServletContext context;
        private String jdbc_source;
        private String softnamTable;
        private static String collectionName;

        /**
        * @see javax.servlet.http.HttpServlet#void 
(javax.servlet.http.HttpServletRequest, 
javax.servlet.http.HttpServletResponse)
        */
        public void doGet(HttpServletRequest req, HttpServletResponse 
resp)
                throws ServletException, IOException {
                performTask(req, resp);
        }

        /**
        * @see javax.servlet.http.HttpServlet#void 
(javax.servlet.http.HttpServletRequest, 
javax.servlet.http.HttpServletResponse)
        */
        public void doPost(HttpServletRequest req, HttpServletResponse 
resp)
                throws ServletException, IOException {
                performTask(req, resp);
        }

        private void getDS() {
                try {
                        Hashtable parms = new Hashtable();
                        parms.put(
                                "java.naming.factory.initial",
 "com.ibm.websphere.naming.WsnInitialContextFactory");
                        InitialContext cxt = new InitialContext(parms);
                        ds = (DataSource) cxt.lookup("jdbc/" + dsname);
                } catch (Exception e) {
                        e.printStackTrace();
                }
        }

        public void init(ServletConfig config) throws ServletException {
                super.init(config);
                try {
                        context = getServletContext();
                        collectionName = 
context.getInitParameter("misc.lib");
                        softnamTable = 
context.getInitParameter("softnam");
                        dsname = context.getInitParameter("ds.name");
                } catch (Exception e) {
                        System.err.println(e);
                }
        }

        public void performTask(HttpServletRequest req, 
HttpServletResponse resp) {
                try {
                        if (ds == null)
                                getDS();
                        HttpSession sess = req.getSession(true);
                        SoftwareNameDAO sd = new SoftwareNameDAO(req, 
resp, ds, softnamTable, collectionName);
                        String url = "";
                        String softwareName_error = sd.insertRecord();
                        if (softwareName_error.equals("")){
                                url = "addsoftwarenameclose.jsp";
                        } else {
                                url = "addsoftwarename.jsp";
                                req.setAttribute("softwareName_error", 
softwareName_error);
                        }
                        getServletConfig()
                                .getServletContext()
                                .getRequestDispatcher(
                                                url).forward(
                                                                req,
                                                                resp);
                } catch (Exception e) {
                        e.printStackTrace();
                }
        }
}

and my DAO

/*
 * Created on May 3, 2006
 *
 * 
 */
package com.csj.hardwaremaintenance;

import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

/**
 * @author RPower
 *
 * 
 */
public class SoftwareNameDAO {
        String softwareName = null;
        String softwareNotes = null;
        Connection conn = null;
        DataSource ds = null;
        DatabaseMetaData dmd = null;
        PreparedStatement insertRecord = null;
        PreparedStatement checkRecord = null;
        ResultSet rs = null;
        String collectionName = null;
        String softnamTable = null;
 

        /**
         * @param req
         * @param resp
         * @param ds
         * @param softwareName
         * @param softwareNotes
         */
        public SoftwareNameDAO(HttpServletRequest req, HttpServletResponse 
resp, DataSource ds, String softnamTable, String collectionName) {
                super();
                if (req.getParameter("softwareName") != null) {
                        this.softwareName = (String) 
req.getParameter("softwareName").toUpperCase();
                } else {
                        this.softwareName = "";
                }
                if (req.getParameter("softwareNotes") != null) {
                        this.softwareNotes = (String) 
req.getParameter("softwareNotes").toUpperCase();
                } else {
                        this.softwareNotes = "";
                }
                this.ds = ds;
                this.softnamTable = softnamTable;
                this.collectionName = collectionName;
        }

        /**
         * 
         */
        public String insertRecord() {
                String retfield = "";
                try {
                        conn = ds.getConnection();
                        dmd = conn.getMetaData();
                        checkRecord =
                                conn.prepareStatement(
                                                "SELECT * FROM "
                                                + collectionName
                                                + 
dmd.getCatalogSeparator()
                                                + softnamTable
                                                + " WHERE SOFTNM = '"
                                                + softwareName.trim()
                                                + "'");
                        rs = checkRecord.executeQuery();
                        if (rs.next()){
                                //return error that record exists
                                retfield = "Cannot add record, Software 
Name '" + softwareName.trim() + "' already exists!";
                        } else {
                                insertRecord =
                                        conn.prepareStatement(
                                                        "INSERT INTO "
                                                        + collectionName
                                                        + 
dmd.getCatalogSeparator()
                                                        + softnamTable
                                                        + " (SOFTNM, 
SOFTNT) "
                                                        + "VALUES(?,?)");
                                insertRecord.setString(1, softwareName);
                                insertRecord.setString(2, softwareNotes);
                                insertRecord.execute();
                                retfield = "";
                        }
                } catch (SQLException e) {
                        e.printStackTrace();
                } finally {
                        if(rs!=null){
                                try {
                                        rs.close();
                                } catch (SQLException e1) {
                                        e1.printStackTrace();
                                }
                                rs = null;
                        }
                        if(checkRecord!=null){
                                try {
                                        checkRecord.close();
                                } catch (SQLException e1) {
                                        e1.printStackTrace();
                                }
                                checkRecord = null;
                        }
                        if(insertRecord!=null){
                                try {
                                        insertRecord.close();
                                } catch (SQLException e1) {
                                        e1.printStackTrace();
                                }
                                insertRecord = null;
                        }
                        if(conn!=null){
                                try {
                                        conn.close();
                                } catch (SQLException e1) {
                                        e1.printStackTrace();
                                }
                                conn = null;
                        }
                }
                return retfield;
 
        }
 
}


Ron Power
Programmer
Information Services
City Of St. John's, NL
P.O. Box 908
St. John's, NL
A1C 5M2
709-576-8132
rpower@xxxxxxxxxx
http://www.stjohns.ca/
___________________________________________________________________________
Success is going from failure to failure without a loss of enthusiasm. - 
Sir Winston Churchill





This email communication and accompanying documents is intended only for the 
individual or entity to which it is addressed and may contain information that 
is confidential, privileged or exempt from disclosure under applicable law. Any 
use of this information by individuals or entities other than the intended 
recipient is strictly prohibited. If you have received this in error, please 
notify the sender and delete all the copies (electronic or otherwise) 
immediately.