当前位置： 首页 > news >正文

JPA 基本查询（五）

news 2026/4/3 3:47:24

JPA 查询Exists示例

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();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 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;
}

下面的代码来自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();}
}

以下代码来自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());}}

以下代码来自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();}
}

以下代码来自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();}
}

下载 Query_Exists.zip
下载 Query_Not_Exists.zip

以下是数据库转储。

Table Name: ADDRESSTable 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: nullColumn Name: DEPARTMENT_ID,Column Type: INTEGER:Column Value: 1Column Name: MANAGER_ID,Column Type: INTEGER:Column Value: nullTable Name: PROJECTTable Name: PROJECT_PROFESSOR

JPA 查询AVG简单示例

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 = 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 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;
}

以下代码来自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();}
}

以下代码来自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();}
}

以下代码来自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());}}

以下代码来自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();}}

下面的代码来自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();}
}

下载 Query_AVG_Simple.zip

上面的代码生成以下结果。

以下是数据库转储。

Table Name: ADDRESSTable 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: nullColumn Name: DEPARTMENT_ID,Column Type: INTEGER:Column Value: 1Column Name: MANAGER_ID,Column Type: INTEGER:Column Value: nullTable Name: PROJECTTable Name: PROJECT_PROFESSOR

JPA 查询AVG连接GroupBy示例

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 {@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();}}

下面的代码来自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();}
}

以下代码来自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();}
}

以下代码来自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());}}

以下代码来自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();}
}

下面的代码来自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, AVG(e.salary) FROM Department d JOIN d.employees e GROUP BY d.name").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;
}

下载 Query_AVG_Join_GroupBy.zip

上面的代码生成以下结果。

以下是数据库转储。

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

JPA 查询COUNT示例

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 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;
}

以下代码来自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();}
}

以下代码来自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();}}

以下代码来自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();}
}

以下代码来自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());}}

下面的代码来自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();}
}

下载 Query_AVG.zip

上面的代码生成以下结果。

以下是数据库转储。

Table Name: ADDRESSTable 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: nullColumn Name: DEPARTMENT_ID,Column Type: INTEGER:Column Value: 1Column Name: MANAGER_ID,Column Type: INTEGER:Column Value: nullTable Name: PROJECTTable Name: PROJECT_PROFESSOR

JPA 查询计数其中子查询示例

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 {@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());}}

下面的代码来自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();}
}

下面的代码来自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 m FROM Professor m WHERE (SELECT COUNT(e) "+ "FROM Professor e WHERE e.manager = m) > 0").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);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 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;
}

以下代码来自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();}
}

以下代码来自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();}}

以下代码来自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();}
}

下载 Query_Count_Where_Subquery.zip

以下是数据库转储。

Table Name: ADDRESSTable Name: DEPARTMENTRow:Column Name: ID,Column Type: INTEGER:Column Value: 1Column Name: NAME,Column Type: VARCHAR:Column Value: DesignTable Name: PHONETable 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: nullColumn Name: DEPARTMENT_ID,Column Type: INTEGER:Column Value: 1Column Name: MANAGER_ID,Column Type: INTEGER:Column Value: nullTable Name: PROJECTTable Name: PROJECT_PROFESSOR

JPA 查询函数示例

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 {@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();}
}

下面的代码来自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();em.createQuery("UPDATE Phone p SET p.number = "+ "CONCAT("0",SUBSTRING(p.number,"+ "LOCATE(p.number, "-"), 4)), p.type = "Business" ").executeUpdate();}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 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;
}

以下代码来自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();}
}

以下代码来自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());}}

以下代码来自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();}}

下面的代码来自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();}
}

下载 Query_Substring.zip

以下是数据库转储。

Table Name: ADDRESSTable 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: 0111Column Name: TYPE,Column Type: VARCHAR:Column Value: BusinessColumn 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: nullColumn Name: DEPARTMENT_ID,Column Type: INTEGER:Column Value: 1Column Name: MANAGER_ID,Column Type: INTEGER:Column Value: nullTable Name: PROJECTTable Name: PROJECT_PROFESSOR

相关文章：

JPA 基本查询（五）

JPA 查询Exists示例 JPA教程 - JPA 查询Exists示例 如果子查询返回任何行&#xff0c;则EXISTS条件返回true。 以下代码显示如何在JPQL中使用带有子查询的EXISTS运算符。 List l em.createQuery("SELECT e FROM Professor e WHERE EXISTS " "(SELECT p FROM …...

编程日记 2024/12/28 5:53:43

hiprint结合vue2项目实现静默打印详细使用步骤

代码地址是&#xff1a;vue-plugin-hiprint: hiprint for Vue2/Vue3 ⚡打印、打印设计、可视化设计器、报表设计、元素编辑、可视化打印编辑 本地安装包地址&#xff1a;electron-hiprint 发行版 - Gitee.com 1、先安装hipint安装包在本地 2、项目运行npm&#xff08;socket.…...

编程日记 2024/12/28 5:51:41

项目报 OutOfMemoryError 、GC overhead limit exceeded 问题排查以及解决思路实战

项目报 OutOfMemoryError、GC overhead limit exceeded 问题排查以及解决思路实战 前言&#xff1a; 问题现象描述&#xff1a; 1&#xff0c;生产环境有个定时任务&#xff0c;没有初始化告警数据【告警数据量为1000多个】 2&#xff0c;其他定时任务执行正常 3&#xff0c;查…...

编程日记 2024/12/28 5:47:31

【计算机-显示屏灰阶测试】

硬计算机-显示屏灰阶测试 ■ 对比度■ 清晰度■ 灰度色阶&#xff08;色带&#xff09;■ 对比率■■ ■ 对比度 在一个性能良好的显示器上&#xff0c;您可观察到每种颜色的标尺都可分为从 1 至 32、大致上等宽但不同亮度的色带。即使是在刻度1处的色带也应该隐约可见。 一个…...

编程日记 2024/12/28 5:46:30

CSS系列（40）-- Container Queries详解

前端技术探索系列&#xff1a;CSS Container Queries详解 &#x1f4e6; 致读者&#xff1a;探索组件响应式的艺术 &#x1f44b; 前端开发者们&#xff0c; 今天我们将深入探讨 CSS Container Queries&#xff0c;这个强大的组件级响应式特性。 基础概念 &#x1f680; 容…...

编程日记 2024/12/28 5:45:25

工作生活做事慢效率低原因及解决方案

时间和效率管理具体版&#xff08;初阶&#xff09;&#xff08;一&#xff09; 工作&生活做事慢效率低原因及解决方案 一、效率慢的原因&#xff08;动物解析法&#xff08;编者自创&#xff09;&#xff09; 打败你的可能是生活的小事 1.无头苍蝇无流程 做事之前没有想…...

编程日记 2024/12/28 5:44:23

各种数据库类型介绍

在软件开发和数据处理领域&#xff0c;数据库扮演着至关重要的角色。它们用于存储、检索和管理大量数据&#xff0c;是信息系统不可或缺的基础。以下是几种常用的数据库类型及其简要介绍&#xff1a; 1.关系型数据库&#xff08;Relational Databases&#xff09; 关系型数据库…...

编程日记 2024/12/28 5:42:21

了解智能运维

智能运维 &#xff08;一&#xff09;运维工作的转变 随着技术发展&#xff0c;运维工作从基础的搬机器、插网线、装系统等体力活儿&#xff0c;逐渐转变为更侧重服务器管理、代码管理、日志分析、监控告警、流量管理及故障排查等的脑力劳动。如今&#xff0c;运维人员拿到的…...

编程日记 2024/12/28 5:41:20

js实现仿windows文件名称排序

引言&#xff1a; 在JavaScript中&#xff0c;数组排序是一个常见的操作&#xff0c;但默认的Array.sort()方法只能进行简单的字符串比较。在处理复杂数据时&#xff0c;我们需要自定义排序函数来满足特定的需求。本文将通过一个具体的代码示例&#xff0c;解释如何实现一个仿w…...

编程日记 2024/12/28 5:39:19

基于Oauth2的SSO单点登录---前端

Vue-element-admin 是一个基于 Vue.js 和 Element UI 的后台管理系统框架&#xff0c;提供了丰富的组件和功能&#xff0c;可以帮助开发者快速搭建现代化的后台管理系统。 一、基本知识 &#xff08;一&#xff09;Vue-element-admin 的主要文件和目录 vue-element-admin/ |--…...

编程日记 2024/12/28 5:38:17

springboot 使用注解设置缓存时效

springboot 使用注解设置缓存时效 import org.apache.commons.lang3.StringUtils; import org.springframework.data.redis.cache.RedisCache; import org.springframework.data.redis.cache.RedisCacheConfiguration; import org.springframework.data.redis.cache.RedisCach…...

编程日记 2024/12/28 5:37:14

QGIS二次开发（地图符号库操作）

实习三 地图符号库操作 3.1 任务要求 基于QGIS&#xff0c;实现地图符号的设计/存储与显示&#xff1b;基于QGIS实现一个点、线、面shp矢量图层文件的显示。通过设置引用的符号&#xff0c;改变矢量图层的显示效果&#xff1b;可编辑地图的符号库汇中的点符号、线符号、面符号…...

编程日记 2024/12/28 5:36:12

线性代数行列式

目录 二阶与三阶行列式 二元线性方程组与二阶行列式 三阶行列式 全排列和对换 排列及其逆序数 对换 n阶行列式的定义 行列式的性质 二阶与三阶行列式 二元线性方程组与二阶行列式 若是采用消元法解x1、x2的话则得到以下式子 有二阶行列式的规律可得&#xff1a;分…...

编程日记 2024/12/28 5:34:11

Vision Transformer (ViT) 论文的第二句话

Vision Transformer (ViT) 论文的第二句话 flyfish 原句&#xff1a; “In vision, attention is either applied in conjunction with convolutional networks, or used to replace certain components of convolutional networks while keeping their overall structure in…...

编程日记 2024/12/28 5:29:05

Github 2024-12-27 Java开源项目日报Top10

根据Github Trendings的统计，今日(2024-12-27统计)共有10个项目上榜。根据开发语言中项目的数量，汇总情况如下： 开发语言项目数量Java项目9Kotlin项目1C#项目1非开发语言项目1C++项目1《Hello 算法》：动画图解、一键运行的数据结构与算法教程 创建周期：476 天协议类型：Ot…...

编程日记 2024/12/28 5:28:04

气相色谱-质谱联用分析方法中的常用部件，分流平板更换

分流平板&#xff0c;是气相色谱-质谱联用分析方法中的一个常用部件&#xff0c;它可以实现气相色谱柱流与MS检测器流的分离和分流。常见的气质联用仪分流平板有很多种&#xff0c;如单层T型分流平板、双层T型分流平板、螺旋分流平板等等。 操作视频http://www.spcctech.com/v…...

编程日记 2024/12/28 5:27:03

centos7 免安装mysql5.7及配置（支持多个mysql）

一&#xff09; 下载免安装包&#xff1a; mysql下载地址: https://dev.mysql.com/downloads/mysql/下载时&#xff0c;选择以前5.7版本&#xff1a; image 下载第一个TAR压缩包&#xff1a; image 二&#xff09; 定义安装路径并解压安装包 1、假设需要把MySQL放到 /usr/local…...

编程日记 2024/12/28 5:25:01

Python的Pandas--Series的创建和实现

1.Series函数的格式&#xff1a; pandas.Series(data,index,dtype,name,copy) data&#xff1a;一组数据&#xff08;ndarray类型、list、dict等类&#xff09;或标量值 index&#xff1a;数据索引标签。如果不指定&#xff0c;默认为整数&#xff0c;从0开始 dtype&#x…...

编程日记 2024/12/28 5:23:59

OCR实践-问卷表格统计

前言 书接上文 OCR实践—PaddleOCROCR实践-Table-Transformer 本项目代码已开源 放在 Github上&#xff0c;欢迎参考使用&#xff0c;Star https://github.com/caibucai22/TableAnalysisTool 主要功能说明&#xff1a;对手动拍照的问卷图片进行统计分数&#xff08;对应分数…...

编程日记 2024/12/28 5:22:56

uniapp中的条件编译

在script中 // #ifdef APP-PLUS console.log("11"); // #endif// #ifdef MP-WEIXIN console.log("22"); // #endif 在template中 <!-- #ifdef APP-PLUS --><view>哈哈哈</view> <!-- #endif --><!-- #ifdef MP-WEIXIN -->…...

编程日记 2024/12/28 5:21:53

【仅限头部车企工控厂商内部流通】C++27静态反射安全合规包：覆盖MISRA C++:2023 Annex A.12及AUTOSAR C++14兼容性桥接层

第一章&#xff1a;C27 静态反射工业应用案例C27 将正式引入标准化的静态反射&#xff08;Static Reflection&#xff09;核心特性&#xff0c;基于 std::reflexpr 和反射元对象模型&#xff08;ROM&#xff09;&#xff0c;使编译期类型结构可被直接查询与遍历&#xff0c;无需…...

编程新知 2026/4/3 2:29:51

GESP2025年3月认证C++三级( 第一部分选择题（1-8））

&#x1f3af; 第1题&#xff1a;Base64魔法箱&#x1f4dc; 题目核心&#xff1a; &#x1f449; 每 3个字节 → 变成4个字节1、&#x1f9e0; 故事理解有一个魔法机器&#xff1a;&#x1f449; 每放进去 3个苹果 &#x1f34e;&#x1f34e;&#x1f34e;&#xff08;不足3个…...

编程新知 2026/4/3 2:15:43

新手福音：在快马平台用一句话描述，AI帮你生成专属技能展示网页代码

作为一个刚入门编程的新手&#xff0c;想要展示自己的技能却无从下手&#xff1f;最近我发现了一个超级友好的工具&#xff0c;完全是为我们这种小白量身定做的。只需要简单描述需求&#xff0c;就能自动生成一个完整的个人技能展示网页项目&#xff0c;而且所有代码都带着详细…...

编程新知 2026/4/3 1:34:51

嵌入式系统接口技术详解与应用实践

1. 嵌入式系统接口技术概述在嵌入式系统开发中&#xff0c;接口技术是连接处理器与外部设备的关键桥梁。作为一名嵌入式开发工程师&#xff0c;我经常需要根据项目需求选择合适的接口方案。本文将基于多年实战经验&#xff0c;深入解析各类嵌入式接口的工作原理、应用场景和选型…...

编程新知 2026/4/3 0:56:38

Shox96 Progmem：嵌入式Flash短字符串高效压缩方案

1. Shox96 Progmem 压缩库技术解析&#xff1a;面向嵌入式 Flash 的短字符串高效压缩方案1.1 工程背景与设计动因在资源受限的嵌入式系统中&#xff0c;Flash 存储空间始终是关键瓶颈。以典型 Cortex-M0/M3 MCU&#xff08;如 STM32F072、nRF52832&#xff09;为例&#xff0c;…...

编程新知 2026/4/3 0:54:37

OpenClaw故障排除手册：千问3.5-9B连接问题大全

OpenClaw故障排除手册&#xff1a;千问3.5-9B连接问题大全 1. 开篇&#xff1a;为什么需要这份手册&#xff1f; 上周我在本地部署OpenClaw对接千问3.5-9B模型时&#xff0c;连续遇到了三个诡异的连接错误。每次报错都像在玩解谜游戏——错误信息含糊不清&#xff0c;社区讨论…...

编程新知 2026/4/3 0:38:31

并联混合动力船舶能量管理策略与SOC约束优化研究

并联混合动力船舶能量管理策略与SOC约束优化研究 摘要 本文针对并联混合动力船舶能量管理问题，基于等效燃油消耗最小化策略（ECMS），构建了包含柴油机、电动机、电池及船舶动力学系统的仿真模型。通过调整电池荷电状态（SOC）约束范围，分析其对燃油经济性、电池寿命及系统…...

编程新知 2026/4/3 0:04:02

关于准备智慧校园建设专项资金申报材料的参考清单

✅作者简介&#xff1a;合肥自友科技 &#x1f4cc;核心产品&#xff1a;智慧校园平台(包括教工管理、学工管理、教务管理、考务管理、后勤管理、德育管理、资产管理、公寓管理、实习管理、就业管理、离校管理、科研平台、档案管理、学生平台等26个子平台) 。公司所有人员均有多…...

编程新知 2026/4/2 23:25:00

利用快马平台快速构建b站a8直播观看页面原型

利用快马平台快速构建B站A8直播观看页面原型 最近想尝试开发一个B站A8直播的观看页面原型&#xff0c;主要想验证一下直播相关的技术方案。作为一个前端开发者&#xff0c;我深知从头开始搭建这样一个页面需要花费不少时间&#xff0c;特别是在处理视频流、弹幕互动和响应式设…...

编程新知 2026/4/2 23:20:59

告别截图贴图！用MATLAB的text函数+LaTeX，在图像任意位置添加公式注释（含α, β, ∑等符号）

科研图像标注革命&#xff1a;MATLABLaTeX实现动态公式嵌入全攻略 在学术论文与科研报告中&#xff0c;数据可视化图表的质量直接影响研究成果的呈现效果。传统方式中&#xff0c;研究者往往需要先导出图像&#xff0c;再通过第三方软件&#xff08;如Photoshop或PPT&#xff0…...

编程新知 2026/4/2 22:46:22