mybatis-foreach
1 前言
mybatis中的foreach虽说用了很长时间了，但用的时候 还是会出现问题，所以记录一下。本来自己也都有代码的，不过 (https://blog.****.net/hjh908778/article/details/79034395/) 写的不错也没必要再来一篇，推荐！！！
2 结论
- #{item} 集合中是数字类型，字符串类型，或者其他类型
打印的sql:==> Preparing: select * from user where 1=1 and id in ( ? , ? ) ==> Parameters: 1(Integer), 2(Integer) - ${item} 集合中是数字类型，或者数字类型的字符串（“00121”）,"12opp256"不可以，
打印的sql:==> Preparing: select * from user where 1=1 and id in ( 1 , 2 ) ==> Parameters:
因为这种 会把集合中的元素转成数字类型 - 是非数字类型的字符串可以通过给${item}加上一对单引号
可以这样写
<if test="list !=null">
and name in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
'${item}'
</foreach>
或者
<foreach collection="list" index="index" item="item" open="('" separator="','" close="')">
${item}
</foreach>
</if>
即，给每个元素加上一对''
，当然了，对于字符串类型的集合都该加上（如果用$时），只是因为mysql不区分大小写
and name in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
${item}
</foreach>
Preparing: select * from user where 1=1 and name in ( 1 , 2 ) ；//查询名字为'1','2' 的用户。
- #{devIdList[${index}]} 这种都可以的
打印的sql:==> Preparing: select * from user where 1=1 and id in ( ? , ? ) ==> Parameters: 1(Integer), 2(Integer)
3 代码
解析foreach标签的类 public class ForEachSqlNode implements SqlNode
3.00 配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties>
<property name="dialect" value="MYSQL" />
</properties>
<settings>
<!-- 打印查询语句 -->
<setting name="logImpl" value="STDOUT_LOGGING" />
</settings>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/test_user?useUnicode=true"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/chaoge/dao/userDao-mapping.xml"/>
<mapper resource="com/chaoge/dao/role2.xml"/>
</mappers>
</configuration>
3.0 sql
CREATE TABLE user (
`id` int(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
`deleteFlag` int(1) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8 COLLATE = utf8_bin ROW_FORMAT = Dynamic;
3.1 实体类
public class User {
private int id;
private String name;
private String password;
private int age;
private int deleteFlag;
private Map paramMap;
...
}
3.2 Dao
public interface UserDao {
public User findUserById (int userId);
public List<User> findUserByName ( String userId);
//public List<User> findUserByName (@Param("name") String userId);
public List<User> getByIdList(List<Integer> list);
public List<User> getByNameList(List<String> list);
public List<User> getByMap(Map map);
public List<User> getByIdList2(List<Integer> list);
public List<User> getByNameList2(List<String> list);
public List<User> getByMap2(Map map);
public List<User> getByIdList3(List<Integer> list);
public List<User> getByNameList3(List<String> list);
public List<User> getByMap3(Map map);
public List<User> getByUser1(User user);
public List<User> getByUser2(User user);
public List<User> getByUser3(User user);
}
3.1 调用
package com.chaoge;
import com.chaoge.dao.UserDao;
import com.chaoge.entity.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class Foreach {
//Mybatis 通过SqlSessionFactory获取SqlSession, 然后才能通过SqlSession与数据库进行交互
private static SqlSessionFactory getSessionFactory() {
SqlSessionFactory sessionFactory = null;
String resource = "configuration.xml";
try {
sessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader(resource));
} catch (IOException e) {
e.printStackTrace();
}
return sessionFactory;
}
public static void main(String[] args) throws Exception {
SqlSessionFactory sessionFactory = getSessionFactory();
SqlSession sqlSession = sessionFactory.openSession();
sqlSession.getConnection().setAutoCommit(true);
UserDao userDao = sqlSession.getMapper(UserDao.class);
// There is no getter for property named 'name' in 'class java.lang.String'
// List<User> w = userDao.findUserByName("W");
// System.out.println(w);
List<Integer> list = new ArrayList<>();
list.add(1);
list.add(2);
List<String> list2 = new ArrayList<String>();
list2.add("w");
list2.add("z");
System.out.println("===================================================================================");
//1 List<Integer> List<String> #{item}
try {
List<User> byIdList = userDao.getByIdList(list);
System.out.println(byIdList);
}catch (Exception e){
e.printStackTrace();
}
try {
List<User> byNameList = userDao.getByNameList(list2);
System.out.println(byNameList);
}catch (Exception e){
e.printStackTrace();
}
try {
Map map = new HashMap();
map.put("ids",list);
map.put("names",list2);
List<User> byMapList = userDao.getByMap(map);
System.out.println(byMapList);
}catch (Exception e){
e.printStackTrace();
}
System.out.println("===================================================================================");
//2 List<Integer> List<String> ${item}
try {
List<User> byIdList = userDao.getByIdList2(list);
System.out.println(byIdList);
}catch (Exception e){
e.printStackTrace();
}
try {
List<User> byNameList = userDao.getByNameList2(list2);
System.out.println(byNameList);
}catch (Exception e){
e.printStackTrace();
}
try {
Map map = new HashMap();
map.put("ids",list);
map.put("names",list2);
List<User> byMapList = userDao.getByMap2(map);
System.out.println(byMapList);
}catch (Exception e){
e.printStackTrace();
}
System.out.println("===================================================================================");
//3 map.put("ids",List<Integer>);
// map.put("names",List<String>);
// #{ids[${index}]}
try {
List<User> byIdList = userDao.getByIdList3(list);
System.out.println(byIdList);
}catch (Exception e){
e.printStackTrace();
}
try {
List<User> byNameList = userDao.getByNameList3(list2);
System.out.println(byNameList);
}catch (Exception e){
e.printStackTrace();
}
try {
Map map = new HashMap();
map.put("ids",list);
map.put("names",list2);
List<User> byMapList = userDao.getByMap3(map);
System.out.println(byMapList);
}catch (Exception e){
e.printStackTrace();
}
System.out.println("===================================================================================");
//4 根据实体中的map
// #{ids[${index}]}
try {
User user = new User();
user.setName("W");
Map parammap = new HashMap();
parammap.put("ids",list);
parammap.put("names",list2);
user.setParamMap(parammap);
List<User> byMapList = userDao.getByUser1(user);
System.out.println(byMapList);
}catch (Exception e){
e.printStackTrace();
}
try {
User user = new User();
Map parammap = new HashMap();
user.setName("W");
parammap.put("ids",list);
parammap.put("names",list2);
user.setParamMap(parammap);
List<User> byMapList = userDao.getByUser2(user);
System.out.println(byMapList);
}catch (Exception e){
e.printStackTrace();
}
try {
User user = new User();
user.setName("W");
Map parammap = new HashMap();
parammap.put("ids",list);
parammap.put("names",list2);
user.setParamMap(parammap);
List<User> byMapList = userDao.getByUser3(user);
System.out.println(byMapList);
}catch (Exception e){
e.printStackTrace();
}
}
}
3.1 mapper
<mapper namespace="com.chaoge.dao.UserDao">
<select id="findUserById" resultType="com.chaoge.entity.User" >
select * from user where id = #{id}
</select>
<select id="findUserByName" parameterType="java.lang.String" resultType="com.chaoge.entity.User" >
select * from user where 1=1
<!-- 注意看，是在if test=验证的时候发生的 There is no getter for property named 'name' in 'class java.lang.String'，而并非是and name = #{name} 的时候发生的错误-->
<!-- <if test="name != null">
and name = #{name}
</if> -->
<!-- 用_parameter 代替字符串的入参，-->
<if test="_parameter != null">
and name = #{name}
</if>
<!-- 或者在dao接口中指定。public List<User> findUserByName(@Param("name") String userId) -->
<!-- <if test="name != null">
and name = #{name}
</if>-->
</select>
<!-- ================================= #{item}================================= -->
<select id="getByIdList" parameterType="java.lang.Integer" resultType="com.chaoge.entity.User" >
select * from user where 1=1
<if test="list !=null">
and id in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
</select>
<select id="getByNameList" parameterType="java.lang.String" resultType="com.chaoge.entity.User" >
select * from user where 1=1
<if test="list !=null">
and name in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
</select>
<select id="getByMap" parameterType="Map" resultType="com.chaoge.entity.User" >
select * from user where 1=1
<if test="ids !=null">
and name in
<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="names !=null">
and id in
<foreach collection="names" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
</select>
<!-- ================================= ${item}================================= -->
<select id="getByIdList2" parameterType="java.lang.Integer" resultType="com.chaoge.entity.User" >
select * from user where 1=1
<if test="list !=null">
and id in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
${item}
</foreach>
</if>
</select>
<select id="getByNameList2" parameterType="java.lang.String" resultType="com.chaoge.entity.User" >
select * from user where 1=1
<if test="list !=null">
and name in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
${item}
</foreach>
</if>
</select>
<select id="getByMap2" parameterType="Map" resultType="com.chaoge.entity.User" >
select * from user where 1=1
<if test="ids !=null">
and name in
<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
${item}
</foreach>
</if>
<if test="names !=null">
and id in
<foreach collection="names" index="index" item="item" open="(" separator="," close=")">
${item}
</foreach>
</if>
</select>
<!-- ================================= #{devIdList[${index}]}================================= -->
<select id="getByIdList3" parameterType="java.lang.Integer" resultType="com.chaoge.entity.User" >
select * from user where 1=1
<if test="list !=null">
and id in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{list[${index}]}
</foreach>
</if>
</select>
<select id="getByNameList3" parameterType="java.lang.String" resultType="com.chaoge.entity.User" >
select * from user where 1=1
<if test="list !=null">
and name in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{list[${index}]}
</foreach>
</if>
</select>
<select id="getByMap3" parameterType="Map" resultType="com.chaoge.entity.User" >
select * from user where 1=1
<if test="ids !=null">
and name in
<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
#{ids[${index}]}
</foreach>
</if>
<if test="names !=null">
and id in
<foreach collection="names" index="index" item="item" open="(" separator="," close=")">
#{names[${index}]}
</foreach>
</if>
</select>
<!-- ================================= user.map #{item} #{item} #{devIdList[${index}]}================================= -->
<select id="getByUser1" parameterType="java.lang.Integer" resultType="com.chaoge.entity.User" >
select * from user where 1=1
<if test="name != null">
and name = #{name}
</if>
<if test="paramMap.ids !=null">
and id in
<foreach collection="paramMap.ids" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
<if test="paramMap.names !=null">
and name in
<foreach collection="paramMap.names" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</if>
</select>
<select id="getByUser2" parameterType="java.lang.String" resultType="com.chaoge.entity.User" >
select * from user where 1=1
<if test="name != null">
and name = #{name}
</if>
<if test="paramMap.ids !=null">
and name in
<foreach collection="paramMap.ids" index="index" item="item" open="(" separator="," close=")">
${item}
</foreach>
</if>
<if test="paramMap.names !=null">
and name in
<foreach collection="paramMap.names" index="index" item="item" open="(" separator="," close=")">
${item}
</foreach>
</if>
</select>
<select id="getByUser3" parameterType="com.chaoge.entity.User" resultType="com.chaoge.entity.User" >
select * from user where 1=1
<if test="name != null">
and name = #{name}
</if>
<if test="paramMap.ids !=null">
and id in
<foreach collection="paramMap.ids" index="index" item="item" open="(" separator="," close=")">
#{paramMap.ids[${index}]}
</foreach>
</if>
<if test="paramMap.names !=null">
and name in
<foreach collection="paramMap.names" index="index" item="item" open="(" separator="," close=")">
#{paramMap.names[${index}]}
</foreach>
</if>
</select>
</mapper>