[SOLVED] Pass values to a prepared statement where there are 2 values to insert in 6 places

Pass values to a prepared statement where there are 2 values to insert in 6 places

I have the following SQL string:

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

As you can see, I need to replace :userId and :friendId with ? before I execute the SQL string using JdbcTemplate. Usually when I do that, I do something like this:

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);

The issue here is that the SQL string is more complicated and I am not sure how am I supposed to pass the userId once and apply it to 2 places.

I have variables int friendId = 2 and int userId = 4. I tried doing something like this:

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

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

Is there a more intuitive way to achieve this?

Solution

This is exactly what the NamedParameterJdbcTemplate was created for:

String sql = ...;
NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
Map<String, String> params = Map.of("friendId", "2", "userId", "4");

List<FriendMessageDTO> collection = 
    template.query(sql, params, new FriendMessageRowMapper());