public List<CrawlResource> getAllCrawlResources( Long channelId, String title, String status, int start, int end) { List<CrawlResource> list = null; Connection conn = null; PreparedStatement pst = null; ResultSet rs = null; String sql = ""; try { conn = JavaOracle.getConn(); if (channelId != null) { title = (title == null || "".equals(title)) ? " 1=1 " : " a.res_title like '%" + title + "%' "; if ("-1".equals(status)) { status = " 1=1 "; } else { status = " a.res_status = " + status; } sql = " select * from (select * from (select a.res_id,a.channel_id,a.res_title,a.res_link,to_char(a.create_time,'yy/mm/dd/hh24:mm:ss') create_time,decode(a.res_status,'0','未审','1','已审') res_status, dbms_lob.getlength(a.res_text) res_text_len, nvl(a.res_img_path_set,'') res_img_path_set, rownum row_num from twap_public_crawl_resource a where exists(select 1 from twap_public_channel t where a.channel_id = t.channel_id start with t.channel_id = ? connect by prior t.channel_id = t.channel_pid) and " + title + " and " + status + " and rownum <= ? order by a.res_id desc) b) c where c.row_num >= ? order by c.res_id desc "; System.out.println(sql); pst = conn.prepareStatement(sql); pst.setLong(1, channelId); pst.setInt(2, end); pst.setInt(3, start); } else { sql = " select a.res_id,a.channel_id,a.res_title,a.res_link,to_char(a.create_time,'yy/mm/dd/hh24:mm:ss') create_time from twap_public_crawl_resource a order by a.res_id desc "; pst = conn.prepareStatement(sql); } rs = pst.executeQuery(); list = new ArrayList<CrawlResource>(); CrawlResource resource = null; while (rs.next()) { resource = new CrawlResource(); resource.setChannelId(rs.getLong("channel_id")); resource.setCreateTime(rs.getString("create_time")); resource.setLink(rs.getString("res_link")); resource.setResId(rs.getLong("res_id")); resource.setStatus(rs.getString("res_status")); resource.setTitle(rs.getString("res_title")); // TODO 临时处理 resource.setText(rs.getObject("res_text_len").toString()); String picStr = rs.getString("res_img_path_set"); if ("".equals(picStr) || picStr == null) { resource.setPics("0"); } else { resource.setPics(String.valueOf(picStr.split(",").length)); } list.add(resource); } } catch (Exception e) { e.printStackTrace(); } finally { close(conn, pst, rs); } return list; }