How to use SQL instead of JPQL?

Question

This week I went through a problem, to be able to build a Java API using JPQL, but my Java API is not yet completed and I can already predict that in the future my project will need reporting, and imagine doing in JPQL? It’s gonna be a bigger hell!

The problem I had is to perform a simple JPQL query that resembles select * from noticia order by data_noticia asc; and I had a lot of trouble understanding how to create this query, I was able to create the query as you can see below, and it’s working perfectly;

package br.com.mdw.repository.noticia;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.util.StringUtils;

import br.com.mdw.model.Noticia;
import br.com.mdw.model.Noticia_;
import br.com.mdw.repository.filter.NoticiaFilter;

public class NoticiaRepositoryImpl implements NoticiaRepositoryQuery {

    @PersistenceContext
    private EntityManager manager;

    @Override
    public Page<Noticia> filtrar(NoticiaFilter noticiaFilter, Pageable pageable) {

        CriteriaBuilder builder = manager.getCriteriaBuilder();

        CriteriaQuery<Noticia> criteria = builder.createQuery(Noticia.class);

        Root<Noticia> root = criteria.from(Noticia.class);

          criteria.select(root);
          criteria.orderBy(builder.desc(root.get("dataNoticia")));


        Predicate[] predicates = criarRestricoes(noticiaFilter, builder, root);

        criteria.where(predicates);

        /*Esse é o trecho do código responsável em realizar a consulta simples */


        TypedQuery<Noticia> query = manager.createQuery(criteria);

        adicionarRestricoesDePaginacao(query, pageable);

        return new PageImpl<>(query.getResultList(), pageable, total(noticiaFilter));
    }

    private Predicate[] criarRestricoes(NoticiaFilter noticiaFilter, CriteriaBuilder builder, Root<Noticia> root) {
        List<Predicate> predicates = new ArrayList<>();

        if (!StringUtils.isEmpty(noticiaFilter.getTitulo())) {
            predicates.add(builder.like(builder.lower(root.get(Noticia_.titulo)),
                    "%" + noticiaFilter.getTitulo().toLowerCase() + "%"));
        }

        if (!StringUtils.isEmpty(noticiaFilter.getConteudo())) {
            predicates.add(builder.like(builder.lower(root.get(Noticia_.conteudo)),
                    "%" + noticiaFilter.getConteudo().toLowerCase() + "%"));
        }

        if (noticiaFilter.getDataNoticia() != null) {
            predicates
                    .add(builder.greaterThanOrEqualTo(root.get(Noticia_.dataNoticia), noticiaFilter.getDataNoticia()));
        }

        if (!StringUtils.isEmpty(noticiaFilter.getFont())) {
            predicates.add(builder.like(builder.lower(root.get(Noticia_.font)),
                    "%" + noticiaFilter.getFont().toLowerCase() + "%"));
        }

        return predicates.toArray(new Predicate[predicates.size()]);
    }

    private void adicionarRestricoesDePaginacao(TypedQuery<Noticia> query, Pageable pageable) {
        int paginaAtual = pageable.getPageNumber();
        int totalRegistrosPorPagina = pageable.getPageSize();
        int primeiroRegistroDaPagina = paginaAtual * totalRegistrosPorPagina;

        query.setFirstResult(primeiroRegistroDaPagina);
        query.setMaxResults(totalRegistrosPorPagina);
    }

    private Long total(NoticiaFilter noticiaFilter) {
        CriteriaBuilder builder = manager.getCriteriaBuilder();
        CriteriaQuery<Long> criteria = builder.createQuery(Long.class);
        Root<Noticia> root = criteria.from(Noticia.class);

        Predicate[] predicates = criarRestricoes(noticiaFilter, builder, root);
        criteria.where(predicates);

        criteria.select(builder.count(root));
        return manager.createQuery(criteria).getSingleResult();
    }

}

You may notice that there is a filter, but before the filter a simple JPQL query is performed on this code snippet;

CriteriaQuery<Noticia> criteria = builder.createQuery(Noticia.class);

Root<Noticia> root = criteria.from(Noticia.class);

  criteria.select(root);
  criteria.orderBy(builder.desc(root.get("dataNoticia")));

What is my difficulty?

I would like to do the query using SQL, but I don’t know how the code could look right above, I’ve tried to do everything, but the code gets wrong, is giving errors in some parts and as I did the hardest part I think it will be simple for someone to help me, anything I’ll be willing to ask questions to receive help.

Answer 1

How you wanted to see a possible implementation of this same filter not using the CriteriaAPI, follows a version (untested..) with the JPQL.

I have added only those parts of the code that have been changed.. the rest remain identical to your version.

    public Page filtrar(NoticiaFilter noticiaFilter, Pageable pageable) {

        String baseQuery = "SELECT n FROM Noticia n ";
        String whereQuery = criarWhere(noticiaFilter);
        String orderBy = "ORDER BY n.dataNoticia DESC";

        TypedQuery query = manager.createQuery(
                baseQuery + whereQuery + orderBy
        );

        adicionarParameters(query, noticiaFilter);
        adicionarRestricoesDePaginacao(query, pageable);

        return new PageImpl(query.getResultList(), pageable, total(noticiaFilter));
    }

    private String criarWhere(NoticiaFilter noticiaFilter) {
        String where = "WHERE 1=1 "; 

        if (!StringUtils.isEmpty(noticiaFilter.getTitulo())) {
            where += "AND " + Noticia_.titulo.getName() + " LIKE :titulo";
        }

        if (!StringUtils.isEmpty(noticiaFilter.getConteudo())) {
            where += "AND " + Noticia_.conteudo.getName() + " LIKE :conteudo";
        }

        if (noticiaFilter.getDataNoticia() != null) {
            where += "AND " + Noticia_.dataNoticia.getName() + " >= :dataNoticia'";
        }

        if (!StringUtils.isEmpty(noticiaFilter.getFont())) {
            where += "AND " + Noticia_.font.getName() + " LIKE :font'";
        }

        return where;
    }

    private void adicionarParameters(TypedQuery query, NoticiaFilter noticiaFilter) {
        if (!StringUtils.isEmpty(noticiaFilter.getTitulo())) {
            query.setParameter("titulo", "%" + noticiaFilter.getTitulo().toLowerCase() + "%");
        }

        if (!StringUtils.isEmpty(noticiaFilter.getConteudo())) {
            query.setParameter("conteudo", "%" + noticiaFilter.getConteudo().toLowerCase() + "%");
        }

        if (noticiaFilter.getDataNoticia() != null) {
            query.setParameter("dataNoticia", noticiaFilter.getDataNoticia());
        }

        if (!StringUtils.isEmpty(noticiaFilter.getFont())) {
            query.setParameter("font", "%" + noticiaFilter.getFont().toLowerCase() + "%");
        }
    }