如何通过参数实现native query原生SQL的动态条件查询？

你遇到的问题本质是@Query注解的value属性为编译期固定常量，无法在运行时调用方法动态拼接SQL片段，可选择以下两种方案实现需求：

方案1：SQL内置参数判断（最小改动，无需调整现有结构） #

直接在原生SQL中加入参数逻辑判断，不用拼接SQL即可实现动态过滤，修改后的代码如下：

@Query(value = " select * " +
        "from t_user usr " +
        "left outer join t_sale_order ord on usr.id = ord.user_idx " +
        "LEFT OUTER JOIN ( " +
            "SELECT " +
                "sale_order_idx, sum(taxable_amount) + sum(non_taxable_amount) as amount " +
            "FROM t_sale_receipt " +
            "GROUP BY sale_order_idx " +
        ") receipt " +
        "ON receipt.sale_order_idx = ord.id " +
        "where NOT exists ( " +
            "select 1 from t_encourage_sent_list sl " +
            "where sl.user_idx  = usr.id and sl.push_idx = ?1 " +
        ") " +
        /* 新增动态条件开始 */
        "AND ( " +
            "?2 IS NULL " +
            "OR (?2 = 0 AND NOT EXISTS ( SELECT 1 FROM t_user_study us WHERE us.user_idx = usr.id )) " +
            "OR (?2 != 0 AND EXISTS ( SELECT 1 FROM t_user_study us WHERE us.user_idx = usr.id )) " +
        ") " +
        /* 新增动态条件结束 */
        "AND usr.active = 1", nativeQuery = true)
List<User> findEncouragePushMsgTarget(Integer pushIdx, Integer readBook);

逻辑说明：

• 当readBook为null时，整段条件恒为真，相当于不添加该过滤规则
• 当readBook为0时，执行NOT EXISTS逻辑，查询无学习记录的用户
• 当readBook不为0时，执行EXISTS逻辑，查询有学习记录的用户

注意你原有静态方法存在两个明显bug：

1. 字符串是不可变类，replace方法只会返回新字符串，不会修改原字符串，你原有写法的替换逻辑完全不生效
2. 逻辑写反：if ( readBook != null ) return ""; 表示只要传了readBook参数就不添加该条件，和你要实现的逻辑完全相反

方案2：自定义Repository实现动态SQL拼接（适合多动态条件扩展场景） #

如果后续还要新增更多动态过滤条件，可自定义Repository实现类，自行拼接SQL：

1. 先定义自定义查询接口

public interface CustomUserRepository {
    List<User> findEncouragePushMsgTarget(Integer pushIdx, Integer readBook);
}

2. 实现接口自行拼接SQL

@Repository
public class CustomUserRepositoryImpl implements CustomUserRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<User> findEncouragePushMsgTarget(Integer pushIdx, Integer readBook) {
        StringBuilder sql = new StringBuilder("select * from t_user usr " +
                "left outer join t_sale_order ord on usr.id = ord.user_idx " +
                "LEFT OUTER JOIN ( " +
                    "SELECT sale_order_idx, sum(taxable_amount) + sum(non_taxable_amount) as amount " +
                    "FROM t_sale_receipt GROUP BY sale_order_idx " +
                ") receipt ON receipt.sale_order_idx = ord.id " +
                "where NOT exists ( " +
                    "select 1 from t_encourage_sent_list sl where sl.user_idx  = usr.id and sl.push_idx = :pushIdx " +
                ") ");
        // 动态拼接条件
        if (readBook != null) {
            if (readBook == 0) {
                sql.append("AND NOT EXISTS ( SELECT 1 FROM t_user_study us WHERE us.user_idx = usr.id ) ");
            } else {
                sql.append("AND EXISTS ( SELECT 1 FROM t_user_study us WHERE us.user_idx = usr.id ) ");
            }
        }
        sql.append("AND usr.active = 1");
        // 执行查询
        Query query = entityManager.createNativeQuery(sql.toString(), User.class);
        query.setParameter("pushIdx", pushIdx);
        return query.getResultList();
    }
}

3. 让原来的UserRepository继承CustomUserRepository接口即可使用该方法

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