Book search system
CityBook Libraries' current search only supports exact book titles. They need a flexible search feature that allows visitors to search by partial title, author name, or any combination of these criteria.
You'll create a dynamic query builder that constructs SQL queries based on the search criteria provided by users. The HikariSetup class is already configured for you.
Diese Übung ist Teil des Kurses
Querying a PostgreSQL Database in Java
Anleitung zur Übung
- Append the title condition to the query.
- Add AND if you added a title condition or WHERE if not, to the query.
- Complete the query to sort by title.
Interaktive Übung
Vervollständige den Beispielcode, um diese Übung erfolgreich abzuschließen.
public class Main {
public static void main(String[] args) {
String titleSearch = "first";
String authorSearch = null;
StringBuilder sql = new StringBuilder(
"SELECT b.title, a.first_name AS author " +
"FROM books b " +
"JOIN book_authors ba ON b.book_id = ba.book_id " +
"JOIN authors a ON ba.author_id = a.author_id ");
boolean hasTitleSearch = false;
if (titleSearch != null && !titleSearch.isEmpty()) {
// Append the title condition
sql.____("WHERE b.title ILIKE ? ");
hasTitleSearch = true;
}
if (authorSearch != null && !authorSearch.isEmpty()) {
// Add AND if you added title condition or WHERE if not
sql.append(hasTitleSearch ? "AND " : "____");
sql.append(" a.first_name ILIKE ? ");
}
// Complete the query to sort by title
sql.append("____ ____ b.title");
System.out.println(sql);
}
}