I revised my code today but still not sure if this is the correct way to do datasource connection. Here are my codes.
Thank you Julian and Gregg for responding to my questions.
ackage gov.state.il.us.pe.AL_ALSPkga;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class pealListUsers extends HttpServlet {
public void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
performTask(req, resp);
}
public void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
performTask(req,resp);
}
public void performTask(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
try {
// Get input parameter and keep it on the HTTP session
String userId = req.getParameter("userId").toUpperCase();
if (userId.equals("")) {
// Retrieve list of users and store in an array
pealListUsersSearchBean listUsers = new pealListUsersSearchBean();
pealUser[] users = listUsers.executeSearch();
req.setAttribute("users", users);
// Call the presentation renderer
getServletContext().getRequestDispatcher("/ALSReports/ListUsers.jsp").forward(req, resp);
} else {
// Retrieve user details
pealUserDetailsSearchBean userDetails = new pealUserDetailsSearchBean();
pealUser user = userDetails.executeSearch(userId);
pealAdjustorSearchBean adjustor = new pealAdjustorSearchBean();
pealAdjustor adj = adjustor.executeSearch(userId);
req.setAttribute("user", user);
req.setAttribute("adjustor", adj);
getServletContext().getRequestDispatcher("/ALSReports/UserDetails.jsp").forward(req, resp);
}
} catch (Exception e) {
req.setAttribute("message", e.getMessage());
req.setAttribute("forward", "../ALSReports/UserSecurity.jsp");
getServletContext().getRequestDispatcher("/ALSReports/ShowException.jsp").forward(req, resp);
}
}
}
package gov.state.il.us.pe.AL_ALSPkga;
import java.sql.ResultSet;
import java.sql.SQLException;
public class pealUserDetailsSearchBean {
/**
* execute the search and return the result.
*/
public pealUser executeSearch(String userId) throws UserDoesNotExistException,
CloseResultSetException, CloseStatementException, CloseConnectionException {
pealUser user = new pealUser();
try {
user.initConnection();
user.createStatement();
//String ssUSER = (String) session.getAttribute("ssUSER");
//String requestURI = req.getRequestURI();
//user.executeInsert(ssUSER, requestURI, "ALS User Security Details");
ResultSet rs = user.getUserDetails(userId);
} catch (SQLException e){
throw new UserDoesNotExistException();
} finally {
try {
user.closeResultset();
} catch (SQLException e) {
throw new CloseResultSetException();
}
try {
user.closeStatement();
} catch (SQLException e) {
throw new CloseStatementException();
}
try {
user.closeConnection();
} catch (SQLException e) {
throw new CloseConnectionException();
}
}
return user;
}
}
package gov.state.il.us.pe.AL_ALSPkga;
import java.sql.ResultSet;
import java.sql.SQLException;
public class pealAdjustorSearchBean {
/**
* execute the search and return the result.
*/
public pealAdjustor executeSearch(String userId) throws ConnectionErrorException,
CloseResultSetException, CloseStatementException, CloseConnectionException {
pealAdjustor adjustor = new pealAdjustor();
try {
adjustor.initConnection();
adjustor.createStatement();
ResultSet adj = adjustor.getAdjustor(userId);
} catch (SQLException e) {
throw new ConnectionErrorException();
} finally {
try {
adjustor.closeResultset();
} catch (SQLException e) {
throw new CloseResultSetException();
}
try {
adjustor.closeStatement();
} catch (SQLException e) {
throw new CloseStatementException();
}
try {
adjustor.closeConnection();
} catch (SQLException e) {
throw new CloseConnectionException();
}
}
return adjustor;
}
}
package gov.state.il.us.pe.BL_BillingPkga;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
public class peblDBConnectBean {
//Variables visible to this class and its subclasses
protected Connection dbConnect = null;
protected Statement stmt = null;
protected PreparedStatement pstmt = null;
protected ResultSet rs = null;
protected SimpleDateFormat sdf = new SimpleDateFormat
("EEE,' ' MMM d' 'yyyy");
/*********************************************************************
* Initialize the database connection
*/
public void initConnection() throws SQLException {
peblTestConnection con = new peblTestConnection();
dbConnect = con.getDBConnection();
}
/*********************************************************************
* Create statement
*/
public void createStatement() throws SQLException {
stmt = dbConnect.createStatement();
}
/*********************************************************************
* Close result set
*/
public void closeResultset() throws SQLException {
if (rs != null) {
rs.close();
}
}
/*********************************************************************
* Close statement
*/
public void closeStatement() throws SQLException {
if (stmt != null) {
stmt.close();
}
}
/*********************************************************************
* Close database connection
*/
public void closeConnection() throws SQLException {
if (dbConnect != null) {
dbConnect.close();
}
}
/*********************************************************************
* Move to the next row of the result set if it exists and return
* true if there is another row of data
*/
public boolean next() throws SQLException {
return rs.next();
}
}
package gov.state.il.us.pe.BL_BillingPkga;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
public class peblWebBillingBean extends peblDBConnectBean {
//Variables visible to this class only
private int seq;
private String userId;
private String pageAccessed;
private String systemId;
private Timestamp dateAdded;
private String addUser;
private String user;
private int totalCount;
private int pageCount;
/*********************************************************************
* Execute the database insert
*/
public void executeInsert(String u, String s, String p)
throws SQLException {
String sqlText =
("INSERT INTO PER966.WEBBILLINGTBL "
+ "(USERID, SYSTEMID, PAGE_ACCESSED, DATE_ADDED, ADD_USER) "
+ "VALUES (?, ?, ?, CURRENT TIMESTAMP, USER)");
//create the prepare statement
pstmt = dbConnect.prepareStatement(sqlText);
setUserId(u);
setSystemId(s);
setPageAccessed(p);
pstmt.clearParameters();
pstmt.setString(1, userId);
pstmt.setString(2, systemId);
pstmt.setString(3, pageAccessed);
pstmt.executeUpdate();
pstmt.close();
}
/*********************************************************************
* Execute the database query
*/
public void executeSQL2() throws SQLException {
String sqlText =
("SELECT SEQ, USERID, PAGE_ACCESSED, SYSTEMID, "
+ "DATE_ADDED, ADD_USER "
+ "FROM PER966.WEBBILLINGTBL "
+ "ORDER BY SEQ DESC ");
rs = stmt.executeQuery(sqlText);
}
/*********************************************************************
* Execute the database query
*/
public void executeSQLUser() throws SQLException {
String sqlText =
("SELECT USERID,PAGE_ACCESSED,COUNT(*) as TOTAL_COUNT "
+ "FROM PER966.WEBBILLINGTBL "
+ "GROUP BY USERID,PAGE_ACCESSED "
+ "ORDER BY USERID,3 DESC,2 ");
rs = stmt.executeQuery(sqlText);
}
/*********************************************************************
* Execute the database query
*/
public void executeSQLPage() throws SQLException {
String sqlText =
("SELECT PAGE_ACCESSED,COUNT(*) as PAGE_COUNT "
+ "FROM PER966.WEBBILLINGTBL "
+ "GROUP BY PAGE_ACCESSED "
+ "ORDER BY 2 DESC ");
rs = stmt.executeQuery(sqlText);
}
/*********************************************************************
* Execute the database query
*/
public void executeSQLDate() throws SQLException {
String sqlText =
("SELECT DATE(DATE_ADDED) AS DATE_ADDED,PAGE_ACCESSED,COUNT(*) as PAGE_COUNT "
+ "FROM PER966.WEBBILLINGTBL "
+ "GROUP BY DATE_ADDED,PAGE_ACCESSED "
+ "ORDER BY DATE_ADDED DESC,PAGE_ACCESSED ");
rs = stmt.executeQuery(sqlText);
}
/*********************************************************************
* Execute the database query
*/
public String getUser() throws SQLException {
String userDB2 = null;
String sqlText =
("SELECT USER FROM SYSIBM.SYSDUMMY1 ");
stmt = dbConnect.createStatement();
rs = stmt.executeQuery(sqlText);
if (rs.next()) {
userDB2 = rs.getString(1);
}
rs.close();
stmt.close();
return userDB2;
}
/*********************************************************************
* Getters and setters
*/
public String getAddUser() throws SQLException {
String addUser = rs.getString("ADD_USER");
return addUser;
}
public String getDateAdded() throws SQLException {
String dateAdded = rs.getString("DATE_ADDED");
return dateAdded;
}
public String getPageAccessed() throws SQLException {
String pageAccessed = rs.getString("PAGE_ACCESSED");
return pageAccessed;
}
public int getSeq() throws SQLException {
int seq = rs.getInt("SEQ");
return seq;
}
public String getSystemId() throws SQLException {
String systemId = rs.getString("SYSTEMID");
return systemId;
}
public String getUserId() throws SQLException {
String userId = rs.getString("USERID");
return userId;
}
public int gettotalCount() throws SQLException {
int totalCount = rs.getInt("TOTAL_COUNT");
return totalCount;
}
public int getpageCount() throws SQLException {
int pageCount = rs.getInt("PAGE_COUNT");
return pageCount;
}
public void setPageAccessed(String p) {
if (p.length() > 40) {
pageAccessed = p.substring(0, 40);
}
else if (p == null) {
pageAccessed = " ";
}
else {
pageAccessed = p;
}
}
public void setSystemId(String s) {
if (s.length() > 18) {
systemId = s.substring(0, 18);
}
else if (s == null) {
systemId = " ";
}
else {
systemId = s;
}
}
public void setUserId(String u) {
if (u.length() > 18) {
userId = u.substring(0, 18);
}
else if (u == null) {
userId = " ";
}
else {
userId = u;
}
}
}
package gov.state.il.us.pe.BL_BillingPkga;
import java.sql.Connection;
import java.util.Hashtable;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletConfig;
import javax.sql.DataSource;
public class peblConnection {
private java.sql.Connection dbCon = null ;
private javax.sql.DataSource ds = null ;
public Connection getDBConnection(){
Context ctx = null;
try {
Hashtable env = new Hashtable() ;
env.put(Context.INITIAL_CONTEXT_FACTORY,"com.ibm.ejs.ns.jndi.CNInitialContextFactory") ;
ctx = new InitialContext(env) ;
ds =(javax.sql.DataSource)ctx.lookup("jdbc/DB2THLOC") ;
// ds=(javax.sql.DataSource)ctx.lookup("jdbc/default_pool_datasource");
ctx.close() ;
dbCon = ds.getConnection() ;
}
catch(Exception es) {
System.out.print("Error occured in init() ---- "+es.getMessage()) ;
}
return dbCon ;
}
}
package gov.state.il.us.pe.AL_ALSPkga;
import gov.state.il.us.pe.BL_BillingPkga.peblWebBillingBean;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
public class pealUser extends peblWebBillingBean {
//Variables visible to this class only
private String userId;
private String addDt;
private String addUser;
private String firstName;
private String lastName;
private String name;
private String dept;
private String div;
private String deptDiv;
private String email;
private String phoneNo;
private String workLoc;
private String workAdd1;
private String workAdd2;
private String workCity;
private String workAdZip;
private String ereports;
private int accessLvl;
private String agyAccess;
Vector results;
// List Users Methods
/*********************************************************************
* Execute the database query to retrieve all users from the
* PER320.SECURITYTBL table.
*/
public void getUsers() throws SQLException {
String sqlText =
("SELECT DISTINCT USERID, "
+ "STRIP(LAST_NAME)|| ', '||STRIP(FIRST_NAME) AS NAME, "
+ "DEPT||DIV AS DEPTDIV, "
+ "SUBSTR(PHONE_NO,1,3)||'-'||SUBSTR(PHONE_NO,4,3)||'-'|| "
+ "SUBSTR(PHONE_NO,7,4) AS PHONE_NO "
+ "FROM PER320.SECURITYTBL "
+ "ORDER BY USERID ");
rs = stmt.executeQuery(sqlText);
}
/*********************************************************************
* Connect to database and execute SQL.
* If first row existed in the ResultSet then create a user instance
* from the row.
*/
public Vector getListUsers() throws SQLException {
getUsers();
results = new Vector();
if (rs.next()) {
pealUser user = new pealUser();
user.populate(rs);
results.add(user);
parseResultSet();
}
return results;
}
/*********************************************************************
* Read each row in the ResultSet and create a user instance from
* the row.
*/
public void parseResultSet() throws SQLException {
while (rs.next()) {
pealUser user = new pealUser();
user.populate(rs);
results.add(user);
}
}
/*********************************************************************
* Initialize the datamembers of this class using data from the
* current row of the ResultSet.
*/
public void populate (ResultSet rset) throws SQLException {
setUserId(rset.getString("USERID"));
setName(rset.getString("NAME"));
setDeptDiv(rset.getString("DEPTDIV"));
setPhoneNo(rset.getString("PHONE_NO"));
}
// User Details Methods
/*********************************************************************
* Execute the database query to retrieve a user from the
* PER320.SECURITYTBL table.
*/
public ResultSet getUserDetails(String userId) throws SQLException,
UserDoesNotExistException {
String sqlText =
("SELECT USERID, FIRST_NAME, LAST_NAME, "
+ "DEPT||DIV AS DEPTDIV, DEPT, DIV, EMAIL, "
+ "SUBSTR(PHONE_NO,1,3)||'-'||SUBSTR(PHONE_NO,4,3)||'-'|| "
+ "SUBSTR(PHONE_NO,7,4) AS PHONE_NO, "
+ "WORK_LOC, WORK_ADD1, WORK_ADD2, WORK_CITY, WORK_ADZIP, "
+ "EREPORTS, ACCESS_LVL, AGY_ACCESS "
+ "FROM PER320.SECURITYTBL "
+ "WHERE USERID = '" + userId + "' ");
rs = stmt.executeQuery(sqlText);
if (rs.next()) {
setUserId(rs.getString("USERID"));
setDeptDiv(rs.getString("DEPTDIV"));
setPhoneNo(rs.getString("PHONE_NO"));;
setFirstName(rs.getString("FIRST_NAME"));
setLastName(rs.getString("LAST_NAME"));
setEmail(rs.getString("EMAIL"));
setWorkLoc(rs.getString("WORK_LOC"));
setWorkAdd1(rs.getString("WORK_ADD1"));
setWorkAdd2(rs.getString("WORK_ADD2"));
setWorkCity(rs.getString("WORK_CITY"));
setWorkAdZip(rs.getString("WORK_ADZIP"));
setEreports(rs.getString("EREPORTS"));
setAccessLvl(rs.getInt("ACCESS_LVL"));
setAgyAccess(rs.getString("AGY_ACCESS"));
} else {
throw new UserDoesNotExistException();
}
return rs;
}
//
/*********************************************************************
* Execute the database query to update a user from the
* PER320.SECURITYTBL table.
*/
public void executeUpdate() throws SQLException {
String sqlUpdate =
("UPDATE PER320.SECURITYTBL "
+ "SET FIRST_NAME = ?, LAST_NAME = ?, DEPT = ?, DIV = ?, "
+ "EMAIL = ?, PHONE_NO = ?, WORK_LOC = ?, WORK_ADD1 = ?, "
+ "WORK_ADD2 = ?, WORK_CITY = ?, WORK_ADZIP = ?, "
+ "EREPORTS = ?, ACCESS_LVL = ?, AGY_ACCESS = ? "
+ "WHERE USERID = ? ");
//create the prepared statement
pstmt = dbConnect.prepareStatement(sqlUpdate);
pstmt.clearParameters();
pstmt.setString(1, firstName);
pstmt.setString(2, lastName);
pstmt.setString(3, dept);
pstmt.setString(4, div);
pstmt.setString(5, email);
pstmt.setString(6, phoneNo);
pstmt.setString(7, workLoc);
pstmt.setString(8, workAdd1);
pstmt.setString(9, workAdd2);
pstmt.setString(10, workCity);
pstmt.setString(11,workAdZip);
pstmt.setString(12, ereports);
pstmt.setInt(13, accessLvl);
pstmt.setString(14, agyAccess);
pstmt.setString(15, userId);
pstmt.executeUpdate();
pstmt.close();
}
/*********************************************************************
* Getters and Setters
*
*/
public String getUserId() throws SQLException {
return userId;
}
public String getFirstName() throws SQLException {
return firstName;
}
public String getLastName() throws SQLException {
return lastName;
}
public String getName() throws SQLException {
return name;
}
public String getWorkLoc() throws SQLException {
return workLoc;
}
public String getWorkAdd1() throws SQLException {
return workAdd1;
}
public String getWorkAdd2() throws SQLException {
return workAdd2;
}
public String getWorkCity() throws SQLException {
return workCity;
}
public String getWorkAdZip() throws SQLException {
return workAdZip;
}
public String getEmail() throws SQLException {
return email;
}
public String getEreports() throws SQLException {
return ereports;
}
public int getAccessLvl() throws SQLException {
return accessLvl;
}
public String getAgyAccess() throws SQLException {
return agyAccess;
}
public String getDept() throws SQLException {
return dept;
}
public String getDiv() throws SQLException {
return div;
}
public String getDeptDiv() throws SQLException {
return deptDiv;
}
public String getPhoneNo() throws SQLException {
return phoneNo;
}
public void setPhoneNo(String s) {
phoneNo = s;
}
public void setAccessLvl(int i) {
accessLvl = i;
}
public void setAgyAccess(String s) {
agyAccess = s;
}
public void setDept(String s) {
dept = s;
}
public void setDiv(String s) {
div = s;
}
public void setEmail(String s) {
email = s;
}
public void setEreports(String s) {
ereports = s;
}
public void setFirstName(String s) {
firstName = s;
}
public void setLastName(String s) {
lastName = s;
}
public void setWorkAdd1(String s) {
workAdd1 = s;
}
public void setWorkAdd2(String s) {
workAdd2 = s;
}
public void setWorkAdZip(String s) {
workAdZip = s;
}
public void setWorkCity(String s) {
workCity = s;
}
public void setWorkLoc(String s) {
workLoc = s;
}
public void setDeptDiv(String s) {
deptDiv = s;
}
public void setName(String s) {
name = s;
}
public void setUserId(String s) {
userId = s;
}
}
package gov.state.il.us.pe.AL_ALSPkga;
import gov.state.il.us.pe.BL_BillingPkga.peblWebBillingBean;
import java.sql.ResultSet;
import java.sql.SQLException;
public class pealAdjustor extends peblWebBillingBean {
//Variables visible to this class only
private String adjustorCd;
private String userId;
private String adjustorName;
private String active;
// User Details Methods
/*********************************************************************
* Execute the database query to retrieve a user from the
* PER320.ADJUSTORTBL table.
*/
public ResultSet getAdjustor(String userId) throws SQLException {
String sqlText =
("SELECT ADJUSTOR_CD, ACTIVE "
+ "FROM PER320.ADJUSTORTBL "
+ "WHERE USERID = '" + userId + "' ");
rs = stmt.executeQuery(sqlText);
if (rs.next()) {
setAdjustorCd(rs.getString("ADJUSTOR_CD"));
setActive(rs.getString("ACTIVE"));
}
return rs;
}
/*********************************************************************
* Getters and Setters
*
*/
public String getUserId() throws SQLException {
return userId;
}
public String getActive() {
return active;
}
public String getAdjustorCd() {
return adjustorCd;
}
public String getAdjustorName() {
return adjustorName;
}
public void setActive(String s) {
active = s;
}
public void setAdjustorCd(String s) {
adjustorCd = s;
}
public void setAdjustorName(String s) {
adjustorName = s;
}
public void setUserId(String s) {
userId = s;
}
}
