The first join
CityBook Libraries wants to display book categories alongside search results. Only books with assigned categories should appear in the results.
Join the categories table to enrich the book data. The HikariSetup class is already configured for you.
Diese Übung ist Teil des Kurses
Querying a PostgreSQL Database in Java
Anleitung zur Übung
- Complete the query to join with the
categoriestable usingcategory_id. - Select the proper join type to show only books that have a category.
Interaktive Übung
Vervollständige den Beispielcode, um diese Übung erfolgreich abzuschließen.
public class Main {
public static void main(String[] args) throws SQLException {
HikariDataSource ds = HikariSetup.createDataSource();
// Join with the categories table using category_id
String query = """
SELECT b.*, c.name, c.description
FROM books b
____ JOIN ____ c on b.____ = c.____
WHERE b.publication_year >= ?
AND b.status = 'available'
ORDER BY publication_year ASC
""";
try (Connection conn = ds.getConnection();
PreparedStatement pstmt = conn.prepareStatement(query)) {
pstmt.setInt(1, 2000);
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
System.out.printf("ID: %d, Title: %s (%d), Category: %s - %s%n",
rs.getInt("book_id"), rs.getString("title"), rs.getInt("publication_year"),
rs.getString("name"), rs.getString("description"));
}
}
}
}
}