Running a RAW query in spring boot gives different result than Running a SQL query on workbench-CodePudding

I have a query for retrieving the permissions of the user from the MySQL database, which I want to implement in the Spring Boot application, however, the results of the same query running in the workbench are giving different results than the spring boot raw query.

1:The query and result in the workbench

select itsm_permission.id,itsm_permission.name from itsm_employee inner join employee_group 
        on employee_group.employee_id = itsm_employee.employee_id inner join itsm_group 
        on itsm_group.id = employee_group.group_id inner join group_role on 
        group_role.group_id = employee_group.group_id inner join role_permission 
        on role_permission.role_id = group_role.role_id inner join itsm_permission 
        on itsm_permission.id = role_permission.permission_id where itsm_employee.employee_id=257;

The result is:

253 editalarm
252 addalarm
251 importalarm
250 exportalarm
249 viewallalarms

2: the same query when it is executed in Spring Boot application:

 @Query(
  value = "select itsm_permission.id,itsm_permission.name from itsm_employee inner join employee_group on "  
          "employee_group.employee_id = itsm_employee.employee_id "  
          "inner join itsm_group on itsm_group.id = employee_group.group_id inner join "  
          "group_role on group_role.group_id = employee_group.group_id inner join role_permission on "  
          "role_permission.role_id = group_role.role_id inner join itsm_permission on "  
          "itsm_permission.id = role_permission.permission_id "  
          "where itsm_employee.employee_id=:id", nativeQuery = true)
          List<Permission> findAllPermissionsByEmployeeId(Long id);

the result is:

select itsm_permission.id,itsm_permission.name from itsm_employee inner join 
employee_group on employee_group.employee_id = itsm_employee.employee_id 
inner join itsm_group on itsm_group.id = employee_group.group_id inner join 
group_role on group_role.group_id = employee_group.group_id inner join 
role_permission on role_permission.role_id = group_role.role_id inner join 
itsm_permission on itsm_permission.id = role_permission.permission_id where 
itsm_employee.employee_id=?;

nested exception is org.hibernate.exception.SQLGrammarException: could not execute 
query] with root cause:

java.sql.SQLException: Column 'active' not found.

Question Updated: All tables, employee, group, role, and permission are having one column as "active: boolean" which is currently "true" for all records.

CodePudding user response：

Actually, You are selecting a subset [id, name] of the columns of the table itsm_permission. However, the results are being mapped to the managed entity Permission. So the mapping fails when it looks for the active column in the resultset, which is missing.

You can avoid this exception by either of these two approaches. There are several more. Here is a good discussion of this - Spring JPA selecting specific columns

solution: Keep the existing query and change the return type of the findAllPermissionsByEmployeeId() method to Object[]. Here for each Object[], Object[0] is id and Object[1] is name.

@Query(
  value = "select itsm_permission.id,itsm_permission.name from itsm_employee inner join employee_group on "  
          "employee_group.employee_id = itsm_employee.employee_id "  
          "inner join itsm_group on itsm_group.id = employee_group.group_id inner join "  
          "group_role on group_role.group_id = employee_group.group_id inner join role_permission on "  
          "role_permission.role_id = group_role.role_id inner join itsm_permission on "  
          "itsm_permission.id = role_permission.permission_id "  
          "where itsm_employee.employee_id=:id", nativeQuery = true)
          List<Object[]> findAllPermissionsByEmployeeId(Long id);