JPA CriteriaBuilder ManyToOne and null values

user_2597775 0
Viewed 633

I'm trying to create a Specification for filtering a table. This table has 2 @ManyToOne relationships (that can be null) and I'd like to apply the filter inside them. Like a search function.

Assume this structure

public class X {
   @ManyToOne(targetEntity = A.class, fetch = FetchType.EAGER)
   @JoinColumn(nullable = true, name = "aID")
   private A a;

   @ManyToOne(targetEntity = B.class, fetch = FetchType.EAGER)
   @JoinColumn(nullable = true, name = "bID")
   private B b;
}

Also assume that A and B have a String attribute called name.

So, what I want to make a Specification for X where I can match a String value across the name of A or B.

This was my approach:

public static Specification<X> filterName(String value) {
   return new Specification<X>() {
      public Predicate toPredicate(Root<X> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
    
         Expression<String> aName = root.get("a").get("name").as(String.class);
         Expression<String> bName = root.get("b").get("name").as(String.class);

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

         if (!value.isEmpty()) {
            predicates.add(builder.like(aName, "%" + value + "%"));
            predicates.add(builder.like(bName, "%" + value + "%"));
         }else{
            return null;
         }

         Predicate predicate = builder.or(predicates.toArray(new Predicate[0]));
         return predicate;
      }
   };
}

I was really hoping it will work, but it did not.

What I would like is to display those rows where the name of A OR the name of B is like the input value.

If I enable the logs to display the SQL query this is what I get:

select xentity0_.aId, xentity0_.bId from table_x xtab cross join table_a atab cross join table_b btab where xtab.aId=atab.id and xtab.bId=btab.id and (atab.name like ?) and (btab.name like ?)

I think the problem is with the where xtab.aId=atab.id and xtab.bId=btab.id part. Because as A or B can be null, then not always that condition is met.

Assume this table for X:

ID aId bId
1 null 1
2 1 null
3 2 2

this table for A:

ID name
1 John
2 Lucy

this table for B:

ID name
1 Luke
2 Mike

When looking for %Lu% using the Specification for X what I would like to see is

ID aId bId
1 null 1
3 2 2

because of Lucy in A and Luke in B. Instead what I see is the last row (where both A and B are not null)

many-to-one spring-boot criteria-api jpa
1 Answers

The problem is that, CriteriaBuilder is using cross join by default (what you have mentioned about BTW). That's the reason why it will not get the data where A or B is null. You can get those records by setting Join type to LEFT:

root.join(Entity.childEntity, JoinType.LEFT)

In your case, try to modify your code as below:

Expression<String> aName = root.join("a", JoinType.LEFT).get("name").as(String.class);
Expression<String> bName = root.join("b", JoinType.LEFT).get("name").as(String.class);

Beside of that, I would recommend to use MetaModel for CriteriaBuilder.

To not create and maintain Metamodel classes manually, you can use one of available generators, like Hibernate JPA Metamodel Generator, OpenJPA, EclipseLink or DataNucleus.

I personally prefer Hibernate JPA 2 Metamodel Generator. To use it, the only thing you need to do is to add the dependency to pom.xml file:

<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-jpamodelgen</artifactId>
    <version>5.6.5.Final</version>
    <scope>provided</scope>
</dependency>

and the following code under annotationProcessorPaths:

 <plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-compiler-plugin</artifactId>
    <configuration>
      <annotationProcessorPaths>
        <path>
          <groupId>org.hibernate</groupId>
          <artifactId>hibernate-jpamodelgen</artifactId>
          <version>5.6.5.Final</version>
        </path>
      </annotationProcessorPaths>
    </configuration>
  </plugin>

After that, when you build your application with Maven, Metamodels for all Entitiers will be automatically built.

user_88644830
Related