I am working on some academic task and I'm stuck. The situation is as follows:
I have three simple database tables and Java @Entity classes generated from them.
Table Mark:
STUDENT_ID (PK)
SUBJECT_ID (PK)
MARK
public class Mark implements Serializable {
private BigDecimal mark;
@Id
@Column(name = "STUDENT_ID", nullable = false, insertable = false, updatable = false)
private BigDecimal studentId;
@Id
@Column(name = "SUBJECT_ID", nullable = false, insertable = false, updatable = false)
private BigDecimal subjectId;
@ManyToOne
@JoinColumn(name = "SUBJECT_ID")
private Subject subject;
@ManyToOne
@JoinColumn(name = "STUDENT_ID")
private Student student;
(...)
}
Table Student:
INDEX_NO (PK)
FNAME
LNAME
FACULTY_ID
public class Student implements Serializable {
@Column(name = "FACULTY_ID", nullable = false)
private BigDecimal facultyId;
@Column(nullable = false, length = 20)
private String fname;
@Id
@Column(name = "INDEX_NO", nullable = false)
private BigDecimal indexNo;
@Column(nullable = false, length = 20)
private String lname;
@OneToMany(mappedBy = "student")
private List<Mark> marksList1;
(...)
}
Table Student:
ID (PK)
NAME
public class Subject implements Serializable {
@Id
@Column(nullable = false)
private BigDecimal id;
@Column(nullable = false, length = 60)
private String name;
@OneToMany(mappedBy = "subject")
private List<Mark> marksList;
(...)
}
The task is to obtain two lists:
1. The list of students which does not have marks from all of the subjects yet.
2. The list of subjects from which particular student does not have a mark.
I am not too good at SQL, so I have problem with query construction. It can be solved by any JPA-like way, i.e. JPQL, Criteria API.
So I am asking you for a help.
Thank you for your interest.
I'm not sure if this is correct but maybe this can help you solve your issue. The size() function in JPQL can count a collection.
These are 2 JPQL query's
1)
select m.student
from Mark m
where size(m.subject) <>
size(select s from Subject s)
2)
select s
from Subject s
where s not in
(select m.s from Mark m where m.student.indexNo = :indexNo)
:indexNo is then the student's id that should be set using a parameter.