[Java] SQL Mapper 만들기

SQL Mapper 만들기

• 보통은 이렇게 구현해서 사용하지는 않는다.
• 해당 구현은 특수 케이스 였고 트랜잭션을 제거해야 됐기 때문에 
• 직접 구현해서 사용을 했다.

 

@Service
public class SQLExecutor {
  private final DataSource dataSource;

  public SQLExecutor(
  @Qualifier("sqlDataSource")
  DataSource dataSource) {
    this.dataSource = dataSource;
  }

  public List<JSONObject> excute(String query) {
    List<JSONObject> rows = new ArrayList<>();
    try {
      Connection con = null;
      con = this.dataSource.getConnection();
      Statement statement = con.createStatement();
      ResultSet rs = statement.executeQuery(query);

      ResultSetMetaData metaData = rs.getMetaData();
      int sizeOfColumn = metaData.getColumnCount();
      while (rs.next()) {
        JSONObject jsonObject = new JSONObject();
        for (var indexOfcolumn = 0; indexOfcolumn < sizeOfColumn; indexOfcolumn++) {
          String column = metaData.getColumnName(indexOfcolumn + 1);
          int columnType = metaData.getColumnType(indexOfcolumn + 1);
          addColumnToJsonObject(columnType, rs, column, jsonObject);
        }
        rows.add(jsonObject);
      }

      con.close();
      return rows;
    } catch (SQLTimeoutException e) {
      throw new InvalidInputException(e);
    } catch (SQLDataException e) {
      throw new ExternalApiException(e);
    } catch (SQLClientInfoException e) {
      throw new ExternalApiException(e);
    } catch (SQLException e) {
      throw new ExternalApiException(e);
    }
  }


  public <T> List<T> listResult(String query, Class<T> clazz) {
    try {
      List<T> resultList = new ArrayList<>();
      List<JSONObject> excute = excute(query);
      for(JSONObject jsonObject: excute) {
        Object o objectMapper.readValue(jsonObject, clazz);
        resultList.add((T)o);
      }
      return resultList;
    } catch (ExternalApiException e) {
      throw e;
    } catch (InvalidInputException e) {
      return new JSONArray();
    }
  }

  public <T> T uniqueResult(String query, Class<T> clazz) {
    List<T> ts = listResult(query, clazz);
    if (CollectionUtils.isEmpty(ts)) {
      return (T) new Object();
    }
    return ts.get(0);
  }


  public static JSONObject addColumnToJsonObject(int type, ResultSet rs, String columnName, JSONObject jsonObject)
      throws SQLException {
    if (type == Types.BOOLEAN) {
      boolean aBoolean = rs.getBoolean(columnName);
      jsonObject.put(columnName, aBoolean);
    } else if (type == Types.TINYINT) {
      byte aByte = rs.getByte(columnName);
      jsonObject.put(columnName, aByte);
    } else if (type == Types.SMALLINT) {
      short aShort = rs.getShort(columnName);
      jsonObject.put(columnName, aShort);
    } else if (type == Types.INTEGER) {
      int anInt = rs.getInt(columnName);
      jsonObject.put(columnName, anInt);
    } else if (type == Types.BIGINT) {
      long aLong = rs.getLong(columnName);
      jsonObject.put(columnName, aLong);
    } else if (type == Types.FLOAT) {
      float aFloat = rs.getFloat(columnName);
      jsonObject.put(columnName, aFloat);
    } else if (type == Types.DOUBLE) {
      BigDecimal bigDecimal = rs.getBigDecimal(columnName);
      jsonObject.put(columnName, bigDecimal);
    } else if (type == Types.NUMERIC) {
      BigInteger bigInteger = rs.getBigDecimal(columnName).toBigInteger();
      jsonObject.put(columnName, bigInteger);
    } else if (type == Types.DECIMAL) {
      BigDecimal bigDecimal = rs.getBigDecimal(columnName);
      jsonObject.put(columnName, bigDecimal);
    } else if (type == Types.DATE) {
      Date date = rs.getDate(columnName);
      jsonObject.put(columnName, date);
    } else if (type == Types.TIME) {
      Time time = rs.getTime(columnName);
      jsonObject.put(columnName, time);
    } else if (type == Types.TIMESTAMP) {
      Timestamp timestamp = rs.getTimestamp(columnName);
      jsonObject.put(columnName, timestamp.toLocalDateTime());
    } else if (type == Types.TIMESTAMP_WITH_TIMEZONE) {
      Timestamp timestamp = rs.getTimestamp(columnName);
      jsonObject.put(columnName, timestamp.toLocalDateTime().atZone(ZoneId.of("UTC")));
    } else if (type == Types.VARCHAR) {
      String string = rs.getString(columnName);
      jsonObject.put(columnName, string);
    } else {
      String string = rs.getString(columnName);
      jsonObject.put(columnName, string);
    }
    return jsonObject;
  }
}