Erro SqlTreeCreationException: Could not locate TableGroup - When paging a CRITERIA API query

Hibernate ORM
1

I’m using spring boot 3.3.3 with hibernate 6.5.2.Final. I’m trying to paginate a query built using CRITERIA API and I’m getting the error:

org.hibernate.sql.ast.SqlTreeCreationException: Could not locate TableGroup - br.uesc.lab_cidades.domain.entities.Arquivo(43792771594787)

Arquivo.java

@Entity
@Table(name = "tbl_arquivo")
public class Arquivo {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotBlank(message = "Campo Título Arquivo é obrigatório")
    @Column(name = "titulo", length = 100, nullable = false, unique = true)
    private String titulo;

    private String descricao;

    @Column(name = "ano_publicacao")
    private Integer anoPublicacao;

    @Column(name = "arquivo_url", nullable = false, unique = true)
    @NotBlank(message = "Campo Url Arquivo é obrigatório")
    private String arquivoUrl;

    @ManyToOne
    @JoinColumn(name = "localidade_id", referencedColumnName = "id")
    private Localidade localidade;

    @ManyToMany
    @JoinTable(
            name = "arquivo_categoria",
            joinColumns = @JoinColumn(name = "arquivo_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "categoria_id", referencedColumnName = "id")
    )
    private List<Categoria> categorias;

    @ManyToMany
    @JoinTable(
            name = "arquivo_palavra_chave",
            joinColumns = @JoinColumn(name = "arquivo_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "palavra_chave_id", referencedColumnName = "id")
    )
    private List<PalavraChave> palavrasChave;

    @ManyToMany
    @JoinTable(
            name = "arquivo_autor",
            joinColumns = @JoinColumn(name = "arquivo_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "autor_id", referencedColumnName = "id")
    )
    private List<Autor> autores;
}

CustomizedArquivoImpl.java

@Repository
public class CustomizedArquivoRepositoryImpl implements CustomizedArquivoRepository {

    private final EntityManager entityManager;

    public CustomizedArquivoRepositoryImpl(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Override
    public Page<Arquivo> buscarPorPalavras(String palavras, Pageable pageable) {
        String[] keywords = palavras.split(" ");

        // Configuring a Criteria API
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();

        CriteriaQuery<Arquivo> query = cb.createQuery(Arquivo.class);

        Root<Arquivo> arquivo = query.from(Arquivo.class);

        List<Predicate> predicates = new ArrayList<>();

        // For each word, add a LIKE condition
        for (String keyword : keywords) {
            System.out.println(keyword);
            predicates.add(cb.like(cb.lower(arquivo.get("titulo")), "%" + keyword.toLowerCase() + "%"));
        }

        // Joining the conditions with OR
        query.where(cb.or(predicates.toArray(new Predicate[0])));

        // Running the query with pagination
        List<Arquivo> documentos = entityManager.createQuery(query)
                .setFirstResult((int) pageable.getOffset())
                .setMaxResults(pageable.getPageSize())
                .getResultList();


        // Creating the query to count the total results
        CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
        Root<Arquivo> countRoot = countQuery.from(Arquivo.class);
        countQuery.select(cb.count(countRoot)).where(cb.or(predicates.toArray(new Predicate[0])));

        Long total = entityManager.createQuery(countQuery).getSingleResult();

        return new PageImpl<>(documentos, pageable, total);
    }
}

Being able to check and error consists of counting the total results, but I can’t solve it:

        // Creating the query to count the total results
        CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
        Root<Arquivo> countRoot = countQuery.from(Arquivo.class);
        countQuery.select(cb.count(countRoot)).where(cb.or(predicates.toArray(new Predicate[0])));

        Long total = entityManager.createQuery(countQuery).getSingleResult();
2

Hello @joaocarlosjr, the error you’re seeing is caused by the fact that you’re re-using criteria objects created for one CriteriaQuery (the original, paginated query) in another query instance (the count query). That is not supported, and will cause issue like the ones you’re encountering.

You’ll be please to know that this was a common request and the Hibernate team implemented native count query support through the our criteria API
extensions, in particular the org.hibernate.query.criteria.JpaCriteriaQuery#createCountQuery method. You can use this in your code by e.g. unwrapping the EntityManager

final HibernateCriteriaBuilder cb = entityManager.unwrap(Session.class).getCriteriaBuilder();
// or
final JpaCriteriaQuery<Arquivo> = (JpaCriteriaQuery<Arquivo>) cb.createQuery(Arquivo.class);

Find out more in our user guide.

3

Thanks. I managed to solve it and it looked like this:

@Repository
public class CustomizedArquivoRepositoryImpl implements CustomizedArquivoRepository {

    private final EntityManager entityManager;

    public CustomizedArquivoRepositoryImpl(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Override
    public Page<Arquivo> buscarPorPalavras(String palavras, Pageable pageable) {
        String[] keywords = palavras.split(" ");

        // Configuring a Criteria API
        HibernateCriteriaBuilder cb = entityManager.unwrap(Session.class).getCriteriaBuilder();

       JpaCriteriaQuery<Arquivo> query = cb.createQuery(Arquivo.class);

        Root<Arquivo> arquivo = query.from(Arquivo.class);

        List<Predicate> predicates = new ArrayList<>();

        // For each word, add a LIKE condition
        for (String keyword : keywords) {
            System.out.println(keyword);
            predicates.add(cb.like(cb.lower(arquivo.get("titulo")), "%" + keyword.toLowerCase() + "%"));
        }

        // Joining the conditions with OR
        query.where(cb.or(predicates.toArray(new Predicate[0])));

        // Running the query with pagination
        List<Arquivo> documentos = entityManager.createQuery(query)
                .setFirstResult((int) pageable.getOffset())
                .setMaxResults(pageable.getPageSize())
                .getResultList();


        // Total count of results
       Long total = entityManager.createQuery(query.createCountQuery()).getSingleResult();

        return new PageImpl<>(documentos, pageable, total);
    }