MyBatis入门之批量操作

第一节  前期准备

0.MyBatis框架+Spring框架+Druid框架

1.创建mybatis-demo2项目

2.添加依赖

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.5</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.6</version>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.4</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>4.3.12.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>4.3.12.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.3.12.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-expression</artifactId>
    <version>4.3.12.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-test</artifactId>
    <version>4.3.12.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>1.3.1</version>
</dependency>
<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-core</artifactId>
    <version>1.1.7</version>
</dependency>
<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>1.1.7</version>
</dependency>

3.数据库sql和上一节MyBatis入门之HelloWorld相同，如下

CREATE DATABASE mybatis_demo default character set utf8;

CREATE TABLE user(
 id INT(11) PRIMARY KEY AUTO_INCREMENT,
 username VARCHAR(100) NOT NULL COMMENT '用户名',
 email VARCHAR(100) COMMENT '邮件地址',
 age INT(11) COMMENT '性别',
 gender tinyint(1) COMMENT '性别',
 create_time timestamp NULL DEFAULT NULL COMMENT '创建时间',
 update_time timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
)ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 COMMENT='用户表';

4.创建包路径

cn.im.domain   存放映射数据库实体类
cn.im.mapper   存放映射数据库操作接口

 

****准备工作结束****

 

第二节  代码编写

1.创建映射数据库实体类User

package cn.im.domain;
import java.util.Date;
/**
 * Created by mybatis-demo
 * Author: hushuang
 * Date: 2017/11/9
 * Time: 上午10:11
 * Email: hd1611756908@163.com
 * Description :创建映射数据库的实体对象
 */
public class User {
    //用户ID
    private Integer id;
    //用户名称
    private String username;
    //用户邮箱
    private String email;
    //用户年龄
    private int age;
    //用户性别
    private int gender;
    //创建时间
    private Date createTime;
    //更新时间
    private Date updateTime;
    public User() {
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public int getGender() {
        return gender;
    }
    public void setGender(int gender) {
        this.gender = gender;
    }
    public Date getCreateTime() {
        return createTime;
    }
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
    public Date getUpdateTime() {
        return updateTime;
    }
    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", email='" + email + '\'' +
                ", age=" + age +
                ", gender=" + gender +
                ", createTime=" + createTime +
                ", updateTime=" + updateTime +
                '}';
    }
}

2.创建操作映射数据库的接口类

package cn.im.mapper;
import cn.im.domain.User;
import java.util.List;
/**
 * Created by mybatis-demo
 * Author: hushuang
 * Date: 2017/11/9
 * Time: 下午1:48
 * Email: hd1611756908@163.com
 * Description : 测试各种批量操作
 */
public interface UserMapper {
    /**
     * 批量插入用户
     * @param users
     * @return
     * @throws Exception
     */
    int batchAddUsers(List<User> users) throws Exception;
    /**
     * 批量更新用户信息
     * @param users
     * @return
     * @throws Exception
     */
    int batchUpdateUsers(List<User> users) throws Exception;
    /**
     * 根据ID批量查询用户信息
     * @param ids
     * @return
     * @throws Exception
     */
    List<User> batchFindUsers(List<Integer> ids) throws Exception;
    /**
     * 批量删除用户信息
     * @param ids
     * @return
     * @throws Exception
     */
    int batchDeleteUsersByIds(List<Integer> ids) throws Exception;
}

3.在resources文件夹下创建mapper文件夹用于存放映射数据库接口的xml文件UserMapper.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="cn.im.mapper.UserMapper">


    <!-- 批量插入 -->
    <insert id="batchAddUsers" parameterType="user" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO user(username,email,age,gender,create_time,update_time) VALUES
        <foreach collection="list" separator="," item="item">
            (#{item.username},#{item.email},#{item.age},#{item.gender},#{item.createTime},#{item.updateTime})
        </foreach>
    </insert>

    <!-- 批量更新 -->
    <update id="batchUpdateUsers" parameterType="user">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            UPDATE user 
            <set>
                <if test="item.username!=null">username=#{item.username},</if>
                <if test="item.email!=null">email=#{item.email},</if>
                <if test="item.gender!=0">gender=#{item.gender},</if>
                <if test="item.age!=0">age=#{item.age},</if>
                <if test="item.updateTime==null">update_time=now()</if>
            </set>
            WHERE id=#{item.id}
        </foreach>
    </update>

    <!-- 批量查询 -->
    <select id="batchFindUsers" resultType="user">
        SELECT id,username,email,age,gender,create_time as createTime,update_time as updateTime FROM user WHERE id IN 
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

    <!-- 批量删除 -->
    <delete id="batchDeleteUsersByIds">
        DELETE FROM user WHERE id IN 
        <foreach collection="list" item="item" open="(" separator="," close=")">
          #{item}
        </foreach>
    </delete>
</mapper>

4.在resources文件夹下创建applicationContext.xml配置文件用于整合Druid,MyBatis等

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
                           http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
                           http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">


    <!-- 导入数据库连接池配置的属性文件 -->
    <context:property-placeholder location="classpath:db.properties" />

    <!-- 数据库连接池设置
         设置参考链接官网：https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98
         下面的《9. Druid有没有参考配置》
         1.配置数据源Druid
     -->

    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
          init-method="init" destroy-method="close">
        <!-- 基本属性 url、user、password -->
        <property name="url" value="${jdbc.url}" />
        <property name="username" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />

        <!-- 配置初始化大小、最小、最大 -->
        <property name="initialSize" value="1" />
        <property name="minIdle" value="1" />
        <property name="maxActive" value="20" />

        <!-- 配置获取连接等待超时的时间 -->
        <property name="maxWait" value="60000" />

        <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis" value="60000" />

        <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis" value="300000" />

        <property name="validationQuery" value="SELECT 'x'" />
        <property name="testWhileIdle" value="true" />
        <property name="testOnBorrow" value="false" />
        <property name="testOnReturn" value="false" />

        <!-- 打开PSCache，并且指定每个连接上PSCache的大小 -->
        <property name="poolPreparedStatements" value="false" />
        <property name="maxPoolPreparedStatementPerConnectionSize" value="20" />
        <!-- 配置监控统计拦截的filters -->
        <property name="filters" value="stat" />
    </bean>
    <!--
        2.注册sqlsessionFactory
        俗话说为什么这么配置，我是怎么知道这么配置的：追本溯源当然是mybatis-spring这个依赖提供的整合方式
        中文官网地址：http://www.mybatis.org/spring/zh/index.html    官网介绍了下面的 2 3 4 步，都在官网中。
    -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!-- 装配druid数据源 -->
        <property name="dataSource" ref="dataSource"></property>
        <!-- 导入mybatis全局配置文件 -->
        <property name="configLocation" value="classpath:mybatis-config.xml"></property>
        <!-- 扫描entity包，使用别名 -->
        <property name="typeAliasesPackage" value="cn.im.domain"></property>
        <!-- 配置sql映射文件位置 -->
        <property name="mapperLocations" value="classpath:mapper/*.xml"></property>
    </bean>
    <!-- 3.注册dao接口，动态实现dao接口配置，注入到spring容器中 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!-- 配置sqlSessionFactory -->
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
        <!-- 扫描mapper接口包，将里面的接口全部加入到IOC容器中 -->
        <property name="basePackage" value="cn.im.mapper"></property>
    </bean>
    <!-- 4.事务管理 -->
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>
    <!-- 5.开启基于注解的声明式事物 -->
    <tx:annotation-driven transaction-manager="transactionManager"/>
</beans>

5.在resources文件夹下创建db.properties文件用于保存数据库的基本配置信息

#数据库基本配置
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/mybatis_demo?useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowMultiQueries=true
jdbc.username=root
jdbc.password=root

6.在resources文件夹下创建mybatis-config.xml配置文件，用于保存myabtis的基本配置信息

<?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>
    <!-- mybatis的配置 -->

   
</configuration>

7.项目结构如下图

image.png

第三节  测试代码UserTest

package cn.im;

import cn.im.domain.User;
import cn.im.mapper.UserMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by mybatis-demo
 * Author: hushuang
 * Date: 2017/11/9
 * Time: 下午1:46
 * Email: hd1611756908@163.com
 * Description : 使用Spring框架提供的测试工具进行测试
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:applicationContext.xml"})
public class UserTest {

    private static final Logger LOG = LoggerFactory.getLogger(UserTest.class);

    @Autowired
    private UserMapper userMapper;


    /**
     * 批量删除
     */
    @Test
    public void testBatchDeleteUsersByIds(){
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1003);
        ids.add(1004);
        try {
            int i = userMapper.batchDeleteUsersByIds(ids);
            LOG.info("影响行数i={}",i);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 批量查询
     */
    @Test
    public void testBatchFindUsers(){
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1000);
        ids.add(1001);
        ids.add(1002);
        try {
            List<User> users = userMapper.batchFindUsers(ids);
            LOG.info("用户数量count={},用户列表users={}",users.size(),users);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 批量添加
     */
    @Test
    public void testBatchAddUsers(){

        List<User> users = new ArrayList<User>();
        User user1 = new User();
        user1.setUsername("polly");
        user1.setEmail("polly@163.com");
        user1.setAge(11);
        user1.setGender(1);
        user1.setCreateTime(new Date());
        user1.setUpdateTime(new Date());
        users.add(user1);

        User user2 = new User();
        user2.setUsername("lucy");
        user2.setEmail("lucy@163.com");
        user2.setAge(13);
        user2.setGender(2);
        user2.setCreateTime(new Date());
        user2.setUpdateTime(new Date());
        users.add(user2);

        try {
            int i = userMapper.batchAddUsers(users);
            LOG.info("影响行数i={}",i);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 批量更新用户信息
     */
    @Test
    public void testBatchUpdateUsers(){


        List<User> users = new ArrayList<User>();
        User user1 = new User();
        user1.setId(1003);
        user1.setUsername("小明");
        user1.setEmail("xaoming@163.com");
        user1.setAge(17);
        user1.setGender(1);
        user1.setCreateTime(new Date());
        user1.setUpdateTime(new Date());
        users.add(user1);

        User user2 = new User();
        user2.setId(1004);
        user2.setUsername("老王");
        user2.setEmail("laowang@163.com");
        user2.setAge(80);
        user2.setGender(1);
        user2.setCreateTime(new Date());
        user2.setUpdateTime(new Date());
        users.add(user2);

        try {
            int i = userMapper.batchUpdateUsers(users);
            LOG.info("影响行数i={}",i);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

**以上为个人总结MyBatis的批量操作的测试，如果有什么疑问可以下方评论进行交流，如果有什么错误欢迎指出。

代码已经上传到github

git clone https://github.com/hd1611756908/mybatis-demo.git

下面的mybatis-demo2子项目