SELECT IN clause in JDBCTemplates is not working

Question

I am tying to follow other example like this How to use SELECT IN clause in JDBCTemplates? to use in clause in my query, but I am getting this error from queryForInt:

The method queryForInt(String, Object[]) in the type JdbcTemplate is not applicable for the arguments (String, Map)

This is my code:

    List groupList = getGroupsForUser(username);


    List<String> groupPara = new ArrayList<String>(); 

    for (Iterator groups = groupList.iterator(); groups.hasNext();) {
        Group g = (Group) groups.next();
        groupPara.add(g.getGroupName());
    }
    Map namedParameters = Collections.singletonMap("listOfValues", groupPara);

    int groupPermissions = getJdbcTemplate().queryForInt( "SELECT MAX(roleid) FROM groupaccess "
            + "WHERE inode = '"+inode.getId()+"' AND groupname in (:listOfValues)", namedParameters);

    return groupPermissions;

I got it to work by doing this, although is not very elegant:

List groupList = getGroupsForUser(username);
String groupPara = ("(");
Object[] params = new Object[groupList.size()+1];
params[0]=inode.getId();
int index = 1;
for (Iterator groups = groupList.iterator(); groups.hasNext();) {
    Group g = (Group) groups.next();
    params[index]=g.getGroupName();
    index++;
    groupPara += " ? ,";
}
groupPara = groupPara.substring(0, groupPara.length() - 1);
groupPara+=")";

int groupPermissions = getJdbcTemplate().queryForInt( "SELECT MAX(roleid) FROM groupaccess "
        + "WHERE inode = ? AND groupname in "+groupPara, params);

return groupPermissions;

It clearly states that it expects `array` and not `Map` as the 2nd parameter. So, what is your question? — PM 77-1, Oct 01 '13 at 18:23
my question is how am I suppose to pass in a list as parameter? In every example I seen, http://stackoverflow.com/questions/4504592/how-to-use-select-in-clause-in-jdbctemplates, http://stackoverflow.com/questions/8489138/jdbctemplate-in-clause-for-string-elements, http://stackoverflow.com/questions/4504592/how-to-use-select-in-clause-in-jdbctemplates, http://stackoverflow.com/questions/1327074/how-to-execute-in-sql-queries-with-springs-jdbctemplate-effectivly, they passed a map object. — help, Oct 01 '13 at 18:34

Jatin · Answer 1 · 2013-10-01T18:38:04.737

2

jdbcTemplate does not have any method queryForInt(String, Map). Instead you need to use NamedParameterJdbcTemplate . i.e:

Map<String,Object> namedParameters = Collections.singletonMap("listOfValues", groupPara);

int groupPermissions = namedParameterJdbcTemplate.queryForInt( "SELECT MAX(roleid) FROM groupaccess "
        + "WHERE inode = '"+inode.getId()+"' AND groupname in (:listOfValues)", namedParameters);

Or there is a workaround below:

Set<Integer> ids = groupPara;

MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("listOfValues", ids);

List<Integer> foo = getJdbcTemplate().queryForInt( "SELECT MAX(roleid) FROM groupaccess "
            + "WHERE inode = '"+inode.getId()+"' AND groupname in (:listOfValues)", getRowMapper(), namedParameters);

edited Oct 01 '13 at 18:38

answered Oct 01 '13 at 18:25

Jatin

31,116
15
98
163

@PM77-1 Nope a type. Corrected it – Jatin Oct 01 '13 at 18:39
@help you might want to google "jdbctemplate sql IN clause" for more examples :) – Jatin Oct 01 '13 at 18:40
Why? It's not my question. I'm just a bystander here. – PM 77-1 Oct 01 '13 at 18:42
@PM77-1 Oops sorry. mistake again – Jatin Oct 01 '13 at 18:42
Still doesnt work for me. From here, http://docs.spring.io/spring/docs/3.0.x/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html, it seems no function for jdbcTemplate take MapSqlParameterSource as parameter – help Oct 01 '13 at 19:08
Yes that is what I mentioned. You need to use NamedParameterJdbcTemplate for that – Jatin Oct 01 '13 at 19:09

SELECT IN clause in JDBCTemplates is not working

1 Answers1