public static ReviewerPubCitation getReviewerPubCitationByID(int pid) {
Connection conn = null;
PreparedStatement ps = null;
ReviewerPubCitation rp = null;
List<Integer> citationIds = null;
List<Integer> referenceIds = null;
try {
conn = pool.getConnection();
String query = "SELECT id, title, authors from " + rr_pub + " where id = ?";
ps = conn.prepareStatement(query);
ps.setInt(1, pid);
ResultSet rs1 = ps.executeQuery();
ResultSet rs2 = null;
if (rs1.next()) {
rp = new ReviewerPubCitation();
rp.setPubId(rs1.getInt("id"));
rp.setTitle(rs1.getString("title"));
rp.setAuthorStr(rs1.getString("authors"));
query = "SELECT citeid from " + rr_citation + " where citedid = ?";
ps = conn.prepareStatement(query);
ps.setInt(1, rp.getPubId());
rs2 = ps.executeQuery();
if (rs2.next()) {
citationIds = new ArrayList<Integer>();
citationIds.add(rs2.getInt("citeid"));
rp.setCitationIds(citationIds);
while (rs2.next()) {
citationIds.add(rs2.getInt("citeid"));
}
rp.setCitationIds(citationIds);
}
query = "SELECT citedid from " + rr_citation + " where citeid = ?";
ps = conn.prepareStatement(query);
ps.setInt(1, rp.getPubId());
rs2 = ps.executeQuery();
if (rs2.next()) {
referenceIds = new ArrayList<Integer>();
referenceIds.add(rs2.getInt("citedid"));
while (rs2.next()) {
referenceIds.add(rs2.getInt("citedid"));
}
rp.setReferenceIds(referenceIds);
}
}
return rp;
} catch (SQLException e) {
e.printStackTrace();
} catch (Throwable e) {
e.printStackTrace();
} finally {
ConnectionPool.close(conn);
}
return null;
}
public static List<ReviewerPubCitation> getReviewerPubCitationByID(List<Integer> pids) {
Connection conn = null;
PreparedStatement ps = null;
ReviewerPubCitation rp = null;
List<Integer> citationIds = null;
List<Integer> referenceIds = null;
List<ReviewerPubCitation> rps = new ArrayList<ReviewerPubCitation>();
try {
conn = pool.getConnection();
String query = "SELECT id, title, authors from " + rr_pub + " where id in (";
for (int i = 0; i < pids.size(); i++) {
if (i < pids.size() - 1) {
query += String.valueOf(pids.get(i)) + ",";
} else {
query += String.valueOf(pids.get(i));
}
}
query += ")";
ps = conn.prepareStatement(query);
ResultSet rs1 = ps.executeQuery();
ResultSet rs2 = null;
while (rs1.next()) {
rp = new ReviewerPubCitation();
rp.setPubId(rs1.getInt("id"));
rp.setTitle(rs1.getString("title"));
rp.setAuthorStr(rs1.getString("authors"));
query = "SELECT citeid from " + rr_citation + " where citedid = ?";
ps = conn.prepareStatement(query);
ps.setInt(1, rp.getPubId());
rs2 = ps.executeQuery();
if (rs2.next()) {
citationIds = new ArrayList<Integer>();
citationIds.add(rs2.getInt("citeid"));
rp.setCitationIds(citationIds);
while (rs2.next()) {
citationIds.add(rs2.getInt("citeid"));
}
rp.setCitationIds(citationIds);
}
query = "SELECT citedid from " + rr_citation + " where citeid = ?";
ps = conn.prepareStatement(query);
ps.setInt(1, rp.getPubId());
rs2 = ps.executeQuery();
if (rs2.next()) {
referenceIds = new ArrayList<Integer>();
referenceIds.add(rs2.getInt("citedid"));
while (rs2.next()) {
referenceIds.add(rs2.getInt("citedid"));
}
rp.setReferenceIds(referenceIds);
}
rps.add(rp);
}
if (rps.size() > 0) {
return rps;
} else {
return null;
}
} catch (SQLException e) {
e.printStackTrace();
} catch (Throwable e) {
e.printStackTrace();
} finally {
ConnectionPool.close(conn);
}
return null;
}