How to use Annotations with iBatis (myBatis) for an IN query?

Question

We'd like to use only annotations with MyBatis; we're really trying to avoid xml. We're trying to use an "IN" clause:

@Select("SELECT * FROM blog WHERE id IN (#{ids})") 
List<Blog> selectBlogs(int[] ids); 

MyBatis doesn't seem able to pick out the array of ints and put those into the resulting query. It seems to "fail softly" and we get no results back.

It looks like we could accomplish this using XML mappings, but we'd really like to avoid that. Is there a correct annotation syntax for this?

Answer 1

I believe this is a nuance of jdbc's prepared statements and not MyBatis. There is a link here that explains this problem and offers various solutions. Unfortunately, none of these solutions are viable for your application, however, its still a good read to understand the limitations of prepared statements with regards to an "IN" clause. A solution (maybe suboptimal) can be found on the DB-specific side of things. For example, in postgresql, one could use:

"SELECT * FROM blog WHERE id=ANY(#{blogIds}::int[])"

"ANY" is the same as "IN" and "::int[]" is type casting the argument into an array of ints. The argument that is fed into the statement should look something like:

"{1,2,3,4}"