Refactoring for Spring Data returning ListObject []

Question

Refactoring for Spring Data returning ListObject []

Navigation

#1 by (2 votes)

0

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

@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 change adds extra complexity, due to different syntax and entity mappings, and this select is a 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?

java spring spring-jpa

asked by anonymous 03.08.2017 / 13:25

1 answer

Swipe Refresh in TextView Error logging into php site [duplicate]

score 2 · Answer 1

I think the best way for you to work for this situation is with pojos and not with objects.

Answering your question if you should prioritize the use of JPQL or use NativeQuery the answer is:

DEPENDS

The advantage of JPQL is that if you change databases during the project, this change tends to be much less problematic compared to the native query. Since some expressions vary from bank to bank.

The disadvantage of JPQL is a performance issue. 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 this I ALWAYS opt for Pojos creation. Here's 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;