Hibernate generate unnecessary cross join and duplicate results-CodePudding

I need to write a dynamic query with spring data jpa and criteria api, but hibernate generate unnecessary cross join and duplicate results.

Entity class

@Id
@GeneratedValue(generator = "uuid")
@GenericGenerator(name = "uuid", strategy = "org.hibernate.id.UUIDGenerator")
private String id;
private String accountId;
@CreationTimestamp
private LocalDateTime createdTs;
@UpdateTimestamp
private LocalDateTime updatedTs;

Query:

    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> query = cb.createQuery(Long.class);
    Root<FavouriteItemEntity> root = query.from(MyEntity.class);

    Path<String> accountIdPath = root.get("accountId");
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(cb.equal(accountIdPath, accountId));
    query.select(cb.count(query.from(MyEntity.class)));
    query.where(predicates.toArray(new Predicate[0]));
    return entityManager.createQuery(query)
            .getSingleResult();

Hql generate cross join and duplicate results

Hibernate: select count(favouritei1_.id) as col_0_0_ from favourite favouritei0_ cross join favourite favouritei1_ where favouritei0_.account_id=?

instead of

Hibernate: select count(f.id) from favourite f where f.account_id = ?

why is this happening?

CodePudding user response：

This is a common mistake when working with Criteria queries. You call query.from twice, thus a cross join. Call it once instead:

query.select(cb.count(root));