public void saveResources(List<CrawlResource> resources, String userName) {
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
String sql =
" insert into TWAP_PUBLIC_CRAWL_RESOURCE_STA(RES_ID,CHANNEL_ID,RES_TITLE,RES_LINK,RES_CONTENT) "
+ "values(SEQ_TWAP_PUBLIC_CRAWL_RESOURCE.Nextval,?,?,?,?) ";
try {
conn = JavaOracle.getConn();
conn.setAutoCommit(false);
pst = conn.prepareStatement(sql);
for (CrawlResource resource : resources) {
pst.setLong(1, resource.getChannelId());
pst.setString(2, resource.getTitle());
pst.setString(3, resource.getLink());
pst.setString(4, userName);
pst.addBatch();
}
pst.executeBatch();
conn.commit();
conn.setAutoCommit(true);
} catch (Exception e) {
e.printStackTrace();
if (conn != null) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
} finally {
close(conn, pst, rs);
}
}
public CrawlResource getCrawlResourceDetail(Long resId) {
CrawlResource resource = null;
Connection conn = null;
PreparedStatement pst = null;
ResultSet rs = null;
String sql =
" select a.res_id,a.channel_id,a.res_title,a.res_link,a.res_content,a.res_text,to_char(a.create_time,'yyyy-mm-dd hh24:mi:ss') create_time,a.res_status,a.res_img_path_set,a.res_file_path_set from twap_public_crawl_resource a where a.res_id = ? ";
try {
conn = JavaOracle.getConn();
pst = conn.prepareStatement(sql);
pst.setLong(1, resId);
rs = pst.executeQuery();
if (rs.next()) {
Clob clob = rs.getClob("res_text");
Reader inStream = clob.getCharacterStream();
char[] c = new char[(int) clob.length()];
inStream.read(c);
// data是读出并需要返回的数据，类型是String
String data = new String(new String(c).getBytes(), "GBK");
inStream.close();
resource = new CrawlResource();
resource.setChannelId(rs.getLong("channel_id"));
resource.setContent(data);
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"));
resource.setImgPathSet(rs.getString("res_img_path_set"));
resource.setFilePathSet(rs.getString("res_file_path_set"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
close(conn, pst, rs);
}
return resource;
}
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;
}