Spring data jpa-基于自定义查询的投影

Question

我需要有一个自定义查询的spring数据仓库方法，并希望使用基于类的投影。

看这个https://docs . spring . io/spring-data/JPA/docs/current/reference/html/# projections

@Entity
public class Person {
  @Id
  private Long id;
  private String firstName;
  private String lastName;
  private int age;
}

@Value // lombok annotation to create constructor, equals and hash-code
public class PersonDTO {
  private String firstName;
  private String lastName;
}

public interface PersonRepository extends Repository<Person, Long> {

List<PersonProjection> findDistinct();

@Query("select distinct firstName, lastName from Person")
List<PersonProjection> findDistinctQuery();

@Query(value = "select distinct first_name, last_name from person", nativeQuery = true)
List<PersonProjection> findDistinctNativeQuery();

}

• findDistinct运行良好。
• findDistinctQuery和FindDitinctNativeQuery抛出

找不到能够从类型[org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap]转换为类型[com.x.PersonDTO]的转换器

有没有办法让它和类(而不是接口)一起工作？

Answer 1

我不确定是否有用于本机查询的Spring数据解决方案，但您可以使用JPA ConstructorResult：

@Entity
@NamedNativeQuery(
        name="Person.findDistinctNativeQuery",
        query="select distinct first_name as firstName, last_name as lastName from person",
        resultSetMapping="PersonMapping"
)
@SqlResultSetMapping(name="PersonMapping",
        classes={
                @ConstructorResult(targetClass=PersonDTO.class, columns={
                        @ColumnResult(name="firstName", type=String.class),
                        @ColumnResult(name="lastName", type=String.class)
                })
        })
public class Person {
    @Id
    private Long id;
    private String firstName;
    private String lastName;
    private int age;
}

@Value // lombok annotation to create constructor, equals and hash-code
public class PersonDTO {
    private String firstName;
    private String lastName;
}

public interface PersonRepository extends Repository<Person, Long> {

    List<PersonProjection> findDistinct();

    @Query("select distinct firstName, lastName from Person")
    List<PersonProjection> findDistinctQuery();

    @Query(name = "Person.findDistinctNativeQuery", nativeQuery = true)
    List<PersonDTO> findDistinctNativeQuery();

}

或者，您可以从findDistinctNativeQuery（）返回对象[]，然后手动创建PersonDTO。

通过HQL，您还可以使用基于类的投影，唯一的条件PersonDTO必须具有all参数构造函数，其参数名称必须与根实体类的属性相匹配:

public interface PersonRepository extends Repository<Person, Long> {

    ...

    List<PersonDTO> findDistinct();

}

如果PersonDTO属性与基本实体属性不匹配，则可以使用HQL动态实例化。同样，这不适用于本机查询：

public interface PersonRepository extends Repository<Person, Long> {

    ...

    @Query("select new com.x.PersonDTO(firstName, lastName) from Person")
    List<PersonDTO> findDistinct();

}

Answer 2

这也有效：

public interface PersonRepository extends CrudRepository<Person, Long> {

    @Query(nativeQuery = true,
        value = "SELECT DISTINCT fist_name AS firstName, last_name AS lastName FROM person ")
    List<PersonNameOnly> findDistinctNames();

    interface PersonNameOnly {
        String getFirstName;
        String getLastName;
    }
}

Answer 3

请将您的方法写在如下个人知识库中

@Query(value = "select distinct new xxx.xxx.dto.PersonDTO(p.first_name, p.last_name) from person p")
List<PersonProjection> findDistinctNativeQuery();

“xxx.xxx.dto.PersonDTO”这应该指向您的完全限定类名。

为了您的参考，请调查一下https://www.bytestree.com/spring/spring-data-jpa-projections-5-ways-return-custom-object/