| Code with Finding: |
class SocialNetworkDatabaseRegions {
/**
* Gets a list of regions that the user has access to.
* If the user is an admin, the user can see all regions.
* Also returns, with each region, the most recently posted posts.
* Assumes boardName is not null and is a valid board, and that the user
* has already been authorized to access this board.
* */
public static String getRegionListRecentPosts(Connection conn, String username, String boardName){
String regionsAndPosts = "print Regions:;";
PreparedStatement regionPstmt = null;
String fetchRegionsMember = "SELECT rname, privilege FROM " +
boardName + ".regionprivileges " +
"WHERE username = ?";
Statement regionStmt = null;
String fetchRegionsAdmin = "SELECT * FROM " + boardName + ".regions";
PreparedStatement recentPostsPstmt = null;
String fetchRecentPosts = "SELECT rname, pid, P.postedBy, P.datePosted, MAX(P.dateLastUpdated), MAX(R.dateReplied)" +
"FROM " + boardName + ".posts AS P LEFT OUTER JOIN " +
boardName + ".replies AS R USING (rname, pid) " +
"WHERE rname = ? GROUP BY pid ORDER BY P.dateLastUpdated DESC";
ResultSet regionsResults = null;
ResultSet recentPostsResults = null;
boolean sqlex = false;
try {
String role = DatabaseAdmin.getUserRole(conn, username);
if (role.equals("member")) {
regionPstmt = conn.prepareStatement(fetchRegionsMember);
regionPstmt.setString(1, username);
regionsResults = regionPstmt.executeQuery();
}
else if (!role.equals("")) { //user is an admin
regionStmt = conn.createStatement();
regionsResults = regionStmt.executeQuery(fetchRegionsAdmin);
}
else { //error occurred while acquiring role
return "print Error: Database Error while querying viewable regions. Contact an admin.;";
}
recentPostsPstmt = conn.prepareStatement(fetchRecentPosts);
while (regionsResults.next()) {
/*For each region, fetch the two most recent posts*/
if (role.equals("member")) {
regionsAndPosts += "print \t" +
(regionsResults.getString("privilege").equals("viewpost") ? specialStrPostable : "") +
regionsResults.getString("rname") + ";";
}
else {
regionsAndPosts += "print \t" + specialStrPostable + regionsResults.getString("rname") + ";";
}
recentPostsPstmt.setString(1, regionsResults.getString("rname"));
recentPostsResults = recentPostsPstmt.executeQuery();
if (recentPostsResults.next()) {
if (recentPostsResults.getTimestamp("P.datePosted") != null) {
regionsAndPosts += "print \t\tMost Recent Activity | Post#" + recentPostsResults.getInt("pid") +
"[" + recentPostsResults.getString("P.postedBy") + "];";
if (recentPostsResults.getTimestamp("MAX(R.dateReplied)") != null) {
regionsAndPosts += "print \t\t " +
"Most Recent Reply at " +
recentPostsResults.getTimestamp("MAX(R.dateReplied)").toString() + ";";
}
}
else { //LEFT INNER JOIN can return a null row as an answer.
regionsAndPosts += "print \t\tNo Posts for this Region;";
}
}
else {
regionsAndPosts += "print \t\tNo Posts for this Region;";
}
}
}
catch (SQLException e) {
e.printStackTrace();
sqlex = true;
}
finally {
DBManager.closePreparedStatement(regionPstmt);
DBManager.closeStatement(regionStmt);
DBManager.closePreparedStatement(recentPostsPstmt);
DBManager.closeResultSet(regionsResults);
DBManager.closeResultSet(recentPostsResults);
}
if (regionsAndPosts.equals("print Regions:;") && !sqlex) { //boardName assumed to be valid.
return "print No Regions for this Board";
}
else if (sqlex) {
return "print Error: Database Error while querying viewable regions. Contact an admin.;";
}
else return regionsAndPosts;
}
}
|
