How to reuse native query code for different tables and views?-CodePudding

I was wondering if this is even possible. If we have situation like this:

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

and

@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 we have two exact same where clause, is there any way to create something to reuse this?

CodePudding user response：

Sure!

Simply place this part in a constant (final static in Java) an use it in the annotation.

Example

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

And then the constant

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