Specification and predicates collection using AND operator CriteriaBuilder

Question

In order to get a List of elements Applicant from my database, I'm facing an issue when I try to retrieve them.

I know in this example that I'm fetching through a bad method : fetch on String ids of entities but this is subject to a refactor ;)

Here is my specification and the predicates loop with a join :

public static Specification<Applicant> applicantsMatchMobility(final String... mobilities) {
    return new Specification<Applicant>() {

        @Override
        public Predicate toPredicate(Root<Applicant> root, CriteriaQuery<?> query, CriteriaBuilder builder) {

            Collection<Predicate> predicates = new ArrayList<>();

            Join<Applicant, Mobility> applicantMobilityJoin = root.join("mobility");

            for(String mobility : mobilities) {
                predicates.add(builder.equal(applicantMobilityJoin.<Mobility>get("id"), Integer.parseInt(mobility)));
            }

            return builder.and(predicates.toArray(new Predicate[predicates.size()]));

        }

    };
}

It works fine when I pass only one id of Mobility entity but when it's a String ids array > 1 this returns always 0..

Here is my junit test :

 @Test
public void ShouldHaveOneApplicantWhenSearchMobilityMultipleTotallyEquals(){

    String mobilitiesId[] = {"0", "1"};
    List<Applicant> applicantList = applicantRepository.findAll(applicantsMatchMobility(mobilitiesId));
    Assert.assertNotNull(applicantList);
    Assert.assertEquals("Result", 1, applicantList.size());

    Applicant applicant = applicantList.get(0);
    Assert.assertNotNull(applicant);
    Assert.assertEquals("Result", "XBNC", applicant.getFirstName());

}

Here is the generated request from this :

select
    * 
from
    applicant applicant0_ 
inner join
    applicant_mobility mobility1_ 
        on applicant0_.id=mobility1_.id_applicant 
inner join
    mobility mobility2_ 
        on mobility1_.id_mobility=mobility2_.id 
where
    mobility2_.id=0 
    and mobility2_.id=1

Here is my dataset for testing :

    <?xml version='1.0' encoding='UTF-8'?>
<dataset>
    <MOBILITY ID="0" NAME="sud"/>
    <MOBILITY ID="1" NAME="nice"/>
    <MOBILITY ID="2" NAME="cannes"/>
    <MOBILITY ID="3" NAME="nowhere"/>

    <TAGS ID="0" NAME="tags1"/>
    <TAGS ID="1" NAME="tags2"/>
    <TAGS ID="2" NAME="tags3"/>

    <STATUS ID="0" NAME="status1"/>
    <STATUS ID="1" NAME="status2"/>

    <AVAILABILITY ID="0" NAME="availability1"/>

    <APPLICANT ID="0" LAST_NAME="XBNC" FIRST_NAME="XBNC" YEAR="2001" WAGE_CLAIM="50" ID_STATUS="0" ID_AVAILABILITY="0" />
    <APPLICANT ID="1" LAST_NAME="XBN" FIRST_NAME="XBN" YEAR="0" WAGE_CLAIM="70" ID_STATUS="1" ID_AVAILABILITY="0"/>
    <APPLICANT ID="2" LAST_NAME="MI" FIRST_NAME="MI" YEAR="1995" WAGE_CLAIM="0" ID_STATUS="1" ID_AVAILABILITY="0"/>
    <APPLICANT ID="3" LAST_NAME="bisTronomique" FIRST_NAME="bisTronomique" YEAR="-400" WAGE_CLAIM="0" ID_STATUS="0" ID_AVAILABILITY="0"/>

    <APPLICANT_MOBILITY ID_APPLICANT="0" ID_MOBILITY="0"/>
    <APPLICANT_MOBILITY ID_APPLICANT="0" ID_MOBILITY="1"/>

    <APPLICANT_MOBILITY ID_APPLICANT="1" ID_MOBILITY="0"/>
    <APPLICANT_MOBILITY ID_APPLICANT="1" ID_MOBILITY="2"/>

    <APPLICANT_MOBILITY ID_APPLICANT="2" ID_MOBILITY="1"/>

    <APPLICANT_TAGS ID_APPLICANT="0" ID_TAGS="0"/>
    <APPLICANT_TAGS ID_APPLICANT="0" ID_TAGS="1"/>
    <APPLICANT_TAGS ID_APPLICANT="0" ID_TAGS="2"/>

</dataset>

Has you can see in this test I need to get back only the "XBNC" Applicant because he contains the 2 corresponding MobilityeID ..

Answer 1

This is a basic SQL logic problem, and I'm guessing you are trying to retrieve 2 rows corresponding to mobiliy IDs 0 and 1: the where clause (critria) in your current code builds [where id = '0' and id = '1'], which will always return no rows as a particular table field cannot be equal to two values at the same time.
Instead of and(), you need to use the or() method, which will build a where clause like [where id = '0' or id = '1']. Provided exactly one row and one row only has mobilityID set to 1, and one row and one row only has mobilityID set to 0, then you should obtain two rows back from running your query.

Update:
After your question edit, I think I can see your your issue lies. This is still a SQL problem, but it means your query need to be slightly more sophisticated. You need to obtain a query that looks like:

select * from applicant applicant0_ inner join applicant_mobility mobility1_ on applicant0_.id=mobility1_.id_applicant inner join mobility mobility2_ on mobility1_.id_mobility=mobility2_.id where exists ( select * from applicant_mobility where id_applicant = applicant0_.id and id_mobility = 1; ) and exists ( select * from applicant_mobility where id_applicant = applicant0_.id and id_mobility = 0; )
Note the join between applicant and applicant_mobility in the sub-queries. This is effectively saying: for each applicant instance the main query finds, verify that it is in a set where there is at least one corresponding applicant_mobility instance with mobility set to 0, and another where mobility is set to 1.
So the loop in your function needs to create sub-queries per mobility id provided, and then use the builder and() and exists() to put it all together.

Answer 2

Add to what @NotSoOldNick has specified. What you seems to require is a IN Clause to retrieve only matching Applicant with matching Mobility. You can change it to the following or somewhat similar.

public static Specification<Applicant> applicantsMatchMobility(final String... mobilities) {
    return new Specification<Applicant>() {

        @Override
        public Predicate toPredicate(Root<Applicant> root, CriteriaQuery<?> query, CriteriaBuilder builder) {

            Collection<Predicate> predicates = new ArrayList<>();

            Join<Applicant, Mobility> applicantMobilityJoin = root.join("mobility");

            Expression<String> idExp = applicantMobilityJoin.<Mobility>get("id");

            return builder.and(idExp.in(mobilities));

        }

    };
}