JPA标准GROUPBY⼦句
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');
SQL
现在，请按照以下步骤执⾏操作: -
第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() {
return s_id;
}
public void setS_id(int s_id) {
this.s_id = s_id;
}
public String getS_name() {
return s_name;
}
public void setS_name(String s_name) {
this.s_name = s_name;
}
public int getS_age() {
return s_age;
}
public void setS_age(int s_age) {
this.s_age = s_age;
}
Java
第2步: 将实体类和其他数据库配置映射到l⽂件中。
⽂件:l -
<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
xmlns="/xml/ns/persistence"
xmlns:xsi="/2001/XMLSchema-instance"
xsi:schemaLocation="/xml/ns/persistence /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="sql.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"
groupby分组value="create-or-extend-tables" />
</properties>
</persistence-unit>
</persistence>
XML
这⾥，我们创建了基本实体类并将配置映射到l⽂件中，可以通过以下⽅式执⾏不同类型的GROUP BY排序操作 -在这⾥，我们将通过⼀个简单的⽰例演⽰如何从数据库指定GROUP BY条件查询获取分组后的数据。
⽂件:StudentGroup.java -
package com.yiibai.jpa.jpql;
import com.yiibai.jpa.StudentEntity;
import javax.persistence.*;
import iteria.*;
import java.util.*;
public class StudentGroup {
public static void main(String args[]) {
EntityManagerFactory emf = ateEntityManagerFactory("Student_details");
EntityManager em = ateEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class);
Root<StudentEntity> stud = cq.from(StudentEntity.class);
cq.("s_age"), cb.count(stud)).("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.close();
emf.close();
}
}
Java
执⾏上⾯代码，得到以下输出结果 -
s_age    Count
20    1
21    1
24    1 26    1

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