Refactoring for Spring Data returning List<Object[]>

Question

I came across a project where I have several methods in Repository of this kind:

@Query(value = "SELECT "
        + "  i.uf, "
        + "  i.cidade "
        + "FROM Imovel AS i "
        + "WHERE i.ativo = 'Sim' AND "
        + "      EXISTS (SELECT 1 "
        + "              FROM ImovelFoto AS f "
        + "              WHERE f.codigoImovel = i.codigo)"
        + "GROUP BY i.uf, i.cidade", nativeQuery = true)    
List<Object[]> findUFCidade();

I hope to switch to an object like this:

public class LocalizacaoAgrupadaDTO {

    private String uf;
    private String cidade;

    // Getters e Setters omitidos
}

In this answer in Soen, the solution is to change the query to JPQL.

However, this exchange adds extra complexity, due to the different syntax and even the mapping of the entities, and this select is grouping, with no direct relation to the entities.

What is the best way to do this refactoring with Spring Data JPA?

Should I always prioritize the use of JPQL or is it possible with nativeQuery?

Answer 1

I believe that the best way for you to work for this situation is with pojos and not with Objects.

Answering your question whether you should prioritize the use of JPQL or use Nativequery the answer is:

DEPENDS

To head start of JPQL is that if you change databases in the course of the project, this change tends to be much less probabilistic compared to the Native query. Since some expressions vary from bank to bank.

To downside JPQL is about performance. A Native query tends to be more performative, since it is not necessary to perform a "build" to convert the JPQL expression into an sql for a given database.

For situations like me ALWAYS I choose to create Pojos. Below an example using JPA with Hibernate:

public List<Perfil> listAll() {
    return manager.createNativeQuery("SELECT * FROM perfil", Perfil.class).getResultList();
}

Entity / Pojo Profile:

@Entity(name= "perfil")
public class Perfil extends BaseEntity{

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "id_perfil")
    private Long idPerfil;

    private String descricao;

    @Column(name = "dt_criacao")
    private Date dtCriacao;

    @Column(name = "dt_edicao")
    private Date dtEdicao;