please help in Jdbc connectivity

[Usha G]

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.

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;
	}

}

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.

[dickersonka]

you probably want to move this to DBUtil and return a resultset from it

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

[Usha G]

Thank you so much for the clarification! This is very helpful.

Usha.