惠州如何在JPQL中使用连接子句使用AVG？_北大青鸟IT学校

相信大家在学习Java的过程都会遇到各种各样的问题，例如：如何在JPQL中使用连接子句使用AVG？那么下面我们一起来看看惠州北大青鸟学术部老师是怎么讲解这道题的。

以下代码显示如何在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;

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

}

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

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

以下代码来自Phone.java。

package cn.w3cschool.common;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.ManyToOne;

@Entitypublic 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;

@Entitypublic 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;
   }    
   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
@Inheritancepublic 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() +
               ", 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;

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

 @PersistenceContext  private EntityManager em;
}

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

以下是数据库转储。

Table Name: ADDRESS
Row:
   Column Name: ID,
   Column Type: INTEGER:
   Column Value: 0

   Column Name: CITY,
   Column Type: VARCHAR:
   Column Value: null

   Column Name: STATE,
   Column Type: VARCHAR:
   Column Value: BC

   Column Name: STREET,
   Column Type: VARCHAR:
   Column Value: null

   Column Name: ZIP,
   Column Type: VARCHAR:
   Column Value: null





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: 0

   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

更多Java资讯关注网站动态，或者来惠州北大青鸟新方舟校区了解一下。

Java

标签：惠州计算机JAVA软件开发惠州计算机Java软件开发惠州计算机JAVA培训惠州计算机JAVA软件开发学校惠州计算机Java软件开发培训JAVAJava软件开发北大青鸟IT计算机学校北大青鸟IT软件学校北大青鸟IT学校