本机查询中的Spring JPA存储库子查询

小编典典

本机查询中的Spring JPA存储库子查询

spring-boot

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

资料库

@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中运行了查询，并且运行正常。

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

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

阅读 273

2020-05-30

共1个答案

小编典典

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

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

https://zh.wikibooks.org/wiki/Java_Persistence/Tables#Example_mapping_annotations_for_an_entity_with_multiple_tables

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

更新的映射：

@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可以使用的注释：

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
}

2020-05-30