Example #1
0
  /**
   * 페이지 쿼리를 쓸수 없을경우 쓴다.
   *
   * @param sql
   * @param pageNum
   * @param pageSize
   * @return
   * @throws SQLException
   */
  public ReportEntity select_page(String sql, int pageNum, int pageSize) throws SQLException {

    if (pageNum < 1) {
      KJFLog.log("Invalid PageNum");
      throw new SQLException("Invalid PageNum");
    }

    KJFLog.sql(sql);

    ReportEntity entity = null;
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
      conn = this.getConnection();
      ps = conn.prepareStatement(sql);

      rs = ps.executeQuery();

      ResultSetMetaData rsmd = rs.getMetaData();

      int columnCnt = rsmd.getColumnCount();
      int typeList[] = new int[columnCnt];

      entity = new ReportEntity();

      for (int col = 1; col <= columnCnt; col++) {
        entity.addName(rsmd.getColumnLabel(col));
        entity.addType(rsmd.getColumnTypeName(col));
        typeList[col - 1] = rsmd.getColumnType(col);
      }

      // 시작 및 끝 RowNO
      int startRowNO = (pageNum * pageSize) - (pageSize - 1);
      int endRowNO = pageNum * pageSize;

      while (rs.next()) {

        if (rs.getRow() >= startRowNO) {
          List row = new ArrayList();
          for (int col = 1; col <= columnCnt; col++) {
            if (typeList[col - 1] == java.sql.Types.DATE) row.add(KJFDate.date(rs.getDate(col)));
            else if (typeList[col - 1] == java.sql.Types.BLOB) row.add(rs.getBytes(col));
            else row.add(rs.getString(col));
          }

          entity.addRow(row);
        }
        if (rs.getRow() == endRowNO) {
          break;
        }
      }

    } catch (SQLException e) {
      KJFLog.log("ReportDAO.select error", e.toString());
      throw e;

    } finally {
      if (rs != null) rs.close();
      if (ps != null) ps.close();

      this.release(conn);
    }

    return entity;
  }
Example #2
0
  /**
   * Oralce인 경우는 이것을 사용해야 한다.
   *
   * @param sql String
   * @param pageNum int
   * @param pageSize int
   * @return ReportEntity
   * @throws SQLException
   */
  public ReportEntity select_page_query(String sql, int pageNum, int pageSize) throws SQLException {

    KJFLog.sql(sql);

    ReportEntity entity = null;

    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
      conn = this.getConnection();
      ps = conn.prepareStatement(getPageQuery(sql, pageNum, pageSize));

      if (!inputArg.isEmpty()) {
        for (int i = 1; i <= inputArg.size(); i++) {
          if (inputArg.get(new Integer(i)) instanceof Double)
            ps.setDouble(i, ((Double) inputArg.get(new Integer(i))).doubleValue());
          else if (inputArg.get(new Integer(i)) instanceof Float)
            ps.setFloat(i, ((Float) inputArg.get(new Integer(i))).floatValue());
          else if (inputArg.get(new Integer(i)) instanceof Integer)
            ps.setInt(i, ((Integer) inputArg.get(new Integer(i))).intValue());
          else if (inputArg.get(new Integer(i)) instanceof Long)
            ps.setLong(i, ((Long) inputArg.get(new Integer(i))).longValue());
          else if (inputArg.get(new Integer(i)) instanceof java.sql.Date)
            ps.setDate(i, (java.sql.Date) inputArg.get(new Integer(i)));
          else ps.setString(i, (String) inputArg.get(new Integer(i)));
        }
      }

      rs = ps.executeQuery();

      ResultSetMetaData rsmd = rs.getMetaData();

      int columnCnt = rsmd.getColumnCount();
      int typeList[] = new int[columnCnt];

      entity = new ReportEntity();

      for (int col = 1; col <= columnCnt; col++) {
        entity.addName(rsmd.getColumnLabel(col));
        entity.addType(rsmd.getColumnTypeName(col));
        typeList[col - 1] = rsmd.getColumnType(col);
      }

      /////////////////////////////////////////////////////////////////
      //  해당 페이지로 이동한다.
      //    Orace에서는 필요 없다.
      /////////////////////////////////////////////////////////////////

      int cnt = 0;
      while (rs.next()) {
        /////////////////////////////////////////////////////////////////
        // 지정한 Page Size만큼만 읽어 온다.
        // Oracle에서는 핑요없다.
        /////////////////////////////////////////////////////////////////
        List row = new ArrayList();
        for (int col = 1; col <= columnCnt; col++) {
          if (typeList[col - 1] == java.sql.Types.DATE) row.add(KJFDate.date(rs.getDate(col)));
          else if (typeList[col - 1] == java.sql.Types.BLOB) row.add(rs.getBlob(col));
          else row.add(rs.getString(col));
        }
        entity.addRow(row);
      }

    } catch (SQLException e) {
      KJFLog.log("ReportDAO.select error", e.toString());
      throw e;

    } finally {
      if (rs != null) rs.close();
      if (ps != null) ps.close();
      this.release(conn);
    }

    return entity;
  }
Example #3
0
  /**
   * 쿼리를 수행한다
   *
   * @param sql String
   * @return ReportEntity
   * @throws SQLException
   */
  public ReportEntity select(String sql) throws SQLException {

    KJFLog.sql(sql);

    ReportEntity entity = null;

    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
      conn = this.getConnection();
      ps = conn.prepareStatement(sql);

      if (!inputArg.isEmpty()) {
        for (int i = 1; i <= inputArg.size(); i++) {
          if (inputArg.get(new Integer(i)) instanceof Double)
            ps.setDouble(i, ((Double) inputArg.get(new Integer(i))).doubleValue());
          else if (inputArg.get(new Integer(i)) instanceof Float)
            ps.setFloat(i, ((Float) inputArg.get(new Integer(i))).floatValue());
          else if (inputArg.get(new Integer(i)) instanceof Integer)
            ps.setInt(i, ((Integer) inputArg.get(new Integer(i))).intValue());
          else if (inputArg.get(new Integer(i)) instanceof Long)
            ps.setLong(i, ((Long) inputArg.get(new Integer(i))).longValue());
          else if (inputArg.get(new Integer(i)) instanceof java.sql.Date)
            ps.setDate(i, (java.sql.Date) inputArg.get(new Integer(i)));
          else ps.setString(i, (String) inputArg.get(new Integer(i)));
        }
      }

      rs = ps.executeQuery();

      ResultSetMetaData rsmd = rs.getMetaData();
      int columnCnt = rsmd.getColumnCount();
      int typeList[] = new int[columnCnt];

      entity = new ReportEntity();

      for (int col = 1; col <= columnCnt; col++) {
        entity.addName(rsmd.getColumnLabel(col));
        entity.addType(rsmd.getColumnTypeName(col));
        typeList[col - 1] = rsmd.getColumnType(col);
      }

      while (rs.next()) {
        List row = new ArrayList();
        for (int col = 1; col <= columnCnt; col++) {
          if (typeList[col - 1] == java.sql.Types.DATE) {
            row.add(KJFDate.date(rs.getDate(col)));
          } else if (typeList[col - 1] == java.sql.Types.BLOB) {
            row.add(rs.getBlob(col));
          } else {
            row.add(rs.getString(col));
          }
        }
        entity.addRow(row);
      }

    } catch (SQLException e) {
      System.out.println(e);
      throw e;

    } finally {

      if (rs != null) rs.close();
      if (ps != null) ps.close();

      this.release(conn);
    }

    return entity;
  }