在JPA Spring Boot中选择特定列

Question

我想从表中获取一些特定列，但我收到了以下错误：无法执行查询；SQL[SELECT name，image FROM characters order by name asc]；嵌套异常为org.hubinate.exception.SQLGrammaException：无法执行查询。

o.h.engine.jdbc.spi.SqlExceptionHelper:SQL错误：0，SQL状态：42703o.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

您可以将返回类型更改为Page<对象[]>：

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

Answer 2

如果你想选择自定义列，你可以创建一个自定义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

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

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

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

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

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

CharacterImageDto a = entityViewManager.find(entityManager, CharacterImageDto.class, id);

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

Page<CharacterImageDto> findAll(Pageable pageable);

最棒的是，它只会获取实际需要的状态！Blaze Persistence实体视图也支持映射集合，例如

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