选择JPA Spring Boot中的特定列

Question

我想从我的表中获取一些特定的列，但我得到了这个错误：无法执行查询；SQL[按名称顺序从字符中选择名称、图像asc]；嵌套的异常是org。冬眠例外SQLGrammarException:无法执行查询。

o、 引擎。jdbc。spi。SqlExceptionHelper:SQL错误：0，SQLState:42703 o.h.engine。jdbc。spi。SqlExceptionHelper:在此结果集中找不到列名id。

我想运行以下查询：

public interface CharacterRepository extends JpaRepository<CharacterEntity, Long> {
    @Query(value = "SELECT name, image FROM characters", countQuery = "SELECT COUNT(name) FROM characters", nativeQuery = true)
    Page<CharacterEntity> getCharacters(Pageable pageable);
}

我的整个班级：

@Entity(name = "characters")
@Getter @Setter
public class CharacterEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    private long id;

    @Column(nullable = false)
    private String image;

    @Column(nullable = false, length = 100)
    private String name;

    @Column(nullable = false)
    private Integer age;

    @Digits(integer = 8, fraction = 2)
    private BigDecimal weight;

    private String story;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "character")
    private List<MovieDetailEntity> movies;

}

我的Dto课程：

@Getter @Setter
public class CharacterDto implements Serializable {

    private static final long serialVersionUID = 1L;

    private long id;
    private String image;
    private String name;
    private Integer age;
    private BigDecimal weight;
    private String story;
    private List<MovieDetailDto> movies;

    public CharacterDto() {

    }
    public CharacterDto(long id, String image, String name, Integer age, BigDecimal weight, String story) {
        this.id = id;
        this.image = image;
        this.name = name;
        this.age = age;
        this.weight = weight;
        this.story = story;
    }
}

Answer 1

我认为这是Blaze持久性实体视图的完美用例。

我创建了这个库，以便在JPA模型和自定义接口或抽象类定义的模型之间进行简单的映射，比如Spring数据在类固醇上的投影。其思想是，您可以按照自己喜欢的方式定义目标结构（域模型），并通过JPQL表达式将属性（getter）映射到实体模型。

在Blaze Persistence实体视图中，用例的DTO模型可能如下所示：

@EntityView(CharacterEntity.class)
public interface CharacterImageDto {
    @IdMapping
    Long getId();
    String getImage();
}

查询是将实体视图应用于查询的问题，最简单的就是按id进行查询。

CharacterizeMagedTo a=entityViewManager。查找（entityManager，CharacterImageDto.class，id）

Spring数据集成让您可以像使用Spring数据投影一样使用它：https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-数据特征

Page<CharacterImageDto> findAll(Pageable pageable);

最好的部分是，它只会获取实际需要的状态！Blaze持久实体视图也支持映射集合。

@EntityView(CharacterEntity.class)
public interface CharacterDto {
    @IdMapping
    Long getId();
    String getImage();
    Set<MovieDto> getMovies();

    @EntityView(MovieDetailEntity.class)
    interface MovieDto {
        @IdMapping
        Long getId();
        String getName();
    }
}

Answer 2

如果要选择自定义colum，则需要创建自定义dto并将数据加载到其中。不能将自定义数据加载到托管实体中，但可以使用jpql选择到自定义dto中。

package my.class.package;

class Chars{
    private String name;
    private IDONTKNOW image;  
    [other fields, constructors, getters,setters ...]
}

然后您可以查询：

@Query("SELECT new my.class.package.Chars(c.name,c.image) FROM characters c")
Page<Chars> getCharacters(Pageable pageable);

您还可以使用这种查询通过连接不同表（实体）来加载这些表（实体）中的字段。

例如（我的一个旧的，但也许它会帮助你）：

   public class ProgrammerNameAndCity {
       private String name;
       private String city;
   }
    
    @Entity
    @Table(name = "programmer")
    public class Programmer extends BaseEntity {
    
        @Basic
        private String name;
    
        @Column(name = "id_number", nullable = false, unique = true)
        private String idNumber;
    
        //hibernate specific
        @Type(type = "yes_no")
        private Boolean senior;
    
        @Basic
        private Integer salary;
    
        @OneToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
        @JoinColumn(name = "address_id")
        private Address address;
    
    }
    
    @Entity
    @Table
    public class Address extends BaseEntity {
    
        @Basic
        private Integer zip;
    
        @Basic
        private String city;
    
        @Basic
        private String street;
    
        @Column(name = "house_number")
        private Integer houseNumber;
    
        @OneToOne(mappedBy = "address")
        private Programmer programmer;
    
    }
    
   @Query("select new mypackage.ProgrammerNameAndCity(p.name,p.address.city) from Programmer p where  p.idNumber=?1")
   ProgrammerNameAndCity findNameAndCityByIdNumber(String idNumber);

Answer 3

您可以将返回类型更改为“页面”，而不是返回页面

@Query(value = "SELECT name, image FROM characters", countQuery = "SELECT COUNT(name) FROM characters", nativeQuery = true)
Page<Object[]> getCharacters(Pageable pageable);