Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to pass an Integer Array to IN clause in MyBatis

There is a query in my Mybatis containing an IN clause which is basically a set of Id's ( Integers)

I am now stuck on how can I pass an Integer array to this IN clause so that it pulls up the proper records.Tried by passing a String containing the ID's to the IN clause , but this did not work as expected.

Code Sample below

Mybatis Method using Annotations

@Select(SEL_QUERY)     @Results(value = {@Result(property="id",column="ID")})     List<Integer> getIds(@Param("usrIds") Integer[] usrIds); 

Query

select distinct ID from table a where a.id in ( #{usrIds} ) 

Method Call

Integer[] arr = new Integer[2]; arr[0] = 1; arr[1] = 2;  mapper.getIds(arr) 

This is not working , Mybatis throws an error when I call the mapper method

Any suggestions please

like image 690
Vivek Avatar asked Jan 06 '12 07:01

Vivek


People also ask

Does MyBatis use prepared statement?

MyBatis does four main things: It executes SQL safely and abstracts away all the intricacies of JDBC. It maps parameter objects to JDBC prepared statement parameters. It maps rows in JDBC result sets to objects.

What is result map in MyBatis?

The resultMap element is the most important and powerful element in MyBatis. It's what allows you to do away with 90% of the code that JDBC requires to retrieve data from ResultSet s, and in some cases allows you to do things that JDBC does not even support.

What is TypeHandler in MyBatis?

typeHandlers. Whenever MyBatis sets a parameter on a PreparedStatement or retrieves a value from a ResultSet, a TypeHandler is used to retrieve the value in a means appropriate to the Java type.

What is Parametertype in MyBatis?

MyBatis provides various attributes for insert mapper, but largely we use id and parameter type. id is unique identifier used to identify the insert statement. On the other hand, parametertype is the class name or the alias of the parameter that will be passed into the statement.


1 Answers

The myBatis User Guide on Dynamic SQL has an example on how to use a foreach loop to build the query string, which works for lists and arrays.

Prior to release 3.2 you had to use xml configuration to use dynamic sql, with newer versions it should also be possible to use dynamic sql in annotations.

<select id="selectPostIn" resultType="domain.blog.Post">     SELECT *     FROM POST P     WHERE ID in     <foreach item="item" index="index" collection="list"              open="(" separator="," close=")">         #{item}     </foreach> </select> 
like image 99
Jörn Horstmann Avatar answered Oct 11 '22 15:10

Jörn Horstmann