mysql jpa总分数_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();

}

@PersistenceContext

private 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 {

@Id

protected int id;

protected String name;

@ManyToMany

protected Collection employees = new ArrayList();

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 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 {

@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 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 {

@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: " + 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 {

@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 phones = new ArrayList();

@ManyToOne

private Department department;

@ManyToOne

private Professor manager;

@OneToMany(mappedBy="manager")

private Collection directs = new ArrayList();

@ManyToMany(mappedBy="employees")

private Collection projects = new ArrayList();

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 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 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 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 {

@Id

private int id;

private String name;

@OneToMany(mappedBy="department")

private Set employees = new HashSet();

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 getProfessors() {

return employees;

}

public String toString() {

return "Department no: " + getId() +

", name: " + getName();

}

}下载 Query_AVG.zip

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

以下是数据库转储。Table Name: ADDRESS

Table Name: DEPARTMENT

Row:

Column Name: ID,

Column Type: INTEGER:

Column Value: 1

Column Name: NAME,

Column Type: VARCHAR:

Column Value: Design

Table Name: PHONE

Row:

Column Name: ID,

Column Type: BIGINT:

Column Value: 1

Column Name: NUMBER,

Column Type: VARCHAR:

Column Value: 111-111-1111

Column Name: TYPE,

Column Type: VARCHAR:

Column Value: null

Column Name: EMPLOYEE_ID,

Column Type: INTEGER:

Column Value: 0

Table Name: PROFESSOR

Row:

Column Name: ID,

Column Type: INTEGER:

Column Value: 0

Column Name: NAME,

Column Type: VARCHAR:

Column Value: TOM

Column Name: SALARY,

Column Type: BIGINT:

Column Value: 1111

Column Name: STARTDATE,

Column Type: DATE:

Column Value: null

Column Name: ADDRESS_ID,

Column Type: INTEGER:

Column Value: null

Column Name: DEPARTMENT_ID,

Column Type: INTEGER:

Column Value: 1

Column Name: MANAGER_ID,

Column Type: INTEGER:

Column Value: null

Table Name: PROJECT

Table Name: PROJECT_PROFESSOR