如何在JPQL中实现基于用户角色的动态条件过滤查询

问题背景 #

系统包含ROOT与Simple User两种用户角色：

• ROOT角色可查看全部数据
• Simple User角色仅能查看其所属公司的特定数据

针对某页面的数据过滤需求，原本编写了两条JPQL查询语句：

// ROOT角色查询
"SELECT new com.data.dto.RelatorioFinaceiroDTO(r.id, r.dth_reserva, r.km_retirada,r.km_devolucao) FROM Reserva r WHERE  (r.dth_reserva >= :dtInicio AND r.dth_reserva <= :dtFim) AND r.veiculo.perfil=1 AND r.reserva.status=5 "

// Simple User角色查询
"SELECT new com.data.dto.RelatorioFinaceiroDTO(r.id, r.dth_reserva, r.km_retirada,r.km_devolucao) FROM Reserva r WHERE  (r.dth_reserva >= :dtInicio AND r.dth_reserva <= :dtFim) AND r.veiculo.perfil=1 AND r.reserva.status=5 AND r.veiculo.empresa.id = :empresaid"

两者唯一区别是Simple User的查询多了r.veiculo.empresa.id = :empresaid条件。尝试用包含:role参数的单条查询合并，写法如下：

"SELECT new com.data.dto.RelatorioFinaceiroDTO(r.id, r.dth_reserva, r.km_retirada,r.km_devolucao) FROM Reserva r WHERE  (r.dth_reserva >= :dtInicio AND r.dth_reserva <= :dtFim) AND r.veiculo.perfil=1 AND (CASE WHEN :role!='ROOT' THEN r.reserva.status=5 AND r.veiculo.empresa.id = :empresaid ELSE TRUE END)"

问题分析与解决方案 #

你当前的CASE写法不正确，因为JPQL的CASE表达式返回的是具体值（如字符串、数字），不能直接返回布尔条件用于WHERE子句的逻辑判断。以下是两种正确的实现方式：

方式一：逻辑表达式直接组合条件

用逻辑或（OR）直接关联角色判断与公司ID过滤条件，语法符合JPQL规范，实现简洁：

String jpql = "SELECT new com.data.dto.RelatorioFinaceiroDTO(r.id, r.dth_reserva, r.km_retirada,r.km_devolucao) " +
              "FROM Reserva r " +
              "WHERE (r.dth_reserva >= :dtInicio AND r.dth_reserva <= :dtFim) " +
              "AND r.veiculo.perfil=1 " +
              "AND r.reserva.status=5 " +
              "AND (:role = 'ROOT' OR r.veiculo.empresa.id = :empresaid)";

逻辑说明：

• 当角色为ROOT时，:role = 'ROOT'为true，整个AND条件直接成立，不会校验公司ID
• 当角色为Simple User时，会自动校验r.veiculo.empresa.id = :empresaid，符合权限控制要求

方式二：动态构建JPQL（灵活扩展）

如果后续有更多角色或复杂过滤规则，推荐用动态拼接的方式生成查询，避免冗余条件：

StringBuilder jpqlBuilder = new StringBuilder();
jpqlBuilder.append("SELECT new com.data.dto.RelatorioFinaceiroDTO(r.id, r.dth_reserva, r.km_retirada,r.km_devolucao) ")
           .append("FROM Reserva r ")
           .append("WHERE (r.dth_reserva >= :dtInicio AND r.dth_reserva <= :dtFim) ")
           .append("AND r.veiculo.perfil=1 ")
           .append("AND r.reserva.status=5");

// 非ROOT角色才添加公司ID过滤条件
if (!"ROOT".equals(roleParam)) {
    jpqlBuilder.append(" AND r.veiculo.empresa.id = :empresaid");
}

String finalJpql = jpqlBuilder.toString();

优势：生成的SQL更简洁，ROOT角色的查询不会包含多余的条件判断，性能更优。

注意事项 #

• 参数绑定：ROOT角色时，部分JPA实现可能要求所有命名参数都存在，可给empresaid设置一个无效值（如-1），因条件不会走到该分支；
• 安全问题：始终使用命名参数（如:role、:empresaid），禁止直接拼接字符串值到JPQL中，避免SQL注入风险。

内容的提问来源于stack exchange，提问作者Lucas silva