No Value specified for parameter 2

Question

I am getting this error when trying to run my Spring boot. java.sql.SQLException: No value specified for parameter 2 My code is this:

    public UserTemp findHistoryByID(Integer Patient_Number) {
    String sql = "select Col1\n" +
            "from (\n" +
            "    select Past_Diagnoses_1 as Col1\n" +
            "    from patienthistory\n" +
            "    where Patient_Number = ?\n" +
            "    union\n" +
            "    select Past_Diagnoses_2 as Col1\n" +
            "    from patienthistory" +
            "    where Patient_Number = ?" +
            "    ) as T;";

        return jdbcTemplate.queryForObject(sql, new Object[]{Patient_Number}, (rs, rowNum) ->
            new UserTemp(
                    rs.getString("Col1")

            ));
}

Answer 1

As in the comments, you are having 2 placeholders in the SQL query. So you have to pass patient_number 2 times.

Coming to your second question, it depends on your requirement.

1. If you need a single result, you need to fix it on the DB side as it's a data issue or the query used is not proper.
2. If more than one result is allowed, you can use jdbcTemplate.queryForList() instead of jdbcTemplate.queryForObject(). And change the return type of findHistoryByID() to List<Map<String,Object>> and all callers of this function.

Note: Here key for each Map in List is column names returned from DB.

More information on jdbcTemplate.queryForList() is in official documentation