java - 在 Spring Data JPA 中的 native 查询之间共享 SQL

Question

我在一个 Spring Data JPA Repository 接口(interface)中有一些查询，这些查询调用相同的数据库函数并连接到同一组表。查询之间的唯一区别是连接表之一和从该连接表中选择的列之一。以下是两个查询的示例:

   @Query(value = "SELECT m.managerCode, s.employeeID, s.FullName, "
            + "s.employeeType, v.propertyValue "
            + "FROM User.fSysFunction(:binData) a " 
            + "JOIN User.vEmployee s " 
            + "ON s.employeeID = a.employeeID " 
            + "JOIN App.vManager m "
            +  "ON a.ManagerID = m.ManagerID "
            + "LEFT OUTER JOIN App.vPropertyValue v "
            + "ON s.employeeID = v.employeeID " 
            + "  AND v.PropertyName = 'BinID' "             
            + "where s.employeeType in ('set', 'ft', 'rs', 'wtr', 'wlt', 'ma') ",
           nativeQuery = true)
    List<EmployeeManagerDto> getEmployeeManagerDtos(@Param("binData") byte[] binData);

   @Query(value = "SELECT m.supervisorCode, s.employeeID, s.FullName, " // select col different
            + "s.employeeType, v.propertyValue "
            + "FROM User.fSysFunction(:binData) a " 
            + "JOIN User.vEmployee s " 
            + "ON s.employeeID = a.employeeID " 
            + "JOIN App.vSupervisor m " // a different join here
            +  "ON a.SupervisorID = m.SupervisorID "
            + "LEFT OUTER JOIN App.vPropertyValue v "
            + "ON s.employeeID = v.employeeID " 
            + "  AND v.PropertyName = 'BinID' "             
            + "where s.employeeType in ('set', 'ft', 'rs', 'wtr', 'wlt', 'ma') ",
           nativeQuery = true)
    List<EmployeeSupervisorDto> getEmployeeSueprvisorDtos(@Param("binData") byte[] binData);

我想避免查询之间的 SQL 文本重复。我怎样才能做到这一点？

更新

这就是我想要实现的目标:

 @Query(value = getQueryText("m.managerCode", "JOIN App.vManager m "
            +  "ON a.ManagerID = m.ManagerID "),
           nativeQuery = true)
    List<EmployeeManagerDto> getEmployeeManagerDtos(@Param("binData") byte[] binData);

 @Query(value = getQueryText("m.supervisorCode", "JOIN App.vSupervisor m"
            +  "ON a.SupervisorID = m.SupervisorID "),
           nativeQuery = true)
List<EmployeeSupervisorDto> getEmployeeSueprvisorDtos(@Param("binData") byte[] binData);

 default String getQueryText(selectColumnText, joinText) {
     return "SELECT " + selectColumnText + ", s.employeeID, s.FullName, " 
            + "s.employeeType, v.propertyValue "
            + "FROM User.fSysFunction(:binData) a " 
            + "JOIN User.vEmployee s " 
            + "ON s.employeeID = a.employeeID " 
            + joinText
            + "LEFT OUTER JOIN App.vPropertyValue v "
            + "ON s.employeeID = v.employeeID " 
            + "  AND v.PropertyName = 'BinID' "             
            + "where s.employeeType in ('set', 'ft', 'rs', 'wtr', 'wlt', 'ma')"
  }

这当然行不通，但希望能展示我想要实现的目标。

Answer 1

可能有很多事情，有必要了解数据库ER，但也可能是因为不同的表有两行构成重复行，如果每一列都有相同的数据，那么你可以使用 GROUP BY 和枚举选择的每一列，例如

" GROUP BY p.supervisorCode, s.employeeID, s.FullName, s.employeeType, v.propertyValue "

或者，如果数据库是 SQL Server，您可以像这样使用 DISTINCT

"SELECT distinct p.supervisorCode, s.employeeID, s.FullName, " 
            + "s.employeeType, v.propertyValue "...