JPA多表查询案例

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

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

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

以下为案例代码

A类

package com.jason.bean;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 * 对象A、B通过属性imsi关联
 * 
 * @author jason
 * 
 */
@Entity
public class A implements Serializable {
	private static final long serialVersionUID = -8137583777189687541L;
    private Integer id;
    private String imsi;
    private String sipss;

    public A() {
        super();
    }

    public A(String imsi, String sipss) {
        super();
        this.imsi = imsi;
        this.sipss = sipss;
    }
    
    @Id
    @GeneratedValue
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }
    
    @Column(nullable = false, length = 20)
    public String getImsi() {
        return imsi;
    }

    public void setImsi(String imsi) {
        this.imsi = imsi;
    }
    
    @Column(nullable = false, length = 20)
    public String getSipss() {
        return sipss;
    }

    public void setSipss(String sipss) {
        this.sipss = sipss;
    }

}

B类：

package com.jason.bean;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class B implements Serializable {
	private static final long serialVersionUID = 8233175635769479405L;
    private Integer id;
    private String imsi;
    private String imsig;
    private String mdn;

    public B() {
        super();
    }

    public B(String imsi, String imsig, String mdn) {
        super();
        this.imsi = imsi;
        this.imsig = imsig;
        this.mdn = mdn;
    }
    
    @Id
    @GeneratedValue
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }
    
    @Column(nullable = false, length = 20)
    public String getImsi() {
        return imsi;
    }

    public void setImsi(String imsi) {
        this.imsi = imsi;
    }
    
    @Column(nullable = false, length = 20)
    public String getImsig() {
        return imsig;
    }

    public void setImsig(String imsig) {
        this.imsig = imsig;
    }
    
    @Column(nullable = false, length = 20)
    public String getMdn() {
        return mdn;
    }

    public void setMdn(String mdn) {
        this.mdn = mdn;
    }

}

C类：

package com.jason.bean;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 * 对象B、C通过属性imsig关联
 * 
 * @author jason
 * 
 */
@Entity
public class C implements Serializable {
	private static final long serialVersionUID = -5511221269726326126L;
    private Integer id;
    private String imsig;
    private String ki;

    public C() {
        super();
    }

    public C(String imsig, String ki) {
        super();
        this.imsig = imsig;
        this.ki = ki;
    }
    
    @Id
    @GeneratedValue
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }
    
    @Column(nullable = false, length = 20)
    public String getImsig() {
        return imsig;
    }

    public void setImsig(String imsig) {
        this.imsig = imsig;
    }
    
    @Column(nullable = false, length = 20)
    public String getKi() {
        return ki;
    }

    public void setKi(String ki) {
        this.ki = ki;
    }

}

Abc类：

package com.jason.bean;

import java.io.Serializable;

/**
 * 多表查询时用来构造集合的实体类
 * 
 * @author jason
 *
 */
public class Abc implements Serializable {
	private static final long serialVersionUID = -7365473055198228075L;
	/** 实体类主键 * */
    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;
    }

}

QueryTest测试类：

package junit.test;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;

import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;

import com.jason.bean.A;
import com.jason.bean.Abc;
import com.jason.bean.B;
import com.jason.bean.C;


/**
 * 多表查询
 * 
 * @author jason
 * 
 */
@SuppressWarnings("unchecked")
public class QueryTest {
	private static EntityManagerFactory factory;
	private static EntityManager em;
	@BeforeClass
	public static void setUpBeforeClass() throws Exception {
		 factory = Persistence.createEntityManagerFactory("jason");
		 em = factory.createEntityManager();
	     em.getTransaction().begin();
	}
	
    @Test
    public void add() throws Exception {
        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"));
    }

    /**
     * 多表查询对象A、B
     * 
     * @throws Exception
     */
	@Test
    public void queryAB() throws Exception {
        List<Abc>  abcs = em.createQuery("select new com.jason.bean.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("========");
        }
    }
    
    /**
     * 多表查询对象B、C
     * 
     * @throws Exception
     */
    @Test
    public void queryBC() throws Exception {
        List<Abc> abcs = em.createQuery("select new com.jason.bean.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("=======");
        }
    }
    
    /**
     * 多表查询对象A、B、C
     * 
     * @throws Exception
     */
    @Test
    public void queryABC() throws Exception {
        List<Abc> abcs = em.createQuery("select new com.jason.bean.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("=======");
        }
    }
    
    @Test
    public void test() throws Exception {
        Persistence.createEntityManagerFactory("jason");
    }
    
    @AfterClass
	public static void tearDownAfterClass() throws Exception {
    	em.getTransaction().commit();
        em.close();
        factory.close();
	}
    
}