使用@Query注释的Spring Data存储库方法中返回DTO对象

Question

我有一个仓库，这个接口中我有一个方法想要返回一个ComentarioEditalDto而不是ComentarioEdital，但是当我调用这个方法时，会抛出一个异常，告诉我没有转换器可以使用，我如何转换以返回另一个类的实例？

import br.edu.ifrn.rederenova.dto.ComentarioEditalDto;
import br.edu.ifrn.rederenova.model.ComentarioEdital;
import java.util.List;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface ComentarioRepository extends CrudRepository<ComentarioEdital, Long>{

    @Query(value = "SELECT c.id, c.texto, c.data_ultima_edicao, c.autor_id, " +
           "c.data_criacao, c.edital_id, c.excluida FROM comentario_edital c INNER JOIN edital e ON e.id = c.edital_id " +
           "WHERE (c.excluida = FALSE OR c.excluida IS NULL) AND e.numero = :numero ORDER BY c.data_criacao DESC",
            nativeQuery = true)
    public List<ComentarioEditalDto> findAllByEdital(@Param("numero") String nuEdital);

}

我移除了获取和设置方法

import br.edu.ifrn.rederenova.model.ComentarioEdital;
import br.edu.ifrn.rederenova.model.Edital;
import br.edu.ifrn.rederenova.model.Usuario;
import java.util.Calendar;
import java.util.List;

public class ComentarioEditalDto {

    private Long id;
    private String texto;
    private Calendar dataCriacao;
    private Calendar dataUltimaEdicao;
    private List<ComentarioEdital> respostasComentario;
    private Usuario autor;
    private Edital edital;
    private Boolean excluida;

}

我移除了Gets和Sets

import com.fasterxml.jackson.annotation.JsonIgnore;
import java.io.Serializable;
import java.util.Calendar;
import java.util.List;
import java.util.Objects;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.validation.constraints.NotBlank;
import org.springframework.format.annotation.DateTimeFormat;

@Entity
@SequenceGenerator(allocationSize = 1, initialValue = 1, name = "comentario_seq", sequenceName = "comentario_seq")
public class ComentarioEdital implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "comentario_seq")
    private Long id;
    @NotBlank(message = "Não deve estar vazio")
    private String texto;
    @Temporal(TemporalType.TIMESTAMP)
    @DateTimeFormat(pattern = "dd/MM/yyyy")
    private Calendar dataCriacao;
    @Temporal(TemporalType.TIMESTAMP)
    @DateTimeFormat(pattern = "dd/MM/yyyy")
    private Calendar dataUltimaEdicao;
    @OneToMany(cascade = {CascadeType.REMOVE, CascadeType.REFRESH}, fetch = FetchType.EAGER)
    @JoinTable(name = "respostas_comentario",
                joinColumns = @JoinColumn(name = "pergunta_id"),
                inverseJoinColumns = @JoinColumn(name = "resposta_id"))
    private List<ComentarioEdital> respostasComentario;
    @ManyToOne(fetch = FetchType.EAGER)
    private Usuario autor;
    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    private Edital edital;
    @JsonIgnore
    private Boolean excluida;
}

一个小的异常

org.springframework.core.convert.ConverterNotFoundException: 找不到能够将类型 [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] 转换为类型 [br.edu.ifrn.rederenova.dto.ComentarioEditalDto] 的转换器 at org.springframework.core.convert.support.GenericConversionService.handleConverterNotFound(GenericConversionService.java:321) ~[spring-core-5.0.7.RELEASE.jar:5.0.7.RELEASE] at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:194) ~[spring-core-5.0.7.RELEASE.jar:5.0.7.RELEASE]

- Maykon Oliveira

Answer 1

你需要在你的@Query中返回一个dto。

@Query(value = "SELECT new br.edu.ifrn.rederenova.dto.ComentarioEditalDto(c.id, c.texto, c.dataCriacao, c.dataUltimaEdicao, c.excluida) " +
        "FROM ComentarioEdital c ....

• 不要编写本地查询，可以使用JPQL
• 使用SELECT new package.DTOClass，例如SELECT new br.edu.ifrn.rederenova.dto.ComentarioEditalDto
• 在ComentarioEditalDto中定义一个与你传递给SELECT new br.edu.ifrn.rederenova.dto.ComentarioEditalDto相同参数的构造函数

- ValerioMC

---

我该如何使用这个解决方案来创建我的DTO页面？ - GuanacoBE

Answer 2

首先，不要删除您的POJO的getter/setter方法。 其次，如果您想从存储库返回自定义对象，我建议您使用QueryDSL。您可以像这样使用它： 存储库：

@Repository
public interface EntityRepository extends JpaRepository<Entity, Long>, EntityRepositoryCustom, QuerydslPredicateExecutor<Entity> {
}

自定义存储库:

public interface EntityRepositoryCustom {
    List<EntityDTO> getEntityDTOsByCriteria(String arg0, String args1);
}

存储库实现：

public class EntityRepositoryImpl implements EntityRepositoryCustom {

    @Autowired
    private CardHolderRepository cardHolderRepository;

    @Autowired
    private EntityManager entityManager;

    @Override
    public List<EntityDTO> getEntityDTOsByCriteria(String arg0, String args1) {
    final List<EntityDTO> projections = new JPAQuery(entityManager, jpqlTemplates)
                    .from(entity)
                    .orderBy(entity.somefield.asc())
                    .list(ConstructorExpression.create(EntityDTO.class, entity.somefield,
                                    entity.someOtherfield));
    return projections;
}

- ksadjad

Answer 3

只需将类ComentarioEditalDto更改为接口，像这样：

public interface ComentarioEditalDto {

    BigInteger getId();
    String getTexto();
    Calendar getDataCriacao();
    Calendar getDataUltimaEdicao();
    List<ComentarioEdital> getRespostasComentario(); --> attention to this ComentarioEdital
    Usuario getAutor();
    Edital getEdital();
    Boolean isExcluida();

}

- Eduardo Nobre