This tutorial will show how to select columns other than entities.
Entity selection
In previous examples we saw how to select entities by using CriteriaQuery#select() i.e.
CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);//query object
Root<Employee> employee = query.from(Employee.class);//from part
query.select(employee);//selection part
List<Employee> employees = entityManager.createQuery(query).getResultList();
Single field selection
First, let's have a look at select() method signature in CriteriaQuery class:
CriteriaQuery<T> select(Selection<? extends T> selection);
The parameter Selection defines an item that is to be returned in a query result. In above entity selection example we selected Root<Employee> (query.select(employee)). Root class also implements Selection interface (Root > From > Path > Expression> Selection). To select a single field other entities we can use Root.get() method which returns a Path instance (also a sub-interface of Selection). For example:
CriteriaQuery<String> query = criteriaBuilder.createQuery(String.class);//query object
Root<Employee> employee = query.from(Employee.class);//from part
query.select(employee.get("name"));//single field selection
List<String> list = entityManager.createQuery(query).getResultList()
Or using metamodel object:
query.select(employee.get(Employee_.name));//single field selection
Multiple fields selection
To select multiple fields, we need to pass CompoundSelection (sub-interface of Selection ) to CriteriaQuery#select() method.
One way to create CompoundSelection is to use array() method of CriteriaBuilder :
CompoundSelection<Object[]> array(Selection<?>... selections);
For example:
CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);//query object
Root<Employee> employee = query.from(Employee.class);
query.select(criteriaBuilder.array(employee.get(Employee_.id), employee.get(Employee_.name)));
List<Object[]> list = entityManger.createQuery(query).getResultList();
Alternatively we can use CriteriaQuery#multiselect() instead of CriteriaQuery#select() .
CriteriaQuery<T> multiselect(Selection<?>... selections);
For example:
CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);//query object
Root<Employee> employee = query.from(Employee.class);
query.multiselect(employee.get(Employee_.name), employee.get(Employee_.salary));
List<Object[]> list = entityManager.createQuery(query).getResultList();
According to CriteriaQuery#multiselect() document:
If the type of the criteria query is CriteriaQuery<Object> or if the criteria query was created without specifying a type, and only a single argument is passed to the multiselect method, an instance of type Object will be returned for each row.
If the type of the criteria query is CriteriaQuery<Object> or if the criteria query was created without specifying a type, and more than one argument is passed to the multiselect method, an instance of type Object[] will be instantiated and returned for each row. The elements of the array will correspond to the arguments to the multiselect method, in the specified order.
Example
Entity
@Entity
public class Employee {
@Id
@GeneratedValue
private long id;
private String name;
private double salary;
private String dept;
.............
}
Main class
public class ExampleMain {
private static EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("example-unit");
public static void main(String[] args) {
try {
persistEmployees();
findAllEmployeeEntities();
findEmployeeNames();
findEmployeeNamesAndIds();
findEmployeeNamesAndSalaries();
} finally {
entityManagerFactory.close();
}
}
public static void persistEmployees() {
Employee employee1 = Employee.create("Diana", 2000, "IT");
Employee employee2 = Employee.create("Rose", 4500, "Admin");
Employee employee3 = Employee.create("Denise", 2000, "Admin");
Employee employee4 = Employee.create("Mike", 4000, "IT");
Employee employee5 = Employee.create("Linda", 4500, "Sales");
EntityManager em = entityManagerFactory.createEntityManager();
em.getTransaction().begin();
em.persist(employee1);
em.persist(employee2);
em.persist(employee3);
em.persist(employee4);
em.persist(employee5);
em.getTransaction().commit();
em.close();
}
private static void findAllEmployeeEntities() {
System.out.println("-- finding Employee entities - entity selection --");
EntityManager em = entityManagerFactory.createEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Employee> query = cb.createQuery(Employee.class);//query object
Root<Employee> employee = query.from(Employee.class);//from part
query.select(employee);//entity selection
TypedQuery<Employee> typedQuery = em.createQuery(query);
List<Employee> resultList = typedQuery.getResultList();
resultList.forEach(System.out::println);
em.close();
}
private static void findEmployeeNames() {
System.out.println("-- Employee names - single field selection --");
EntityManager em = entityManagerFactory.createEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<String> query = cb.createQuery(String.class);//query object
Root<Employee> employee = query.from(Employee.class);//from part
query.select(employee.get(Employee_.name));//query selection ('select x, y, ... from' part)
TypedQuery<String> typedQuery = em.createQuery(query);
List<String> resultList = typedQuery.getResultList();
resultList.forEach(System.out::println);
em.close();
}
private static void findEmployeeNamesAndIds() {
System.out.println("-- Employee names and ids - multi selection using select() + array() --");
EntityManager em = entityManagerFactory.createEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);//query object
Root<Employee> employee = query.from(Employee.class);//from part
query.select(cb.array(employee.get(Employee_.id), employee.get(Employee_.name)));//multiple selection
TypedQuery<Object[]> typedQuery = em.createQuery(query);
List<Object[]> resultList = typedQuery.getResultList();
resultList.forEach(arr -> System.out.println(Arrays.toString(arr)));
em.close();
}
private static void findEmployeeNamesAndSalaries() {
System.out.println("-- Employee names and salaries - multi selection using multiselect() --");
EntityManager em = entityManagerFactory.createEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> query = cb.createQuery(Object[].class);//query object
Root<Employee> employee = query.from(Employee.class);//from part
query.multiselect(employee.get(Employee_.name), employee.get(Employee_.salary));//multiple selection
TypedQuery<Object[]> typedQuery = em.createQuery(query);
typedQuery.getResultList()
.forEach(arr -> System.out.println(Arrays.toString(arr)));
em.close();
}
} -- finding Employee entities - entity selection -- Employee{id=1, name='Diana', salary=2000.0, dept='IT'} Employee{id=2, name='Rose', salary=4500.0, dept='Admin'} Employee{id=3, name='Denise', salary=2000.0, dept='Admin'} Employee{id=4, name='Mike', salary=4000.0, dept='IT'} Employee{id=5, name='Linda', salary=4500.0, dept='Sales'} -- Employee names - single field selection -- Diana Rose Denise Mike Linda -- Employee names and ids - multi selection using select() + array() -- [1, Diana] [2, Rose] [3, Denise] [4, Mike] [5, Linda] -- Employee names and salaries - multi selection using multiselect() -- [Diana, 2000.0] [Rose, 4500.0] [Denise, 2000.0] [Mike, 4000.0] [Linda, 4500.0]
Example ProjectDependencies and Technologies Used: - h2 1.4.197: H2 Database Engine.
- hibernate-core 5.3.2.Final: Hibernate's core ORM functionality.
Implements javax.persistence:javax.persistence-api version 2.2 - hibernate-jpamodelgen 5.3.2.Final: Annotation Processor to generate JPA 2 static metamodel classes.
- JDK 1.8
- Maven 3.5.4
|