jpa多表查询

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")
@Entity
public 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")
@Entity
public 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")
@Entity
public 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");
    }
    
    
}