Zacznij terazZacznij za darmo

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.

To ćwiczenie jest częścią kursu

Querying a PostgreSQL Database in Java

Zobacz kurs

Instrukcje do ćwiczenia

  • Complete the query to join with the categories table using category_id.
  • Select the proper join type to show only books that have a category.

Interaktywne ćwiczenie praktyczne

Spróbuj tego ćwiczenia, uzupełniając ten przykładowy kod.

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"));
                }
            }
        }
    }
}
Edytuj i uruchom kod