JDBC Connection Code

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;


/** DBUtility is used to create
*
*/
public class DBUtility {
private final static int RETRY_ATTEMPTS = 10;
private final static String ABC_DATA_SOURCE = "ABCDSS";
// Time in milliseconds
private final static int CONNECTION_WAIT_TIME = 100;

public static Connection getConnection() throws Exception {
return getConnection(ABC_DATA_SOURCE);
}
static {
System.out.println("STARTING UP ABC");
}

public static Connection getConnection(String dataSource) throws Exception {
Connection conn = null;
boolean connOk = false;
int tryCounter = 0;
while (connOk==false && tryCounter {
try {
Context ctx = new InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup(dataSource);
conn = ds.getConnection();
try {
// test the connection - if we have a bad connection or we
// can't get one, this will throw an exception
if (conn.isReadOnly()) {
}
connOk = true;
} catch (Exception e) {
// wait(CONNECTION_WAIT_TIME);
tryCounter++;
logConnection(dataSource, tryCounter);
conn.close();

}
} catch (Exception e) {
// wait(CONNECTION_WAIT_TIME);
tryCounter++;
logConnection(dataSource, tryCounter);
if (tryCounter==RETRY_ATTEMPTS)
{
System.err.println("Cannot get connection from datasource:"+dataSource+" FINAL TRY");
throw new Exception("Cannot get connection.");
}
e.printStackTrace();
}
}
return conn;
}

public static void releaseConnection(Connection conn) {
if (conn != null) {
int tryCounter = 1;
while (tryCounter <= RETRY_ATTEMPTS) {
try {
if (conn.isClosed() == false){
conn.close();
}
break;
} catch (Exception e) {
if (tryCounter <= RETRY_ATTEMPTS)
tryCounter++;
else
System.err.println("Cannot close Connection after "
+ RETRY_ATTEMPTS + " attempts.");
}
}
}
}

public static void closeResultSet(ResultSet rs) {
if (rs != null) {
int tryCounter = 1;
while (tryCounter <= RETRY_ATTEMPTS) {
try {
// if (rs.isLast() == false){
rs.close();
// }
break;
} catch (Exception e) {
if (tryCounter <= RETRY_ATTEMPTS)
tryCounter++;
else
System.err.println("Cannot close Connection after "
+ RETRY_ATTEMPTS + " attempts.");
}
}
}
}

public static void closeStatement(Statement stmt) {
if (stmt != null) {
int tryCounter = 1;
while (tryCounter <= RETRY_ATTEMPTS) {
try {
// if (stmt.isClosed() == false){
stmt.close();
// }
break;
} catch (Exception e) {
if (tryCounter <= RETRY_ATTEMPTS)
tryCounter++;
else
System.err.println("Cannot close Connection after "
+ RETRY_ATTEMPTS + " attempts.");
}
}
}
}

private static void logConnection(String datasource, int tryCounter) {
System.err.println("Unable to obtain a connection from pool "
+ datasource + " after " + tryCounter + " attempts.");
}
}

JDBC (DAO Layer) application code

JDBC code which passes one input and fetches the other values from database table.

Connection objConnection=null;
PreparedStatement objPreparedStmt = null;
ResultSet objResultSet = null;

try {
objConnection = DBUtility.getConnection();
objPreparedStmt = objConnection.prepareStatement(SELECT PARAMETERCODE, PARAMETERVALUE, SYSTEMSTATUSKEY FROM OWN_GTEM.CONTROLPARAMETERS WHERE PARAMETERCODE=?);

objPreparedStmt.setString(1, objUserDTO.getOpeningRemarksCode());
objResultSet = objPreparedStmt.executeQuery();

while (objResultSet.next()) {
openingRemarksCode = objResultSet.getString("PARAMETERCODE");
openingRemarksValue = objResultSet.getString("PARAMETERVALUE");
openingRemarksStatus = objResultSet.getString("SYSTEMSTATUSKEY");
}
DBUtility.closeResultSet(objResultSet);
objPreparedStmt.executeUpdate();
objConnection.commit();

} catch (Exception ex) {
ABCLogger. ABCLog(CLASS_NAME,
"In updateOpeningRemarks()->SQLException:" + ex.getMessage(),ABCLogger.ERROR);
ex.printStackTrace();
} finally {
DBUtility.closeStatement(objPreparedStmt);
DBUtility.closeResultSet(objResultSet);
DBUtility.releaseConnection(objConnection);
}