0

I am trying to create a Hibernate specification with parentheses but was not able to do it.

I have many specifications. My problem is with the following specification:

public static Specification<ListingEntity> locations(List<String> locations) {
    if (locations == null || locations.isEmpty()) {
        return null;
    } else {
        return (root, query, builder) -> {
            Join<ListingEntity, GeoLevelTwoEntity> joinGeoLevelTwo = root.join("geoLevelTwoEntity");
            Join<GeoLevelTwoEntity, GeoLevelOneEntity> joinGeoLevelOne = joinGeoLevelTwo.join("geoLevelOneEntity");

            Predicate predicate = builder.conjunction();
            for (String location : locations) {

                if (location.contains("_")) {
                    String[] locationParts = location.split("_");
                    Predicate predicateLevelOne = builder.equal(joinGeoLevelOne.get("geographyLevelOne"), locationParts[1]);

                    Predicate predicateLevelTwo = joinGeoLevelTwo.get("geographyLevelTwo").in(locationParts[0]);
                    Predicate locationPredicate = builder.and(predicateLevelOne, predicateLevelTwo);

                    predicate = builder.or(predicate, locationPredicate);
                }

            }
            return predicate;

        };
    }
}

It creates the following SQL script:

from 
  listings le1_0 
  join geo_level_two glte1_0 on glte1_0.id = le1_0.geography_level_two_id 
  join geo_level_one gloe1_0 on gloe1_0.id = glte1_0.geography_level_one_id 
where 
  1 = 1 
  or gloe1_0.geography_level_one = ? 
  and glte1_0.geography_level_two in (?) 
  or gloe1_0.geography_level_one = ? 
  and glte1_0.geography_level_two in (?) 
order by 
  (
    select 
      0
  ) offset ? rows fetch first ? rows only

I want it to create the following way:

where 
  1 = 1 
  and ( gloe1_0.geography_level_one = ? 
  and glte1_0.geography_level_two in (?) 
  or gloe1_0.geography_level_one = ? 
  and glte1_0.geography_level_two in (?) )
order by

I have other specifications that create and conditions like and floor = 2 etc. Thus I cannot think it without using parentheses. I think it needs to look like the following:

(geographyL1 = 'New York' and geographyL2 = 'USA' or geographyL1 = 'London' and geographyL2 = 'UK') and floor = 2 and price > 100000

I have no issue with other specifications that generate the following SQL. Each condition has its own separate specification:

floor = 2 and price > 100000 and numberOfRooms = 2

What should I do so that my specification for location lies inside parentheses?

Improve this question
2
  • What should be the effect when there are no locations upon which to filter? Is everything accepted, or is nothing accepted? Commented Sep 29 at 18:00
  • If there is no filter is entered everything should be listed/returned. Commented Sep 29 at 19:19

2 Answers 2

Reset to default
1

It creates the following SQL script:

[...]

where 
  1 = 1 
  or gloe1_0.geography_level_one = ? 
  and glte1_0.geography_level_two in (?)

[...]

Well yes, it does. For the predicate, you start with builder.conjunction(), which corresponds to the 1 = 1, and then you add zero or more additional pieces via builder.or(). Of course the top-level operators are all OR.

builder.conjunction() is intended to provide a starting point for an arbitrary sequence of predicates joined with AND, whereas for a similar sequence joined with OR, you should start with builder.disjunction() instead. Do bear in mind, however, that these two have opposite logical sense when there are zero (other) predicates in the sequence.

If you want to reject everything when there are no locations on which to filter

... or if you're willing to assume that that that situation will never arise, then you could just switch from builder.conjunction() to builder.disjunction().

            Predicate predicate = builder.disunction();
            for (String location : locations) {
                // ...
            }
            return predicate;

I would expect the generated criteria to be something like

where 
  1 <> 1 
  or gloe1_0.geography_level_one = ? 
  and glte1_0.geography_level_two in (?) 
  or gloe1_0.geography_level_one = ? 
  and glte1_0.geography_level_two in (?)

, which, you should satisfy yourself, is logically equivalent to what you say you are looking for. At least when there are any locations to filter on.

Otherwise

... you need a special case when there are no locations. One way to do that would be to proceed as above, but at the end,

            return predicate.getExpressions().isEmpty()
                    ? builder.conjunction()
                    : predicate;

(Or possibly you need predicate.getExpressions().size() < 2 instead of .isEmpty(). Or you can use any other mechanism you choose to determine whether any locations have been added to the predicate.)

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

But in this case location New York and USA or London and UK and floor = 2 will return unexpected results. That is why I was asking for parentheses. (location New York and location USA or location London and location UK) and floor = 2
If you're writing SQL code directly then you worry about parentheses. If you're using a CriteriaBuilder then you perform operations at that level of abstraction, and let the system figure out the parentheses. In particular, if you end up with the kind of issue you now describe then it arises from using an inappropriate mechanism to combine the predicate produced by this method with your floor predicate. That mechanism not having been presented in the question, and such a problem not even having been mentioned, these are out of scope.
I was unable to write the correct CriteriaBuilder or predicate.
This answer already presents the only changes required to your original code to produce a predicate with the required meaning. Again, if you have issues around combining that predicate with others then that's a separate question. CriteriaBuilder does not have or need provision for explicit parentheses.
0 
return (root, query, builder) -> {
    Join<ListingEntity, GeoLevelTwoEntity> joinGeoLevelTwo = root.join("geoLevelTwoEntity");
    Join<GeoLevelTwoEntity, GeoLevelOneEntity> joinGeoLevelOne = joinGeoLevelTwo.join("geoLevelOneEntity");

    HashMap<String, Byte> specialCities = new HashMap<>();
    specialCities.put("Define special ones here", (byte) 8);
    specialCities.put("and here etc", (byte) 9);

    List<Predicate> groupPredicates = new ArrayList<>();
    for (String location : locations) {

        if (location.contains("_")) {
            String[] locationParts = location.split("_");
            String country = locationParts[0];
            String bigCity = locationParts[1];

            Predicate levelOnePredicate = builder.equal(joinGeoLevelOne.get("geographyLevelOne"), bigCity);
            Predicate levelTwoPredicate = builder.equal(joinGeoLevelTwo.get("geographyLevelTwo"), country);

            List<Predicate> innerPredicates = new ArrayList<>();
            innerPredicates.add(levelOnePredicate);
            innerPredicates.add(levelTwoPredicate);

            Predicate groupPredicate = builder.and(innerPredicates.toArray(innerPredicates.toArray(new Predicate[0])));
            groupPredicates.add(groupPredicate);
        } else if (specialCities.containsKey(location)) {
            Predicate levelOnePredicate = builder.equal(joinGeoLevelOne.get("geographyLevelOne"), location);
            groupPredicates.add(levelOnePredicate);
        } else {
            Predicate smallerCityPredicate = builder.equal(joinGeoLevelTwo.get("geographyLevelTwo"), location);
            groupPredicates.add(smallerCityPredicate);
        }

    }
    return builder.or(groupPredicates.toArray(new Predicate[0]));

};
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.