java - 如何使用JPA通过外键搜索mySql数据库表？

Question

我有 2 个类(映射到 MySql 数据库的 BusinessAccount 和项目(如下所示))，其中 BusinessAccounts 和项目之间存在 1:M 关系。我已成功将数据插入数据库，但在查询数据库时遇到问题。我遇到的问题是我没有 Projects 类中的外键 'contractor_id' 的 getter 或 setter。我要执行的查询是通过在 Projects 表中搜索外键引用，返回给定 BusinessAccount 的所有项目的名称列表。我可以在 mySQL 中做到这一点，但由于在 Projects 类中没有将 contractor_id 引用为 java 实体，所以我不确定如何从我的 java 类中执行此搜索。 (注意:我试图在 Projects 类中声明外键以及 getter 和 setter，但是因为我已经通过类中的 1:Many 关系映射了这些，所以它不会编译，因为它们被标记为重复实体。)我确定很明显我遗漏了一些东西，但非常感谢任何帮助!

public List<Projects> getProjectList() {
    factory = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);
    EntityManager em = factory.createEntityManager();   
    List<Projects> projectList = new ArrayList<Projects>();

        em.getTransaction().begin();

        String sessionEmail=Util.getEmail();
        Query myQuery = em.createQuery("SELECT u FROM BusinessAccount u WHERE u.email=:email");
        myQuery.setParameter("email", sessionEmail);

        List<BusinessAccount> userList=myQuery.getResultList();
        BusinessAccount account =userList.get(0);

        Query myQuery2 = em.createQuery("SELECT distinct p.* FROM BusinessAccount u "
                + "INNER JOIN Projects p ON p.contractor_id=:userID");

/*Note p.contractor_id above refers to the entity in the 
mysql database (and won't work obviously), I want to refer 
to it's java equivalent but am not sure how to do that*/

        myQuery2.setParameter("userID", account.getId());
        projectList=myQuery2.getResultList();
        em.getTransaction().commit();
        em.close();

        return projectList;

    } 


@Entity
@Table(name = "business_accounts")
public class BusinessAccount {

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

    @Column(name = "first_name")
    private String firstName;

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

        @OneToMany(mappedBy = "businessAccount", fetch = FetchType.EAGER, cascade = { CascadeType.ALL })
    private List<Projects> projects;

    public int getId() {
    return id;
    }

    public void setId(int id) {
    this.id = id;
    }

    public String getFirstName() {
    return firstName;
    }

    public void setFirstName(String firstName) {
    this.firstName = firstName;
    }

    public String getSurname() {
    return surname;
    }

       public List<Projects> getProjects()
    {
    if (projects == null)
    {
        projects = new ArrayList<Projects>();
    }

    return projects;
    }

   public void setProjects(List<Projects> projects)
    {
    this.projects = projects;
    }

    }


@Entity
@Table(name = "projects")
public class Projects {

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

@Column(name = "project_name")
private String projectName;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumns({ @JoinColumn(name = "contractor_id", referencedColumnName="id") })
private BusinessAccount businessAccount;


public BusinessAccount getBusinessAccount() {
    if (businessAccount == null) {
        businessAccount = new BusinessAccount();
    }
    return businessAccount;
}

public void setBusinessAccount(BusinessAccount businessAccount) {
    this.businessAccount = businessAccount;
}

public int getProject_id() {
    return project_id;
}

public void setProject_id(int project_id) {
    this.project_id = project_id;
}

public String getProjectName() {
    return projectName;
}

public void setProjectName(String projectName) {
    this.projectName = projectName;
}

}

Answer 1

JPA 查询类似于(您需要使用关系属性，但不需要外键本身 - 请尝试，它可能需要一些调整):

SELECT p FROM BusinessAccount u, IN(u.projects) p WHERE u.id=:userId

但是您真的需要查询吗？您可以从属性中获取相关项目:

BusinessAccount account = ...
List<Projects> projectList = account.getProjects();