注意：JPA @Query动态添加参数

0、前言

记录实际工作中的经验

1、在mysql中慎用'2022-09-06' != NULL

2、例子

其它参数包含了String和Integer类型

/**
 * Order inner join Detail查询数据list
 */
@Query(value = "SELECT" +
        " epos.order_id AS orderId," +
        " epos.dining_day AS diningDay," +
        " epos.dining_time AS diningTime," +
        " epos.user_name AS userName," +
        " epos.dept_name AS deptName," +
        " epos.amount AS eposAmount," +
        " detail.detail_id AS detailId," +
        " detail.dish_name AS dishName," +
        " detail.amount AS detailAmount" +
        " FROM epos_order epos" +
        " INNER JOIN epos_order_detail detail ON epos.order_id = detail.order_id" +
        " WHERE epos.deleted = 0 AND detail.deleted = 0" +
        " AND IF(ISNULL(?1) OR ?1 == '',1 = 1,epos.dining_day = ?1)" +
        " AND IF(ISNULL(?2),1 = 1,epos.dining_time = ?2)" +
        " ORDER BY epos.order_time DESC", nativeQuery = true)
List<CollectHaveDIshVoInt> queryOrderJoinDetailList(String diningDay, Integer diningTime);