Mybatis 3 selectkey inside foreach
MyBatis 3 - Spring
I want to insert a list of items and for each item ID should be generated from "TVA_UPSELLADMIN_CHANNEL_SEQ.nextVal" but I am getting .xml validation error that you cannot sub-child "selectKey" inside "foreach".
<insert id="insertServiceMappings" parameterType="java.util.List">
INSERT
<foreach collection="list" item="channel" index="index" >
<selectKey keyProperty="id" resultType="long" order="BEFORE">
SELECT TVA_UPSELLADMIN_CHANNEL_SEQ.nextVal from dual
</selectKey>
into tva_upselladmin_channel (id,source_id, service_id, name) values (#{id}, #{channel.sourceId}, #{channel.serviceId}, #{channel.name})
</foreach>
</insert>
source to share
MyBatis in the current version (or any other) does not allow use <selectKey>
inside a tag <forEach>
. This is just for iterating over the list.
The MyBatis dtd check section for the tag <forEach>
looks like this:
<!ELEMENT foreach (#PCDATA | include | trim | where | set | foreach | choose | if | bind)*>
<!ATTLIST foreach
collection CDATA #REQUIRED
item CDATA #IMPLIED
index CDATA #IMPLIED
open CDATA #IMPLIED
close CDATA #IMPLIED
separator CDATA #IMPLIED
>
So, to solve your problem, you need to iterate over the list, specifying all identifiers with additional select
definition in your XML. It would be like this:
<select id="nextvalKey" resultType="java.lang.Integer">
SELECT TVA_UPSELLADMIN_CHANNEL_SEQ.nextVal from dual
</select>
Your insert section will look like this:
<insert id="insertServiceMappings" parameterType="java.util.List">
<foreach collection="list" item="channel" index="index" >
INSERT INTO tva_upselladmin_channel (id,source_id, service_id, name)
VALUES ( #{id}, #{channel.sourceId}, #{channel.serviceId}, #{channel.name});
</foreach>
</insert>
Don't forget ;
after the insert command, because ORACLE doesn't support multiple insert values, for example. insert ... values (1,'bla'), (2, 'ble'), (3, 'bli');
)
So the second part should have a method in your implementation to set each ID for the items in the list. It will be something like:
public void someMethodInsertList(List<SomeObject> list){
//normally I do an implementation that allows me to use
//sqlSessionTemplate from mybatis through an extended class
for ( SomeObject obj : list ){
obj.setId( getSqlSessionTemplate.selectOne( 'nextvalKey' ) );
}
getSqlSessionTemplate.insert( 'insertServiceMappings', list );
}
Hope it helps
source to share
I found another solution as below, it worked for me!
<insert id="insertServiceMappings" parameterType="java.util.List" useGeneratedKeys="true">
<selectKey keyProperty="id" resultType="java.lang.Long" order="BEFORE">
SELECT TVA_UPSELLADMIN_CHANNEL_SEQ.nextVal as id FROM DUAL
</selectKey>
INSERT INTO
tva_upselladmin_channel (id,source_id, service_id, name)
SELECT TVA_UPSELLADMIN_CHANNEL_SEQ.nextVal, A.* from (
<foreach collection="list" item="channel" index="index" separator="union all">
SELECT
#{channel.sourceId} as source_id,
#{channel.serviceId} as service_id,
#{channel.name}) as name
FROM DUAL
</foreach> ) A
</insert>
source to share