PostgreSQL query works on pgAdmin but not in Spring Boot

The query SELECT * FROM books WHERE (isbn || ' ' || author || ' ' || name) ILIKE '%el%' returns

from the full db when executed in pgAdmin (PostgreSQL tool)

But the same query doesn’t seem to work when I try to run it in Spring Boot, it returns an empty list. I do not know if my query is PostgreSQL specific or if it’s native SQL, if it isn’t native SQL then I guess it is reasonable that it does not work seeing as I have nativeQuery = true which if I have understood things correctly means that native SQL is expected. Without it, the application does not even run. If the case is as described, how do I specify that I want to use PostgreSQL in the query?

BookController.java

@GetMapping("/search")
public String search(@RequestParam(value = "keyword") String keyword, Model model) {
    List<Book> books = bookService.search(keyword);
    model.addAttribute("books", books);
    return "books";
}

BookService.java

public List<Book> search(String keyword) {
      return bookRepository.search(keyword);
}

BookRepository.java

@Repository
public interface BookRepository extends CrudRepository<Book, Long> {

    List<Book> findAll();

    @Query(value = "SELECT * FROM books WHERE (isbn || ' ' || author || ' ' || name) ILIKE '%?%'", nativeQuery = true)
    List<Book> search(String keyword);

}

Books.html the value I am entering is el.

<form class="col-12 col-lg-auto mb-3 mb-lg-0 me-lg-3" th:action="@{books/search}" method="get">
     <input th:name="keyword" type="search" class="form-control form-control-dark" placeholder="Search..." aria-label="Search">
</form>

Answer

The parameter ? may not be applied properly in the query. Change '%?%' to '%' || ? || '%' as in:

@Query(value = "SELECT * FROM books WHERE (isbn || ' ' || author || ' ' || name) ILIKE '%' || ? || '%'", nativeQuery = true)

Advertisement

Answer