在Spring Data Rest项目中使用任意查询作为投影 [英] Using arbitrary query as projection in spring data rest project
问题描述
在某些存储库中如何使用任意SQL查询(我的意思是本机SQL查询)?我的实际问题是:
How it is possible to use arbitrary sql query (I mean native sql query) in some repository? My actual problem is this:
@Data //lombok thing
@Entity
public class A extends AuditModel {
private long id;
private String name;
@OneToMany(mappedBy="a") //Comments.a is owning side of association, i.e. comments table does have column called a_id as foreign key
@ToString.Exclude
private Set<Comments> comments = new HashSet();
@OneToMany(mappedBy="a") //SimpleFile.a is owning side of association
private Set<SimpleFile> comments = new HashSet();
}
比我有我的存储库，该存储库使用HAL + json表示形式展示了不错的Crud接口.我正在尝试通过一些投影/视图来丰富它，特别是由于Web UI在单个请求中加载一页数据.我知道摘录和预测，但是它们似乎不够强大.
Than I have my repository, which exposes nice crud interface using HAL+json representation. I am trying to enrich it with some projection/view particularly due to web UI to load one page data in single request. I am aware of excerps and projections, but they seems not to be enough powerful.
@Repository
@RepositoryRestResource
@Transactional(readOnly = true)
public interface ARepository extends PagingAndSortingRepository<A, Long> {
Page<A> findByNameContaining(String namePart, Pageable pageable);
@Query(
value = "SELECT a.name,\n" +
"(SELECT CAST(count(ac.id) AS int) FROM COMMENTS ac WHERE ac.a_id = a.id),\n" +
"(SELECT listagg(asf.id) FROM SIMPLE_FILES asf WHERE asf.a_id = a.id)\n" +
"FROM AS a\n" +
"WHERE a.id = :id",
nativeQuery = true
)
Optional<ACustomPage42DTO> getByIdProjectedForScreen42(Long id);
}
我也尝试过使用JPQL，但是在这里我遇到了获取联接的问题(因为我对JPQL不熟悉).我上次的评估查询是这样的:
I have also tried to use JPQL, but there I had problem with fetch join (as I am not familiar with JPQL). My last evaluation query was something like this:
@Query("SELECT new sk.qpp.qqq.documents.projections.ACustomPage42DTO(" +
"a " +
"(SELECT CAST(count(ac) AS int) FROM COMMENTS ac WHERE ac.a = a)" +
")\n" +
"FROM A a\n" +
"LEFT JOIN FETCH a.simpleFiles\n" +
"WHERE a.id = :id"
)
我想获得一些一般性建议，以了解哪种方法最好地实现在DTO中返回的自定义和复杂查询(理想情况下，在需要时提供一些特定的操作链接).
I would like to get some general advice about what approach is best to implement custom and complex query to be returned in DTO (ideally with some specific links to actions when needed).
PS:实现接口并返回简单(原始)数据.还可以使用JPQL创建自定义DAO实例(例如，具有简单类型和类型为 A 的单个实例).给定实体端点的搜索方法中确实出现了使用给定查询方法的方法.我想拥有一些更合理的东西，所以我想拥有在春季数据剩余项目中定义的投影项目.
PS: Implementing interface and returning simple (primitive) data works. Also using JPQL to create custom DAO instance works (with simple types and with single instance of type A for example). Method for using given query method does appear in search methods of given entity endpoint. I would like to have something more reasonable, so I would like to have projection as defined in spring data rest project.
我的DTO对象完全在我的控制之下.我更喜欢它使用来自项目lombok的 @Value 或 @Data 批注，但这不是必需的.我还尝试了这些版本的DTO定义(使用接口可用于简单数据，类似地，类可用于简单数据).
I have my DTO object fully under my control. I prefer it to use @Value or @Data annotation from project lombok, but it is not a need. I have tried also these versions of DTO definition (using interface works for simple data and similarly class works for simple data).
interface ACustomPage42DTO {
String getName();
long getCommentsCount();
Object getAsdf();
}
或者使用具有一些好处的等效类，例如可能使用自定义toString()方法，或对计算数据使用一些自定义getter:
Or using equivalent class with some bonus, like custom toString() method possible, or some custom getter for computed data:
@Value //lombok thing, imutable "POJO"
public class ACustomPage42DTO {
String name;
long commentsCount;
Set<SimpleFile> simpleFiles;
public ACustomPage42DTO(A a, long count) {
// constructor used by JPQL, if it works
name = a.getName();
this.commentsCount = count;
this.simpleFiles = a.getSimpleFiles(); // should be already fetched, due to fetch join in JPQL
}
}
两种工作方法都可以使用搜索" URL(而不是投影)来调用.我在网址 http://localhost:9091/api/上看到我的方法 getByIdProjectedForScreen42 a/搜索列表.我想使用它(我认为这是正确"的方式) http://本地主机:8080/api/a?projection = ACustomPage42DTOProjection .
Both working approaches can be called using "search" url, instead of projection. I see my method getByIdProjectedForScreen42 on url http://localhost:9091/api/a/search listing. I would like to use it like (I think that is the "right" way) http://localhost:8080/api/a?projection=ACustomPage42DTOProjection .
推荐答案
问题非常广泛，涉及几个方面:
Question is quite broad and touches couple of aspects:
- 使用
@Query的自定义JPA存储库方法 - 在您的
@Query中选择结果 - 将
@Query结果映射到接口 - 通过
@RepositoryRestResource公开新的存储库方法
- custom JPA repository method using
@Query - selecting results in your
@Query - mapping
@Queryresults to an interface - exposing new repository method through
@RepositoryRestResource
TLDR:写了一个有关几个基本测试的例子 https://github.com/ivarprudnikov/test-spring-jpa-repository-query-exposed-through-http
TLDR: wrote an example of what is talked about with couple of basic tests https://github.com/ivarprudnikov/test-spring-jpa-repository-query-exposed-through-http
正如您已经提到的那样，它非常简单，只需使用 @Query 注释一个方法，并确保您的返回类型与查询所返回的内容相对应，例如:
As you have mentioned it is quite straightforward, just annotate a method with @Query and make sure your return type corresponds to what is being returned from the query, eg:
public interface FooRepository extends JpaRepository<FooEntity, Long> {
@Query(nativeQuery = true, value = "select f from foo f where f.name = :myParam")
Optional<FooEntity> getInSomeAnotherWay(String myParam);
}
在您的 @Query
中选择结果您已经给出了一个示例，但为了简化和简化，我将对其进行简化.
selecting results in your @Query
You have given an example already but I'll simplify to make it easier and shorter.
给出实体 FooEntity.java 和 BarEntity.java :
@Entity
@Table(name = "foo")
public class FooEntity {
@Id
@Column(name = "id", unique = true, nullable = false)
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "name", nullable = false)
private String name;
@OneToMany(mappedBy = "foo")
private Set<BarEntity> bars = new HashSet<>();
// getter setters excluded for brevity
}
@Entity
@Table(name = "bar")
public class BarEntity {
@Id
@Column(name = "id", unique = true, nullable = false)
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "name", nullable = false)
private String name;
@ManyToOne(targetEntity = FooEntity.class)
@JoinColumn(name = "foo_id", nullable = false, foreignKey = @ForeignKey(name = "fk_bar_foo"))
private FooEntity foo;
// getter setters excluded for brevity
}
我们现在要返回包含 FooEntity.name 和 FooEntity.bars 的计数的自定义结果集:
We want now to return custom result set which contains FooEntity.name and count of FooEntity.bars:
SELECT f.name as name, count(b.id) as barCount FROM foo f, bar b WHERE f.id = :id AND b.foo_id = :id
+-----------------+----------+
| name | barCount |
+-----------------+----------+
| Jonny tables | 1 |
+-----------------+----------+
将 @Query 结果映射到接口
要映射以上结果集，我们需要一个接口，使吸气剂很好地反映出所选择的内容:
mapping @Query results to an interface
To map above result set we need an interface where getters nicely reflect what is being selected:
public interface ProjectedFooResult {
String getName();
Long getBarCount();
}
现在，我们可以将存储库方法重写为:
Now we can rewrite our repository method to:
@Query(nativeQuery = true,
value = "SELECT f.name as name, count(b.id) as barCount FROM foo f, bar b WHERE f.id = :id AND b.foo_id = :id")
Optional<ProjectedFooResult> getByIdToProjected(Long id);
通过 @RepositoryRestResource
公开新的存储库方法对此我不是很熟悉，但是在添加了 org.springframework.data:spring-data-rest-hal-browser 依赖项之后，我得到了一个不错的接口，该接口在对存储库进行注释后公开了可用的方法. @RepositoryRestResource .对于包含上述详细信息的给定存储库:
exposing new repository method through @RepositoryRestResource
I am not very familiar with this but after adding org.springframework.data:spring-data-rest-hal-browser dependency I got this nice interface that exposed available methods after repository was annotated with @RepositoryRestResource. For a given repository which contains above mentioned details:
@RepositoryRestResource(path = "foo")
public interface FooRepository extends JpaRepository<FooEntity, Long> {
@Query(nativeQuery = true, value = "SELECT f.name as name, count(b.id) as barCount FROM foo f, bar b WHERE f.id = :id AND b.foo_id = :id")
Optional<ProjectedFooResult> getByIdToProjected(Long id);
}
在本地运行时，该方法将通过 http://localhost:8080/foo/search/getByIdToProjected?id = 1 公开.
the method will be exposed through http://localhost:8080/foo/search/getByIdToProjected?id=1 when running locally.
如上所述，参考实现在Github上https://github.com/ivarprudnikov/test-spring-jpa-repository-query-exposed-through-http
As mentioned above the reference implementation is on Github https://github.com/ivarprudnikov/test-spring-jpa-repository-query-exposed-through-http
这篇关于在Spring Data Rest项目中使用任意查询作为投影的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
