All geek questions in one place

Ordering query results Hibernation criteria using information from child objects of requested objects

I have two people Person and Book. Only one copy of a specific book is stored in the system (when adding a book, the application checks if this book was already found before adding a new row to the database). The corresponding source code for the objects can be found below:

@Entity @Table(name="persons") @SequenceGenerator(name="id_sequence", sequenceName="hibernate_sequence") public class Person extends BaseModel { @Id @Column(name = "id") @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_sequence") private Long id = null; @ManyToMany(targetEntity=Book.class) @JoinTable(name="persons_books", joinColumns = @JoinColumn( name="person_id"), inverseJoinColumns = @JoinColumn( name="book_id")) private List<Book> ownedBooks = new ArrayList<Book>(); } @Entity @Table(name="books") @SequenceGenerator(name="id_sequence", sequenceName="hibernate_sequence") public class Book extends BaseModel { @Id @Column(name = "id") @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "id_sequence") private Long id = null; @Column(name="name") private String name = null; }

My problem is that I want to find people who own some books that belong to a particular person. The returned list of persons should be ordered using the following logic: the person who owns the majority of books should be in the first list, the second person in the list did not have as many books as the first person, but more than the third person. The code for the method executing this request is added below:

 @Override public List<Person> searchPersonsWithSimilarBooks(Long[] bookIds) { Criteria similarPersonCriteria = this.getSession().createCriteria(Person.class); similarPersonCriteria.add(Restrictions.in("ownedBooks.id", bookIds)); //How to set the ordering? similarPersonCriteria.addOrder(null); return similarPersonCriteria.list(); }

My question is, what can this be done using Hibernate? And if so, how can this be done? I know that I can implement Comparator, but I would prefer to use Hibernate to solve this problem.

When using SQL, the result I want can be achieved with the following SQL query:

 select p.name, count(p.name) as bookCount from persons p, books b, person_book pb where pb.person_id = p.id and pb.book_id = b.id and b.id in (1,2,3,4,5,6) group by name order by bookCount desc

I am trying to figure out how to translate this into a Criteria request. I am trying to use Projections, but it seems to me that I cannot match the results with the Person object.

+4
source share
2 answers

I finally managed to solve this problem. The following method works as expected:

 @Override public List<Person> searchPersonsWithSimilarBooks(Long[] bookIds) { Criteria similarPersonCriteria = this.getSession().createCriteria(Person.class, "p"); Criteria bookCriteria = similarPersonCriteria.createCriteria("ownedBooks", "b"); bookCriteria.add(Restrictions.in("b.id", bookIds)); similarPersonCriteria.setProjection(Projections.projectionList() .add(Projections.groupProperty("p.id"), "id") .add(Projections.rowCount(), "similarBookCount")); similarPersonCriteria.addOrder(Order.desc("similarBookCount")); similarPersonCriteria.setResultTransformer(new AliasToBeanResultTransformer(Person.class)); return similarPersonCriteria.list(); }

I also updated my person class by adding a transient property like likeBookCount, which might be useful in some situations.

+1
source

Source: https://habr.com/ru/post/1306570/

More articles:


All Articles