public List<DataTransferObject> get(DataTransferObject record, List<String> fieldNames)
throws SQLException, Exception {
checkConnection();
// Connection conn = DButil.getConnection();
SQLPlaceholder placeholder = getValuesFromDTO(record, fieldNames, false);
String sql = "SELECT * FROM " + TABLE_NAME;
if (!placeholder.createWhere().equals("")) {
sql += " WHERE " + placeholder.createWhere();
}
List<DataTransferObject> records = new ArrayList<DataTransferObject>();
PreparedStatement stmt = null;
ResultSet result = null;
try {
stmt = conn.prepareStatement(sql);
placeholder.replacePlaceholders(stmt);
prglog.debug("[PRG] selection SQL: " + stmt.toString());
lastSQL = stmt.toString();
result = stmt.executeQuery();
result.last();
int rowcount = result.getRow();
prglog.debug("[PRG] number of rows: " + rowcount);
result.beforeFirst();
while (result.next()) {
records.add(result2record(result, record.getClass().newInstance()));
}
} finally {
if (result != null) result.close();
if (stmt != null) stmt.close();
}
// DButil.closeConnection(conn);
return records;
}
public int updateByTrackingId(DataTransferObject newRecord, int trackingId)
throws SQLException, Exception {
checkConnection();
// Connection conn = DButil.getConnection();
SQLPlaceholder placeholder = getValuesFromDTO(newRecord);
String sql =
"UPDATE "
+ TABLE_NAME
+ " SET "
+ placeholder.createUpdate()
+ " WHERE tracking_id = '"
+ trackingId
+ "'";
PreparedStatement stmt = null;
int rowNumber;
try {
stmt = conn.prepareStatement(sql);
placeholder.replacePlaceholders(stmt);
prglog.debug("[PRG] " + stmt.toString());
lastSQL = stmt.toString();
rowNumber = execute(stmt);
} finally {
stmt.close();
}
// DButil.closeConnection(conn);
return rowNumber;
}
public int updateByExternal(DataTransferObject newRecord, RecordDTO originalRecord)
throws SQLException, Exception {
checkConnection();
// Connection conn = DButil.getConnection();
SQLPlaceholder placeholder = getValuesFromDTO(newRecord);
String sql =
"UPDATE "
+ TABLE_NAME
+ " SET "
+ placeholder.createUpdate()
+ " WHERE external_id = '"
+ originalRecord.getExternalId()
+ "'"
+ " AND repository_code = '"
+ originalRecord.getRepositoryCode()
+ "'"
+ " AND record_type = "
+ originalRecord.getRecordType();
PreparedStatement stmt = null;
int rowNumber;
try {
stmt = conn.prepareStatement(sql);
placeholder.replacePlaceholders(stmt);
prglog.debug("[PRG] " + stmt.toString());
lastSQL = stmt.toString();
rowNumber = execute(stmt);
} finally {
stmt.close();
}
// DButil.closeConnection(conn);
return rowNumber;
}
public int update(DataTransferObject newRecord, DataTransferObject oldRecord)
throws SQLException, Exception {
checkConnection();
SQLPlaceholder placeholder = getValuesFromDTO(newRecord);
SQLPlaceholder placeholderOld = getValuesFromDTO(oldRecord);
String sql =
"UPDATE "
+ TABLE_NAME
+ " SET "
+ placeholder.createUpdate()
+ " WHERE "
+ placeholderOld.createWhere();
PreparedStatement stmt = null;
int rowNumber;
try {
stmt = conn.prepareStatement(sql);
placeholder.replacePlaceholders(stmt);
placeholderOld.replacePlaceholders(stmt, placeholder.getDataValues().size() + 1);
prglog.debug("[PRG] update SQL: " + stmt.toString());
lastSQL = stmt.toString();
rowNumber = execute(stmt);
} finally {
stmt.close();
}
// DButil.closeConnection(conn);
return rowNumber;
}
public List<Integer> insert(DataTransferObject record) throws SQLException, Exception {
checkConnection();
// Connection conn = DButil.getConnection();
SQLPlaceholder placeholder = getValuesFromDTO(record);
String sql = "INSERT INTO " + TABLE_NAME + placeholder.createInsert();
List<Integer> insertedIds = new ArrayList<Integer>();
PreparedStatement stmt = null;
ResultSet rs = null;
int execrs = -1;
try {
stmt = conn.prepareStatement(sql);
placeholder.replacePlaceholders(stmt);
prglog.debug("[PRG] insertion SQL: " + stmt.toString());
lastSQL = stmt.toString();
execrs = executeUpdate(stmt);
// prglog.info("Value of the result of execute Update " + execrs);
if (execrs == -1) {
// prglog.debug("Inside the if loop again doing the execute update");
stmt = conn.prepareStatement(sql);
placeholder.replacePlaceholders(stmt);
prglog.debug("[PRG] After resetting connection, insertion SQL: " + stmt.toString());
stmt.executeUpdate();
}
rs = stmt.getGeneratedKeys();
ResultSetMetaData rsMetaData;
int columnCount;
while (rs.next()) {
rsMetaData = rs.getMetaData();
columnCount = rsMetaData.getColumnCount();
for (int i = 1; i <= columnCount; i++) {
insertedIds.add((Integer) rs.getInt(i));
}
}
} finally {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
}
// DButil.closeConnection(conn);
return insertedIds; // execute(stmt);
}
/**
* Delete the specified record(s).
*
* @param record
* @return The number of deleted records
* @throws SQLException
* @throws Exception
*/
public int delete(DataTransferObject record) throws SQLException, Exception {
checkConnection();
// Connection conn = DButil.getConnection();
SQLPlaceholder placeholder = getValuesFromDTO(record, null, true);
String deleteSQL = "DELETE FROM " + TABLE_NAME + " WHERE " + placeholder.createWhere();
PreparedStatement stmt = null;
int rowsNumber;
try {
stmt = conn.prepareStatement(deleteSQL);
placeholder.replacePlaceholders(stmt);
prglog.debug("[PRG] deletion SQL: " + stmt.toString());
lastSQL = stmt.toString();
rowsNumber = execute(stmt);
} finally {
stmt.close();
}
// DButil.closeConnection(conn);
return rowsNumber;
}