I have a sql which is similar to the following. (As I cannot use production database table names and the query is quite messy)
select book_name, auther_name, price
from library_books
where book_name=?1, auther_name = ?2, price < ?3
and exists (select * from best_sellers where best_sellers.isbn = library_books.isbn
and best_sellers.name = library_books.book_name)
This is actually a query used for filtering report. As example, if the screen is getting a list of books, user may search all books or filter search based on criteria. In the existing code, the query is concatenated as below
if (StrinUtils.isNotEmpty(search_name)){
sb.append ("book name like '%"+search_name+"'%");
}
But now this logic is moved to spring data to use specifications and criteria builder.
public Specification<T> getQuerySpecification(final SearchForm searchForm) {
return new Specification<T>() {
@Override
public Predicate toPredicate(Root<T> recordsRoot, CriteriaQuery<?> query,
criteriaBuilder cb) {
Predicate predicate = cb.conjunction();
if (StringUtils.isNotEmpty(searchForm.getName)){
predicate.getExpressions().add (cb.equal(cb.lower(recordsRoot.get("bookName").as(String.class)), searchForm.getName.toLowerCase(Locale.ENGLISH)) );
}
return predicate;
}
};
}
My question is, how do I manage the following part in the original query using spring data. Note that it also involves a different table call best_sellers
and exists (select * from best_sellers where best_sellers.isbn = library_books.isbn)
Entities would be as following
LibraryBooks
- bookName
- authorName
- price
- isbn
- ....
Best_Sellers
- name
- isbn
- printsSold
Join is not an option as the actual entities are complex than the one in this example. Hence also do not mind the illogical entity relationship, as this is a sample. Real question is how to use spring data and criteria builder when you need to merge a sub query using "exists". Thanks for your help
