When trying to search for multiple values of a relation, duplicate results are returned.
How can I avoid these duplicates?
We have a method on our service that builds the Criteria
:
@Override
protected Criteria createCriteria(Map<String, Object> values) {
Criteria criteria = super.createCriteria(values);
if (criteria != null && values != null) {
// other criteria restrictions
Set<MDTErkenning> erkenningen = (Set<MDTErkenning>) values.get("erkenningen");
if (erkenningen != null && !erkenningen.isEmpty()) {
criteria.createAlias("erkenningen", "erkenningen");
criteria.add(Restrictions.in("erkenningen." + CollectionPropertyNames.COLLECTION_ELEMENTS, erkenningen));
}
}
return criteria;
}
which is used to get out paged results and do a count:
@Override
public List<T> findByCriteria(Map<String, Object> values, int offset, int amount) {
Criteria criteria = createCriteria(values)
.setFirstResult(offset)
.setMaxResults(amount);
return criteria.list();
}
@Override
public Long getCount(Map<String, Object> values) {
Criteria criteria = createCriteria(values).setProjection(Projections.rowCount());
return (Long) criteria.uniqueResult();
}
Main entity
@Entity
@Table(name = "mdt")
public class MultiDisciplinairTeam {
@Id
@Column(name = "mdt_uuid")
private String id;
// other mappings
@Column(name = "erkenning")
@Cascade(org.hibernate.annotations.CascadeType.ALL)
@ElementCollection(fetch = FetchType.EAGER)
@Enumerated(EnumType.STRING)
@JoinTable(name = "mdt_erkenning", joinColumns = {@JoinColumn(name = PK_NAME)})
@Fetch(FetchMode.SELECT)
private Set<MDTErkenning> erkenningen;
// getters & setters
}
Enum instead of entity
public enum MDTErkenning {
ZORG("Zorg"),
IMB("IMB"),
PAB("PAB"),
ROLSTOELEN("Rolstoelen");
private String beschrijving;
private MDTErkenning(String beschrijving) {
this.beschrijving = beschrijving;
}
public String getBeschrijving() {
return this.beschrijving;
}
}
The join table mdt_erkenning
with some data:
------------------------
| mdt_uuid | erkenning |
------------------------
| <id 1> | ZORG |
| <id 1> | PAB |
| <id 2> | ZORG |
| <id 2> | IMB |
------------------------
So if I query for eg. ZORG and PAB I get 3 instances of my entity: 2 with <id 1>
and 1 with <id 2>
.
I thought of putting the result list in a Set
to eliminate the duplicates but then the pagination isn't correct anymore.
What should I do?
Try adding setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
for removing duplicate entries and setProjection(Projections.distinct(Projections.property("id")))
for getting correct row count while creating criteria object.
Try to use this projection:
.setProjection(Projections.countDistinct("id"))
For your example:
@Override
public Long getCount(Map<String, Object> values) {
Criteria criteria = createCriteria(values).setProjection(Projections.countDistinct("id"));
return (Long) criteria.uniqueResult();
}
I am using Hibernate 4.3.6.Final.
I know it is an old thread but in cases, if someone is still having this problem, this Worked for me. More details here
public List<User> getUserByUsername (String username)
{
Session session = sessionFactory.getCurrentSession();
return session.createCriteria(User.class)
.add(Restrictions.eq("username", username))
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
.list();
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With