How to Pass String for delete multiple row by using spring MVC jdbcTempates in parameterized query

Question

I am stuck on spring mvc jdbcTemplates. I want to delete multiple images by id . But I am not able to achieve in jdbcTemplates.

I am using String to store all ids and separate them by comma.

public int deleteHomeImage(String imageId, String imageName) {   // imageId = 1,2,3 like that 
    logger.debug("Image id is:"+imageId);
    System.out.println("dbyy"+imageId);  // output in console 1,2,3
    String arry[] = imageId.split(",");
    int ab[]=new int[arry.length];
    List<Integer> lis = new ArrayList();
    for(int i=0;i<ab.length;i++)
    {
    //((imageId[i]));
        lis.add(Integer.parseInt(arry[i]));
        System.out.println(arry[i]);   
    }
    Map<String, Object> params = new HashMap<String, Object>();
    params.put("imageId", arry);


    //int deleteFlag = getJdbcTemplate().update(QueryConstant.imageHomeDelete, params);
    //int deleteFlag = getJdbcTemplate().update(QueryConstant.imageHomeDelete).
    //Query q=.createQuery("DELETE FROM INTERNET_IMG_ADV WHERE IMG_ID IN(:id)");
    int deleteFlag = getJdbcTemplate().update(QueryConstant.imageHomeDelete,lis);

    //int deleteFlag = getJdbcTemplate().update(QueryConstant.imageHomeDelete, new Object[]{params});
    return deleteFlag;
}

SQL Query----

String imageAdvDelete = "DELETE FROM INTERNET_IMG_ADV WHERE IMG_ID IN(:id)"

But i got error in console --

SEVERE: Servlet.service() for servlet [DispatcherServlet] in context with path [/ona_new] threw exception [Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [DELETE FROM INTERNET_IMG_DETAILS WHERE IMG_ID in ? ]; SQL state [null]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type] with root cause java.sql.SQLException: Invalid column type

Answer 1

Below code snippet will solve your problem.

  NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate());
    List imageIds= Arrays.asList(new Integer[] {1, 2, 3});
    Map<String,List> params = Collections.singletonMap("ids", imageIds);
    int deleteFlag = namedTemplate .update(QueryConstant.imageHomeDelete,params);