如何为不同表与视图复用原生SQL查询的重复代码？

我想了解是否可以实现以下场景的代码复用：现有两个Spring Data JPA原生查询，代码如下：

@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子句、排序及分页逻辑完全一致，请问是否有办法复用这些重复的代码部分？

针对这种仅表名、返回/参数实体不同，其余SQL逻辑完全一致的场景，有几种实用的复用方式：

1. 提取静态SQL片段常量 #

最直接的方式是把重复的SQL部分提取为静态常量，在两个查询中拼接使用，避免重复编写：

首先创建一个公共的SQL常量类，统一管理重复片段：

public class CommonSqlConstants {
    public static final String COMMON_WHERE_CLAUSE = "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) ";

    public static final String COMMON_ORDER_PAGINATION = "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 ";
}

然后在具体的Repository中引用这些常量：

public interface TableARepository extends JpaRepository<TableAItems, Long> {
    @Query(nativeQuery = true,
            value = "SELECT TA.* FROM TABLE_A TA " + CommonSqlConstants.COMMON_WHERE_CLAUSE + CommonSqlConstants.COMMON_ORDER_PAGINATION)
    List<TableAItems> findByFilter(@Param(value = "filter") TAFilter filter);
}

public interface TableBRepository extends JpaRepository<TableBItems, Long> {
    @Query(nativeQuery = true,
            value = "SELECT TB.* FROM TABLE_B TB " + CommonSqlConstants.COMMON_WHERE_CLAUSE + CommonSqlConstants.COMMON_ORDER_PAGINATION)
    List<TableBItems> findByFilter(@Param(value = "filter") TBFilter filter);
}

这种方式简单易维护，适合少量类似查询的场景。

2. 自定义通用Repository基类 #

如果后续还有更多类似表需要复用相同逻辑，可以通过泛型封装通用Repository基类：

步骤1：定义通用Filter接口 #

让TAFilter和TBFilter实现该接口，确保两者包含查询所需的所有字段：

public interface BaseFilter {
    String getName();
    String getCode();
    List<String> getUserWorkFlowIds();
    OrderColumn getOrderByColumn();
    OrderDirection getOrderDirection();
    Integer getPageSize();
    Integer getPageIndex();
}

// TAFilter实现示例
public class TAFilter implements BaseFilter {
    // 实现接口所有方法
}

// TBFilter实现示例
public class TBFilter implements BaseFilter {
    // 实现接口所有方法
}

步骤2：定义通用Repository接口 #

@NoRepositoryBean
public interface BaseRepository<T, ID, F extends BaseFilter> extends JpaRepository<T, ID> {
    List<T> findByFilter(F filter);
}

步骤3：实现通用Repository逻辑 #

public class BaseRepositoryImpl<T, ID, F extends BaseFilter> extends SimpleJpaRepository<T, ID> implements BaseRepository<T, ID, F> {
    private final EntityManager entityManager;
    private final Class<T> entityClass;

    public BaseRepositoryImpl(Class<T> entityClass, EntityManager entityManager) {
        super(entityClass, entityManager);
        this.entityClass = entityClass;
        this.entityManager = entityManager;
    }

    @Override
    public List<T> findByFilter(F filter) {
        // 从实体的@Table注解获取表名
        Table tableAnnotation = entityClass.getAnnotation(Table.class);
        String tableName = tableAnnotation.name();
        // 生成实体别名（比如TABLE_A取TA）
        String entityAlias = tableName.substring(0, 2).toUpperCase();

        // 拼接完整SQL
        String sql = String.format("SELECT %s.* FROM %s %s %s %s",
                entityAlias,
                tableName,
                entityAlias,
                CommonSqlConstants.COMMON_WHERE_CLAUSE,
                CommonSqlConstants.COMMON_ORDER_PAGINATION);

        // 创建原生查询并绑定参数
        Query query = entityManager.createNativeQuery(sql, entityClass);
        query.setParameter("filter", filter);

        return query.getResultList();
    }
}

步骤4：配置并使用通用Repository #

在Spring启动类中指定自定义Repository基类：

@EnableJpaRepositories(repositoryBaseClass = BaseRepositoryImpl.class)
@SpringBootApplication
public class Application {
    // ...
}

然后让具体Repository继承通用基类：

public interface TableARepository extends BaseRepository<TableAItems, Long, TAFilter> {
}

public interface TableBRepository extends BaseRepository<TableBItems, Long, TBFilter> {
}

这种方式适合大量类似表的场景，一次性封装通用逻辑，后续新增表只需继承基类即可。

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