JPAのJPQLサブクエリ
8150 ワード
JPAのJPQLサブクエリ
例:
1.Employeeエンティティークラス:
2.Departmentエンティティクラス:
3.テストクラス
4.xmlプロファイル:
例:
1.Employeeエンティティークラス:
@Entity
public class Employee {
private Integer empId;
private String empAddress;
private Integer empAge;
private String empGender;
private String empName;
private Double empSalary;
private Date empStartDate;
private Integer empStatus;
// private String deptId;
private Department dept;
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
public Integer getEmpId() {
return empId;
}
public void setEmpId(Integer empId) {
this.empId = empId;
}
public String getEmpAddress() {
return empAddress;
}
public void setEmpAddress(String empAddress) {
this.empAddress = empAddress;
}
public Integer getEmpAge() {
return empAge;
}
public void setEmpAge(Integer empAge) {
this.empAge = empAge;
}
public String getEmpGender() {
return empGender;
}
public void setEmpGender(String empGender) {
this.empGender = empGender;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public Double getEmpSalary() {
return empSalary;
}
public void setEmpSalary(Double empSalary) {
this.empSalary = empSalary;
}
public Date getEmpStartDate() {
return empStartDate;
}
public void setEmpStartDate(Date empStartDate) {
this.empStartDate = empStartDate;
}
public Integer getEmpStatus() {
return empStatus;
}
public void setEmpStatus(Integer empStatus) {
this.empStatus = empStatus;
}
@ManyToOne
@JoinColumn(name="deptId")
public Department getDept() {
return dept;
}
public void setDept(Department dept) {
this.dept = dept;
}
@Override
public String toString() {
return "Employee [empId=" + empId + ", empAddress=" + empAddress + ", empAge=" + empAge + ", empGender="
+ empGender + ", empName=" + empName + ", empSalary=" + empSalary + ", empStartDate=" + empStartDate
+ ", empStatus=" + empStatus + "]";
}
public Employee(Integer empId, String empAddress, Integer empAge, String empGender, String empName,
Double empSalary, Date empStartDate, Integer empStatus) {
super();
this.empId = empId;
this.empAddress = empAddress;
this.empAge = empAge;
this.empGender = empGender;
this.empName = empName;
this.empSalary = empSalary;
this.empStartDate = empStartDate;
this.empStatus = empStatus;
}
public Employee() {
}
public Employee(Integer empId, String empName, Double empSalary) {
super();
// System.out.println(" ");
this.empId = empId;
this.empName = empName;
this.empSalary = empSalary;
}
}
2.Departmentエンティティクラス:
@Entity
public class Department {
private Integer deptId;
private String deptName;
private String deptManager;
private Set emps = new HashSet();
@OneToMany(mappedBy="dept",fetch=FetchType.LAZY)
// @JoinColumn(name="deptId")
public Set getEmps() {
return emps;
}
public void setEmps(Set emps) {
this.emps = emps;
}
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
public Integer getDeptId() {
return deptId;
}
public void setDeptId(Integer deptId) {
this.deptId = deptId;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
public String getDeptManager() {
return deptManager;
}
public void setDeptManager(String deptManager) {
this.deptManager = deptManager;
}
@Override
public String toString() {
return "Department [deptId=" + deptId + ", deptName=" + deptName + ", deptManager=" + deptManager + "]";
}
public Department(Integer deptId, String deptName, String deptManager) {
super();
this.deptId = deptId;
this.deptName = deptName;
this.deptManager = deptManager;
}
public Department() {
super();
}
}
3.テストクラス
/**
*
*
* @author boge
*
*/
public class SubQueryTest {
EntityManagerFactory emf;
EntityManager em;
EntityTransaction transaction;
/*
* :
* :
*
* :
* 1、 =>dept(1,2)
* select d.deptId from Department where d.deptManger !=" "
* 2、 ,
* select e from Employee where e.dept.deptId IN (1,2)
*/
@Test
public void test4(){
String jpql="select e from Employee e "
+ "where e.dept.deptId IN "
+ "(select d.deptId from Department d where d.deptManager !=' ')";
Query query = em.createQuery(jpql);
List resultList = query.getResultList();
for (Employee emp : resultList) {
System.out.println(emp.getEmpName()+":"+emp.getDept().getDeptId());
}
}
//
/*
* :
* :
* : 10 20 30 40 50
* select emp from Employee emp where emp.dept.deptId = 4
* :
* e.empAge > ALL(10 20 30 40 50)
*/
@Test
public void test2(){
String jpql = "select e from Employee e "
+ "where e.empAge > "
+ "ALL(select emp.empAge from Employee emp where emp.dept.deptId = 4)";
Query query = em.createQuery(jpql);
List resultList = query.getResultList();
for (Object object : resultList) {
System.out.println(object);
}
}
/**
* , , 4
* (10 20 30 40 50)
*/
//
@Test
public void test3(){
String jpql = "select e from Employee e "
+ "where e.empAge > "
+ "ANY(select emp.empAge from Employee emp where emp.dept.deptId = 4)";
Query query = em.createQuery(jpql);
List resultList = query.getResultList();
for (Object object : resultList) {
System.out.println(object);
}
}
/* 3
* , : 3
*
*/
@Test
public void test1(){
String jpql = "SELECT e from Employee e where e.dept.deptId>2";
Query query = em.createQuery(jpql);
List resultList = query.getResultList();
for (Object object : resultList) {
System.out.println(object);
}
}
@Before
public void before() {
// 1、 EntityMangerFactory
emf = Persistence.createEntityManagerFactory("jpa-03");
// 2、 EntityManger
// , ,
em = emf.createEntityManager();
transaction = em.getTransaction();
transaction.begin();
}
@After
public void after() {
transaction.commit();
em.close();
}
}
4.xmlプロファイル:
org.hibernate.ejb.HibernatePersistence
com.kmu.entity.Employee
com.kmu.entity.Department