Filtering the data
CityBook Libraries wants users to search for available books by publication year. Users will input a year, and the system should return all available books published in or after that year.
You will use a secure approach with prepared statements. The HikariSetup class is already configured for you.
This exercise is part of the course
Querying a PostgreSQL Database in Java
Exercise instructions
- Write the
WHEREclause to query books with publication year greater than or equal to a placeholder. - Filter based on
status, allowing only'available'books. - Set the publication year parameter for the prepared statement.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
public class Main {
public static void main(String[] args) throws SQLException {
HikariDataSource ds = HikariSetup.createDataSource();
// Complete the query to filter the data based on the requirements.
String query = "SELECT * FROM books ____ publication_year >= ____ AND status = '____'";
try (Connection conn = ds.getConnection();
PreparedStatement pstmt = conn.prepareStatement(query)) {
// Set the publication year parameter
____.____(1, 2000);
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
System.out.printf("ID: %d, Title: %s (%d)%n", rs.getInt("book_id"), rs.getString("title"), rs.getInt("publication_year"));
}
}
}
}
}