Updating database records

Its always best practice to use executeUpdate() method instead of executeQuery().

Returns either the row count for INSERT, UPDATE, or DELETE statements or 0 for SQL statements that return nothing

updateFlag =objPreparedStmt.executeUpdate();

if(updateFlag==1) objRequestDTO.setReqStatusForMsg(1);

We can make use of the returned value to set status.

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

How to handle non-nullable fields in database while insert/update through JDBC.

Suppose if we have a non-nullable field in database table and while inserting/updating through jdbc how to ensure a null value isn't passed to it?

Using a ternary operator we can check if the value that we are trying to insert is null. If yes then pass an empty string or else pass the orginal value.

objPreparedStmt.setString(7, null == objRequestDTO.getUserId() ? ABCApplicationConstants.COMMON_EMPTYSTRING : objRequestDTO.getUserId());

JDBC Useful Links

Creating a Data Source Instance and Connecting (without JNDI)

JDBC Connection Pooling

JDBC Driver Type Comparison