foreach标签
foreach标签可以对数组、Map实现Iterable接口。
foreach标签有以下几个属性。
(1)collection:必填,集合、数组或Map的名称。
(2)item:变量名,即从迭代的对象中取出的每一个值。
(3)index:索引的属性名。当迭代的对象为Map时,该值为Map中的Key。
(4)open:循环开头的字符串。
(5)close:循环结束的字符串。
(6)separator:每次循环的分隔符。
collection值的设定与接口方法中的参数有关。
(1)只有一个数组参数或集合参数。默认情况下集合collection=list,数组collection=array。推荐使用@Param来指定参数的名称,例如在参数前添加@Param("ids"),则填写collection=ids。
(2)多参数:多参数请使用@Param来指定,否则SQL中会很不方便。
(3)参数是Map:指定为Map中对应的Key即可。其实@Param最后也转化为Map。
(4)参数是对象:使用属性.属性即可。
在where条件中使用foreach标签可以在where条件中使用foreach标签,如按id集合查询、按id集合删除等。
(1)需求:查询用户id集合中的所有用户信息。
(2)动态SQL。
项目情况截图
数据库,建表
create table z_student(
id int not null auto_increment primary key,
name varchar(20) not null,
phone varchar(20) null,
email varchar(50) null,
sex tinyint(4) null comment '0女,1男',
locked tinyint null comment '状态,0正常,1锁定',
gmt_created datetime default CURRENT_TIMESTAMP comment '存入库的时间',
gmt_modified datetime default CURRENT_TIMESTAMP comment '修改时间',
deletes int(11) null
) comment '学生表'
insert into z_student values(null,'tom','130','xx@xx.com',1,0,null,null,0);
insert into z_student values(null,'tom2','130','xx@xx.com',1,0,null,null,0);
insert into z_student values(null,'tom3','130','xx@xx.com',1,0,null,null,0);
insert into z_student values(null,'tom4','130','xx@xx.com',1,0,null,null,0);
代码如下:
Student.java
package com.shrimpking.code06;
import java.util.Date;
/**
* @author user1
*/
public class Student
{
private int id;
private String name;
private String phone;
private String email;
private int sex;
private int locked;
private Date gmtCreated;
private Date gmtModified;
private int deletes;
public Student()
{
}
public Student(int id, String name, String phone, String email, int sex, int locked, Date gmtCreated, Date gmtModified, int deletes)
{
this.id = id;
this.name = name;
this.phone = phone;
this.email = email;
this.sex = sex;
this.locked = locked;
this.gmtCreated = gmtCreated;
this.gmtModified = gmtModified;
this.deletes = deletes;
}
public int getId()
{
return id;
}
public void setId(int id)
{
this.id = id;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
public String getPhone()
{
return phone;
}
public void setPhone(String phone)
{
this.phone = phone;
}
public String getEmail()
{
return email;
}
public void setEmail(String email)
{
this.email = email;
}
public int getSex()
{
return sex;
}
public void setSex(int sex)
{
this.sex = sex;
}
public int getLocked()
{
return locked;
}
public void setLocked(int locked)
{
this.locked = locked;
}
public Date getGmtCreated()
{
return gmtCreated;
}
public void setGmtCreated(Date gmtCreated)
{
this.gmtCreated = gmtCreated;
}
public Date getGmtModified()
{
return gmtModified;
}
public void setGmtModified(Date gmtModified)
{
this.gmtModified = gmtModified;
}
public int getDeletes()
{
return deletes;
}
public void setDeletes(int deletes)
{
this.deletes = deletes;
}
@Override
public String toString()
{
return "Student{" + "id=" + id + ", name='" + name + '\'' + ", phone='" + phone + '\'' + ", email='" + email + '\'' + ", sex=" + sex + ", locked=" + locked + ", gmtCreated=" + gmtCreated + ", gmtModified=" + gmtModified + ", deletes=" + deletes + '}';
}
}
StudentMapper.java
package com.shrimpking.code06;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface StudentMapper
{
public List<Student> selectByStudentList(List<Integer> ids);
}
StudentMapper.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="com.shrimpking.code06.StudentMapper">
<sql id="base_sql">
id,name,phone,email,sex,locked,gmt_created,gmt_modified
</sql>
<select id="selectByStudentList" resultType="Student">
select
<include refid="base_sql"/>
from z_student
where id in
<foreach collection="list" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
</mapper>
mybatis.xml
<?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>
<!-- 起别名 -->
<typeAliases>
<package name="com.shrimpking.code06"/>
</typeAliases>
<!-- 环境 -->
<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://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimeZone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="mysql123"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/shrimpking/code06/StudentMapper.xml"/>
</mappers>
</configuration>
StudentTest.java
package com.shrimpking.code06;
import com.shrimpking.utils.DaoUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public class StudentTest
{
@Test
public void test() throws IOException
{
SqlSession sqlSession = DaoUtils.getSqlSession("code06/mybatis.xml");
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Integer> list = new ArrayList<>();
list.add(1);
list.add(3);
list.add(5);
List<Student> students = mapper.selectByStudentList(list);
for (Student student : students)
{
System.out.println(student);
}
sqlSession.close();
}
}
DaoUtils.java
package com.shrimpking.utils;
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.io.Reader;
/**
* @author user1
*/
public class DaoUtils
{
private static Reader reader;
private static SqlSessionFactory sqlSessionFactory;
public static SqlSession getSqlSession(String config) throws IOException
{
try
{
reader = Resources.getResourceAsReader(config);
//
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
}
catch (IOException e)
{
e.printStackTrace();
}
finally
{
reader.close();
}
return sqlSessionFactory.openSession();
}
}