Java教程 第57页

JPA教程 – JPA查询AVG简单示例 JPQL中的聚合查询的语法与SQL的语法非常相似。 有五个支持的聚合函数 AVG COUNT MIN MAX SUM 结果可以分组在GROUP BY子句中，并使用HAVING子句进行过滤。 聚合JPQL查询可以使用同一查询中的许多聚合函数： SELECT d, COUNT(e), MAX(e.salary), AVG(e.salary) FROM Department d JOIN d.employees e GROUP BY d HAVING COUNT(e) >= 5 JPQL中的AVG函数计算属性的平均值。 以下行计算平均工资。 "SELECT AVG(e.salary) FROM Professor e") 聚合查询 聚合查询对结果进行分组，并应用聚合函数以获取有关查询结果的摘要信息。 聚合查询的语法如下： SELECT <select_expression> FROM <from_clause> [WHERE <conditional_expression>] [GROUP BY <group_by_clause>] [HAVING <conditional_expression>] [ORDER BY <order_by_clause>] 例子 下面的代码来自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 AVG(e.salary) FROM Professor 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(); d.setId(1); d.setName("Design"); p.setDepartment(d); d.getProfessors().add(p); Phone phone =...

JPA教程 – JPA查询AVG连接GroupBy示例 以下代码显示如何在JPQL中使用连接子句使用AVG。 List l = em.createQuery( "SELECT d.name, AVG(e.salary) FROM Department d JOIN d.employees e GROUP BY d.name") .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;...

JPA教程 – JPA查询COUNT示例 COUNT函数用于计算表中的元素。 "SELECT d.name, COUNT(e), AVG(e.salary) " + "FROM Department d JOIN d.employees e GROUP BY d.name") 例子 下面的代码来自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 d.name, COUNT(e), AVG(e.salary) " + "FROM Department d JOIN d.employees e GROUP BY d.name") .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(); 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(d); } private static void printResult(Object result) { if (result == null) { System.out.print("NULL"); } else if (result instanceof...

JPA教程 – JPA 查询计数其中子查询示例 以下代码显示如何在子查询中使用COUNT函数。 List l = em.createQuery( "SELECT m FROM Professor m WHERE (SELECT COUNT(e) " + "FROM Professor e WHERE e.manager = m) > 0") .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...

JPA教程 – JPA查询函数示例 JPQL支持的函数表达式如下所示。 函数 描述 ABS(number) 返回number参数的无符号版本。结果类型与参数类型(整数，浮点型或双精度型)相同。 CONCAT(string1，string2) 连接string1和string2。 CURRENT_DATE 返回数据库服务器定义的当前日期。 CURRENT_TIME 从数据库服务器返回当前时间。 CURRENT_TIMESTAMP 返回数据库服务器的当前时间戳。 INDEX(identification variable) 返回有序列表中实体的位置。 LENGTH(string) 返回字符串参数中的字符数。 LOCATE(string1，string2 [，start]) 返回string1中string1的位置，可选择从start指示的位置开始。如果找不到字符串，结果为零。 LOWER(string) 返回字符串参数的小写形式。 MOD(number1，number2) 返回数字参数number1和number2的模数作为整数。 SIZE(collection) 返回集合中元素的数量，如果集合为空则返回0。 SQRT(number) 返回数字参数的平方根作为double。 SUBSTRING(string, start, end) 返回输入字符串的一部分，从由start开始到length字符指示的索引开始。字符串索引从一个开始。 UPPER(string) 返回字符串参数的大写形式。 TRIM([[LEADING | TRAILING | BOTH] [char] FROM] string) 删除字符串中的前导和/或尾随字符。如果不使用可选的LEADING，TRAILING或BOTH关键字，则删除前导和尾随字符。默认修剪字符是空格字符。     以下代码显示如何在JPQL中使用CONCAT，SUBSTRING和LOCATE字符串函数。 em.createQuery( "UPDATE Phone p SET p.number = " + "CONCAT("0",SUBSTRING(p.number," + "LOCATE(p.number, "-"), 4)), p.type = "Business" ") .executeUpdate(); 例子 以下代码来自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;...

JPA教程 – JPA查询选择两个属性示例 从别名开始，我们可以使用点（.）运算符浏览实体关系。 以下JPQL从一个实体中选择两个属性。 List l = em.createQuery("SELECT e.name, e.salary FROM Professor e") 例子 下面的代码来自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.name, e.salary FROM Professor e") .getResultList(); for (Object p : l) { printResult(p); } } private void prepareData() { Professor p = new Professor(); p.setId(0); p.setName("TOM"); Department d = new Department(); d.setId(1); d.setName("Design"); p.setDepartment(d); d.getProfessors().add(p); em.persist(p); 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...

JPA教程 – JPA查询选择两个实体示例 以下JPQL从两个实体中选择。 List l = em.createQuery("SELECT d, m FROM Department d, Professor m WHERE d = m.department").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() { return name; }...

JPA教程 – JPA查询简介示例 最简单的JPQL查询选择单个实体类型的所有实例。 考虑下面的查询: SELECT e FROM Employee e JPQL尽可能使用SQL语法。 SQL查询从表中选择。JPQL从应用程序域模型的实体中选择。 语法 选择查询的整体形式如下: SELECT <select_expression> FROM <from_clause> [WHERE <conditional_expression>] [ORDER BY <order_by_clause>] 例子 下面的代码来自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<Professor> l = em.createQuery("SELECT e FROM Professor e").getResultList(); for(Professor p:l){ System.out.println(p); } } private void prepareData(){ Professor p = new Professor(); p.setId(0); p.setName("TOM"); em.persist(p); } @PersistenceContext private EntityManager em; } 以下代码来自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...

JPA教程 – JPA查询左连接示例 以下代码显示了如何在JPQL中使用左连接。 List l = em.createQuery( "SELECT e, d FROM Professor e LEFT JOIN e.department d") .getResultList(); 例子 以下代码来自Phone.java。 package cn.w3cschool.common; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.ManyToOne; @Entity public class Phone { @Id private long id; private String number; private String type; @ManyToOne Professor 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: " +...

JPA教程 – JPA查询连接三个实体示例 以下JPQL显示如何连接三个实体。 List l = em.createQuery( "SELECT DISTINCT p FROM Department d JOIN d.employees e JOIN e.projects p") .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() { return...