wood burning stoves 2.0*
The moose likes JDBC and the fly likes Datasource Connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Datasource Connection " Watch "Datasource Connection " New topic
Author

Datasource Connection

Michael Hoang
Greenhorn

Joined: Aug 17, 2004
Posts: 5
Can anyone tells me if this the correct way of getting connection using a servlet? I need to process against 2 beans after the else statement, do I need to get connection and close connection and then open connection again when I goes against my second bean? Can I open the connection just one time and process both beans? Any help is appreciated.



[Edited by Gregg to include UBB Code Tags to make reading your code easier.]
[ August 17, 2004: Message edited by: Gregg Bolinger ]
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Michael,

Welcome to JavaRanch!

It's hard to say what you need to do for sure as your JDBC is wrapped in the user object. If the implementation is competent (and mirrors JDBC) then you will only need to initConnection() and closeConnection() once. I assume your code is not working as you have it at the moment. You currently only have one initConnection() thus when you come to use the connection the second time it is closed (by the previous closeConnection()).

Also note that convention recommends that your peal class names (and all class names) should start with a capital P.

HTH

Jules
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

Julian is right. There is no way to know if you are doing it correctly because you haven't shown us the code that actually does the JDBC work. With that being said, if you are not using a Connection Pool within your web application you really should. Creating a connection is an expensive function and if you are doing this everytime you need a connection in your web app, things can get bogged down really quickly.

Using a Connection Pool, your app server or servlet container will create N number of connections for you that are leased when requested and then returned to the pool when your app is finished using them. It's basically a pool of open connections just sitting and waiting for you.


GenRocket - Experts at Building Test Data
Michael Hoang
Greenhorn

Joined: Aug 17, 2004
Posts: 5
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;
}
}
Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15299
    
    6

I gotta be honest. I hardly looked at your code. You didn't use the UBB Code tags that I suggested in your first post so not only was it really hard to read, but I don't like looking at that much code just to see if you are "doing it the right way". Especially since you aren't having problems, it's just a matter of proper coding.

I did manage to scrape out:



Which leads me to believe you are indeed using a Connection Pool which is always a good idea. Beyond that, there is just too much code their to filter through for us wee little volenteers.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Datasource Connection
 
Similar Threads
Interesting problem in Forwarding request dispatcher
InstantiationException
Servlet - Urgent help required.
drop down list
Pass session from JSP to servlet?