Dowemo
0 0 0 0

I'm trying to implement"multiple id incoming sql statements".

1, I tried to convert array StringUtils, join ( array, & & & ); array to string and separated with comma

No success. Because it's"123, 234, 435"in the sql, and we need the"123","234","microformats"format.

2, oracle split string to table

A where id in ( str2table _ * _ from _ table ( ( # % ) ) ) ) of * from ( ( ) );

And str2table can write:

Create or replace type ty_object as object ( col_name varchar2 ( 200 ) );
/
Create or replace type ty_table as table of ty_object;
/
Create or replace function str2table ( v_str in varchar2 )
V_delimiter in varchar2 )
The purpose of this function is to convert a string separated by a specific character to a cursor form to iterate through this cursor
Return ty_table as
A v_ty_table ty_table;
BEGIN
Select ty_object ( regexp_substr ( v_str )
'[ ^ '| | v_delimiter | | '] + '
1,
Level,
'I ') ) )
BULK COLLECT
Into v_ty_table
FROM DUAL
Connect by level <=
Length ( v_str ) -
Length ( regexp_replace ( v_str, '| | v_delimiter | | ') ) + 1;
Return v_ty_table;
A end;


From: http://blog. Csdn. net/e_wsq/article/details/52381846.


In this way, the format of"123","234","345"is available.



3, you can also use the method provided by the mybatis

1. 当查询的参数只有一个时 
 findByIds(List<Long> ids)
 1.a 如果参数的类型是List, 则在使用时,collection属性要必须指定为 list
复制代码
 <select id="findByIdsMap" resultMap="BaseResultMap"> Select
 <include refid="Base_Column_List"/>
 from jria where ID in <foreach item="item" index="index" collection="list" 
 open="(" separator="," close=")"> #{item}
 </foreach>
 </select> 
复制代码
 findByIds(Long[] ids)
 1.b 如果参数的类型是Array,则在使用时,collection属性要必须指定为 array
复制代码
 <select id="findByIdsMap" resultMap="BaseResultMap">
 select <include refid="Base_Column_List"/>
 from jria where ID in <foreach item="item" index="index" collection="array" 
 open="(" separator="," close=")"> #{item}
 </foreach>
 </select> 
复制代码
2. 当查询的参数有多个时,例如 findByIds(String name, Long[] ids)
这种情况需要特别注意,在传参数时,一定要改用Map方式, 这样在collection属性可以指定名称
下面是一个示例
复制代码
 Map<String, Object> params = new HashMap<String, Object>(2);
 params.put("name", name);
 params.put("ids", ids);
 mapper.findByIdsMap(params);
 <select id="findByIdsMap" resultMap="BaseResultMap">
 select <include refid="Base_Column_List"/>
 from jria where ID in <foreach item="item" index="index" collection="ids" 
 open="(" separator="," close=")"> #{item}
 </foreach>
 </select> 

From: http://blog. Csdn. net/jarniyy/article/details/51169242.




Copyright © 2011 Dowemo All rights reserved.    Creative Commons   AboutUs