The DISTINCT operator is functionally equivalent to the SQL operator of the same name.
Once the result set is collected, duplicate values are removed so that only unique results are returned.
The following JPQL uses DISTINCT operator in many to one map.
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(); } }
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 PersonDaoImpl.java.
package com.java2s.common; import java.io.PrintWriter; import java.util.Arrays; 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); executeAndPrintQuery("SELECT DISTINCT e.department FROM Employee e"); } private void executeAndPrintQuery(String queryString) { try { Query query = em.createQuery(queryString); printQueryResult(queryString, query.getResultList()); } finally { em.close(); } } private void printQueryResult(String queryString, List result) { System.out.println("EJB QL: " + queryString+"\n"); System.out.println("Result:\n"); if (result.isEmpty()) { System.out.println("No results Found\n"); } else { for (Object o : result) { System.out.println(resultAsString(o)); } } } private String resultAsString(Object o) { if (o instanceof Object[]) { return Arrays.asList((Object[])o).toString(); } else { return String.valueOf(o); } } @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 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 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