Java教程 第56页

JPA教程 – JPA查询Is Empty示例 IS EMPTY运算符是IS NULL的逻辑等价物，但是对于集合。 查询可以使用IS EMPTY运算符或IS NOT EMPTY来检查集合关联路径是否解析为空集合或至少有一个值。 我们可以使用EMPTY来检查属性是否为空。 以下JPQL显示如何使用EMPTY获取员工withno项目。 Query unassignedQuery = em.createQuery("SELECT e " + "FROM Employee e " + "WHERE e.projects IS EMPTY"); 例子 下面的代码来自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"; Query unassignedQuery = em.createQuery("SELECT e " + "FROM Employee e " + "WHERE e.projects IS EMPTY"); List l = unassignedQuery.getResultList(); System.out.println(l); } public Collection<Employee> findAllEmployees() { return...

JPA教程 – JPA查询Like示例 JPQL支持SQL LIKE操作符，以提供有限形式的字符串模式匹配。 每个LIKE表达式由要搜索的字符串表达式，以及定义匹配条件的模式字符串和可选转义序列组成。 模式字符串使用的通配符是单字符通配符的下划线(_)和多字符通配符的百分号(%)。 在下面的JPQL中，我们使用两个下划线字符的前缀来通配字符串候选的前两个字符。 SELECT d FROM Department d WHERE d.name LIKE "__Eng%" 模式匹配区分大小写。 例子 下面的代码来自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 { @Id private 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(); } } 以下代码来自Address.java。 package cn.w3cschool.common; import javax.persistence.Entity; import javax.persistence.Id; @Entity public class Address {...

JPA教程 – JPA查询OrderBy两个属性示例 以下代码显示如何按两个属性排序，一个升序，另一个降序。 List l = em.createQuery("SELECT e FROM Professor e " + "JOIN e.department d ORDER BY d.name, e.name DESC") .getResultList(); 例子 以下代码来自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 { @Id private int id; private String name; private long salary; @Temporal(TemporalType.DATE) private Date startDate; @OneToOne private Address address; @OneToMany(mappedBy="employee") private Collection<Phone> phones = new ArrayList<Phone>(); @ManyToOne private Department department; @ManyToOne private 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()...

JPA教程 – JPA查询OrderBy描述示例 以下代码显示如何按实体的属性按降序排序。 List l = em.createQuery("SELECT e FROM Professor e ORDER BY e.name DESC") .getResultList(); 例子 以下代码来自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 { @Id protected int id; protected String name; @ManyToMany protected 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()...

JPA教程 – JPA查询AND条件示例 以下JPQL显示如何使用AND组合两个条件。 "SELECT e FROM Professor e WHERE e.salary >= 40 AND e.salary <= 50") 例子 以下代码来自Address.java。 package cn.w3cschool.common; import javax.persistence.Entity; import javax.persistence.Id; @Entity public class Address { @Id private 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...

JPA教程 – JPA查询日期参数示例 我们可以在查询中使用日期类型值。 以下代码使用EntityManager创建具有两个参数的查询。 然后它传递两个日期类型值。 em.createQuery("SELECT e " + "FROM Professor e " + "WHERE e.startDate BETWEEN :start AND :end") .setParameter("start", new Date(), TemporalType.DATE) .setParameter("end", new Date(), TemporalType.DATE) .getResultList(); 例子 以下代码来自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.NamedNativeQuery; import javax.persistence.OneToMany; import javax.persistence.OneToOne; import javax.persistence.Table; import javax.persistence.Temporal; import javax.persistence.TemporalType; @Entity @Table(name = "EMP") public class Professor { @Id private int id; private String name; private long salary; @Temporal(TemporalType.DATE) private Date startDate; @OneToOne private Address address; @OneToMany(mappedBy="employee") private Collection<Phone> phones = new ArrayList<Phone>(); @ManyToOne private Department department; @ManyToOne private 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...

JPA教程 – JPA查询编号索引参数示例 以下代码显示了如何在JPQL中使用数字索引参数。 Collection expired = em.createQuery(“SELECT u FROM Professor u WHERE u.id > ?1”).setParameter(1, 1).getResultList(); 例子 以下代码来自Address.java。 package cn.w3cschool.common; import javax.persistence.Entity; import javax.persistence.Id; @Entity public class Address { @Id private 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...

JPA教程 – JPA 查询实体参数值示例 以下代码显示如何将实体值作为JPQL的参数传递。 List l = em.createQuery( "SELECT e " + "FROM Professor e " + "WHERE e.department = :dept AND " + " e.salary = (SELECT MAX(e2.salary) " + " FROM Professor e2 " + " WHERE e2.department = :dept)") .setParameter("dept", d) .getResultList(); 例子 下面的代码来自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 { @Id private 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...

JPA教程 – JPA查询命名参数示例 JPQL支持两种类型的参数绑定语法。 第一种是位置绑定，其中参数在查询字符串中通过问号后面跟随参数号来指示。 执行查询时，开发人员指定应替换的参数编号。 SELECT e FROM Employee e WHERE e.department = ?1 AND e.salary > ?2 命名参数在查询字符串中用冒号后跟参数名称指示。 当执行查询时，开发人员指定应该替换的参数名称。 这种类型的参数允许更具描述性的参数说明符。 SELECT e FROM Employee e WHERE e.department = :dept AND e.salary > :base 以下代码显示如何使用命名参数。 List l = em.createQuery( "SELECT e " + "FROM Professor e " + "WHERE e.department = :dept AND " + " e.salary = (SELECT MAX(e2.salary) " + " FROM Professor e2 " + " WHERE e2.department = :dept)") .setParameter("dept", d) .getResultList(); 例子 以下代码来自Address.java。 package cn.w3cschool.common; import javax.persistence.Entity; import javax.persistence.Id; @Entity public class Address { @Id private 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 =...

JPA教程 – JPA 查询Exists示例 如果子查询返回任何行，则EXISTS条件返回true。 以下代码显示如何在JPQL中使用带有子查询的EXISTS运算符。 List l = em.createQuery( "SELECT e FROM Professor e WHERE EXISTS " + "(SELECT p FROM Phone p WHERE p.employee = e)") .getResultList(); 以下代码显示如何使用NOT EXISTS。 List l = em.createQuery( "SELECT e FROM Professor e WHERE NOT EXISTS (SELECT p FROM e.phones p)") .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 EXISTS " + "(SELECT p FROM Phone p WHERE p.employee = e)") .getResultList(); for (Object p : l) { printResult(p); } } private void prepareData() { Professor p = new Professor(); p.setId(0); p.setName("TOM"); p.setSalary(1111L); Department d = new Department();...