JPA Tutorial - JPA Query Is Empty Example

The IS EMPTY operator is the logical equivalent of IS NULL, but for collections.

Queries can use IS EMPTY operator or IS NOT EMPTY to check whether a collection association path resolves to an empty collection or has at least one value.

We can use the EMPTY to check if a property is empty.

The following JPQL shows how to use EMPTY to get employee withno projects.

Query unassignedQuery = 
    em.createQuery("SELECT e " +
                   "FROM Employee e " +
                   "WHERE e.projects IS EMPTY");

Example

The following code is from PersonDaoImpl.java.

package com.java2s.common;

import java.util.Collection;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.transaction.annotation.Transactional;

@Transactional
public class PersonDaoImpl {
  public void test() {
    Employee emp = new Employee();
    emp.setName("Tom");
    emp.setSalary(123);
    emp.setStartDate(new Date());
    emp.setId(1);

    Project pro = new Project();
    pro.setName("Design");
    pro.getEmployees().add(emp);

    Department dept = new Department();
    dept.setName("Dept");
    dept.getEmployees().add(emp);

    emp.setDepartment(dept);
    emp.getProjects().add(pro);

    em.persist(dept);
    em.persist(pro);
    em.persist(emp);

    String deptName = "Dept";
    String empName = "Tom";

    Query unassignedQuery = 
        em.createQuery("SELECT e " +
                       "FROM Employee e " +
                       "WHERE e.projects IS EMPTY");

     List l =  unassignedQuery.getResultList();
     System.out.println(l);

  }
  public Collection<Employee> findAllEmployees() {
      return (Collection<Employee>) em.createQuery(
              "SELECT e FROM Employee e").getResultList();
  }
  @PersistenceContext
  private EntityManager em;
}

The following code is from Employee.java.

package com.java2s.common;



import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.OneToMany;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
@Entity
@NamedQueries({
  @NamedQuery(name="findEmployeesAboveSal",
              query="SELECT e " +
                    "FROM Employee e " +
                    "WHERE e.department = :dept AND " +
                    "      e.salary > :sal"),
  @NamedQuery(name="findHighestPaidByDepartment",
              query="SELECT e " +
                    "FROM Employee e " +
                    "WHERE e.department = :dept AND " +
                    "      e.salary = (SELECT MAX(e2.salary) " +
                    "                  FROM Employee e2 " +
                    "                  WHERE e2.department = :dept)")
})
public class Employee {
    @Id
    private int id;
    private String name;
    private long salary;
    @Temporal(TemporalType.DATE)
    private Date startDate;
    
    @ManyToOne
    private Employee manager;
    
    @OneToMany(mappedBy="manager")
    private Collection<Employee> directs;

    @ManyToOne
    private Department department;
    
    @ManyToMany 
    private Collection<Project> projects;

    public Employee() {
        projects = new ArrayList<Project>();
        directs = new ArrayList<Employee>();
    }

    public void setId(int id) {
      this.id = id;
    }

    public void setName(String name) {
      this.name = name;
    }

    public void setSalary(long salary) {
      this.salary = salary;
    }

    public void setStartDate(Date startDate) {
      this.startDate = startDate;
    }

    public void setManager(Employee manager) {
      this.manager = manager;
    }

    public void setDirects(Collection<Employee> directs) {
      this.directs = directs;
    }

    public void setDepartment(Department department) {
      this.department = department;
    }

    public void setProjects(Collection<Project> projects) {
      this.projects = projects;
    }

    public int getId() {
        return id;
    }
    
    public String getName() {
        return name;
    }

    public long getSalary() {
        return salary;
    }

    public Date getStartDate() {
        return startDate;
    }
    
    public Department getDepartment() {
        return department;
    }
    
    public Collection<Employee> getDirects() {
        return directs;
    }
    
    public Employee getManager() {
        return manager;
    }

    public Collection<Project> getProjects() {
        return projects;
    }
    
    public String toString() {
        return "Employee " + getId() + 
               ": name: " + getName() +
               ", salary: " + getSalary() +
               ", dept: " + ((getDepartment() == null) ? null : getDepartment().getName());
    }
}

The following code is from Project.java.

package com.java2s.common;


import java.util.ArrayList;
import java.util.Collection;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.ManyToMany;

@Entity
public class Project {
    @Id
    protected int id;
    protected String name;
    @ManyToMany(mappedBy="projects")
    private Collection<Employee> employees;

    public Project() {
        employees = new ArrayList<Employee>();
    }

    public void setId(int id) {
      this.id = id;
    }

    public void setName(String name) {
      this.name = name;
    }

    public void setEmployees(Collection<Employee> employees) {
      this.employees = employees;
    }

    public int getId() {
        return id;
    }
    
    public String getName() {
        return name;
    }
    
    public Collection<Employee> getEmployees() {
        return employees;
    }
    
    public String toString() {
        return "Project id: " + getId() + ", name: " + getName();
    }
}

The following code is from Department.java.

package com.java2s.common;


import java.util.ArrayList;
import java.util.Collection;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;

@Entity
public class Department {
    @Id
    private int id;
    private String name;
    @OneToMany(mappedBy="department")
    private Collection<Employee> employees;

    public Department() {
        employees = new ArrayList<Employee>();
    }
    
    public void setId(int id) {
      this.id = id;
    }

    public void setName(String name) {
      this.name = name;
    }

    public void setEmployees(Collection<Employee> employees) {
      this.employees = employees;
    }

    public int getId() {
        return id;
    }
    
    public String getName() {
        return name;
    }
    
    public Collection<Employee> getEmployees() {
        return employees;
    }

    public String toString() {
        return "Department no: " + getId() + 
               ", name: " + getName();
    }
}

Download Query_Is_Empty.zip

The code above generates the following result.

The following is the database dump.


Table Name: DEPARTMENT
 Row:
    Column Name: ID,
    Column Type: INTEGER:
    Column Value: 0

    Column Name: NAME,
    Column Type: VARCHAR:
    Column Value: Dept





Table Name: EMPLOYEE
 Row:
    Column Name: ID,
    Column Type: INTEGER:
    Column Value: 1

    Column Name: NAME,
    Column Type: VARCHAR:
    Column Value: Tom

    Column Name: SALARY,
    Column Type: BIGINT:
    Column Value: 123

    Column Name: STARTDATE,
    Column Type: DATE:
    Column Value: 2014-12-29

    Column Name: DEPARTMENT_ID,
    Column Type: INTEGER:
    Column Value: 0

    Column Name: MANAGER_ID,
    Column Type: INTEGER:
    Column Value: null





Table Name: EMPLOYEE_PROJECT
 Row:
    Column Name: EMPLOYEES_ID,
    Column Type: INTEGER:
    Column Value: 1

    Column Name: PROJECTS_ID,
    Column Type: INTEGER:
    Column Value: 0





Table Name: PROJECT
 Row:
    Column Name: ID,
    Column Type: INTEGER:
    Column Value: 0

    Column Name: NAME,
    Column Type: VARCHAR:
    Column Value: Design