How to use in clause when using org.apache.ibatis.jdbc.SQL to construct dynamic sql?

[Aaron Lea]

I.E. how to convert:

id in
<foreach collection="idList" item="id" separator="," open="(" close=")">
    #{id}
</foreach>

to

new SQL(){{
    WHERE("id in (???)");
}};

Please help

[François Schiettecatte]

Aaron

The way I handle this (which is fulgy) is to concatenate the values into a comma delimited string and pass that as a parameter, for example:

<select id="selectList" parameterType="String" resultMap="resultMap">

SELECT id
FROM table
WHERE id IN (${value})
ORDER BY FIND_IN_SET(id, '${value}')

</select>

Not sure if this helps, very interested to see what others come up with.

François

> --
> You received this message because you are subscribed to the Google Groups "mybatis-user" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Brian Pontarelli]

I brought this up a year ago but haven't had time to try and code my solution. I thought a spread operator would be really nice. Something like @{ids}. That would also make using annotations more appealing since it is such a common use case.

-- Brian

Sent from my iPhone

[Ikchan Sim]

Do not support IN statement in a static method.

There are the following ways : 

import static org.apache.ibatis.jdbc.SelectBuilder.*;

import java.util.ArrayList;

import java.util.Collection;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

public class SelectBuilder {

public static void main(String[] args) {

Map<String, Object> parameters = new HashMap<String, Object>();

List<Object> statuses = new ArrayList<Object>();

statuses.add("Y");

statuses.add("N");

parameters.put("statuses", statuses);

String sql = new SelectBuilder().select(parameters);

System.out.println("Execute SQL : \r\n" + sql);

}

public String select(Map<String, Object> parameters) {

BEGIN();

SELECT("SHOP_NO, SHOP_NAME, SHOP_LOCATION, SHOP_STATUS");

FROM("SHOP");

inCondistion("SHOP_STATUS", (Collection) parameters.get("statuses"));

return SQL();

}

public static void inCondistion(String columnName, Collection collections) {

if (collections == null) {

return;

}

StringBuilder inCondition = new StringBuilder("");

if (!collections.isEmpty()) {

inCondition.append(columnName).append(" IN ( ");

Iterator iteratores = collections.iterator();

while (iteratores.hasNext()) {

inCondition.append(String.valueOf(iteratores.next())).append(", ");

}

inCondition.replace(inCondition.length() - 2, inCondition.length(), "");

inCondition.append(" )");

WHERE(inCondition.toString());

} else {

return;

}

}

}

Execute SQL : 

    SELECT SHOP_NO, SHOP_NAME, SHOP_LOCATION, SHOP_STATUS

    FROM SHOP

    WHERE (SHOP_STATUS IN ( Y, N ))

[Ray Sprinkle]

In my code where I reuse the query a lot I create a fixed sized IN set
and then call the query multiple times if needed.

<select id="example">
select *
from something
where value in (#{param[0]}, #{param[1]}, #{param[2], ..., #{param[n]})
</select>

Param ALWAYS has n elements with the unused slots filled with an invalid
value.

There are definite pros and cons to this approach:

Pros
- No dynamic SQL. This can be a real winner if your database executes
prepared statements faster than dynamic one. Mine does.
- Escaping is not a worry, already handled by MyBatis and JDBC.'

Cons
- Need to pad and or split the parameter list. This also creates an
undesirable dependency between the mapper and java.
- May have to call the query multiple times.

On 6/1/2015 10:42 PM, Aaron Lea wrote:
> I.E. how to convert:
> |
>
> idin

> <foreach collection="idList" item="id" separator="," open="(" close=")">
> #{id}
> </foreach>
>
> |
>
> to
> |
>
> new SQL(){{
> WHERE("id in (???)");
> }};
>
> |
>
> Please help
>

> --
> You received this message because you are subscribed to the Google
> Groups "mybatis-user" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to mybatis-user...@googlegroups.com

> <mailto:mybatis-user...@googlegroups.com>.