编写具有多个内部联接&的Spring规范其他条件 [英] Write Spring Specification with multiple inner join & other conditions

问题描述

我正在研究使用JHipster生成的Spring和Java项目. 我遇到了Spring的以下项目的问题:规范，标准和项目列表的筛选.

I am working on a project Spring and Java, generated using JHipster. I encounter a problem with the following items of Spring: Specification, Criteria, and filtering a list of items.

我有一个正常运行的SQL查询，而不是我想翻译"为Spring规范.但是，我找不到方法，因为该查询可在3个表上运行，并且有2个条件.

I have a functioning SQL query, than I want to "translate" as a Spring Specification. But, I don't find how to do it, as the query works on 3 tables, and has 2 conditions.

基本上，这项工作涉及3个表格:合同，转让和付款.入口.一个合同可以在一个或多个传输中，并且一个传输包含1到几个合同.条目是这两个表之间的链接(条目包含contract_id和transfer_id).

Basically, the work concerns 3 tables: contract, transfer & entry. A contract can be inside one or several transfers, and a transfer contains 1 to several contracts. An entry is the link between these two tables (an entry contains a contract_id and a transfer_id).

需要使用规范来获取与未收到的转移相关的合同清单.

The needs is to use the specification to get a list of contracts which are linked to a not received transfer.

我怎么写这个?

我已经查看了几个stackoverflow帖子和问题，但是我找到了仅两个表之间的联接或如何在实体上编写规范的答案.

I have already looked at several stackoverflow posts and questions, but I found answers for a join between only two tables, or how to write specifications on an entity.

在这里，我要翻译的查询:

Here, the query I want to translate:

SELECT c.* 
FROM contract AS c
LEFT JOIN entry AS e ON e.contract_id = c.id 
INNER JOIN transfer AS t ON t.id = e.transfer_id
AND t.status != 'RECEIVED'

在这里，是由JHipster创建的现有合同规范的示例在这里您可以看到JHipster规范如何用作过滤器. 我想将新规范添加到已经存在的规范中

Here, an example of the existing Contract Specification created by JHipster Here you can see how the JHipster specification are used as filter. I want to add the new specification inside the already existing ones

private Specification<Contract> createSpecification(ContractCriteria criteria) {
        Specification<Contract> specification = Specification.where(null);

        if (criteria == null) {
            return specification;
        }

        return specification.and(buildStringSpecification(criteria.getContractNumber(), Contract_.contractNumber))
            .and(buildSpecification(criteria.getStatus(), Contract_.status))
            .and(buildSpecification(
                criteria.getStoreCode(),
                root -> root.join(Contract_.store, JoinType.LEFT).get(Store_.code)));

推荐答案

好的，所以我想我或多或少了解了实体的设计方式.我使用以下JDL创建了一个快速项目:

Okay, so I think I understood more or less how your entities are designed. I created a quick project using the following JDL:

entity Contract {contractNumber String, status String}
entity Transfer {status String}
entity Entry {}

relationship OneToMany {
    Transfer{entries} to Entry{transfer},
    Contract{entries} to Entry{contract}
}

service all with serviceClass
filter all

这不是我设计实体的方式，而是您在项目中使用它们的方式，并且尽可能地简洁.

This is not how I would have designed the entities, but this is how you have them on your project and also as succinct as I could manage.

在一个新的jhipster项目中导入此JDL之后，您的要求是按转移状态过滤合同.

After importing this JDL in a fresh jhipster project your requirement is to filter contracts by transfer status.

我们要做的第一件事是在ContractCriteria.java中创建一个新的StringFilter(为简单起见，我的状态只是一个String，如果您的状态是Enum，则需要创建相应的枚举过滤器).

The first thing we need to do is create a new StringFilter in your ContractCriteria.java (my status is just a String for simplicity, if yours is an Enum then you need you create the corresponding enum filter).

ContractCriteria.java

ContractCriteria.java

public class ContractCriteria implements Serializable, Criteria {
// ...
    private StringFilter transferStatus;

    public ContractCriteria(ContractCriteria other){
        // ...
        this.transferStatus = other.transferStatus == null ? null : other.transferStatus.copy();
    }
// ...
    public StringFilter getTransferStatus() {
        return transferStatus;
    }

    public void setTransferStatus(StringFilter transferStatus) {
        this.transferStatus = transferStatus;
    }
// ...

请记住，也将您的新过滤器添加到hashCode()和equals()中.实施新的过滤器后，您只需在查询服务中使用它即可.

Remember to add your new filter to the hashCode() and equals() too. Once the new filter is implemented you just have to use it in your query service.

ContractQueryService.java

ContractQueryService.java

    protected Specification<Contract> createSpecification(ContractCriteria criteria) {
        Specification<Contract> specification = Specification.where(null);
        if (criteria != null) {
            // ...
            if (criteria.getTransferStatus() != null) {
                specification = specification.and(buildSpecification(criteria.getTransferStatus(),
                    root -> root.join(Contract_.entries, JoinType.LEFT)
                                .join(Entry_.transfer, JoinType.INNER)
                                .get(Transfer_.status)));
            }
        }
        return specification;
    }

三个相关实体如下:

• Contract具有属性:Set<Entry> entries
• Entry具有属性:Transfer transfer和Contract contract
• Transfer具有属性:Set<Entry> entries

• Contract has a property: Set<Entry> entries
• Entry has properties: Transfer transfer and Contract contract
• Transfer has a property: Set<Entry> entries

为了快速开发，Swhip附带了jhipster，因此您可以实时测试所有API(具有管理员权限的/admin/docs).我把客户端留给你:)

For quick development, jhipster comes with Swagger so you can test all your APIs live (/admin/docs with admin privileges). I leave the client side to you :)

这篇关于编写具有多个内部联接&的Spring规范其他条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！