JPA教程 - JPA查询ANY示例
以下代码显示了如何在JPQL中使用ANY运算符。
List l = em.createQuery("SELECT e FROM Professor e WHERE e.department = ANY (SELECT DISTINCT d FROM Department d)").getResultList();
例子
下面的代码来自PersonDaoImpl.java。
package cn.w3cschool.common;import java.util.List;import javax.persistence.EntityManager; import javax.persistence.PersistenceContext;import org.springframework.transaction.annotation.Transactional;@Transactional public class PersonDaoImpl {public void test() {prepareData();List l = em.createQuery("SELECT e FROM Professor e WHERE e.department = ANY (SELECT DISTINCT d FROM Department d)").getResultList();for (Object p : l) {printResult(p);}}private void prepareData() {Address address = new Address();address.setState("BC");Professor p = new Professor();p.setId(0);p.setName("TOM");p.setSalary(1111L);p.setAddress(address);Department d = new Department();d.setId(1);d.setName("Design");p.setDepartment(d);d.getProfessors().add(p);Phone phone = new Phone();phone.setId(1);phone.setNumber("111-111-1111");phone.setProfessor(p);em.persist(p);em.persist(phone);em.persist(address);em.persist(d);}private static void printResult(Object result) {if (result == null) {System.out.print("NULL");} else if (result instanceof Object[]) {Object[] row = (Object[]) result;System.out.print("[");for (int i = 0; i < row.length; i++) {printResult(row[i]);}System.out.print("]");} else if (result instanceof Long || result instanceof Double|| result instanceof String) {System.out.print(result.getClass().getName() + ": " + result);} else {System.out.print(result);}System.out.println();}@PersistenceContextprivate EntityManager em; }
以下代码来自Address.java。
package cn.w3cschool.common;import javax.persistence.Entity; import javax.persistence.Id;@Entity public class Address {@Idprivate int id;private String street;private String city;private String state;private String zip;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getStreet() {return street;}public void setStreet(String address) {this.street = address;}public String getCity() {return city;}public void setCity(String city) {this.city = city;}public String getState() {return state;}public void setState(String state) {this.state = state;}public String getZip() {return zip;}public void setZip(String zip) {this.zip = zip;}public String toString() {return "Address id: " + getId() + ", street: " + getStreet() +", city: " + getCity() +", state: " + getState() +", zip: " + getZip();}}
以下代码来自Professor.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.OneToMany; import javax.persistence.OneToOne; import javax.persistence.Temporal; import javax.persistence.TemporalType;@Entity public class Professor {@Idprivate int id;private String name;private long salary;@Temporal(TemporalType.DATE)private Date startDate;@OneToOneprivate Address address;@OneToMany(mappedBy="employee")private Collection<Phone> phones = new ArrayList<Phone>();@ManyToOneprivate Department department;@ManyToOneprivate Professor manager;@OneToMany(mappedBy="manager")private Collection<Professor> directs = new ArrayList<Professor>();@ManyToMany(mappedBy="employees")private Collection<Project> projects = new ArrayList<Project>();public int getId() {return id;}public void setId(int empNo) {this.id = empNo;}public String getName() {return name;}public void setName(String name) {this.name = name;}public long getSalary() {return salary;}public void setSalary(long salary) {this.salary = salary;}public Date getStartDate() {return startDate;}public void setStartDate(Date startDate) {this.startDate = startDate;}public Collection<Phone> getPhones() {return phones;}public void addPhone(Phone phone) {if (!getPhones().contains(phone)) {getPhones().add(phone);if (phone.getProfessor() != null) {phone.getProfessor().getPhones().remove(phone);}phone.setProfessor(this);}}public Department getDepartment() {return department;}public void setDepartment(Department department) {if (this.department != null) {this.department.getProfessors().remove(this);}this.department = department;this.department.getProfessors().add(this);}public Collection<Professor> getDirects() {return directs;}public void addDirect(Professor employee) {if (!getDirects().contains(employee)) {getDirects().add(employee);if (employee.getManager() != null) {employee.getManager().getDirects().remove(employee);}employee.setManager(this);}}public Professor getManager() {return manager;}public void setManager(Professor manager) {this.manager = manager;}public Collection<Project> getProjects() {return projects;}public void addProject(Project project) {if (!getProjects().contains(project)) {getProjects().add(project);}if (!project.getProfessors().contains(this)) {project.getProfessors().add(this);}}public Address getAddress() {return address;}public void setAddress(Address address) {this.address = address; }public String toString() {return "Professor " + getId() + ": name: " + getName() +", salary: " + getSalary() +", phones: " + getPhones() +", managerNo: " + ((getManager() == null) ? null : getManager().getId()) +", deptNo: " + ((getDepartment() == null) ? null : getDepartment().getId());}}
以下代码来自Phone.java。
package cn.w3cschool.common;import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.ManyToOne;@Entity public class Phone {@Idprivate long id;private String number;private String type;@ManyToOneProfessor employee;public long getId() {return id;}public void setId(long id) {this.id = id;}public String getNumber() {return number;}public void setNumber(String phoneNo) {this.number = phoneNo;}public String getType() {return type;}public void setType(String phoneType) {this.type = phoneType;}public Professor getProfessor() {return employee;}public void setProfessor(Professor employee) {this.employee = employee;}public String toString() {return "Phone id: " + getId() + ", no: " + getNumber() +", type: " + getType();} }
下面的代码来自Department.java。
package cn.w3cschool.common;import java.util.HashSet; import java.util.Set;import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.OneToMany;@Entity public class Department {@Idprivate int id;private String name;@OneToMany(mappedBy="department")private Set<Professor> employees = new HashSet<Professor>();public int getId() {return id;}public void setId(int deptNo) {this.id = deptNo;}public String getName() {return name;}public void setName(String deptName) {this.name = deptName;}public Set<Professor> getProfessors() {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.Inheritance; import javax.persistence.ManyToMany;@Entity @Inheritance public class Project {@Idprotected int id;protected String name;@ManyToManyprotected Collection<Professor> employees = new ArrayList<Professor>();public int getId() {return id;}public void setId(int projectNo) {this.id = projectNo;}public String getName() {return name;}public void setName(String projectName) {this.name = projectName;}public Collection<Professor> getProfessors() {return employees;}public void addProfessor(Professor employee) {if (!getProfessors().contains(employee)) {getProfessors().add(employee);}if (!employee.getProjects().contains(this)) {employee.getProjects().add(this);}}public String toString() {return getClass().getName().substring(getClass().getName().lastIndexOf(".")+1) + " no: " + getId() + ", name: " + getName();} }
上面的代码生成以下结果。
以下是数据库转储。
Table Name: ADDRESSRow:Column Name: ID,Column Type: INTEGER:Column Value: 0Column Name: CITY,Column Type: VARCHAR:Column Value: nullColumn Name: STATE,Column Type: VARCHAR:Column Value: BCColumn Name: STREET,Column Type: VARCHAR:Column Value: nullColumn Name: ZIP,Column Type: VARCHAR:Column Value: nullTable Name: DEPARTMENTRow:Column Name: ID,Column Type: INTEGER:Column Value: 1Column Name: NAME,Column Type: VARCHAR:Column Value: DesignTable Name: PHONERow:Column Name: ID,Column Type: BIGINT:Column Value: 1Column Name: NUMBER,Column Type: VARCHAR:Column Value: 111-111-1111Column Name: TYPE,Column Type: VARCHAR:Column Value: nullColumn Name: EMPLOYEE_ID,Column Type: INTEGER:Column Value: 0Table Name: PROFESSORRow:Column Name: ID,Column Type: INTEGER:Column Value: 0Column Name: NAME,Column Type: VARCHAR:Column Value: TOMColumn Name: SALARY,Column Type: BIGINT:Column Value: 1111Column Name: STARTDATE,Column Type: DATE:Column Value: nullColumn Name: ADDRESS_ID,Column Type: INTEGER:Column Value: 0Column Name: DEPARTMENT_ID,Column Type: INTEGER:Column Value: 1Column Name: MANAGER_ID,Column Type: INTEGER:Column Value: nullTable Name: PROJECTTable Name: PROJECT_PROFESSOR
