本机查询中的Spring JPA存储库子查询 [英] Spring JPA Repository Sub-Query In Native Query

问题描述

我试图在存储库方法上运行本机查询，以便它返回带有一些计数的结果.使用JPQL太复杂了，所以我选择了本机查询.

I am trying to run a native query on a repository method so that it returns the results with some counts. It was too complicated to do with JPQL, so I opted for a native query instead.

存储库

@RepositoryRestResource(collectionResourceRel="projects", path="projects")
interface ProjectRepository extends BaseRepository<Project, Long>, ProjectRepositoryCustom {

    @Query(
        value="SELECT p.id, p.user_id, p.title, p.description, p.created_on, p.version,(SELECT COUNT(0) FROM projectparts WHERE project_id = p.id) AS parts,(SELECT COUNT(0) FROM requests WHERE project_id = p.id) AS requests FROM projects AS p ORDER BY ?#{#pageable}",
        countQuery="SELECT COUNT(0) FROM projects",
        nativeQuery=true
    )
    Page<Project> findAll(Pageable pageable)

}

该实体具有2个用@Transient注释的属性，因此该信息不会持久保存到数据库中.除了2个瞬态属性(其值均返回null)以外，所有数据均恢复正常.当我从控制台复制查询并将其粘贴到MySQL Workbench中时，结果如预期的那样，我看到了所需的计数.无论如何，不​​确定是否需要执行其他任何操作才能使本机查询用作注释.我在子查询SELECT 55 FROM...中硬编码了一个值，只是看它是否与计数有关，并且仍然返回null.我在Workbench中运行了查询，效果很好.

The entity has 2 properties annotated with @Transient so that the info is not persisted to the database. All the data comes back fine except the 2 transient properties which return null for the values. When I copy the query from the console and paste it in MySQL Workbench, the results are as expected and I see the counts that I need. Anyhow, not sure if there is anything else that needs to be done in order to get this native query to work as an annotation. I hard coded a value in the sub-query SELECT 55 FROM... just to see if it was a problem with the count and it still returned as null. I ran the query in Workbench and it works fine.

我尝试从Integer, Long, BigInteger, long, int...更改瞬态属性类型，但没有一个起作用.由于我使用的是Groovy，所以我还尝试了def让Groovy推断类型，但这也不起作用.

I've tried changing the transient property type from Integer, Long, BigInteger, long, int... and none of that made a difference. Since I'm using Groovy, I also tried def to let Groovy infer the type and that didn't work either.

我也尝试从终端运行项目，但仍然无法正常工作.我已经在Mac和Linux上进行过尝试，但无法显示计数结果.

I also tried running the project from the terminal instead and it still didn't work. I've tried it on a Mac and Linux and had no luck with displaying the results of the counts.

推荐答案

这将不起作用.您可以使用SQLConstructorExpression，但是返回的实例将不受管理，这是一个主要缺点.

This will not work. You could use an SQLConstructorExpression however the returned instances would be unmanaged which is a major drawback.

更好的选择是创建一个简单的DB视图，其中包含项目的摘要信息.您可以使用JPA的@SecondaryTable功能将Project实体映射到其表和关联的摘要视图.

An better option is to create a simple DB view which holds the pieces of summary info for the Project. You can them map the Project entity to both it's table and the associated summary view using the @SecondaryTable functionality of JPA.

https://en.wikibooks.org/wiki/Java_Persistence/Tables#Example_mapping_annotations_with_multi_tables

另一个好处是，您可以像对其他任何属性一样对汇总值进行排序和查询.

An added benefit is that you can sort and query on the summary values as for any other property.

更新的映射:

@Entity
@Table(name = "projects")
@SecondaryTable(name = "projects_summary_vw")
public class Project{

   //use Integer rather than int to avoid issue outlined here:
   //http://stackoverflow.com/a/37160701/1356423

    @Column(name  = "parts", table = "projects_summary_vw", 
          insertable="false", updateable="false")
    private Integer partsCount;

    @Column(name  = "requests", table = "requestsCount" 
          insertable="false", updateable="false")
    private Integer requestsCount;

    //other mappings as required
}

不需要自定义查询:

@RepositoryRestResource(collectionResourceRel="projects", 
        path="projects")
interface ProjectRepository extends BaseRepository<Project, Long>, 
        ProjectRepositoryCustom {

}

另一种不符合JPA要求的解决方案可能是使用某些特定于供应商的扩展名而不是视图.例如，Hibernate具有可以使用的@Formula批注:

An alternative non-JPA compliant solution may be to use some vendor specific extension rather than a view. Hibernate for example has an @Formula annotation which could be used:

https://docs.jboss .org/hibernate/orm/5.1/javadocs/org/hibernate/annotations/Formula.html

@Entity
@Table(name = "projects")
public class Project{

    @Formula("my count query as native sql")
    private Integer partsCount;

    @Formula("my count query as native sql")
    private Integer requestsCount;

    //other mappings as required
}

这篇关于本机查询中的Spring JPA存储库子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！