How to handle null value in one to many relation by using Criteria Builder?

Question

How to handle null value in One to many relation by using Criteria Builder.

I am using criteria builder from my API to get the result.

The problem which I am facing is When I am passing the any text which is available in student_group table I am getting the result. When I am giving null so my lastRollResult is null in student table I am getting result but when I am passing combination of both text and null I am not getting result.

My APIs are like this

http://localhost:8080/student?query=grade:5,lastRollResult%40SUCCESS%23FAILURE

In this case I am able to get the data

http://localhost:8080/student?query=grade:5,lastRollResult%null

In this case I am not able to get the data

Now here is what I am trying and how my code looks like.

My controller .

Controller class :

  @RequestMapping(method = RequestMethod.GET)
  public Page<StudentDTO> index(MySpecificationsBuilder builder,
                                @PageableDefault(value = 25, page = 0) Pageable pageable) {
    Specification<Student> spec = builder.build();
    Page<Student> stu = studentService.findAll(spec, pageable);

    /** here some modification on retuen */
  
  }

You can notice in Controller I am building MySpecificationsBuilder where all the criteria is getting set. Here is brief about MySpecificationsBuilder

     public class MySpecificationsBuilder extends BaseSpecification<Student> {
    
     public  MySpecificationsBuilder(final SearchCriteria criteria) {
        super(criteria);
      }

 
  @Override
  protected Expression<String> getPath(SearchCriteria criteria, Root<Student> root) {
    /** some other conditions */
   
 if (criteria.getKey().equals("lastRollResult"))  {    
   if (!"null".contains(criteria.getValue())) {
          Join results = root.join("lastRoll", JoinType.INNER); // In case of text (JoinType.INNER is enum of Inner.
          return results.get("result");
      }else{
        return root.get("lastRoll”); // in case of null
      }
    }
    return root.get(criteria.getKey());
  }

  /** below toPredicate methods  */

}

In MySpecificationsBuilder I am doing some other calculation as resultConstant and toPredicate the resiults.

Here as you can see in case of text I am doing inner join but in case of null I am directly getting the data. So That is the reason I am getting this response. How to fix this values.

Here is my tables details.

@Entity
@Data
@DiscriminatorFormula("case when entity_type is null then ‘Student’ else entity_type end")
@DiscriminatorValue("Student")
public class Student extends AbstractStudent {
  @ManyToOne
  @Fetch(value = FetchMode.SELECT)
  @JoinColumn(name = "created_by_id", insertable = false, updatable = false)
  @EqualsAndHashCode.Exclude
  @ToString.Exclude
  @NotFound(action = NotFoundAction.IGNORE)
  User createdBy;
  @Transient
  private String name;

  @Transient
  private List<String> tags;
}

And AbstractStudent

@RequiresAudit
@Log4j2
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@Data
@Entity
@Table(name = "student_group")
@DiscriminatorColumn(name = "entity_type", discriminatorType = DiscriminatorType.STRING)
public class AbstractStudent extends BaseModel {

  @OneToMany(mappedBy = "student", fetch = FetchType.LAZY)
  @EqualsAndHashCode.Exclude
  @ToString.Exclude
  List<StudentMapping> studentMappings;
  
  @OneToMany(mappedBy = "student", fetch = FetchType.LAZY)
  @EqualsAndHashCode.Exclude
  @ToString.Exclude
  List<StudentDataMapping> studentDataMapping;
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;
  @AuditColumn
 
  @Column(name = "last_roll_id")
  private Long lastRollId;

  
  @OneToMany(mappedBy = "student", fetch = FetchType.LAZY)
  @EqualsAndHashCode.student
  @ToString.Exclude
  private Set<TagUse> tagUses;
  @ManyToOne
  @Fetch(value = FetchMode.SELECT)
  @JoinColumn(name = "last_roll_id", referencedColumnName = "id", insertable = false, updatable = false)
  @EqualsAndHashCode.Exclude
  @ToString.Exclude
  @NotFound(action = NotFoundAction.IGNORE)
  private Result lastRoll;

  /** some other column defination */
}

In My Abstract column you can I have one to one mapping.

Is there any solution like group or any where clause or any other thing by which I can club these two parameter to get result for null as well as text.

Answer 1

It's not clear how do you want to handle the relationship in the query results. If you want to return students data regardless of the existence of the related entity, I think you should try with left join:

    Join results = root.join("lastRoll", JoinType.LEFT); 
    return results.get("result");

Maybe you want to achieve something like this:

if (criteria.getKey().equals("lastRollResult"))  {
    Join results = root.join("lastRoll", JoinType.LEFT); 
    return results.get("result");
}

EDIT:

If this solution is working for you, it means that you are implementing a search for the entity Student, based in certain criteria that may or may not include the related entity Result. To achieve that you can apply a LEFT join to the criteria dynamically, based on the existing criteria inputs.

You could return always the join results, and still, it will work just fine, and you can apply all the predicates for both entities:

    @Override
    protected Expression<String> getPath(SearchCriteria criteria, Root<Student> root) {
      // Some other conditions
  
      Join results = root.join("lastRoll", JoinType.INNER);
      return results.get("result");
    }

However, the generated SQL query will be less performant in some cases. If you handle join inclusion only when it's required you will get better queries and better performance, however, you will need to apply the predicates related to Result entity dynamically too:

public class MySpecificationsBuilder extends BaseSpecification<Student> {

    public MySpecificationsBuilder(final SearchCriteria criteria) {
        super(criteria);
    }

 
    @Override
    protected Expression<String> getPath(SearchCriteria criteria, Root<Student> root) {
      // Some other conditions
   
      // Check if the search criteria contains Result entity
      if (criteria.getKey().equals("lastRollResult")) {   
        Join results = root.join("lastRoll", JoinType.INNER);
        return results.get("result");
      }
      // Result entity is no present in the search criteria
      return root.get(criteria.getKey());
  }

//Below toPredicate methods

}