In the named queries we can also add parameters.
@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 {
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(); } }
The following code is from PersonDaoImpl.java.
package com.java2s.common; import java.util.Collection; import java.util.Date; import javax.persistence.EntityManager; import javax.persistence.NoResultException; import javax.persistence.PersistenceContext; import javax.persistence.TemporalType; 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"; } public Collection<Employee> findEmployeesHiredDuringPeriod(Date start, Date end) { return (Collection<Employee>) em.createQuery("SELECT e " + "FROM Employee e " + "WHERE e.startDate BETWEEN :start AND :end") .setParameter("start", start, TemporalType.DATE) .setParameter("end", end, TemporalType.DATE) .getResultList(); } public Employee findHighestPaidByDepartment(Department dept) { try { return (Employee) em.createNamedQuery("findHighestPaidByDepartment") .setParameter("dept", dept) .getSingleResult(); } catch (NoResultException e) { return null; } } public Collection<Employee> findAllEmployees() { return (Collection<Employee>) em.createQuery( "SELECT e FROM Employee e").getResultList(); } @PersistenceContext private EntityManager em; }
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