/** * 페이지 쿼리를 쓸수 없을경우 쓴다. * * @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; }
/** * 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; }
/** * 쿼리를 수행한다 * * @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; }