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 the answer is the same as is given in this question. You can use myBatis Dynamic SQL in your annotations by doing the following:

@Select({"<script>",          "SELECT *",           "FROM blog",          "WHERE id IN",             "<foreach item='item' index='index' collection='list'",              "open='(' separator=',' close=')'>",              "#{item}",            "</foreach>",          "</script>"})  List<Blog> selectBlogs(@Param("list") int[] ids); 

The <script> element enables dynamic SQL parsing and execution for the annotation. It must be very first content of the query string. Nothing must be in front of it, not even white space.

Note that the variables that you can use in the various XML script tags follow the same naming conventions as regular queries, so if you want to refer to your method arguments using names other than "param1", "param2", etc... you need to prefix each argument with an @Param annotation.

Answer 2

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}"