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

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

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

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

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

> On Jun 1, 2015, at 10:42 PM, Aaron Lea <[hidden email]> wrote:
>
> 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
>
> --
> 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 [hidden email].
> For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

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

> On Jun 2, 2015, at 8:17 AM, François Schiettecatte <[hidden email]> wrote:
>
> 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
>
>> On Jun 1, 2015, at 10:42 PM, Aaron Lea <[hidden email]> wrote:
>>
>> 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
>>
>> --
>> 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 [hidden email].
>> For more options, visit https://groups.google.com/d/optout.
>
> --
> 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 [hidden email].
> For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

Ikchan Sim
In reply to this post by Aaron Lea
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 ))

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

Ray Sprinkle
In reply to this post by Aaron Lea
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 [hidden email]
> <mailto:[hidden email]>.
> For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.