通过foreach标签实现批量插入
可以通过foreach标签来实现批量插入。
(1)动态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.code07;
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.code07;
import java.util.List;
public interface StudentMapper
{
public int insertList(List<Student> students);
}
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.code07.StudentMapper">
<insert id="insertList">
insert into z_student(name,phone,email,sex,locked)
values
<foreach collection="list" item="student" separator=",">
(
#{student.name},#{student.phone},#{student.email},
#{student.sex},#{student.locked}
)
</foreach>
</insert>
</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.code07"/>
</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/code07/StudentMapper.xml"/>
</mappers>
</configuration>
StudentTest.java
package com.shrimpking.code07;
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("code07/mybatis.xml");
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = new ArrayList<>();
Student stu1 = new Student();
stu1.setName("批量1");
stu1.setPhone("133");
stu1.setEmail("qq@qq.com");
stu1.setSex(1);
stu1.setLocked(0);
students.add(stu1);
Student stu2 = new Student(0,"批量2","133","qq@qq.com",1,0,null,null,0);
students.add(stu2);
int i = mapper.insertList(students);
if(i > 0)
{
System.out.println("批量插入成功");
}
sqlSession.commit();
sqlSession.close();
}
}
运行截图