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.
This exercise is part of the course
Querying a PostgreSQL Database in Java
Exercise instructions
- 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.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
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);
}
}