来自查询联接2个表的JPA或Play Framework列表

本文介绍了来自查询联接2个表的JPA或Play Framework列表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

主要是我与JSF合作，所以对这个注释主题是全新的

Mainly I work with JSF so am totally new to this annotation subject

如果有人可以帮助

我想从该查询中获取一个列表

I wanna a list from this query

SELECT  f.CODE  ,f.NAME || '-' || e.NAME  
FROM FS.ELIGIBLE e  RIGHT
OUTER JOIN FS.FINANCIAL_SUPPORT f ON e.CODE = f.CODE ;

上面的查询从2个表中检索一个列表，并将两个表中的名称字段连接起来！

The query above retrieves a list from 2 tables and concatenating the name field from both tables!!

我该如何在JPA中或在Play框架支持的其他查询中进行此项操作?

How can i do this in JPA or in play with another query supported by Play Framework ???

推荐答案

JPA与关系数据库系统不同，它可以像联接，左联接或外部联接那样进行查询，它是对象的映射技术.您也可以像那些RDBMS副本一样执行相同的访存，但是使用不同的方法.

JPA is not like relational database system which you can do your queries like join, left join or outer joins it is a mapping technology of objects. You can also do the same fetch just like those RDBMS counterparts but with a different approach.

您要做的是创建一个对象，然后将第二个对象与第一个对象相关联，这是关联2个或更多对象的正确方法.我正在谈论的对象是您的表.请参阅下面的示例:

What you have to do is make an Object then relate your second Object to your first Object, that is the proper way to relate 2 or more Objects. The Objects I'm talking about is your Table. See my example below:

表1:Items.java
...
//在这里输入...
//在这里像
@Entity
@Table(name ="Items")
公共类Items实现了可序列化的{
私有字符串ID；
私有字符串itemno;
私有字符串描述；
private Set< Vendors>供应商； //这是第二张表(1:n关系)
...
//不要忘记您的构造函数
//在供应商的设置者和获取者中执行以下操作:
@OneToMany(cascade = CascadeType.ALL，fetch = FetchType.EAGER)
@JoinColumn(name ="id")
公共集合< Vendors> getVendors(){
退货商；
}
public void setVendors(Set< Vendors>供应商){
this.vendors =供应商；
}
...
}

Table1: Items.java
...
// do your imports here ...
// do your annotations here like
@Entity
@Table(name="Items")
public class Items implements Serializable {
private String id;
private String itemno;
private String description;
private Set<Vendors> vendors; //this is the 2nd table (1:n relationship)
...
// don't forget your constructor
// in your setter and getter for Vendors do the ff:
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinColumn(name="id")
public Set<Vendors> getVendors() {
return vendors;
}
public void setVendors(Set<Vendors> vendors) {
this.vendors = vendors;
}
...
}

表2:Vendors.java

Table2: Vendors.java

@Entity
@Table(name ="Vendors")
公共类供应商实现可序列化的{
私人长ID；
私人弦乐公司；
私有字符串联系人；
私有字符串序列；
...
公共供应商(){}
...
//在您的二传手&中吸气剂
@Id
public long getId(){
返回ID；
}
公共无效setId(长id){
this.id = id;
}
...
}

@Entity
@Table(name="Vendors")
public class Vendors implements Serializable {
private long id;
private String company;
private String contact;
private String sequence;
...
public Vendors() { }
...
// in your setter & getter
@Id
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
...
}

现在，在查询中，只需按ff中的内容进行常规选择即可:

Now on your query, just do a regular select as in the ff:

public void makeQuery(String seq){
EntityManagerFactory emf = Persistence.createEntityManagerFactory(...);
EntityManager em = emf.createEntityManager();
TypedQuery< Items>查询= em.createQuery(从项目中选择i，j.contact，j.company，j.sequence i左外联接i.vendors j在哪里i.vendors.sequence =:seq
ORDER BY i.id"，Items.class)；
List< Items> items = query.setParameter("sequence"，seq).getResultList();
...
}

public void makeQuery(String seq) {
EntityManagerFactory emf = Persistence.createEntityManagerFactory(...);
EntityManager em = emf.createEntityManager();
TypedQuery<Items> query = em.createQuery("SELECT i, j.contact, j.company, j.sequence FROM Items i LEFT OUTER JOIN i.vendors j WHERE i.vendors.sequence = :seq
ORDER BY i.id", Items.class);
List<Items> items = query.setParameter("sequence", seq).getResultList();
...
}

现在，您可以使用 items.vendors.company ...等来引用第二表的供应商.

Now you can refer to your 2nd table Vendors by using items.vendors.company ... and so on.

此方法的一个缺点是，JPA以Set的形式制作其相关对象，请参见表1(项)中的声明-私有Set供应商.由于它是一个Set，因此与使用List不同，该序列不按顺序接收.

One disadvantage with this one is that, JPA make its related objects in the form of Set, see the declaration in Table1 (Items) - private Set vendors. Since it was a Set, the sequence is not in order it was received, unlike using List.

希望这会有所帮助...

Hope this will help ...

纳尔逊·德格拉西亚斯(Nelson Deogracias)

Regards, Nelson Deogracias

这篇关于来自查询联接2个表的JPA或Play Framework列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！