This quick example shows how to map query result obtained via groupBy() to a user defined object.
@Entity
public class Person {
@Id
@GeneratedValue
private long id;
private String name;
private String dept;
.............
}
public class DeptGroup {
private String name;
private long count;
public DeptGroup(String name, long count) {
this.name = name;
this.count = count;
}
.............
}
public class ExampleMain {
private static EntityManagerFactory emf;
public static void main(String[] args) {
emf = Persistence.createEntityManagerFactory("example-unit");
try {
nativeQuery("SHOW COLUMNS from person");
persistPersons();
nativeQuery("Select * from person");
runGroupByQuery();
} finally {
emf.close();
}
}
private static void persistPersons() {
System.out.println("-- persisting persons --");
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
List<String> deptList = Arrays.asList("Admin", "IT", "Sell");
for (int i = 1; i <= 20; i++) {
Person p = new Person();
p.setName("Person name " + i);
//set dept randomly
int index = ThreadLocalRandom.current()
.nextInt(0, 3);
p.setDept(deptList.get(index));
em.persist(p);
}
em.getTransaction().commit();
em.close();
System.out.println("-- persisting persons finished --");
}
private static void runGroupByQuery() {
System.out.println("-- running group by criteria query --");
System.out.println("-- dept groups having count more than 5 --");
EntityManager em = emf.createEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<DeptGroup> criteriaQuery = cb.createQuery(DeptGroup.class);
Root<Person> root = criteriaQuery.from(Person.class);
Expression<String> groupByExp = root.get("dept").as(String.class);
Expression<Long> countExp = cb.count(groupByExp);
CriteriaQuery<DeptGroup> select =
criteriaQuery.multiselect(groupByExp, countExp);
criteriaQuery.groupBy(groupByExp);
criteriaQuery.having(cb.gt(cb.count(root), 5));
//ordering by count in descending order
criteriaQuery.orderBy(cb.desc(countExp));
TypedQuery<DeptGroup> query = em.createQuery(select);
List<DeptGroup> resultList = query.getResultList();
resultList.forEach(System.out::println);
}
public static void nativeQuery(String s) {
System.out.printf("'%s'%n", s);
EntityManager em = emf.createEntityManager();
Query query = em.createNativeQuery(s);
List list = query.getResultList();
for (Object o : list) {
if (o instanceof Object[]) {
System.out.println(Arrays.toString((Object[]) o));
} else {
System.out.println(o);
}
}
em.close();
}
} Output'SHOW COLUMNS from person' [ID, BIGINT(19), NO, PRI, NULL] [DEPT, VARCHAR(255), YES, , NULL] [NAME, VARCHAR(255), YES, , NULL] -- persisting persons -- -- persisting persons finished -- 'Select * from person' [1, Admin, Person name 1] [2, Admin, Person name 2] [3, Sell, Person name 3] [4, IT, Person name 4] [5, Sell, Person name 5] [6, Admin, Person name 6] [7, Sell, Person name 7] [8, IT, Person name 8] [9, IT, Person name 9] [10, Sell, Person name 10] [11, Sell, Person name 11] [12, Sell, Person name 12] [13, Admin, Person name 13] [14, Admin, Person name 14] [15, Admin, Person name 15] [16, IT, Person name 16] [17, IT, Person name 17] [18, IT, Person name 18] [19, Sell, Person name 19] [20, Sell, Person name 20] -- running group by criteria query -- -- dept groups having count more than 5 -- DeptGroup{name='Sell', count=8} DeptGroup{name='IT', count=6} DeptGroup{name='Admin', count=6}
Also check out this tutorial to understand how multiselect() method works.
Example ProjectDependencies and Technologies Used: - h2 1.4.195: H2 Database Engine.
- hibernate-core 5.2.10.Final: The core O/RM functionality as provided by Hibernate.
Implements javax.persistence:javax.persistence-api version 2.1 - JDK 1.8
- Maven 3.3.9
|
|