@Override
public Answer update(BuildRevisionBatch buildRevisionBatch) {
MessageEvent msg = null;
final String query =
"UPDATE buildrevisionbatch SET system = ?, Country = ?, Environment = ?, Build = ?, Revision = ?, "
+ "Batch = ? WHERE id = ? ";
// Debug message on SQL.
if (LOG.isDebugEnabled()) {
LOG.debug("SQL : " + query);
}
Connection connection = this.databaseSpring.connect();
try {
PreparedStatement preStat = connection.prepareStatement(query);
try {
preStat.setString(1, buildRevisionBatch.getSystem());
preStat.setString(2, buildRevisionBatch.getCountry());
preStat.setString(3, buildRevisionBatch.getEnvironment());
preStat.setString(4, buildRevisionBatch.getBuild());
preStat.setString(5, buildRevisionBatch.getRevision());
preStat.setString(6, buildRevisionBatch.getBatch());
preStat.setLong(7, buildRevisionBatch.getId());
preStat.executeUpdate();
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
msg.setDescription(
msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "UPDATE"));
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
preStat.close();
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException exception) {
LOG.error("Unable to close connection : " + exception.toString());
}
}
return new Answer(msg);
}
@Override
public Answer update(SoapLibrary object) {
MessageEvent msg = null;
final String query =
"UPDATE soaplibrary sol SET Type = ?, `ServicePath` = ?, `Method` = ?, Envelope = ?, ParsingAnswer = ?, Description = ? WHERE Name = ?";
// Debug message on SQL.
if (LOG.isDebugEnabled()) {
LOG.debug("SQL : " + query);
}
Connection connection = this.databaseSpring.connect();
try {
PreparedStatement preStat = connection.prepareStatement(query);
try {
preStat.setString(1, object.getType());
preStat.setString(2, object.getServicePath());
preStat.setString(3, object.getMethod());
preStat.setString(4, object.getEnvelope());
preStat.setString(5, object.getParsingAnswer());
preStat.setString(6, object.getDescription());
preStat.setString(7, object.getName());
preStat.executeUpdate();
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
msg.setDescription(
msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "UPDATE"));
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
preStat.close();
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException exception) {
LOG.warn("Unable to close connection : " + exception.toString());
}
}
return new Answer(msg);
}
@Override
public Answer delete(SoapLibrary object) {
MessageEvent msg = null;
final String query = "DELETE FROM soaplibrary WHERE name = ? ";
// Debug message on SQL.
if (LOG.isDebugEnabled()) {
LOG.debug("SQL : " + query);
}
Connection connection = this.databaseSpring.connect();
try {
PreparedStatement preStat = connection.prepareStatement(query);
try {
preStat.setString(1, object.getName());
preStat.executeUpdate();
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
msg.setDescription(
msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "DELETE"));
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
preStat.close();
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException exception) {
LOG.warn("Unable to close connection : " + exception.toString());
}
}
return new Answer(msg);
}
@Override
public AnswerItem readByKey(String key) {
AnswerItem a = new AnswerItem();
StringBuilder query = new StringBuilder();
SoapLibrary p = new SoapLibrary();
MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
query.append("SELECT * FROM soaplibrary sol WHERE Name = ?");
Connection connection = this.databaseSpring.connect();
try {
PreparedStatement preStat = connection.prepareStatement(query.toString());
preStat.setString(1, key);
ResultSet resultSet = preStat.executeQuery();
// gets the data
while (resultSet.next()) {
p = this.loadFromResultSet(resultSet);
}
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
msg.setDescription(
msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));
} catch (SQLException e) {
LOG.error("Unable to execute query : " + e.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", e.toString()));
} finally {
try {
if (!this.databaseSpring.isOnTransaction()) {
if (connection != null) {
connection.close();
}
}
} catch (SQLException exception) {
LOG.warn("Unable to close connection : " + exception.toString());
}
}
a.setResultMessage(msg);
a.setItem(p);
return a;
}
/**
* Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
final void processRequest(final HttpServletRequest request, final HttpServletResponse response)
throws ServletException, IOException, CerberusException, JSONException {
JSONObject jsonResponse = new JSONObject();
ApplicationContext appContext =
WebApplicationContextUtils.getWebApplicationContext(this.getServletContext());
Answer ans = new Answer();
MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
ans.setResultMessage(msg);
response.setContentType("text/html;charset=UTF-8");
String charset = request.getCharacterEncoding();
// Parameter that are already controled by GUI (no need to decode) --> We SECURE them
// Parameter that needs to be secured --> We SECURE+DECODE them
String testbattery =
ParameterParserUtil.parseStringParamAndDecodeAndSanitize(
URLDecoder.decode(request.getParameter("testBattery"), "UTF-8"), null, charset);
String description =
ParameterParserUtil.parseStringParamAndDecodeAndSanitize(
URLDecoder.decode(request.getParameter("description"), "UTF-8"), null, charset);
// Parameter that we cannot secure as we need the html --> We DECODE them
String batteryContent =
ParameterParserUtil.parseStringParam(request.getParameter("batteryContent"), null);
Answer finalAnswer = new Answer();
if (StringUtil.isNullOrEmpty(testbattery)) {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_EXPECTED);
msg.setDescription(
msg.getDescription()
.replace("%ITEM%", "Battery")
.replace("%OPERATION%", "Create")
.replace("%REASON%", "Battery name is missing!"));
finalAnswer.setResultMessage(msg);
} else {
ITestBatteryService testBatteryService = appContext.getBean(ITestBatteryService.class);
IFactoryTestBattery factoryTestBattery = appContext.getBean(IFactoryTestBattery.class);
TestBattery te = factoryTestBattery.create(0, testbattery, description);
finalAnswer = testBatteryService.create(te);
if (finalAnswer.isCodeEquals(MessageEventEnum.DATA_OPERATION_OK.getCode())) {
/** Adding Log entry. */
ILogEventService logEventService = appContext.getBean(LogEventService.class);
logEventService.createPrivateCalls(
"/CreateTestBattery", "CREATE", "Create Test Battery : " + testbattery, request);
if (batteryContent != null) {
JSONArray batteriesContent = new JSONArray(batteryContent);
ITestBatteryContentService testBatteryContentService =
appContext.getBean(ITestBatteryContentService.class);
IFactoryTestBatteryContent factoryTestBatteryContent =
appContext.getBean(IFactoryTestBatteryContent.class);
ArrayList<TestBatteryContent> arr = new ArrayList<>();
for (int i = 0; i < batteriesContent.length(); i++) {
JSONObject bat = batteriesContent.getJSONObject(i);
TestBatteryContent co =
factoryTestBatteryContent.create(
0, bat.getString("test"), bat.getString("testCase"), testbattery);
arr.add(co);
}
finalAnswer =
testBatteryContentService.compareListAndUpdateInsertDeleteElements(
te.getTestbattery(), arr);
if (finalAnswer.isCodeEquals(MessageEventEnum.DATA_OPERATION_OK.getCode())) {
/** Adding Log entry. */
logEventService.createPrivateCalls(
"/CreateTestBattery",
"Create",
"Create Test battery : " + te.getTestbattery(),
request);
}
}
}
}
/** Formating and returning the json result. */
jsonResponse.put("messageType", finalAnswer.getResultMessage().getMessage().getCodeString());
jsonResponse.put("message", finalAnswer.getResultMessage().getDescription());
response.getWriter().print(jsonResponse);
response.getWriter().flush();
}
@Override
public Answer create(SoapLibrary object) {
MessageEvent msg = null;
StringBuilder query = new StringBuilder();
query.append(
"INSERT INTO soaplibrary (`Name`, `Type`, `ServicePath`, `Method`, `Envelope`, `ParsingAnswer`, `Description`) ");
query.append("VALUES (?,?,?,?,?,?,?)");
// Debug message on SQL.
if (LOG.isDebugEnabled()) {
LOG.debug("SQL : " + query.toString());
}
Connection connection = this.databaseSpring.connect();
try {
PreparedStatement preStat = connection.prepareStatement(query.toString());
try {
preStat.setString(1, object.getName());
preStat.setString(2, object.getType());
preStat.setString(3, object.getServicePath());
preStat.setString(4, object.getMethod());
preStat.setString(5, object.getEnvelope());
preStat.setString(6, object.getParsingAnswer());
preStat.setString(7, object.getDescription());
preStat.executeUpdate();
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
msg.setDescription(
msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "INSERT"));
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
if (exception
.getSQLState()
.equals(SQL_DUPLICATED_CODE)) { // 23000 is the sql state for duplicate entries
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_DUPLICATE);
msg.setDescription(
msg.getDescription()
.replace("%ITEM%", OBJECT_NAME)
.replace("%OPERATION%", "INSERT")
.replace("%REASON%", exception.toString()));
} else {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
}
} finally {
preStat.close();
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException exception) {
LOG.error("Unable to close connection : " + exception.toString());
}
}
return new Answer(msg);
}
@Override
public AnswerList readDistinctValuesByCriteria(
String searchTerm, Map<String, List<String>> individualSearch, String columnName) {
AnswerList answer = new AnswerList();
MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
List<String> distinctValues = new ArrayList<>();
StringBuilder searchSQL = new StringBuilder();
List<String> individalColumnSearchValues = new ArrayList<String>();
StringBuilder query = new StringBuilder();
query.append("SELECT distinct sol.");
query.append(columnName);
query.append(" as distinctValues FROM soaplibrary sol");
query.append(" where 1=1");
if (!StringUtil.isNullOrEmpty(searchTerm)) {
searchSQL.append(" and (sol.Name like ?");
searchSQL.append(" or sol.Type like ?");
searchSQL.append(" or sol.ServicePath like ?");
searchSQL.append(" or sol.Method like ?");
searchSQL.append(" or sol.ParsingAnswer like ?");
searchSQL.append(" or sol.Description like ?");
searchSQL.append(" or sol.Envelope like ?)");
}
if (individualSearch != null && !individualSearch.isEmpty()) {
searchSQL.append(" and ( 1=1 ");
for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {
searchSQL.append(" and sol.");
searchSQL.append(
SqlUtil.getInSQLClauseForPreparedStatement(entry.getKey(), entry.getValue()));
individalColumnSearchValues.addAll(entry.getValue());
}
searchSQL.append(" )");
}
query.append(searchSQL);
query.append(" group by ifnull(sol.").append(columnName).append(",'')");
query.append(" order by sol.").append(columnName).append(" asc");
// Debug message on SQL.
if (LOG.isDebugEnabled()) {
LOG.debug("SQL : " + query.toString());
}
try (Connection connection = databaseSpring.connect();
PreparedStatement preStat = connection.prepareStatement(query.toString())) {
int i = 1;
if (!StringUtil.isNullOrEmpty(searchTerm)) {
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
}
for (String individualColumnSearchValue : individalColumnSearchValues) {
preStat.setString(i++, individualColumnSearchValue);
}
ResultSet resultSet = preStat.executeQuery();
// gets the data
while (resultSet.next()) {
distinctValues.add(
resultSet.getString("distinctValues") == null
? ""
: resultSet.getString("distinctValues"));
}
// get the total number of rows
resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");
int nrTotalRows = 0;
if (resultSet != null && resultSet.next()) {
nrTotalRows = resultSet.getInt(1);
}
if (distinctValues.size()
>= MAX_ROW_SELECTED) { // Result of SQl was limited by MAX_ROW_SELECTED constrain. That
// means that we may miss some lines in the resultList.
LOG.error("Partial Result in the query.");
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);
msg.setDescription(
msg.getDescription()
.replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));
answer = new AnswerList(distinctValues, nrTotalRows);
} else if (distinctValues.size() <= 0) {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
answer = new AnswerList(distinctValues, nrTotalRows);
} else {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
msg.setDescription(
msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));
answer = new AnswerList(distinctValues, nrTotalRows);
}
} catch (Exception e) {
LOG.warn("Unable to execute query : " + e.toString());
msg =
new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED)
.resolveDescription("DESCRIPTION", e.toString());
} finally {
// We always set the result message
answer.setResultMessage(msg);
}
answer.setResultMessage(msg);
answer.setDataList(distinctValues);
return answer;
}
@Override
public AnswerList readByCriteria(
int start,
int amount,
String column,
String dir,
String searchTerm,
Map<String, List<String>> individualSearch) {
AnswerList response = new AnswerList();
MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
List<SoapLibrary> objectList = new ArrayList<SoapLibrary>();
StringBuilder searchSQL = new StringBuilder();
List<String> individalColumnSearchValues = new ArrayList<String>();
StringBuilder query = new StringBuilder();
// SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit
// clauses that
// were applied -- used for pagination p
query.append("SELECT SQL_CALC_FOUND_ROWS * FROM soaplibrary sol ");
query.append(" WHERE 1=1");
if (!StringUtil.isNullOrEmpty(searchTerm)) {
searchSQL.append(" and (sol.Name like ?");
searchSQL.append(" or sol.Type like ?");
searchSQL.append(" or sol.ServicePath like ?");
searchSQL.append(" or sol.Method like ?");
searchSQL.append(" or sol.ParsingAnswer like ?");
searchSQL.append(" or sol.Description like ?");
searchSQL.append(" or sol.Envelope like ?)");
}
if (individualSearch != null && !individualSearch.isEmpty()) {
searchSQL.append(" and ( 1=1 ");
for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {
searchSQL.append(" and ");
String key = "IFNULL(sol." + entry.getKey() + ",'')";
String q = SqlUtil.getInSQLClauseForPreparedStatement(key, entry.getValue());
if (q == null || q == "") {
q = "(sol." + entry.getKey() + " IS NULL OR " + entry.getKey() + " = '')";
}
searchSQL.append(q);
individalColumnSearchValues.addAll(entry.getValue());
}
searchSQL.append(" )");
}
query.append(searchSQL);
if (!StringUtil.isNullOrEmpty(column)) {
query.append(" order by sol.").append(column).append(" ").append(dir);
}
if ((amount <= 0) || (amount >= MAX_ROW_SELECTED)) {
query.append(" limit ").append(start).append(" , ").append(MAX_ROW_SELECTED);
} else {
query.append(" limit ").append(start).append(" , ").append(amount);
}
// Debug message on SQL.
if (LOG.isDebugEnabled()) {
LOG.debug("SQL : " + query.toString());
}
Connection connection = this.databaseSpring.connect();
try {
PreparedStatement preStat = connection.prepareStatement(query.toString());
try {
int i = 1;
if (!StringUtil.isNullOrEmpty(searchTerm)) {
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
}
for (String individualColumnSearchValue : individalColumnSearchValues) {
preStat.setString(i++, individualColumnSearchValue);
}
ResultSet resultSet = preStat.executeQuery();
try {
// gets the data
while (resultSet.next()) {
objectList.add(this.loadFromResultSet(resultSet));
}
// get the total number of rows
resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");
int nrTotalRows = 0;
if (resultSet != null && resultSet.next()) {
nrTotalRows = resultSet.getInt(1);
}
if (objectList.size()
>= MAX_ROW_SELECTED) { // Result of SQl was limited by MAX_ROW_SELECTED constrain.
// That means that we may miss some lines in the resultList.
LOG.error("Partial Result in the query.");
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);
msg.setDescription(
msg.getDescription()
.replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));
response = new AnswerList(objectList, nrTotalRows);
} else if (objectList.size() <= 0) {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
response = new AnswerList(objectList, nrTotalRows);
} else {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
msg.setDescription(
msg.getDescription()
.replace("%ITEM%", OBJECT_NAME)
.replace("%OPERATION%", "SELECT"));
response = new AnswerList(objectList, nrTotalRows);
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
if (resultSet != null) {
resultSet.close();
}
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
if (preStat != null) {
preStat.close();
}
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
try {
if (!this.databaseSpring.isOnTransaction()) {
if (connection != null) {
connection.close();
}
}
} catch (SQLException exception) {
LOG.warn("Unable to close connection : " + exception.toString());
}
}
response.setResultMessage(msg);
response.setDataList(objectList);
return response;
}
@Override
public AnswerItem readByKey(long id) {
AnswerItem ans = new AnswerItem();
BuildRevisionBatch result = null;
final String query = "SELECT * FROM buildrevisionbatch WHERE id = ?";
MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
// Debug message on SQL.
if (LOG.isDebugEnabled()) {
LOG.debug("SQL : " + query);
}
Connection connection = this.databaseSpring.connect();
try {
PreparedStatement preStat = connection.prepareStatement(query);
try {
preStat.setLong(1, id);
ResultSet resultSet = preStat.executeQuery();
try {
if (resultSet.first()) {
result = loadFromResultSet(resultSet);
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
msg.setDescription(
msg.getDescription()
.replace("%ITEM%", OBJECT_NAME)
.replace("%OPERATION%", "SELECT"));
ans.setItem(result);
} else {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
resultSet.close();
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
preStat.close();
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException exception) {
LOG.error("Unable to close connection : " + exception.toString());
}
}
// sets the message
ans.setResultMessage(msg);
return ans;
}
@Override
public Answer create(BuildRevisionBatch buildRevisionBatch) {
MessageEvent msg = null;
StringBuilder query = new StringBuilder();
query.append(
"INSERT INTO buildrevisionbatch (`system`, `Country`, `Environment`, `Build`, `Revision`, `Batch` ) ");
query.append("VALUES (?,?,?,?,?,?)");
// Debug message on SQL.
if (LOG.isDebugEnabled()) {
LOG.debug("SQL : " + query.toString());
}
Connection connection = this.databaseSpring.connect();
try {
PreparedStatement preStat = connection.prepareStatement(query.toString());
try {
preStat.setString(1, buildRevisionBatch.getSystem());
preStat.setString(2, buildRevisionBatch.getCountry());
preStat.setString(3, buildRevisionBatch.getEnvironment());
preStat.setString(4, buildRevisionBatch.getBuild());
preStat.setString(5, buildRevisionBatch.getRevision());
preStat.setString(6, buildRevisionBatch.getBatch());
preStat.executeUpdate();
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
msg.setDescription(
msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "INSERT"));
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
if (exception
.getSQLState()
.equals(SQL_DUPLICATED_CODE)) { // 23000 is the sql state for duplicate entries
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_DUPLICATE);
msg.setDescription(
msg.getDescription()
.replace("%ITEM%", OBJECT_NAME)
.replace("%OPERATION%", "INSERT")
.replace("%REASON%", exception.toString()));
} else {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
}
} finally {
preStat.close();
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException exception) {
LOG.warn("Unable to close connection : " + exception.toString());
}
}
return new Answer(msg);
}
@Override
public AnswerList readByVariousByCriteria(
String system,
String country,
String environment,
int start,
int amount,
String column,
String dir,
String searchTerm,
String individualSearch) {
AnswerList response = new AnswerList();
MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
List<BuildRevisionBatch> resultList = new ArrayList<BuildRevisionBatch>();
StringBuilder searchSQL = new StringBuilder();
StringBuilder query = new StringBuilder();
// SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit
// clauses that
// were applied -- used for pagination p
query.append("SELECT SQL_CALC_FOUND_ROWS * FROM buildrevisionbatch ");
searchSQL.append(" where 1=1 ");
if (!StringUtil.isNullOrEmpty(searchTerm)) {
searchSQL.append(" and (`id` like ?");
searchSQL.append(" or `system` like ?");
searchSQL.append(" or `Country` like ?");
searchSQL.append(" or `Environment` like ?");
searchSQL.append(" or `Build` like ?");
searchSQL.append(" or `Revision` like ?");
searchSQL.append(" or `Batch` like ?");
searchSQL.append(" or `DateBatch` like ?)");
}
if (!StringUtil.isNullOrEmpty(individualSearch)) {
searchSQL.append(" and ( ? )");
}
if (!StringUtil.isNullOrEmpty(system)) {
searchSQL.append(" and (`system` = ?)");
}
if (!StringUtil.isNullOrEmpty(country)) {
searchSQL.append(" and (`country` = ?)");
}
if (!StringUtil.isNullOrEmpty(environment)) {
searchSQL.append(" and (`environment` = ?)");
}
query.append(searchSQL);
if (!StringUtil.isNullOrEmpty(column)) {
query.append(" order by `").append(column).append("` ").append(dir);
}
if ((amount <= 0) || (amount >= MAX_ROW_SELECTED)) {
query.append(" limit ").append(start).append(" , ").append(MAX_ROW_SELECTED);
} else {
query.append(" limit ").append(start).append(" , ").append(amount);
}
// Debug message on SQL.
if (LOG.isDebugEnabled()) {
LOG.debug("SQL : " + query.toString());
}
Connection connection = this.databaseSpring.connect();
try {
PreparedStatement preStat = connection.prepareStatement(query.toString());
try {
int i = 1;
if (!Strings.isNullOrEmpty(searchTerm)) {
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
preStat.setString(i++, "%" + searchTerm + "%");
}
if (!StringUtil.isNullOrEmpty(individualSearch)) {
preStat.setString(i++, individualSearch);
}
if (!StringUtil.isNullOrEmpty(system)) {
preStat.setString(i++, system);
}
if (!StringUtil.isNullOrEmpty(country)) {
preStat.setString(i++, country);
}
if (!StringUtil.isNullOrEmpty(environment)) {
preStat.setString(i++, environment);
}
ResultSet resultSet = preStat.executeQuery();
try {
// gets the data
while (resultSet.next()) {
resultList.add(this.loadFromResultSet(resultSet));
}
// get the total number of rows
resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");
int nrTotalRows = 0;
if (resultSet != null && resultSet.next()) {
nrTotalRows = resultSet.getInt(1);
}
if (resultList.size()
>= MAX_ROW_SELECTED) { // Result of SQl was limited by MAX_ROW_SELECTED constrain.
// That means that we may miss some lines in the resultList.
LOG.error("Partial Result in the query.");
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);
msg.setDescription(
msg.getDescription()
.replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));
response = new AnswerList(resultList, nrTotalRows);
} else if (resultList.size() <= 0) {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
response = new AnswerList(resultList, nrTotalRows);
} else {
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
msg.setDescription(
msg.getDescription()
.replace("%ITEM%", OBJECT_NAME)
.replace("%OPERATION%", "SELECT"));
response = new AnswerList(resultList, nrTotalRows);
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(
msg.getDescription()
.replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));
} finally {
if (resultSet != null) {
resultSet.close();
}
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(
msg.getDescription()
.replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));
} finally {
if (preStat != null) {
preStat.close();
}
}
} catch (SQLException exception) {
LOG.error("Unable to execute query : " + exception.toString());
msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
msg.setDescription(
msg.getDescription().replace("%DESCRIPTION%", "Unable to retrieve the list of entries!"));
} finally {
try {
if (!this.databaseSpring.isOnTransaction()) {
if (connection != null) {
connection.close();
}
}
} catch (SQLException exception) {
LOG.error("Unable to close connection : " + exception.toString());
}
}
response.setResultMessage(msg);
response.setDataList(resultList);
return response;
}
