如何为不同的表和视图重用本机查询代码？

Question

我想知道这是否可能。如果我们遇到这样的情况：

@Query(nativeQuery = true,
            value = "SELECT TA.* " +
                    "FROM TABLE_A TA " +
                    "WHERE " +
                    "(LOWER(NAME) LIKE NVL(CONCAT('%', CONCAT(LOWER(:#{#filter.name}), '%')), NAME) " +
                    "OR LOWER(CODE) LIKE NVL(CONCAT('%', CONCAT(LOWER(:#{#filter.code}), '%')), CODE)) " +
                    "AND (TO_CHAR(USER_WORKFLOW_ID) IN (:#{#filter.userWorkFlowIds}) OR COALESCE(:#{#filter.userWorkFlowIds}, NULL) IS NULL) " +
                    "ORDER BY " +
                    "      CASE " +
                    "         WHEN :#{#filter.orderByColumn.index} = 4 AND :#{#filter.orderDirection.index} = -1 THEN VERSION " +
                    "      END DESC, " +
                    "      CASE " +
                    "         WHEN :#{#filter.orderByColumn.index} = 10 AND :#{#filter.orderDirection.index} = -1 THEN PROGRESS " +
                    "      END DESC " +
                    "OFFSET LOWER(:#{#filter.pageSize} * :#{#filter.pageIndex}) ROWS FETCH NEXT LOWER(:#{#filter.pageSize}) ROWS ONLY ")
    List<TableAItems> findByFilter(@Param(value = "filter") TAFilter filter);

和

@Query(nativeQuery = true,
            value = "SELECT TB.* " +
                    "FROM TABLE_B TB " +
                    "WHERE " +
                    "(LOWER(NAME) LIKE NVL(CONCAT('%', CONCAT(LOWER(:#{#filter.name}), '%')), NAME) " +
                    "OR LOWER(CODE) LIKE NVL(CONCAT('%', CONCAT(LOWER(:#{#filter.code}), '%')), CODE)) " +
                    "AND (TO_CHAR(USER_WORKFLOW_ID) IN (:#{#filter.userWorkFlowIds}) OR COALESCE(:#{#filter.userWorkFlowIds}, NULL) IS NULL) " +
                    "ORDER BY " +
                    "      CASE " +
                    "         WHEN :#{#filter.orderByColumn.index} = 4 AND :#{#filter.orderDirection.index} = -1 THEN VERSION " +
                    "      END DESC, " +
                    "      CASE " +
                    "         WHEN :#{#filter.orderByColumn.index} = 10 AND :#{#filter.orderDirection.index} = -1 THEN PROGRESS " +
                    "      END DESC " +
                    "OFFSET LOWER(:#{#filter.pageSize} * :#{#filter.pageIndex}) ROWS FETCH NEXT LOWER(:#{#filter.pageSize}) ROWS ONLY ")
    List<TableBItems> findByFilter(@Param(value = "filter") TBFilter filter);

我们有两个完全相同的where子句，有没有办法创建一些东西来重用它？

Answer 1

肯定！

只需将这部分放在常量（Java中的final static）中，然后在注释中使用它。

实例

@Query(nativeQuery = true,
            value = "SELECT TB.* " +
                    "FROM TABLE_B TB " +
                    MyClass.WHERE +
                    "ORDER BY " +
                    "      CASE " +
                    "         WHEN :#{#filter.orderByColumn.index} = 4 AND :#{#filter.orderDirection.index} = -1 THEN VERSION " +
                    "      END DESC, " +
                    "      CASE " +
                    "         WHEN :#{#filter.orderByColumn.index} = 10 AND :#{#filter.orderDirection.index} = -1 THEN PROGRESS " +
                    "      END DESC " +
                    "OFFSET LOWER(:#{#filter.pageSize} * :#{#filter.pageIndex}) ROWS FETCH NEXT LOWER(:#{#filter.pageSize}) ROWS ONLY ")
    List<TableBItems> findByFilter(@Param(value = "filter") TBFilter filter);

然后是常数

public final static String WHERE = "WHERE " +
                    "(LOWER(NAME) LIKE NVL(CONCAT('%', CONCAT(LOWER(:#{#filter.name}), '%')), NAME) " +
                    "OR LOWER(CODE) LIKE NVL(CONCAT('%', CONCAT(LOWER(:#{#filter.code}), '%')), CODE)) " +
                    "AND (TO_CHAR(USER_WORKFLOW_ID) IN (:#{#filter.userWorkFlowIds}) OR COALESCE(:#{#filter.userWorkFlowIds}, NULL) IS NULL) " +