Pass multiple values in IN clause using mybatis annotations

Question

I am trying to pass multiple values in a SELECT QUERY consisting of an IN clause. I would like to execute the following query using mybatis:

SELECT * FROM DBA_COMPARISON_ROW_DIF WHERE SCAN_ID in  (#{childScanIDs})

where the childScanIDs variable consists of multiple comma separated values. I am passing these values as a String consisting of all the values separated with a comma. Below is the method being used in the mapper interface.

@Select(getDifferencesByScanIDs)
    @Results({
            @Result(property="owner", column="OWNER"),
            @Result(property="comparisonName", column="COMPARISON_NAME"),
            @Result(property="scanID", column="SCAN_ID"),
            @Result(property="localRowID", column="LOCAL_ROWID"),
            @Result(property="remoteRowID", column="REMOTE_ROWID"),
            @Result(property="indexValue", column="INDEX_VALUE"),
            @Result(property="status", column="STATUS"),
            @Result(property="lastUpdateTime", column="LAST_UPDATE_TIME")
    })
    List<Difference> getDifferencesByScanIDs(@Param("childScanIDs") String childScanIDs);

I am constructing the string of values this way:

String scanIDs = StringUtils.join(cmp.getChildScanIDs(), ",");

When executing the function, it is raising this exception.

### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

### The error may involve com.rs2.automation.soacomparison.dao.differences.DifferencesMapper.getDifferencesByScanIDs-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

Any help would be appreciated. I think the problem is that mybatis is not recognizing the values as separate numbers. Another solution would be to execute the query on each number but I wanted to pass all the values at once. Thanks in advance.

score 8 · Accepted Answer · answered Jul 06 '16 at 14:40

8

SELECT * FROM DBA_COMPARISON_ROW_DIF WHERE SCAN_ID in  (#{childScanIDs})

If we imagine a list like 2, 13, 15, 16, this query will result in:

SELECT * FROM DBA_COMPARISON_ROW_DIF WHERE SCAN_ID in  ('2,13,15,16')

As you can see, the value is treated as String and not as list, which is not what you need.

I would pass cmp.getChildScanIDs() to mybatis, without transforming it to string, and use mybatis' dynamic sql.

Edit the method as:

List<Difference> getDifferencesByScanIDs(@Param("childScanIDs") List<Integer> childScanIDs); // or int[]

Edit the query as:

SELECT * FROM DBA_COMPARISON_ROW_DIF WHERE SCAN_ID in 
    <foreach item="item" index="index" collection="childScanIDs" open="(" separator="," close=")">
    #{item}
    </foreach>

What it does:

<foreach>: starts a foreach loop
item: the name of the current object inside the loop
index: the index variable (not really needed in this case)
collection: the name of the collection (as per your definition in the mapper interface)
open: string to insert before the first iteration
close: string to insert after the last iteration
separator: string to insert between an element and another
#{item}: prints the current item

Link for reference: MyBatis Dynamic SQL

answered Jul 06 '16 at 14:40

BackSlash

21,927
22
96
136

Thanks. I tried this solution but it is giving me the error: ### Error querying database. Cause: org.apache.ibatis.type.TypeException: Error setting null parameter. Most JDBC drivers require that the JdbcType must be specified for all nullable parameters. Cause: java.sql.SQLException: Invalid column type: 1111 – gla315 Jul 06 '16 at 17:46
@gla315 Try telling mybatis which type item is: Replace `#{item}` with `#{item,jdbcType=INTEGER}` inside the foreach – BackSlash Jul 06 '16 at 18:32
Tried that but it is giving me invalid SQL statement. This is the query: @Select({""}) – gla315 Jul 06 '16 at 20:33
This is the way I am passing the list of integers: List list = new ArrayList(); for(String st : cmp.getChildScanIDs()){ Integer intNum = new Integer(st); list.add(intNum); } List differences = mapperLocal.getDifferencesByScanIDs(list); – gla315 Jul 06 '16 at 20:33
@gla315 I don't know if something has changed since 2013, but as stated [there](http://stackoverflow.com/questions/15012691/mybatis-dynamic-sql-inside-annotation) probably dynamic sql doesn't work with annotations. To solve your issue you need dynamic sql, so if you want to use it you need to support XML-based mappers, which is also better IMO because you separate java and sql logic, making things more clear. – BackSlash Jul 06 '16 at 22:02

score 4 · Answer 2 · answered Dec 12 '16 at 19:24

This isn't very clear on the documentation. There are several ways to do this but a straight forward way is to just use OGNL. We evaluate any parameters #{xx} or ${xx} (difference explained in the docs) via OGNL.

So you can for example create

public class InList {
    public static String build(final List<?> params) {
        return Joiner.on(",").join(params);
    }
}

And use it in your query as

WHERE SCAN_ID IN (${@InList@build(childScanIds)})

Note: Using ${} opens up accidental or intentional SQL injection problems but, obviously, that is also dependent on your use case so use it carefully.

Pass multiple values in IN clause using mybatis annotations

2 Answers2