JP QL supports two types of parameter binding syntax.
The first is positional binding, where parameters are indicated in the query string by a question mark followed by the parameter number.
When the query is executed, the developer specifies the parameter number that should be replaced.
SELECT e FROM Employee e WHERE e.department = ?1 AND e.salary > ?2
Named parameters are indicated in the query string by a colon followed by the parameter name.
When the query is executed, the developer specifies the parameter name that should be replaced.
This type of parameter allows for more descriptive parameter specifiers.
SELECT e FROM Employee e WHERE e.department = :dept AND e.salary > :base
The following code shows how to use the named parameters.
List l = em.createQuery( "SELECT e " + "FROM Professor e " + "WHERE e.department = :dept AND " + " e.salary = (SELECT MAX(e2.salary) " + " FROM Professor e2 " + " WHERE e2.department = :dept)") .setParameter("dept", d) .getResultList();
The following code is from Address.java.
package com.java2s.common; import javax.persistence.Entity; import javax.persistence.Id; @Entity public class Address { @Id private int id; private String street; private String city; private String state; private String zip; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getStreet() { return street; } public void setStreet(String address) { this.street = address; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } public String getState() { return state; } public void setState(String state) { this.state = state; } public String getZip() { return zip; } public void setZip(String zip) { this.zip = zip; } public String toString() { return "Address id: " + getId() + ", street: " + getStreet() + ", city: " + getCity() + ", state: " + getState() + ", zip: " + getZip(); } }
The following code is from PersonDaoImpl.java.
package com.java2s.common; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import org.springframework.transaction.annotation.Transactional; @Transactional public class PersonDaoImpl { public void test() { prepareData(); Department d = new Department(); d.setId(1); d.setName("Design"); List l = em.createQuery( "SELECT e " + "FROM Professor e " + "WHERE e.department = :dept AND " + " e.salary = (SELECT MAX(e2.salary) " + " FROM Professor e2 " + " WHERE e2.department = :dept)") .setParameter("dept", d) .getResultList(); for (Object p : l) { printResult(p); } } private void prepareData() { Professor p = new Professor(); p.setId(0); p.setName("TOM"); p.setSalary(1111L); Department d = new Department(); d.setId(1); d.setName("Design"); p.setDepartment(d); d.getProfessors().add(p); em.persist(p); em.persist(d); } private static void printResult(Object result) { if (result == null) { System.out.print("NULL"); } else if (result instanceof Object[]) { Object[] row = (Object[]) result; System.out.print("["); for (int i = 0; i < row.length; i++) { printResult(row[i]); } System.out.print("]"); } else if (result instanceof Long || result instanceof Double || result instanceof String) { System.out.print(result.getClass().getName() + ": " + result); } else { System.out.print(result); } System.out.println(); } @PersistenceContext private EntityManager em; }
The following code is from Department.java.
package com.java2s.common; import java.util.HashSet; import java.util.Set; 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 Set<Professor> employees = new HashSet<Professor>(); public int getId() { return id; } public void setId(int deptNo) { this.id = deptNo; } public String getName() { return name; } public void setName(String deptName) { this.name = deptName; } public Set<Professor> getProfessors() { 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.Inheritance; import javax.persistence.ManyToMany; @Entity @Inheritance public class Project { @Id protected int id; protected String name; @ManyToMany protected Collection<Professor> employees = new ArrayList<Professor>(); public int getId() { return id; } public void setId(int projectNo) { this.id = projectNo; } public String getName() { return name; } public void setName(String projectName) { this.name = projectName; } public Collection<Professor> getProfessors() { return employees; } public void addProfessor(Professor employee) { if (!getProfessors().contains(employee)) { getProfessors().add(employee); } if (!employee.getProjects().contains(this)) { employee.getProjects().add(this); } } public String toString() { return getClass().getName().substring(getClass().getName().lastIndexOf('.')+1) + " no: " + getId() + ", name: " + getName(); } }
The following code is from Phone.java.
package com.java2s.common; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.ManyToOne; @Entity public class Phone { @Id private long id; private String number; private String type; @ManyToOne Professor employee; public long getId() { return id; } public void setId(long id) { this.id = id; } public String getNumber() { return number; } public void setNumber(String phoneNo) { this.number = phoneNo; } public String getType() { return type; } public void setType(String phoneType) { this.type = phoneType; } public Professor getProfessor() { return employee; } public void setProfessor(Professor employee) { this.employee = employee; } public String toString() { return "Phone id: " + getId() + ", no: " + getNumber() + ", type: " + getType(); } }
The following code is from Professor.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.OneToMany; import javax.persistence.OneToOne; import javax.persistence.Temporal; import javax.persistence.TemporalType; @Entity public class Professor { @Id private int id; private String name; private long salary; @Temporal(TemporalType.DATE) private Date startDate; @OneToOne private Address address; @OneToMany(mappedBy="employee") private Collection<Phone> phones = new ArrayList<Phone>(); @ManyToOne private Department department; @ManyToOne private Professor manager; @OneToMany(mappedBy="manager") private Collection<Professor> directs = new ArrayList<Professor>(); @ManyToMany(mappedBy="employees") private Collection<Project> projects = new ArrayList<Project>(); public int getId() { return id; } public void setId(int empNo) { this.id = empNo; } public String getName() { return name; } public void setName(String name) { this.name = name; } public long getSalary() { return salary; } public void setSalary(long salary) { this.salary = salary; } public Date getStartDate() { return startDate; } public void setStartDate(Date startDate) { this.startDate = startDate; } public Collection<Phone> getPhones() { return phones; } public void addPhone(Phone phone) { if (!getPhones().contains(phone)) { getPhones().add(phone); if (phone.getProfessor() != null) { phone.getProfessor().getPhones().remove(phone); } phone.setProfessor(this); } } public Department getDepartment() { return department; } public void setDepartment(Department department) { if (this.department != null) { this.department.getProfessors().remove(this); } this.department = department; this.department.getProfessors().add(this); } public Collection<Professor> getDirects() { return directs; } public void addDirect(Professor employee) { if (!getDirects().contains(employee)) { getDirects().add(employee); if (employee.getManager() != null) { employee.getManager().getDirects().remove(employee); } employee.setManager(this); } } public Professor getManager() { return manager; } public void setManager(Professor manager) { this.manager = manager; } public Collection<Project> getProjects() { return projects; } public void addProject(Project project) { if (!getProjects().contains(project)) { getProjects().add(project); } if (!project.getProfessors().contains(this)) { project.getProfessors().add(this); } } public Address getAddress() { return address; } public void setAddress(Address address) { this.address = address; } public String toString() { return "Professor " + getId() + ": name: " + getName() + ", salary: " + getSalary() + ", phones: " + getPhones() + ", managerNo: " + ((getManager() == null) ? null : getManager().getId()) + ", deptNo: " + ((getDepartment() == null) ? null : getDepartment().getId()); } }
The code above generates the following result.
The following is the database dump.
Table Name: ADDRESS Table Name: DEPARTMENT Row: Column Name: ID, Column Type: INTEGER: Column Value: 1 Column Name: NAME, Column Type: VARCHAR: Column Value: Design Table Name: PHONE Table Name: PROFESSOR Row: Column Name: ID, Column Type: INTEGER: Column Value: 0 Column Name: NAME, Column Type: VARCHAR: Column Value: TOM Column Name: SALARY, Column Type: BIGINT: Column Value: 1111 Column Name: STARTDATE, Column Type: DATE: Column Value: null Column Name: ADDRESS_ID, Column Type: INTEGER: Column Value: null Column Name: DEPARTMENT_ID, Column Type: INTEGER: Column Value: 1 Column Name: MANAGER_ID, Column Type: INTEGER: Column Value: null Table Name: PROJECT Table Name: PROJECT_PROFESSOR