gt:                 msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
context: /**
   * Cerberus Copyright (C) 2013 - 2017 cerberustesting
   * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
   *
   * This file is part of Cerberus.
   *
   * Cerberus is free software: you can redistribute it and/or modify
   * it under the terms of the GNU General Public License as published by
   * the Free Software Foundation, either version 3 of the License, or
   * (at your option) any later version.
   *
   * Cerberus is distributed in the hope that it will be useful,
   * but WITHOUT ANY WARRANTY; without even the implied warranty of
   * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   * GNU General Public License for more details.
   *
   * You should have received a copy of the GNU General Public License
   * along with Cerberus.  If not, see <http://www.gnu.org/licenses/>.
   */
  package org.cerberus.crud.dao.impl;
  
  import com.google.common.base.Strings;
  import java.sql.Connection;
  import java.sql.PreparedStatement;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.sql.Timestamp;
  import java.util.ArrayList;
  import java.util.List;
  import java.util.Map;
  
  import org.apache.logging.log4j.Logger;
  import org.apache.logging.log4j.LogManager;
  import org.cerberus.crud.dao.ICountryEnvParam_logDAO;
  import org.cerberus.crud.entity.CountryEnvParam_log;
  import org.cerberus.database.DatabaseSpring;
  import org.cerberus.engine.entity.MessageEvent;
  import org.cerberus.enums.MessageEventEnum;
  import org.cerberus.crud.factory.IFactoryCountryEnvParam_log;
  import org.cerberus.util.SqlUtil;
  import org.cerberus.util.StringUtil;
  import org.cerberus.util.answer.Answer;
  import org.cerberus.util.answer.AnswerItem;
  import org.cerberus.util.answer.AnswerList;
  import org.springframework.beans.factory.annotation.Autowired;
  import org.springframework.stereotype.Repository;
  
  /**
   * @author vertigo17
   */
  @Repository
  public class CountryEnvParam_logDAO implements ICountryEnvParam_logDAO {
  
      @Autowired
      private DatabaseSpring databaseSpring;
      @Autowired
      private IFactoryCountryEnvParam_log factoryCountryEnvParamLog;
  
      private static final Logger LOG = LogManager.getLogger(CountryEnvParam_logDAO.class);
  
      private final String OBJECT_NAME = "CountryEnvParam_log";
      private final String SQL_DUPLICATED_CODE = "23000";
      private final int MAX_ROW_SELECTED = 100000;
  
      @Override
      public AnswerItem<CountryEnvParam_log> readByKey(long id) {
          AnswerItem<CountryEnvParam_log> ans = new AnswerItem<>();
          CountryEnvParam_log result = null;
          final String query = "SELECT * FROM countryenvparam_log 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 AnswerList<CountryEnvParam_log> readByVariousByCriteria(String system, String country, String environment, String build, String revision, int start, int amount, String column, String dir, String searchTerm, Map<String, List<String>> individualSearch) {
          AnswerList<CountryEnvParam_log> response = new AnswerList<>();
          MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
          msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
          List<CountryEnvParam_log> countryEnvParamLogList = new ArrayList<>();
          StringBuilder searchSQL = new StringBuilder();
          List<String> individalColumnSearchValues = new ArrayList<>();
  
          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 countryenvparam_log ");
  
          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 `Chain` like ?");
              searchSQL.append(" or `Description` like ?");
              searchSQL.append(" or `datecre` like ?");
              searchSQL.append(" or `Creator` like ?)");
          }
          if (individualSearch != null && !individualSearch.isEmpty()) {
              searchSQL.append(" and ( 1=1 ");
              for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {
                  searchSQL.append(" and ");
                  searchSQL.append(SqlUtil.getInSQLClauseForPreparedStatement(entry.getKey(), entry.getValue()));
                  individalColumnSearchValues.addAll(entry.getValue());
              }
              searchSQL.append(" )");
          }
          if (!StringUtil.isNullOrEmpty(system)) {
              searchSQL.append(" and (`system` = ?)");
          }
          if (!StringUtil.isNullOrEmpty(country)) {
              searchSQL.append(" and (`country` = ?)");
          }
          if (!StringUtil.isNullOrEmpty(environment)) {
              searchSQL.append(" and (`environment` = ?)");
          }
          if (!StringUtil.isNullOrEmpty(build)) {
              searchSQL.append(" and (`build` = ?)");
          }
          if (!StringUtil.isNullOrEmpty(revision)) {
              searchSQL.append(" and (`revision` = ?)");
          }
          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 + "%");
                      preStat.setString(i++, "%" + searchTerm + "%");
                      preStat.setString(i++, "%" + searchTerm + "%");
                  }
                  for (String individualColumnSearchValue : individalColumnSearchValues) {
                      preStat.setString(i++, individualColumnSearchValue);
                  }
                  if (!StringUtil.isNullOrEmpty(system)) {
                      preStat.setString(i++, system);
                  }
                  if (!StringUtil.isNullOrEmpty(country)) {
                      preStat.setString(i++, country);
                  }
                  if (!StringUtil.isNullOrEmpty(environment)) {
                      preStat.setString(i++, environment);
                  }
                  if (!StringUtil.isNullOrEmpty(build)) {
                      preStat.setString(i++, build);
                  }
                  if (!StringUtil.isNullOrEmpty(revision)) {
                      preStat.setString(i++, revision);
                  }
                  ResultSet resultSet = preStat.executeQuery();
                  try {
                      //gets the data
                      while (resultSet.next()) {
                          countryEnvParamLogList.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 (countryEnvParamLogList.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<>(countryEnvParamLogList, nrTotalRows);
                      } else if (countryEnvParamLogList.size() <= 0) {
                          msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
                          response = new AnswerList<>(countryEnvParamLogList, nrTotalRows);
                      } else {
                          msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
                          msg.setDescription(msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));
                          response = new AnswerList<>(countryEnvParamLogList, 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(countryEnvParamLogList);
          return response;
      }
  
      @Override
      public AnswerList<CountryEnvParam_log> readLastChanges(String system, String country, Integer nbdays, String envGp) {
          AnswerList<CountryEnvParam_log> response = new AnswerList<>();
          MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
          msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
          List<CountryEnvParam_log> objectList = new ArrayList<>();
          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 cl.* ");
          query.append("FROM countryenvparam_log cl  ");
          query.append("JOIN invariant i on i.value=cl.Environment and i.idname='ENVIRONMENT'  ");
          query.append("WHERE cl.country = ?  ");
          query.append(" and TO_DAYS(NOW()) - TO_DAYS(cl.datecre) <= ? and cl.build != '' and `System`= ? ");
          if (!(StringUtil.isNullOrEmpty(envGp))) {
              query.append(" and i.gp1 = ? ");
          }
          query.append(" ORDER BY cl.id desc ; ");
  
          // Debug message on SQL.
          if (LOG.isDebugEnabled()) {
              LOG.debug("SQL : " + query.toString());
              LOG.debug("SQL.param : " + country);
              LOG.debug("SQL.param : " + nbdays);
              LOG.debug("SQL.param : " + system);
              LOG.debug("SQL.param : " + envGp);
          }
          Connection connection = this.databaseSpring.connect();
          try {
              PreparedStatement preStat = connection.prepareStatement(query.toString());
              try {
                  int i = 1;
                  preStat.setString(i++, country);
                  preStat.setInt(i++, nbdays);
                  preStat.setString(i++, system);
                  if (!(StringUtil.isNullOrEmpty(envGp))) {
                      preStat.setString(i++, envGp);
                  }
                  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 Answer create(CountryEnvParam_log countryEnvParamLog) {
          MessageEvent msg = null;
          StringBuilder query = new StringBuilder();
          query.append("INSERT INTO countryenvparam_log (`system`, `Country`, `Environment`, `Build`, `Revision`, `Chain`, `Description`, `Creator` ) ");
          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, countryEnvParamLog.getSystem());
                  preStat.setString(2, countryEnvParamLog.getCountry());
                  preStat.setString(3, countryEnvParamLog.getEnvironment());
                  preStat.setString(4, countryEnvParamLog.getBuild());
                  preStat.setString(5, countryEnvParamLog.getRevision());
                  preStat.setInt(6, countryEnvParamLog.getChain());
                  preStat.setString(7, countryEnvParamLog.getDescription());
                  preStat.setString(8, countryEnvParamLog.getCreator());
  
                  preStat.executeUpdate();
  