JPA select with two conditions

Question

Help me please add two conditions for JPA.

I have the entity Subject (some subject of a teacher – math, biology, programming and so one). Subject has a field: boolean deleted.

@Entity
public class Subject {
    @Id
    private Long id;
    private String name;
    private boolean deleted;

I have the second entity Teacher. It has Set<Subject> subjects – subjects which are attached to the teacher.

@Entity
@PrimaryKeyJoinColumn(name = "person_id")
public class Teacher extends Person {
    private boolean deleted;
    Set<Subject> linkedSubjects;
}

So I need to get list of subjects which are not yet attached to the teacher AND these subjects should not be deleted.

I can do this separately – get not attached or get not deleted. But how get both of them?

    public List<Subject> findSubjectNotInList(List<Subject> teacherSubjects) { // teacherSubjects - List subjects which still attached to a teacher
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Subject> query = cb.createQuery(Subject.class);
        Root<Subject> root = query.from(Subject.class);
     // Get all not attached:
     // query.select(root).where(root.in(teacherSubjects).not());           

     // Get all not deleted         
        query.select(root).where(cb.isFalse(root.get(Subject_.DELETED))); 

        List<Subject> subjects = em.createQuery(query).getResultList(); 
        return subjects;
    }

Answer 1

Вам нужно создавать предикаты для условия WHERE, а затем их объединять. Делать это можно так:

Predicate predicateForRedColor
  = criteriaBuilder.equal(itemRoot.get("color"), "blue");
Predicate predicateForGradeB
  = criteriaBuilder.equal(itemRoot.get("grade"), "B");
Predicate predicateForRedColorAndGradeB
  = criteriaBuilder.and(predicateForRedColor, predicateForGradeB);

Больше можно прочитать здесь

Answer 2

Решение:

public List<Subject> findSubjectNotInList(List<Subject> teacherSubjects)
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Subject> query = cb.createQuery(Subject.class);
Root<Subject> root = query.from(Subject.class);
query.select(root)
    .where(cb.and(
        root.in(teacherSubjects).not(),
        cb.isFalse(root.get(Subject_.DELETED))));
List<Subject> subjects = em.createQuery(query).getResultList();
return subjects;

БЛОГ НА HUSL

JPA select with two conditions

Ответы (2 шт):