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();
}
}