Using foreach in MyBatis to generate dynamic SQL

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>

コメント

Discover more from 株式会社CONFRAGE ITソリューション事業部

Subscribe now to keep reading and get access to the full archive.

Continue reading

Copied title and URL