JPA教程 - JPA查询连接OrderBy示例
以下代码显示如何使用ORDER BY子句和连接条件。
List l = em.createQuery("SELECT e " +
"FROM Project p JOIN p.employees e " +
"WHERE p.name = :project " +
"ORDER BY e.name")
.setParameter("project", "Design")
.getResultList();
例子
以下代码来自Employee.java。
package cn.w3cschool.common;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
@Entity
@NamedQueries({
@NamedQuery(name="findEmployeesAboveSal",
query="SELECT e " +
"FROM Employee e " +
"WHERE e.department = :dept AND " +
" e.salary > :sal"),
@NamedQuery(name="findHighestPaidByDepartment",
query="SELECT e " +
"FROM Employee e " +
"WHERE e.department = :dept AND " +
" e.salary = (SELECT MAX(e2.salary) " +
" FROM Employee e2 " +
" WHERE e2.department = :dept)")
})
public class Employee {
@Id
private int id;
private String name;
private long salary;
@Temporal(TemporalType.DATE)
private Date startDate;
@ManyToOne
private Employee manager;
@OneToMany(mappedBy="manager")
private Collection<Employee> directs;
@ManyToOne
private Department department;
@ManyToMany
private Collection<Project> projects;
public Employee() {
projects = new ArrayList<Project>();
directs = new ArrayList<Employee>();
}
public void setId(int id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setSalary(long salary) {
this.salary = salary;
}
public void setStartDate(Date startDate) {
this.startDate = startDate;
}
public void setManager(Employee manager) {
this.manager = manager;
}
public void setDirects(Collection<Employee> directs) {
this.directs = directs;
}
public void setDepartment(Department department) {
this.department = department;
}
public void setProjects(Collection<Project> projects) {
this.projects = projects;
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public long getSalary() {
return salary;
}
public Date getStartDate() {
return startDate;
}
public Department getDepartment() {
return department;
}
public Collection<Employee> getDirects() {
return directs;
}
public Employee getManager() {
return manager;
}
public Collection<Project> getProjects() {
return projects;
}
public String toString() {
return "Employee " + getId() +
": name: " + getName() +
", salary: " + getSalary() +
", dept: " + ((getDepartment() == null) ? null : getDepartment().getName());
}
}
下面的代码来自Department.java。
package cn.w3cschool.common;
import java.util.ArrayList;
import java.util.Collection;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
@Entity
public class Department {
@Id
private int id;
private String name;
@OneToMany(mappedBy="department")
private Collection<Employee> employees;
public Department() {
employees = new ArrayList<Employee>();
}
public void setId(int id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setEmployees(Collection<Employee> employees) {
this.employees = employees;
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public Collection<Employee> getEmployees() {
return employees;
}
public String toString() {
return "Department no: " + getId() +
", name: " + getName();
}
}
以下代码来自Project.java。
package cn.w3cschool.common;
import java.util.ArrayList;
import java.util.Collection;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
@Entity
public class Project {
@Id
protected int id;
protected String name;
@ManyToMany(mappedBy="projects")
private Collection<Employee> employees;
public Project() {
employees = new ArrayList<Employee>();
}
public void setId(int id) {
this.id = id;
}
public void setName(String name) {
this.name = name;
}
public void setEmployees(Collection<Employee> employees) {
this.employees = employees;
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public Collection<Employee> getEmployees() {
return employees;
}
public String toString() {
return "Project id: " + getId() + ", name: " + getName();
}
}
下面的代码来自PersonDaoImpl.java。
package cn.w3cschool.common;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import org.springframework.transaction.annotation.Transactional;
@Transactional
public class PersonDaoImpl {
public void test() {
Employee emp = new Employee();
emp.setName("Tom");
emp.setSalary(123);
emp.setStartDate(new Date());
emp.setId(1);
Project pro = new Project();
pro.setName("Design");
pro.getEmployees().add(emp);
Department dept = new Department();
dept.setName("Dept");
dept.getEmployees().add(emp);
emp.setDepartment(dept);
emp.getProjects().add(pro);
em.persist(dept);
em.persist(pro);
em.persist(emp);
String deptName = "Dept";
String empName = "Tom";
List l = em.createQuery("SELECT e " +
"FROM Project p JOIN p.employees e " +
"WHERE p.name = :project " +
"ORDER BY e.name")
.setParameter("project", "Design")
.getResultList();
System.out.println(l);
}
public Collection<Employee> findAllEmployees() {
return (Collection<Employee>) em.createQuery(
"SELECT e FROM Employee e").getResultList();
}
@PersistenceContext
private EntityManager em;
}
上面的代码生成以下结果。
以下是数据库转储。
Table Name: DEPARTMENT
Row:
Column Name: ID,
Column Type: INTEGER:
Column Value: 0
Column Name: NAME,
Column Type: VARCHAR:
Column Value: Dept
Table Name: EMPLOYEE
Row:
Column Name: ID,
Column Type: INTEGER:
Column Value: 1
Column Name: NAME,
Column Type: VARCHAR:
Column Value: Tom
Column Name: SALARY,
Column Type: BIGINT:
Column Value: 123
Column Name: STARTDATE,
Column Type: DATE:
Column Value: 2014-12-29
Column Name: DEPARTMENT_ID,
Column Type: INTEGER:
Column Value: 0
Column Name: MANAGER_ID,
Column Type: INTEGER:
Column Value: null
Table Name: EMPLOYEE_PROJECT
Row:
Column Name: EMPLOYEES_ID,
Column Type: INTEGER:
Column Value: 1
Column Name: PROJECTS_ID,
Column Type: INTEGER:
Column Value: 0
Table Name: PROJECT
Row:
Column Name: ID,
Column Type: INTEGER:
Column Value: 0
Column Name: NAME,
Column Type: VARCHAR:
Column Value: Design
