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