java - 两个表上的 EclipseLink EntityManager SQL JOIN

Question

我有 2 个不同的表:subjects 和 questions，我需要对这 2 个表进行 SQL JOIN。表subjects有其属性:name和shortcut。表 questions 有其属性:question_number、text、subject - 事实上，subject > 表questions 是subject 的快捷方式。

我尝试了这样的事情，我在一个 stackoverflow 主题中看到了这样的内容:

Query q = em.createNativeQuery("SELECT q.question_number, q.text, s.name, s.shortcut FROM "
                                   + "( questions q INNER JOIN subjects s ON q.subject=s.shortcut );", QuestionSubject.class);

QuestionSubject.class 是一个 @Entity 类，具有 questions 表和 subjects 表的属性。调用此方法后，我看到在我的数据库中创建了一个名为 QUESTIONSUBJECT 的新表，而这是我不想做的事情。

有人可以帮我提供其他解决方案吗？

P.S.:我这样做是为了将输出用作 HTTP 请求的响应，因此我需要将这两者合二为一。我需要返回 List 或 JSON 字符串。

编辑:使用 MySQL 数据库。

问题表实体类:

package model;

import java.io.Serializable;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.xml.bind.annotation.XmlRootElement;

@Entity
@Table(name = "questions")
@XmlRootElement
public class Question implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
   @GeneratedValue(strategy=GenerationType.AUTO)
   @Basic(optional = false)
   @Column(name = "question_number")
    private Integer questionNumber;
   @Column(name = "text")
    private String text;
   @Column(name = "subject")
    private String subject;

    public Question() {
    }

    public Question(Integer questionNumber) {
        this.questionNumber = questionNumber;
    }

    public Question( String text, String subject) {
        this.text = text;
        this.subject = subject;
    }

    public Question(Integer questionNumber, String text, String subject) {
        this.questionNumber = questionNumber;
        this.text = text;
        this.subject = subject;
    }

    public Integer getQuestionNumber() {
        return questionNumber;
    }

    public void setQuestionNumber(Integer questionNumber) {
        this.questionNumber = questionNumber;
    }

    public String getText() {
        return text;
    }

    public void setText(String text) {
        this.text = text;
    }

    public String getSubject() {
        return subject;
    }

    public void setSubject(String subject) {
        this.subject = subject;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (questionNumber != null ? questionNumber.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Question)) {
            return false;
        }
        Question other = (Question) object;
        if ((this.questionNumber == null && other.questionNumber != null) || (this.questionNumber != null && !this.questionNumber.equals(other.questionNumber))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "Rest.Questions[ questionNumber=" + questionNumber + " ]";
    }

}

subjects 表实体类。

package model;

import java.io.Serializable;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Lob;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import javax.xml.bind.annotation.XmlRootElement;

@Entity
@Table(name = "subjects")
@XmlRootElement
public class Subject implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
   @Basic(optional = false)
   @NotNull
   @Size(min = 1, max = 5)
   @Column(name = "shortcut")
    private String shortcut;
    @Basic(optional = false)
   @NotNull
   @Lob
   @Size(min = 1, max = 65535)
   @Column(name = "name")
    private String name;

    public Subject() {
    }

    public Subject(String shortcut) {
        this.shortcut = shortcut;
    }

    public Subject(String shortcut, String name) {
        this.shortcut = shortcut;
        this.name = name;
    }

    public String getShortcut() {
        return shortcut;
    }

    public void setShortcut(String shortcut) {
        this.shortcut = shortcut;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (shortcut != null ? shortcut.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Subject)) {
            return false;
        }
        Subject other = (Subject) object;
        if ((this.shortcut == null && other.shortcut != null) || (this.shortcut != null && !this.shortcut.equals(other.shortcut))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "Rest.Subjects[ shortcut=" + shortcut + " ]";
    }

}

QuestionSubject 实体类:

package model;

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class QuestionSubject implements Serializable
{
    @Id
    @Column(name = "question_number")
    private Integer questionNumber;
    @Column(name = "text")
    private String text;

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

    public Integer getQuestionNumber() {
        return questionNumber;
    }

    public void setQuestionNumber(Integer questionNumber) {
        this.questionNumber = questionNumber;
    }

    public String getText() {
        return text;
    }

    public void setText(String text) {
        this.text = text;
    }

    public String getShortcut() {
        return shortcut;
    }

    public void setShortcut(String shortcut) {
        this.shortcut = shortcut;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

Answer 1

该表被创建，因为您定义了一个名为 QuestionSubject 的类，并注释为 @Entity。默认情况下，表名称是类名称。

您可以像在 Subjects 中那样使用 @Table(name = "subjects") 覆盖名称

如果您在类 Question 和 Subject 之间的相关字段上定义 @ManyToMany 映射而不定义 ，几乎会发生相同的情况QuestionSubject 类根本没有。

我建议您查看此处以获取更多信息:

http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Mapping/Relationship_Mappings/Collection_Mappings/ManyToMany

编辑如果您需要多对多映射，则需要此表。否则你只能有一个 oneToMany 或一个。多对一关系(使用外键)。