我有四个描述数据库的表:
讲师

@Entity
@Table(name = "LECTURER")

public class Lecturer
{
    private static final long            serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column(name = "NAME")
    private String name;

    @Column(name = "NOBEL_PRICE_YEAR")
    private int nobel_price_year;
}```


报名(使用讲座和学生)

@Entity
@IdClass(EnrollmentId.class)
@Data
public class Enrollment
{
   @Id
   @JoinColumn(name = "lecture_id", referencedColumnName = "id")
   @ManyToOne
   private Lecture lecture;


   @Id
   @JoinColumn(name = "student_id", referencedColumnName = "id")
   @ManyToOne
   private Student student;

   @Id
   private int year;

   @Column(nullable = false, columnDefinition = "TINYINT(1)")
   private boolean exam_taken;
   @Column(nullable = false, columnDefinition = "TINYINT(1)")
   private boolean exam_passed;
}


自复合主键以来具有ID类

public class EnrollmentId implements Serializable
{

    @Id
    @JoinColumn(name = "lecture_id", referencedColumnName = "id")
    @ManyToOne
    private int lecture;

    @Id
    @JoinColumn(name = "student_id", referencedColumnName = "id")
    @OneToOne
    private int student;

    private int year;
}


学生

@Entity
@Table(name="STUDENT")
@Data
@NamedQuery(name = "Student.findAll", query = "SELECT s FROM Student s")
public class Student
{
    private static final long           serialVersionUID = 1L;

    @Id
    @Column(name="ID", nullable=false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column(name="FIRSTNAME", nullable=false)
    private String firstname;

    @Column(name="LASTNAME", nullable=false)
    private String lastname;

    @Column(name="YEAR_OF_BIRTH")
    private int yearOfBirth;

    @Column(name="GENDER")
    @Enumerated(EnumType.STRING)
    private Gender gender;
}


讲师(使用讲师)

@Entity
@Table(name="LECTURE")
public class Lecture
{
    private static final long serialVersionUID = 1L;

    @Id
    @Column(name = "ID")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column(name="TITLE")
    private String title;

    @JoinColumn(name="LECTURER_ID", referencedColumnName = "id")
    @ManyToOne
    private Lecturer lecturer;

    @Column(name = "ROOM")
    @Enumerated(EnumType.STRING)
    private Room room;

    @Column(name = "DAY_OF_WEEK")
    private DayOfWeek dow;

    @JoinColumn(name = "PREREQUISITE_ID", referencedColumnName = "id")
    @ManyToOne
    private Lecture prerequisite;

}


当我尝试建立查询时,会得到很多结果。 (SQL Server在同一查询上打印129,JPA 90k +)

查询:

Query q = em.createQuery("SELECT"
            + "  l.title, l.dow, l.room "
            + ", t.name, CASE WHEN (t.nobel_price_year != NULL) THEN 'YES' ELSE 'No' END"
            + ", e.year, e.exam_taken, e.exam_passed "
            + ", CONCAT(s.lastname,', ', s.firstname) AS student "
            + "FROM Lecture l "
            + "JOIN Lecturer t "
            + "JOIN Enrollment e "
            + "JOIN Student s "
            + "ORDER BY l.id", EmpireDBExample.class);


该代码是手动生成的,因为SQL Server连接不适用于Eclipse(据我所知/发现)。

映射中有错误吗?
我需要添加加入条件吗?如果是这样,我该如何实现? “ ON a.id = b.id”无效。

最佳答案

好吧,由于ON ...无法正常工作(Hibernate 5会支持它),因此您需要沿着路径加入或使用where条件来筛选不合适的行。目前,您正在交叉参加所有的讲座,讲师,招生和学生,即使它们没有关联。

另一个问题是,从LectureEnrollment没有代码路径,因此您可能想从Enrollment开始。因此查询看起来可能像这样(我将跳过常见部分):

... FROM Enrollment e
      JOIN e.lecture l  //join the lecture for the enrollment
      JOIN l.lecturer t //join the lecturer for the lecture
      JOIN e.student s  //join the enrolled student


最后的建议:“我有四个表描述数据库”-在这里改变您的想法。您有四个描述模型的实体,这些实体已映射到数据库(尽管映射可能会更改)。因此,在处理JPA查询时,您需要考虑实体而不是表,例如因为没有从LectureEnrollment的路径,并且除非您的JPA提供者支持通用的ON,否则您需要找到一个入口点和一条要加入的所有内容的路径(例如,从Enrollment到,因此从Lecture开始。

09-10 03:19
查看更多