如何在JdbcTemplate中复用参数执行含多占位符的复杂SQL？

我有如下SQL语句：

SELECT fm.*, 
       u.username AS friend_username
FROM friend_messages fm
JOIN users u ON 
    (u.id = fm.sender_id AND fm.sender_id = :friendId) 
    OR 
    (u.id = fm.receiver_id AND fm.receiver_id = :friendId)
WHERE 
    (fm.sender_id = :userId AND fm.receiver_id = :friendId) 
    OR 
    (fm.sender_id = :friendId AND fm.receiver_id = :userId)
ORDER BY fm.created_at ASC

由于需要使用JdbcTemplate执行，我得把:userId和:friendId替换成?。通常简单场景我会这么写：

String serverQuery = "SELECT sm.id AS message_id, sm.server_id, sm.sender_id, u.username AS sender_username, sm.content, sm.created_at FROM server_messages sm JOIN users u ON sm.sender_id = u.id WHERE sm.server_id = ? ORDER BY sm.created_at ASC";
List<ServerMessageDTO> collection = this.db.query(serverQuery, new ServerMessageRowMapper(), serverId);

但当前SQL更复杂，userId要填2个位置，friendId要填4个位置，我试过重复传参的方式：
修改后的SQL：

SELECT fm.*, 
       u.username AS friend_username
FROM friend_messages fm
JOIN users u ON 
    (u.id = fm.sender_id AND fm.sender_id = ?) 
    OR 
    (u.id = fm.receiver_id AND fm.receiver_id = ?)
WHERE 
    (fm.sender_id = ? AND fm.receiver_id = ?) 
    OR 
    (fm.sender_id = ? AND fm.receiver_id = ?)
ORDER BY fm.created_at ASC

Java代码：

List<FriendMessageDTO> collection = this.db.query(serverQuery, new FriendMessageRowMapper(), friendId, friendId , userId, friendId, friendId, userId);

请问有没有更直观的实现方式？

1. 使用NamedParameterJdbcTemplate（最推荐） #

Spring提供的NamedParameterJdbcTemplate支持命名参数（即原SQL中的:userId、:friendId），无需手动替换为?，参数对应关系清晰，不会混淆顺序。

示例代码：

// 保留原SQL的命名参数，无需修改
String query = """
    SELECT fm.*, u.username AS friend_username 
    FROM friend_messages fm 
    JOIN users u ON 
        (u.id = fm.sender_id AND fm.sender_id = :friendId) 
        OR 
        (u.id = fm.receiver_id AND fm.receiver_id = :friendId)
    WHERE 
        (fm.sender_id = :userId AND fm.receiver_id = :friendId) 
        OR 
        (fm.sender_id = :friendId AND fm.receiver_id = :userId)
    ORDER BY fm.created_at ASC
""";

// 构造参数Map，每个参数只需设置一次
Map<String, Object> params = new HashMap<>();
params.put("userId", userId);
params.put("friendId", friendId);

// 初始化NamedParameterJdbcTemplate（建议注入到类中，无需每次新建）
NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(db);
List<FriendMessageDTO> collection = namedJdbcTemplate.query(query, params, new FriendMessageRowMapper());

2. 优化SQL减少占位符数量 #

通过简化逻辑，减少同一参数的占位符出现次数，让SQL更简洁，传参更轻松：

优化后的SQL（支持命名参数）：

SELECT fm.*, 
       u.username AS friend_username
FROM friend_messages fm
JOIN users u ON u.id = :friendId AND u.id IN (fm.sender_id, fm.receiver_id)
WHERE (fm.sender_id, fm.receiver_id) IN ((:userId, :friendId), (:friendId, :userId))
ORDER BY fm.created_at ASC

如果坚持使用普通JdbcTemplate，替换为?后：

SELECT fm.*, 
       u.username AS friend_username
FROM friend_messages fm
JOIN users u ON u.id = ? AND u.id IN (fm.sender_id, fm.receiver_id)
WHERE (fm.sender_id, fm.receiver_id) IN ((?, ?), (?, ?))
ORDER BY fm.created_at ASC

传参时按顺序传入friendId, userId, friendId, friendId, userId即可，参数重复次数明显减少。

3. 封装参数数组（兼容普通JdbcTemplate） #

若不想切换模板类，可将参数封装为数组并添加注释，明确每个占位符对应的参数：

// 按SQL中?的顺序封装参数，添加注释明确含义
Object[] params = new Object[]{
    friendId,  // JOIN条件：fm.sender_id = ?
    friendId,  // JOIN条件：fm.receiver_id = ?
    userId,    // WHERE条件1：fm.sender_id = ?
    friendId,  // WHERE条件1：fm.receiver_id = ?
    friendId,  // WHERE条件2：fm.sender_id = ?
    userId     // WHERE条件2：fm.receiver_id = ?
};
List<FriendMessageDTO> collection = this.db.query(serverQuery, new FriendMessageRowMapper(), params);

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