Filtering the groups
The Metropolitan Public Library's new rating statistics feature is a hit! However, patrons have complained that books with only one or two reviews can have misleading averages. A book with a single 5-star review appears just as highly rated as a beloved classic with hundreds of positive reviews.
The library director has asked you to enhance the system by allowing users to filter out books with insufficient reviews.
本练习是课程的一部分
Querying a PostgreSQL Database in Java
练习说明
- Complete the query to filter books with a specified number of reviews.
- Securely set the parameter only to display books with at least three reviews.
交互式实操练习
通过完成这段示例代码来试试这个练习。
public class Main {
public static void main(String[] args) throws SQLException {
HikariDataSource ds = HikariSetup.createDataSource();
String query = """
SELECT book_id, COUNT(*), AVG(rating)
FROM book_reviews
GROUP BY book_id
-- Filter books with a specified number of reviews
____ count(*) >= ?
""";
try (Connection conn = ds.getConnection();
PreparedStatement pstmt = conn.prepareStatement(query)) {
// Display books with at least three reviews
pstmt.____(____, ____);
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
System.out.printf("ID: %d, Count: %d, Average: %.0f%n",
rs.getInt("book_id"), rs.getInt("count"), rs.getDouble("avg"));
}
}
}
}
}