[FIXED] How to reuse native query code for different tables and views?

Issue

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?

Solution

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

Answered By - Simon Martinelli
Answer Checked By - Candace Johnson (JavaFixing Volunteer)