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