加入JPQL，CreateQuery

Question

试图找出JPQL CreateQuery上的连接是如何工作的。我使用SQL代码对经理名称进行了部分搜索：

SELECT
e.EMPLOYEE_ID      AS empId,
e.FIRST_NAME       AS empFirstName,
e.LAST_NAME        AS empLastName,
m.FIRST_NAME || ' ' || m.LAST_NAME AS empMgrName
FROM
EMPLOYEES e
LEFT JOIN
EMPLOYEES m ON e.MANAGER_ID = m.EMPLOYEE_ID
WHERE
LOWER(m.FIRST_NAME || ' ' || m.LAST_NAME) LIKE '%"ManagerName"%'

我试着把它变成JPQL格式，我想出了以下内容：

StringBuilder query = new StringBuilder();
query.setLength(0);
query.append(" FROM ");
query.append("    Employee e  ");
query.append(" JOIN ");
query.append("    e.Manager m  ");
query.append(" WHERE 1 = 1 ");
query.append("    LOWER(m.FIRST_NAME || ' ' || m.LAST_NAME) LIKE :empMgrName ");
Query listEmpQuery = JPA.em().createQuery(query.toString(), Employee.class);
if (!StringUtil.isNullOrEmpty(strMgr)) {
  listEmpQuery.setParameter("empMgrName", "%" + strMgr.toLowerCase() + "%");
}
List<Employee> listEmp = listEmpQuery.getResultList();

假设用户向strMgr输入了"ill gat“的值，以搜索经理名"Bill”。它应该搜查经理比尔盖茨手下的雇员。但在这段代码中，会出现ff错误：

IllegalArgumentException: Cannot create TypedQuery for query with more than one return using requested result type [models.db.Employee]] 

我做错什么了？

参考资料: Employee.class

@Entity
@Table(name="EMPLOYEES")
@SequenceGenerator(name = "EMPLOYEES_SEQ", sequenceName = "EMPLOYEES_SEQ")
public class Employee {

@Id
@GeneratedValue(strategy = GenerationType.AUTO, generator = "EMPLOYEES_SEQ")
public Integer EMPLOYEE_ID;

public String FIRST_NAME;

public String LAST_NAME;

@OneToOne
@JoinColumn(name="MANAGER_ID")
public Employee Manager;

本人自选任经理至员工为“经理”。

Answer 1

问题是，当查询中有多个实体时，必须指定要作为结果的实体(除非是投影)。

query.append(" SELECT e")
query.append(" FROM ");
query.append("    Employee e  ");
query.append(" JOIN ");
query.append("    e.Manager m  ");

或者，如果您希望得到经理作为结果，则为SELECT m。

LIKE条款很好。它的构建是为了防止sql注入。

Answer 2

您正在尝试实现的是左外部获取连接：

query.append("SELECT e FROM ");
query.append("    Employee e  ");
query.append("LEFT JOIN FETCH ");
query.append("    e.Manager");
query.append(" WHERE LOWER(CONCAT(m.FIRST_NAME, ' ', m.LAST_NAME)) LIKE :empMgrName ");