JPAのJPQLサブクエリ

8150 ワード

JPAのJPQLサブクエリ
例:
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