jpa+mysql分组查询_JPA查询GroupByHaving⽰例JPA教程 - JPA查询GroupBy Having例⼦
以下代码显⽰了如何在JPQL中使⽤GROUP BY和HAVING⼦句。List l = em.createQuery(
"SELECT d.name, AVG(e.salary) FROM Department d ployees e " +
"WHERE e.directs IS EMPTY " +
"GROUP BY d.name " +
"HAVING AVG(e.salary) > 50")
例⼦
以下代码来⾃Address.java。package cn.w3cschoolmon;
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();
}
}
下⾯的代码来⾃PersonDaoImpl.java。package cn.w3cschoolmon;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import ansaction.annotation.Transactional;
@Transactional
public class PersonDaoImpl {
public void test() {
prepareData();
List l = em.createQuery(
"SELECT d.name, AVG(e.salary) FROM Department d ployees e " +
"WHERE e.directs IS EMPTY " +
"GROUP BY d.name " +
"HAVING AVG(e.salary) > 50")
.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);
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.Class().getName() + ": " + result);
} else {
System.out.print(result);
}
System.out.println();
}
@PersistenceContext
private EntityManager em;
}
以下代码来⾃Project.java。package cn.w3cschoolmon; 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 (!Projects().contains(this)) {
}
}
public String toString() {
return getClass().getName().substring(getClass().getName().lastIndexOf(".")+1) + " no: " + getId() +
", name: " + getName();
}
}
以下代码来⾃Phone.java。package cn.w3cschoolmon;
import javax.persistence.Entity;
groupby分组
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) {

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。