Write Spring Specification with multiple inner join & other conditions

Question

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.

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.

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.

How can i write this?

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'

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)));

I think your SQL is wrong, your INNER JOIN should be "ON t.id = e.transfer_id". Besides that I think I can help you if you provide more information about the way your entities are modelled. Does your Contract entity have a collection of Transfer? or a collection of Entry? In theory if you want to filter Contracts by transfer status you only need to add the "transferStatus" filter inside your ContractCriteria, and then just use it in your ContractQueryService. — vicpermir, Aug 27 '19 at 15:55
Indeed, I made a typo inside my SQL. Thanks for pointing it, I will fix it. About how the entities are linked: a transfer contains a list of entry, which contains instance of a transfer and of a contract. The contract has not properties linking it to a transfer. So, I must pass by the entry table/entity to get a transfer from a contract. — MrNierda, Aug 28 '19 at 06:24

score 3 · Answer 1 · answered Aug 28 '19 at 19:40

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.

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

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

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;
    }
// ...

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

    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;
    }

The three relevant entities are as follow:

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

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 :)

Thanks for the answer. I will try it. But, it may not help, because the Contract entity has no property `entries`. But, you're right for the rest of entity/property — MrNierda, Aug 29 '19 at 08:56
For the criteria specification to work you have to be able to traverse the entities. If your `Contract` doesn't have a set of entries you should add it, in my opinion. When I get home I can update my answer with the relevalt code inside `Contract.java` to do this. — vicpermir, Aug 29 '19 at 09:03
Thanks, your answer helped me :D I did an error on my request: both joins are LEFT_JOIN. With this correction, I can use a filter on transferStatus as following: `transferStatus.specified=false` to get all contract not linked to a transfer (example). But now, I must find how to get in the same request the contract not linked to a transfer AND the contract whose transferStatus is RECEIVED. If you have any ideas? — MrNierda, Aug 29 '19 at 13:39

Write Spring Specification with multiple inner join & other conditions

1 Answers1

Linked