jpa多表查询 HQL

jpa多表查询可以使用构造器的方式进行多表查询，以下为多表查询的案例。

本案例中有A、B、C三个对象实体类，对象A、B通过属性imsi关联，对象B、C通过imsig关联；

还有一个Abc类，它是一个用来多表查询时构造集合的普通类。

以下为案例代码

A类

package com.ljq.entity;import java.io.Serializable;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.Id;/** * 对象A、B通过属性imsi关联 *  * @author jiqinlin *  */@SuppressWarnings("serial")@Entitypublic class A implements Serializable {    @Id    @GeneratedValue    private Integer id;    @Column(nullable = false, length = 20)    private String imsi;    @Column(nullable = false, length = 20)    private String sipss;    public A() {        super();    }    public A(String imsi, String sipss) {        super();        this.imsi = imsi;        this.sipss = sipss;    }    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getImsi() {        return imsi;    }    public void setImsi(String imsi) {        this.imsi = imsi;    }    public String getSipss() {        return sipss;    }    public void setSipss(String sipss) {        this.sipss = sipss;    }}

B类

package com.ljq.entity;import java.io.Serializable;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.Id;@SuppressWarnings("serial")@Entitypublic class B implements Serializable {    @Id    @GeneratedValue    private Integer id;        @Column(nullable = false, length = 20)    private String imsi;        @Column(nullable = false, length = 20)    private String imsig;        @Column(nullable = false, length = 20)    private String mdn;    public B() {        super();    }    public B(String imsi, String imsig, String mdn) {        super();        this.imsi = imsi;        this.imsig = imsig;        this.mdn = mdn;    }    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getImsi() {        return imsi;    }    public void setImsi(String imsi) {        this.imsi = imsi;    }    public String getImsig() {        return imsig;    }    public void setImsig(String imsig) {        this.imsig = imsig;    }    public String getMdn() {        return mdn;    }    public void setMdn(String mdn) {        this.mdn = mdn;    }}

C类型

package com.ljq.entity;import java.io.Serializable;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.Id;/** * 对象B、C通过属性imsig关联 *  * @author jiqinlin *  */@SuppressWarnings("serial")@Entitypublic class C implements Serializable {    @Id    @GeneratedValue    private Integer id;        @Column(nullable = false, length = 20)    private String imsig;        @Column(nullable = false, length = 20)    private String ki;    public C() {        super();    }    public C(String imsig, String ki) {        super();        this.imsig = imsig;        this.ki = ki;    }    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getImsig() {        return imsig;    }    public void setImsig(String imsig) {        this.imsig = imsig;    }    public String getKi() {        return ki;    }    public void setKi(String ki) {        this.ki = ki;    }}

Abc类型

package com.ljq.entity;import java.io.Serializable;/** * 多表查询时用来构造集合的实体类 *  * @author jiqinlin * */@SuppressWarnings("serial")public class Abc implements Serializable {    /** 实体类主键 * */    private Integer id;    private String imsi;    private String imsig;    private String sipss;    private String mdn;    private String ki;    public Abc() {    }    public Abc(String imsi, String sipss, String mdn) {        this.imsi = imsi;        this.sipss = sipss;        this.mdn = mdn;    }    public Abc(String imsi, String imsig, String mdn, String ki) {        super();        this.imsi = imsi;        this.imsig = imsig;        this.mdn = mdn;        this.ki = ki;    }    public Abc(String imsi, String imsig, String sipss, String mdn, String ki) {        super();        this.imsi = imsi;        this.imsig = imsig;        this.sipss = sipss;        this.mdn = mdn;        this.ki = ki;    }    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getImsi() {        return imsi;    }    public void setImsi(String imsi) {        this.imsi = imsi;    }    public String getImsig() {        return imsig;    }    public void setImsig(String imsig) {        this.imsig = imsig;    }    public String getSipss() {        return sipss;    }    public void setSipss(String sipss) {        this.sipss = sipss;    }    public String getMdn() {        return mdn;    }    public void setMdn(String mdn) {        this.mdn = mdn;    }    public String getKi() {        return ki;    }    public void setKi(String ki) {        this.ki = ki;    }}

MultiListQueryTest测试类

package junit.test;import java.util.List;import javax.persistence.EntityManager;import javax.persistence.EntityManagerFactory;import javax.persistence.Persistence;import org.junit.Test;import com.ljq.entity.A;import com.ljq.entity.Abc;import com.ljq.entity.B;import com.ljq.entity.C;/** * 多表查询 *  * @author jiqinlin *  */public class MultiListQueryTest {    @Test    public void add() throws Exception {        EntityManagerFactory factory = Persistence.createEntityManagerFactory("ljq");        EntityManager em = factory.createEntityManager();        em.getTransaction().begin();                em.persist(new A("41650a", "abcefg1"));        em.persist(new A("41650b", "abcefg2"));        em.persist(new A("41650c", "abcefg3"));                em.persist(new C("imsig_a","12345a"));        em.persist(new C("imsig_b","12345b"));        em.persist(new C("imsig_c","12345c"));        em.persist(new C("imsig_e","12345d"));                em.persist(new B("41650a", "imsig_a", "059188893381"));        em.persist(new B("41650b", "imsig_b", "059188893382"));        em.persist(new B("41650e", "imsig_c", "059188893383"));        em.persist(new B("41650aa", "imsig_123", "059188893384"));        em.persist(new B("41650cc", "imsig_1", "059188893385"));                em.getTransaction().commit();        em.close();        factory.close();    }    /**     * 多表查询对象A、B     *      * @throws Exception     */    @Test    public void queryAB() throws Exception {        EntityManagerFactory factory = Persistence.createEntityManagerFactory("ljq");        EntityManager em = factory.createEntityManager();                List<Abc>  abcs = em.createQuery("select new com.ljq.entity.Abc(a.imsi, a.sipss, b.mdn) " +                "from A a, B b where a.imsi=b.imsi").getResultList();        for(Abc abc : abcs){            System.out.println("imsi:"+abc.getImsi());            System.out.println("sipss:"+abc.getSipss());            System.out.println("mdn:"+abc.getMdn());            System.out.println("========");        }                em.close();        factory.close();    }        /**     * 多表查询对象B、C     *      * @throws Exception     */    @Test    public void queryBC() throws Exception {        EntityManagerFactory factory = Persistence.createEntityManagerFactory("ljq");        EntityManager em = factory.createEntityManager();                List<Abc> abcs = em.createQuery("select new com.ljq.entity.Abc(b.imsi, b.imsig, b.mdn, c.ki) " +                "from B b, C c where b.imsig=c.imsig").getResultList();        for(Abc abc : abcs){            System.out.println("imsi:"+abc.getImsi());            System.out.println("imsig:"+abc.getImsig());            System.out.println("mdn:"+abc.getMdn());            System.out.println("ki:"+abc.getKi());            System.out.println("=======");        }                em.close();        factory.close();    }        /**     * 多表查询对象A、B、C     *      * @throws Exception     */    @Test    public void queryABC() throws Exception {        EntityManagerFactory factory = Persistence.createEntityManagerFactory("ljq");        EntityManager em = factory.createEntityManager();                List<Abc> abcs = em.createQuery("select new com.ljq.entity.Abc(b.imsi, b.imsig, a.sipss, b.mdn, c.ki) " +                "from A a, B b, C c where a.imsi=b.imsi and b.imsig=c.imsig").getResultList();        for(Abc abc : abcs){            System.out.println("imsi:"+abc.getImsi());            System.out.println("imsig:"+abc.getImsig());            System.out.println("sipss:"+abc.getSipss());            System.out.println("mdn:"+abc.getMdn());            System.out.println("ki:"+abc.getKi());            System.out.println("=======");        }                em.close();        factory.close();    }        @Test    public void test() throws Exception {        Persistence.createEntityManagerFactory("ljq");    }        }