トッカンソフトウェア

SpringFrameworkでMyBatisを使ってみる⑦(Javaアプリケーション) foreach

• foreach list
• foreach map
• foreach Param指定
• 属性にMap、Listを指定

foreach list

sql.xml

				
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="spring.test.UserMapper">
	<select id="getUserList" resultType="spring.test.User">
		select id,name from m_user
		<where>
			<foreach item="item" collection="list" open="("
			separator=" or " close=")">
				(
				id = #{item.id}
				and name = #{item.name}
				)
			</foreach>
		</where>
	</select>
</mapper>


			

UserMapper.java

				
package spring.test;

import java.util.List;

public interface UserMapper {

	public List<User> getUserList(List<User> ary);

}


			

SpringBean.java

				
package spring.test;

import java.util.ArrayList;
import java.util.List;

public class SpringBean {

	private final UserMapper userMapper;

	public SpringBean(UserMapper mp) {
		this.userMapper = mp;
	}

	public void show() {
		List<User> args = new ArrayList<>();

		User arg1 = new User();
		arg1.setId("test");
		arg1.setName("テスト");
		args.add(arg1);
		User arg2 = new User();
		arg2.setId("test2");
		arg2.setName("テスト２");
		args.add(arg2);

		List<User> list = userMapper.getUserList(args);
		for (User user : list) {
			System.out.println(user.getId() + "-" + user.getName());
		}
	}
}

			

				
select
  id, name 
from
  m_user 
WHERE
  ( 
    (id = 'test' and name = 'テスト') 
    or (id = 'test2' and name = 'テスト２')
  )


			

foreach Param指定

sql.xml

				
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="spring.test.UserMapper">
	<select id="getUserList" resultType="spring.test.User">
		select id,name from m_user
		<where>
			<foreach item="item" collection="prm" open="("
			separator=" or " close=")">
				(
				id = #{item.id}
				and name = #{item.name}
				)
			</foreach>
		</where>
	</select>
</mapper>


			

UserMapper.java

				
package spring.test;

import java.util.List;

import org.apache.ibatis.annotations.Param;

public interface UserMapper {

	public List<User> getUserList(@Param("prm") List<User> ary);

}


			

foreach map

sql.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="spring.test.UserMapper">
	<select id="getUserList" resultType="spring.test.User">
		select id,name from m_user
		<where>
			<foreach item="value" index="key" collection="_parameter"
				open="(" separator=" or " close=")">
				(
				id = #{key}
				and name = #{value}
				)
			</foreach>
		</where>
	</select>
</mapper>

UserMapper.java

package spring.test;

import java.util.List;
import java.util.Map;

public interface UserMapper {

	public List<User> getUserList(Map<String, String> argMap);

}

SpringBean.java

package spring.test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SpringBean {

	private final UserMapper userMapper;

	public SpringBean(UserMapper mp) {
		this.userMapper = mp;
	}

	public void show() {

		Map<String, String> args = new HashMap<>();

		args.put("test", "テスト");
		args.put("test2", "テスト２");

		List<User> list = userMapper.getUserList(args);
		for (User user : list) {
			System.out.println(user.getId() + "-" + user.getName());
		}
	}
}

属性にMap、Listを指定

sql.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="spring.test.UserMapper">

	<update id="updateMapList" parameterType="spring.test.MapList">
		UPDATE M_USER SET
		<foreach item="value" index="key" collection="testMap"
			separator=",">
			<if test="value != null">
				${key} = #{value}
			</if>
		</foreach>
		<where>
			<if test="testList != null and testList.size() > 0">
				ID IN
				<foreach item="item" index="index" collection="testList"
					open="(" separator="," close=")">
					#{item}
				</foreach>
			</if>
		</where>
	</update>
</mapper>

MapList.java

package spring.test;

import java.util.List;
import java.util.Map;

public class MapList {

	private List<String> testList;
	private Map<String, String> testMap;

	public List<String> getTestList() {
		return testList;
	}

	public void setTestList(List<String> testList) {
		this.testList = testList;
	}

	public Map<String, String> getTestMap() {
		return testMap;
	}

	public void setTestMap(Map<String, String> testMap) {
		this.testMap = testMap;
	}
}

UserMapper.java

package spring.test;

public interface UserMapper {

	public void updateMapList(MapList mapList);

}

SpringBean.java

package spring.test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SpringBean {

	private final UserMapper userMapper;

	public SpringBean(UserMapper mp) {
		this.userMapper = mp;
	}

	public void show() {

		MapList mapList = new MapList();

		Map<String, String> map = new HashMap<String, String>();
		List<String> ids = new ArrayList<String>();

		map.put("attr1", "a");
		map.put("attr2", "b");

		ids.add("1");
		ids.add("2");

		mapList.setTestMap(map);
		mapList.setTestList(ids);

		userMapper.updateMapList(mapList);
	}
}

UPDATE M_USER SET attr1 = 'a', attr2 = 'b' WHERE ID IN ('1', '2')