please help in Jdbc connectivity

Reply

Join Date: Nov 2008
Posts: 2
Reputation: Usha G is an unknown quantity at this point 
Solved Threads: 0
Usha G Usha G is offline Offline
Newbie Poster

please help in Jdbc connectivity

 
0
  #1
Nov 12th, 2008
Hi

I've two files for JDBC connectivity.

1)DBUtil.java - to establish DB connection using JDBC and close DB objects

2) GetEmpl.java - Uses DBUtil to connect to DB and retreives data.

[code language=java]

public class DBUtil {


private static String dataSourceName = getJNDI();
private Connection conn = null;
private PreparedStatement pstmt = null;

private static String getJNDI(){
ResourceBundle rbQuery = ResourceBundle.getBundle("MDA_Common");
String dataSourceName = rbQuery.getString("JDBC_JNDI");

return dataSourceName;
}

private Connection getConnection(){

try{
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup(dataSourceName);
conn = ds.getConnection();
}catch(Exception ex)
{
LogUtil.logger("MDALibrary","DBUtil.class","ERROR","Exception occurred during establishing connection with database."+ex.getMessage());
}
return conn;
}

public PreparedStatement getPreparedStatement(String Query)
{

try{
conn = getConnection();
pstmt = conn.prepareStatement(Query);

}catch(Exception ex)
{
LogUtil.logger("MDALibrary","DBUtil.class","ERROR","Exception occurred during calling procedure"+ex.getMessage());
ex.printStackTrace();
closePreparedStatement();

}
return pstmt;
}

public void closePreparedStatement()
{
try{
this.pstmt.close();
pstmt = null;
this.conn.close();
conn = null;
}catch(Exception e)
{
LogUtil.logger("MDALibrary","DBUtil.class","ERROR","Exception occurred during calling procedure"+e.getMessage());
e.printStackTrace();
}
}

}


public class GetPrevEmplTypeImpl {

public DataObject getTransactionDetails(DataObject transactionInput) {

LogUtil.logBO("MDADB","MDADB --> GetPrevEmplTypeImpl","INFO"," Input of GetPrevEmplTypeImpl is : --> transactionInput: ",transactionInput);

DataObject TransOutput = BOUtil.createDataObject("Transaction");
String TaleoReq = transactionInput.getString("Transaction_Type");
DBUtil db = new DBUtil();
PreparedStatement pstmt = null;
try {

String sQuery = "";

ResourceBundle rbQuery = ResourceBundle.getBundle("MDA_Queries");

sQuery = rbQuery.getString("GetPrevEmplType");
LogUtil.logger("MDADB","MDADB --> GetPrevEmplTypeImpl","INFO","Query::"+sQuery);
pstmt = db.getPreparedStatement(sQuery);
pstmt.setString(1, TaleoReq);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
TransOutput.setString("Status", rs.getString("PREV_EMPL_TYPE"));
}

} catch (Exception sqle) {

LogUtil.logger("MDADB","MDADB --> GetPrevEmplTypeImpl","ERROR","Exception occurred in GetPrevEmplTypeImpl ");
LogUtil.logger("MDADB","MDADB --> GetPrevEmplTypeImpl","ERROR","OutPut from getMessage() "+sqle.getMessage());
LogUtil.logger("MDADB","MDADB --> GetPrevEmplTypeImpl","ERROR","OutPut from getCause() "+sqle.getCause());

}
finally{
db.closePreparedStatement();
try{
pstmt.close();
pstmt=null;
LogUtil.logger("MDADB","MDADB --> GetPrevEmplTypeImpl","INFO","dbConnection closed in GetPrevEmplTypeImpl");
}catch(Exception e){

LogUtil.logger("MDADB","MDADB --> GetPrevEmplTypeImpl","ERROR","Exception occurred while closing the connection for pstmt : "+pstmt);
LogUtil.logger("MDADB","MDADB --> GetPrevEmplTypeImpl","ERROR","OutPut from getMessage() "+e.getMessage());
LogUtil.logger("MDADB","MDADB --> GetPrevEmplTypeImpl","ERROR","OutPut from getCause() "+e.getCause());

}
}
return TransOutput;
}

}

[/code]

In DBUtil.java, does closePreparedStatement() closes the preparedStatement and connection used for the GetPrevEmplTypeImpl transaction? In DBUtil.java, conn and pstmt are declared as private global variables and during closing these DBObjects in GetPrevEmplTypeImpl.java, no reference to the object used is passed to db.closePreparedStatement().

Please share the best way of handling these connections.

Thanks
Usha.
Reply With Quote Quick reply to this message  
Join Date: Aug 2008
Posts: 1,158
Reputation: dickersonka will become famous soon enough dickersonka will become famous soon enough 
Solved Threads: 136
dickersonka dickersonka is offline Offline
Veteran Poster

Re: please help in Jdbc connectivity

 
0
  #2
Nov 12th, 2008
you probably want to move this to DBUtil and return a resultset from it

  1. ResultSet rs = pstmt.executeQuery();

also unless its an abstract class with the prepared statement that you manipulate, i wouldn't try to declare in two places, either let it be passed in, or let it be constructed in DBUtil
Custom Application & Software Development
www.houseshark.net
Reply With Quote Quick reply to this message  
Join Date: Nov 2008
Posts: 2
Reputation: Usha G is an unknown quantity at this point 
Solved Threads: 0
Usha G Usha G is offline Offline
Newbie Poster

Re: please help in Jdbc connectivity

 
0
  #3
Nov 13th, 2008
Thank you so much for the clarification! This is very helpful.

Usha.
Reply With Quote Quick reply to this message  
Reply

This thread is more than three months old.
Perhaps start a new thread instead?
Message:


Thread Tools Search this Thread



About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC