MyBatis入门之批量操作
2017-11-10 本文已影响125人
80b7b6115d87
第一节 前期准备
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子项目
