@Override
public List<Comment> getCommentList() {
List<Comment> commentList = new ArrayList<Comment>();
SqlUtilities.jbdcUtil();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection =
DriverManager.getConnection(
"jdbc:mysql://localhost:3306/fourscorepicks", "fourscorepicks", "fourscorepicks");
preparedStatement =
connection.prepareStatement(
"SELECT * FROM comments c JOIN user u ON u.id = c.user_id ORDER BY date_posted DESC");
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
Comment comment = new Comment();
comment.setDatePosted(resultSet.getTimestamp("date_posted"));
comment.setCommentText(resultSet.getString("comment_text"));
comment.setId(resultSet.getInt("id"));
User user = new User();
user.setId(resultSet.getInt("user_id"));
user.setName(resultSet.getString("name"));
user.setEmail(resultSet.getString("email"));
comment.setUser(user);
commentList.add(comment);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
SqlUtilities.closePreparedStatement(preparedStatement);
SqlUtilities.closeResultSet(resultSet);
SqlUtilities.closeConnection(connection);
}
return commentList;
}
@Override
public Comment getComment(int id) {
SqlUtilities.jbdcUtil();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Comment comment = null;
try {
connection =
DriverManager.getConnection(
"jdbc:mysql://localhost:3306/fourscorepicks", "fourscorepicks", "fourscorepicks");
String query = "SELECT * FROM comments c JOIN user u on c.user_id = u.id WHERE c.id = ?";
preparedStatement = connection.prepareStatement(query);
preparedStatement.setInt(1, id);
resultSet = preparedStatement.executeQuery();
resultSet.next();
comment = new Comment();
comment.setDatePosted(resultSet.getTimestamp("date_posted"));
comment.setCommentText(resultSet.getString("comment_text"));
comment.setId(id);
User user = new User();
user.setId(resultSet.getInt("user_id"));
user.setName(resultSet.getString("name"));
user.setEmail(resultSet.getString("email"));
comment.setUser(user);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
SqlUtilities.closePreparedStatement(preparedStatement);
SqlUtilities.closeConnection(connection);
}
return comment;
}