Using foreach in MyBatis to generate dynamic SQL
You can use foreach in MyBatis to generate dynamic SQL. It is often used in IN clauses.
<select id="selsectA" parameterType="java.util.List" resultType="String"> SELECT ITEM_NAME FROM TBL WHERE ITEM_CD in <foreach item="item" open="(" close=")" collection="dataList" separator=","> #{item} </foreach> </select>
Since this is a foreach, it loops through the dataList as it is. In this case, the following SQL code is generated because “(” is used for open, “)” for close, and “,” is used as the delimiter for each list item.
('01','02','03')
If it is a batch, etc., it can be used with bulk inserts. The following is an example confirmed with DB2.
<insert id="insertA" parameterType="java.util.List"> INSERTT INTO table name (COL1,COL2) VALUES <foreach item="item" collection="list" open="" close="" separator=","> ( #{item.id, jdbcType=INTEGER}, #{item.name, jdbcType=VARCHAR} ) </foreach> </insert>
List, but for classes that inherit from java.util.
(For arrays (even if the instance name is not a list), use collection=”array”.
Note that the base value of index is 0.
If you pass a bean instead of a list as an argument, the getter method will be called on its own.
<select id="SQLID" parameterType="jp.co.confrage.SampleBean" resultType="String"> SELECT ID FROM ${schema}.TBL WHERE YYYY <![CDATA[<=]]> #{yyyy} </select>
The getSchema and getYyyy methods are executed. The getter was also executed when a List filled with beans was passed by parameterType.
List<Bean> list = new ArrayList<Bean>();
The getter of the superclass property of the bean was also executed. This is the case for the following bean configuration.
public class Bean extends SuperBean { … …
However, if the property is a static member class and I try to use it in a foreach, I get the following error.
Caused by: org.apache.ibatis.builder.BuilderException: Error evaluating expression 'user.innerClass'. Cause: org.apache.ibatis.ognl.NoSuchPropertyException: jp.co.confrage.SampleBean.innerClass
Does it mean that there is no such property? This is when you try to use the following. (I’ve broken it down quite a bit)
<foreach item="user" collection="list" separator=","> ${user.innerClass} </foreach>
I thought that static member classes could not be retrieved, but that was not the case. The reason was that getter was not written correctly in camelCase, so it did not work. The reason was the method name getDBSchma. When I correctly set it to getDbSchema, getter was called. So, the following description method is also possible.
<foreach item="user" collection="list" separator=","> ${user.dbSchema.property} </foreach>
コメント