来自查询联接2个表的JPA或Play Framework列表 [英] JPA or Play Framework list from query joining 2 tables

查看:88
本文介绍了来自查询联接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. 表1:Items.java
  2. ...
  3. //在这里输入...
  4. //在这里像
  5. 一样进行注释
  6. @Entity
  7. @Table(name ="Items")
  8. 公共类Items实现了可序列化的{
  9.   私有字符串ID;
  10.   私有字符串itemno;
  11.    私有字符串描述;
  12.    private Set< Vendors>供应商;     //这是第二张表(1:n关系)
  13.    ...
  14.    //不要忘记您的构造函数
  15.    //在供应商的设置者和获取者中执行以下操作:
  16.    @OneToMany(cascade = CascadeType.ALL,fetch = FetchType.EAGER)
  17.    @JoinColumn(name ="id")
  18.   公共集合< Vendors> getVendors(){
  19.     退货商;
  20.    }
  21.    public void setVendors(Set< Vendors>供应商){
  22.     this.vendors =供应商;
  23.    }
  24.    ...
  25. }
  1. Table1: Items.java
  2. ...
  3. // do your imports here ...
  4. // do your annotations here like
  5. @Entity
  6. @Table(name="Items")
  7. public class Items implements Serializable {
  8.    private String id;
  9.    private String itemno;
  10.    private String description;
  11.    private Set<Vendors> vendors;      //this is the 2nd table (1:n relationship)
  12.    ...
  13.    // don't forget your constructor
  14.    // in your setter and getter for Vendors do the ff:
  15.    @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
  16.    @JoinColumn(name="id")
  17.    public Set<Vendors> getVendors() {
  18.       return vendors;
  19.    }
  20.    public void setVendors(Set<Vendors> vendors) {
  21.      this.vendors = vendors;
  22.    }
  23.   ...
  24. }

表2:Vendors.java

Table2: Vendors.java

  1. @Entity
  2. @Table(name ="Vendors")
  3. 公共类供应商实现可序列化的{
  4.   私人长ID;
  5.   私人弦乐公司;
  6.   私有字符串联系人;
  7.   私有字符串序列;
  8.    ...
  9.   公共供应商(){}
  10.    ...
  11.    //在您的二传手&中吸气剂
  12.   @Id
  13.    public long getId(){
  14.     返回ID;
  15.   }
  16.   公共无效setId(长id){
  17.      this.id = id;
  18.    }
  19.      ...
  20.   }
  1. @Entity
  2. @Table(name="Vendors")
  3. public class Vendors implements Serializable {
  4.    private long id;
  5.    private String company;
  6.    private String contact;
  7.    private String sequence;
  8.    ...
  9.    public Vendors() { }
  10.    ...
  11.    // in your setter & getter
  12.   @Id
  13.   public long getId() {
  14.      return id;
  15.   }
  16.    public void setId(long id) {
  17.       this.id = id;
  18.    }
  19.      ...
  20.   }

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

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

  1. public void makeQuery(String seq){
  2.    EntityManagerFactory emf = Persistence.createEntityManagerFactory(...);
  3.    EntityManager em = emf.createEntityManager();
  4.   TypedQuery< Items>查询= em.createQuery( 从项目中选择i,j.contact,j.company,j.sequence i左外联接i.vendors j在哪里i.vendors.sequence =:seq
  5.      ORDER BY i.id",Items.class);
  6.   List< Items> items = query.setParameter("sequence",seq).getResultList();
  7.    ...
  8. }
  1. public void makeQuery(String seq) {
  2.    EntityManagerFactory emf = Persistence.createEntityManagerFactory(...);
  3.    EntityManager em = emf.createEntityManager();
  4.   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
  5.       ORDER BY i.id", Items.class);
  6.   List<Items> items = query.setParameter("sequence", seq).getResultList();
  7.    ...
  8. }

现在,您可以使用 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列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆