当前位置：首页 >数据库 > JPA标准GROUP BY子句

JPA标准GROUP BY子句

GROUP BY子句用于从一个或多个表中收集数据并将它们排列在一个分组中。在Criteria API中，AbstractQuery接口的groupBy()方法用于过滤记录并对它们进行分组。

标准GROUP BY示例

在这里，我们将在student表上执行多个GROUP BY操作。假设该表包含以下记录 -

DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `S_ID` int(11) NOT NULL, `S_NAME` varchar(255) DEFAULT NULL, `S_AGE` int(11) DEFAULT NULL, PRIMARY KEY (`S_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES ('101', 'Gaurav', '24'); INSERT INTO `student` VALUES ('102', 'Rahul', '22'); INSERT INTO `student` VALUES ('103', 'Chris', '20'); INSERT INTO `student` VALUES ('104', 'Ronit', '26'); INSERT INTO `student` VALUES ('105', 'Roy', '21');

现在，请按照以下步骤执行操作: -

第1步: 创建一个实体类。在com.yiibai.jpa包下创建了StudentEntity.java类文件。该类包含三个属性:s_id，s_name，s_age以及所有必需的注解。

文件:StudentEntity.java -

package com.yiibai.jpa;import javax.persistence.*; @Entity @Table(name = "student") public class StudentEntity { @Id private int s_id; private String s_name; private int s_age; public StudentEntity(int s_id, String s_name, int s_age) { super(); this.s_id = s_id; this.s_name = s_name; this.s_age = s_age; } public StudentEntity() { super(); } public int getS_id() { retu s_id; } public void setS_id(int s_id) { this.s_id = s_id; } public String getS_name() { retu s_name; } public void setS_name(String s_name) { this.s_name = s_name; } public int getS_age() { retu s_age; } public void setS_age(int s_age) { this.s_age = s_age; } }

第2步: 将实体类和其他数据库配置映射到 persistence.xml 文件中。

文件:Persistence.xml -

<?xml version="1.0" encoding="UTF-8"?><persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"> <persistence-unit name="Student_details"> <class>com.yiibai.jpa.StudentEntity</class> <properties> <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" /> <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/testdb?serverTimezone=UTC" /> <property name="javax.persistence.jdbc.user" value="root" /> <property name="javax.persistence.jdbc.password" value="123456" /> <property name="eclipselink.logging.level" value="SEVERE" /> <property name="eclipselink.ddl-generation" value="create-or-extend-tables" /> </properties> </persistence-unit> </persistence>

这里，我们创建了基本实体类并将配置映射到persistence.xml 文件中，可以通过以下方式执行不同类型的GROUP BY排序操作 -

在这里，我们将通过一个简单的示例演示如何从数据库指定GROUP BY条件查询获取分组后的数据。

文件:StudentGroup.java -

package com.yiibai.jpa.jpql; import com.yiibai.jpa.StudentEntity; import javax.persistence.*; import javax.persistence.criteria.*; import java.util.*; public class StudentGroup { public static void main(String args[]) { EntityManagerFactory emf = Persistence.createEntityManagerFactory("Student_details"); EntityManager em = emf.createEntityManager(); em.getTransaction().begin(); CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class); Root<StudentEntity> stud = cq.from(StudentEntity.class); cq.multiselect(stud.get("s_age"), cb.count(stud)).groupBy(stud.get("s_age")); System.out.print("s_age"); System.out.println("\t Count"); List<Object[]> list = em.createQuery(cq).getResultList(); for (Object[] object : list) { System.out.println(object[0] + " " + object[1]); } em.getTransaction().commit(); em.close(); emf.close(); } }