CODEWITHSUNDEEP
javahibernatejpaplayframeworkjpql
NoobProgrammer
NoobProgrammer

Reputation: 81

Join on JPQL, CreateQuery

Trying to figure out how the join on JPQL CreateQuery works. I have a partial search function for manager name with the SQL code:

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"%'

I tried to make this into a JPQL format and i came up with the following:

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();

Let say that the user entered the value of "ill gat" to the strMgr to search for the manager name "Bill Gates". It should search the employee that is under the manager bill gates. But in this code the ff error occurs:

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

What did I do wrong?

Reference: 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;

I self join the manager to Employee as "Manager"

Upvotes: 0

Views: 1277

Answers (2)

fhossfel
fhossfel

Reputation: 2181

What you are trying to achieve is LEFT OUTER FETCH JOIN:

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 ");

Upvotes: 1

Maciej Kowalski
Maciej Kowalski

Reputation: 26492

Thing is that when you have more than one entity in the query then you have to specify which one you want as the result (unless its a projection).

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

or SELECT m if you want to have the manager as the result.

The LIKE clause is fine.. its built to prevent sql injection.

Upvotes: 2

Related Questions