How to get distinct results in hibernate with joins and row-based limiting (paging)?

by

You can achieve the desired result by requesting a list of distinct ids instead of a list of distinct hydrated objects.

Simply add this to your criteria:

criteria.setProjection(Projections.distinct(Projections.property("id")));

Now you’ll get the correct number of results according to your row-based limiting. The reason this works is because the projection will perform the distinctness check as part of the sql query, instead of what a ResultTransformer does which is to filter the results for distinctness after the sql query has been performed.

Worth noting is that instead of getting a list of objects, you will now get a list of ids, which you can use to hydrate objects from hibernate later.

More Related Contents:

  1. How to get SQL from Hibernate Criteria API (*not* for logging)
  2. JPA and Hibernate – Criteria vs. JPQL or HQL
  3. Hibernate criteria: Joining table without a mapped association
  4. Hibernate Criteria for Dates
  5. Using hibernate criteria, is there a way to escape special characters?
  6. Hibernate throws MultipleBagFetchException – cannot simultaneously fetch multiple bags
  7. Hibernate JPA Sequence (non-Id)
  8. How do I use annotations to define different types of relationships in Hibernate 4 and Spring?
  9. How to store date/time and timestamps in UTC time zone with JPA and Hibernate
  10. Bypass GeneratedValue in Hibernate (merge data not in db?)
  11. How does the FetchMode work in Spring Data JPA
  12. Hibernate Union alternatives
  13. Location of hibernate.cfg.xml in project?
  14. Hibernate – Batch update returned unexpected row count from update: 0 actual row count: 0 expected: 1
  15. JPA SQL Server No Dialect mapping for JDBC type: -9
  16. JPA entity without id
  17. Hibernate Mapping Package
  18. JPA OneToMany and ManyToOne throw: Repeated column in mapping for entity column (should be mapped with insert=”false” update=”false”)
  19. Hibernate Annotation Placement Question
  20. GenerationType.AUTO vs GenerationType.IDENTITY in hibernate
  21. How to monitor c3p0 connections
  22. Manually call Spring Annotation Validation
  23. Hibernate persist entity without fetching association object. just by id
  24. HQL Hibernate INNER JOIN
  25. HQL joined query to eager fetch a large number of relationships
  26. Clarifying terminology – What does “hydrating” a JPA or Hibernate entity mean when fetching the entity from the DB
  27. getting result set into DTO with native SQL Query in Hibernate
  28. org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags
  29. Hibernate Issue : Foreign key must have same number of columns as referenced primary key
  30. How to get jdbc connection from hibernate session? [duplicate]

Leave a Comment