mybatis的批量更新实例
近来批量添加，删除，更新用的比较多，单一的删除和更新，操作无法满足企业某些业务的需求，故通过以下示例分享知识:
今天通过更新的例子来说明
演示环境为jdk8,maven环境,ssm框架
请准备好环境，数据表可直接使用
一、准备数据表
CREATE TABLE `user` ( `user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID', `username` varchar(20) DEFAULT NULL COMMENT '用户名', `sex` varbinary(20) DEFAULT NULL COMMENT '性别', `phone` varchar(20) DEFAULT NULL COMMENT '电话', `password` varchar(20) DEFAULT NULL COMMENT '密码', `level` int(4) DEFAULT NULL COMMENT '等级', `create_time` datetime DEFAULT NULL COMMENT '用户创建时间', `email` varchar(50) DEFAULT NULL COMMENT '邮箱', `logo` int(2) DEFAULT '0' COMMENT '登录标识', PRIMARY KEY (`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
二、准备JavaBean
package cn.blog.entity; import java.util.Date; import java.util.List; /** * 用户实体 * @author youcong * */ public class User { /** 用户ID*/ private Integer userId; /** 用户名*/ private String username; /** 电话*/ private String phone; /** 密码*/ private String password; /** 等级*/ private Integer level; /** 用户创建时间*/ private String createTime; /** 性别*/ private String sex; /** * 邮箱 */ private String email; /** * 登录标识 * @return */ private Integer logo; public Integer getLogo() { return logo; } public void setLogo(Integer logo) { this.logo = logo; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Integer getUserId() { return userId; } public void setUserId(Integer userId) { this.userId = userId; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username == null ? null : username.trim(); } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone == null ? null : phone.trim(); } public String getPassword() { return password; } public void setPassword(String password) { this.password = password == null ? null : password.trim(); } public Integer getLevel() { return level; } public void setLevel(Integer level) { this.level = level; } public String getCreateTime() { return createTime; } public void setCreateTime(String createTime) { this.createTime = createTime; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } }
三、编写对应的Mapper和xml
package cn.blog.mapper; import java.util.List; import org.apache.ibatis.annotations.Param; import cn.blog.entity.User; /** * 用户接口 * @author 挑战者 * */ public interface UserMapper { /** * 批量修改 */ public void udpateUserLogoStatu(@Param ("users") List<User> users); }
<?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="cn.blog.mapper.UserMapper" >
<resultMap id="BaseResultMap" type="User" >
<id column="user_id" property="userId" jdbcType="INTEGER" />
<result column="username" property="username" jdbcType="VARCHAR" />
<result column="phone" property="phone" jdbcType="VARCHAR" />
<result column="password" property="password" jdbcType="VARCHAR" />
<result column="level" property="level" jdbcType="INTEGER" />
<result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
<result column="email" property="email" jdbcType="VARCHAR" />
<result column="logo" property="logo" jdbcType="INTEGER"/>
</resultMap>
<update id="udpateUserLogoStatu" parameterType="java.util.List">
<foreach collection="users" item="user" index="index" separator=";">
update `user`
<set>
logo = 1
</set>
where logo = #{user.logo}
</foreach>
</update>
</mapper>
四、junit单元测试
package cn.blog.test; import java.util.ArrayList; import java.util.List; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import cn.blog.entity.User; import cn.blog.mapper.UserMapper; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration("classpath:application-config.xml") public class BlogTest { @Autowired private UserMapper userMapper; @Test public void testName() throws Exception { int logo[] = new int[] {0}; for (int i = 0; i < logo.length; i++) { User user = new User(); user.setLogo(logo[i]); List<User> users = new ArrayList<User>(); users.add(user); userMapper.udpateUserLogoStatu(users); } } }
