如何在JdbcTemplate中执行带参数的查询？参数查询无返回值如何解决？

你当前代码查询无数据的核心原因是模糊查询的占位符写法错误，'%?%'中的?会被JDBC识别为普通字符串内容，不会被当做参数占位符处理，最终执行的SQL相当于在匹配包含?字符的记录，自然无法返回符合关键词的结果。

方案1：参数侧拼接模糊匹配符 #

SQL中仅保留占位符，将%拼接在参数内容两侧再传入：

public List<Detection> findAll(String key) {
    String sql = " SELECT * "
            + "FROM "
            + "    People p "
            + "        RIGHT JOIN "
            + "    Detection d ON p.people_id = d.people_id "
            + "        LEFT JOIN "
            + "    CustomerType ct ON p.customer_type = ct.id "
            + "        LEFT JOIN "
            + "    groups g ON p.group_id = g.group_id "
            + "        LEFT JOIN "
            + "    camera ca ON ca.camera_id = d.camera_id "
            + "where (liveness_status <> 'FALSE' and liveness_status <> 'false') AND ( d.people_id like ? OR  full_name Like ? )"
            + " AND d.day_first_time >= CURDATE() - INTERVAL 1 DAY "
            + " ORDER BY created_time DESC ";
    // 给查询关键词前后拼接模糊匹配符
    String fuzzyKey = "%" + key + "%";
    return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(Detection.class), fuzzyKey, fuzzyKey);
}

方案2：SQL侧用CONCAT拼接模糊匹配符 #

通过SQL的CONCAT函数在数据库侧拼接%和参数，无需修改参数传入逻辑：

public List<Detection> findAll(String key) {
    String sql = " SELECT * "
            + "FROM "
            + "    People p "
            + "        RIGHT JOIN "
            + "    Detection d ON p.people_id = d.people_id "
            + "        LEFT JOIN "
            + "    CustomerType ct ON p.customer_type = ct.id "
            + "        LEFT JOIN "
            + "    groups g ON p.group_id = g.group_id "
            + "        LEFT JOIN "
            + "    camera ca ON ca.camera_id = d.camera_id "
            + "where (liveness_status <> 'FALSE' and liveness_status <> 'false') AND ( d.people_id like CONCAT('%',?,'%') OR  full_name Like CONCAT('%',?,'%') )"
            + " AND d.day_first_time >= CURDATE() - INTERVAL 1 DAY "
            + " ORDER BY created_time DESC ";
    return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(Detection.class), key, key);
}

如果需要更灵活的参数绑定方式，可以使用NamedParameterJdbcTemplate，支持具名参数，不需要按占位符顺序传参，避免参数顺序错乱的问题：

// 先注入NamedParameterJdbcTemplate实例
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public List<Detection> findAll(String key) {
    String sql = " SELECT * "
            + "FROM People p "
            + "RIGHT JOIN Detection d ON p.people_id = d.people_id "
            + "LEFT JOIN CustomerType ct ON p.customer_type = ct.id "
            + "LEFT JOIN groups g ON p.group_id = g.group_id "
            + "LEFT JOIN camera ca ON ca.camera_id = d.camera_id "
            + "where (liveness_status <> 'FALSE' and liveness_status <> 'false') "
            + "AND ( d.people_id like CONCAT('%',:searchKey,'%') OR full_name Like CONCAT('%',:searchKey,'%') )"
            + "AND d.day_first_time >= CURDATE() - INTERVAL 1 DAY "
            + "ORDER BY created_time DESC ";
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("searchKey", key);
    return namedParameterJdbcTemplate.query(sql, params, BeanPropertyRowMapper.newInstance(Detection.class));
}

内容的提问来源于stack exchange，提问作者Quách Ngọc Thành