CODEWITHSUNDEEP
sqlmybatis
kasdega
kasdega

Reputation: 18786

What is the mybatis sql variable syntax for literals

I have a bunch of sql in *Dao.java files that look something like:

select * from table where id = #{id};

It works fine if all I want is 1 record where ID is an int. However, I want to write a statement like this:

select * from table where id in (#{idList});

How do I change #{idList} so that the list (which is passed in as a String of concatenated IDs) will not be wrapped with quotes in the actual sql statement that results?

Right now the resulting sql is something like:

select * from table where id in ('1,2,3,4,5');

and I'd like it to be 

select * from table where id in (1,2,3,4,5);

Upvotes: 1

Views: 2672

Answers (1)

Landys
Landys

Reputation: 7757

MyBatis uses ${variable} to support literals. For your case, the sql could be as follows.

select * from table where id in (${idList})

But just remember, ${} CANNOT prevent sql injection. If you can make sure sql injection is not possible for your case, it's fine.

An alternative is to use foreach to support in closure with MyBatis 3. Please refer to http://mybatis.github.io/mybatis-3/dynamic-sql.html#foreach for detail.

For your case, the sql could be:

select * from table where id in
<foreach item="item" index="index" collection="list"
    open="(" separator="," close=")">
    #{item}
</foreach>

And the parameter type is List, but not a string of concatenated IDs.

Upvotes: 2

Related Questions