Issue
I have three related entities Author, Category and Book in my Sping Boot application:
@Entity
class Category {
@Id
Long id;
// other members, getters and setters
}
@Entity
class Author {
@Id
Long id;
// other members, getters and setters
}
@Entity
class Book {
@Id
Long id;
@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "author_id", nullable = false)
private Author author;
@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "category_id", nullable = false)
private Category category;
// getters and setters
}
The user can search for books by author and/or categories. To do so, my application offers a search REST endpoint receiving the following SearchDto:
class SearchDTO {
public List<Long> authorIds;
public List<Long> categoryIds;
}
I'm using the criteria API to write the DB query.
class BookRepositoryCustomImpl implements BookRepositoryCustom {
@PersistenceContext
private EntityManager entityManager;
public search(SearchDto searchDto) {
var cb = entityManager.getCriteriaBuilder();
var query = cb.createQuery(Book.class);
var result = query.from(Book.class);
var predicates = new ArrayList<Predicate>();
if (searchDto.authorIds != null) {
// Version 1
// predicates.add(book.get(Book_.author).in(searchDto.authorIds));
// Version 2
// var authorJoin = book.join(Book_.author);
// predicates.add(authorJoin.get(Author_.id).in(searchDto.authorIds));
}
// Similar code for category constraint.
query.select(result);
query.where(predicates.toArray(Predicate[]::new));
return entityManager.createQuery(query).getResultList();
}
}
Both of my versions work but have their disadvantages.
Version 1 omits the unnecessary join to Author and uses the book's column author_id directly. However, as the ids are numeric values, the SQL statement contains literals but I would like to have bind variables.
When setting the property spring.jpa.properties.hibernate.criteria.literal_handling_mode=BIND
, I get a runtime exception because a Long variable was provided but an Author object was expected. This error does not occur when using literals in the SQL statement.
With version 2, I can use bind variables but the SQL statement contains the unnecessary join to the Author table (INNER JOIN author ON book.author_id = author.id WHERE author.id IN ($1)
).
I can also think of a version 3 where I would use authorRepository.getOne(authorId)
to get a proxy to the author object and use a List<Author>
. The disadvantage of this approach is all the glue code I have to write to convert the ids into entity objects.
Is there a proper way to omit the JOIN but being able to use bind variables?
Solution
You can convert an id into a reference to an Author
with getReference()
:
Author reference = entityManager.getReference(Author.class, authorId);
I'm not sure what's the type of searchDTO.authorIds
, but assuming is a collection:
List<Author> authors = searchDTO.autorhIds.stream()
.map( id -> entityManager.getReference(Author.class, id) )
.collect( Collectors.toList() );
Now you have a list of authors that you can use as parameters for option 1. This is going to create lazy references to the authors in the db without actually querying the db.
UPDATE: I've just realized that geOne()
and getReference()
are the same thing, so you could also write:
List<Author> authors = searchDTO.autorhIds.stream()
.map( authorRepository::getOne )
.collect( Collectors.toList() );
Answered By - Davide
Answer Checked By - Willingham (JavaFixing Volunteer)