Announcement Announcement Module
No announcement yet.
How can Jdbc template be used for UPDATE query with IN clause ? Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • How can Jdbc template be used for UPDATE query with IN clause ?

    I've gone through various forums to handle the IN clause using spring's namedParamJdbcTemplate but i still do not get the stuff I'm exactly looking for.
    Below is my issue:

    I've the following method:

        public void  updateBatchTableForStatus(List<Integer> reportShellIds, String scheduleType) {
            Map<String,List<Integer>> shellIds = Collections.singletonMap("reportShellIds", reportShellIds);
            MapSqlParameterSource parameters = new MapSqlParameterSource();
            parameters.addValue("reportShellIds", shellIds, Types.NUMERIC)
            parameters.addValue("eventType", scheduleType, Types.VARCHAR);
            this.namedParamJdbcTemplate.update(GET_EVENT_METADATA_INFO, parameters);
    The query refered in above method is as defined below:

             public static final String SQL_UPDATE_BATCH_LOOKUP_TABLE_FOR_STATUS_BY_BATCH_IDS = "" +
                        "UPDATE " +
                        TABLE_BATCH_REF + " BLK " +
                        "SET " +
                        "BLK.EXECUTION_STATUS_CODE = :eventType " +
                        "WHERE " +
                        "BLK.BATCH_ID  in(:reportShellIds) ";
    Datatype for BATCH_ID column is Number(24,0) and for the EXECUTION_STATUS_CODE column Varchar.
    I'm using Oracle db.

    However, the above method throws a SQL exception.

    Can someone pls tell me where I'm wrong and what is the fix for it ?

    Many thanks in advance.

    Best Regards

  • #2
    Try these advices:


    • #3
      Hi Artem - Thanks for responding. I've already looked at the posts you suggested, however my query is slightly different. The SQL in my case accepts two parameters from jdbc parameter source. Out of these two parameters, one of them uses an IN clause.
      The posts that I've seen until now have SQL with just one parameter.