一,MyBatis框架介绍
MyBatis是支持定制化SQL,存储过程以及高级映射的优秀的持久层框架。MyBatis避免了几乎所有的JDBC代码和手动设置参数以及获取结果集。MyBatis可以对配置和原生Map使用简单的XML或注解,将接口和Java的POJOS(Plian Old Java Objects,普通的Java对象)映射成数据库中的记录。
二,文章介绍
本文主要基于MyBatis框架的xml进行数据的批量插入操作,批量查询操作之所以会写这篇文章,主要还是觉得xml文件对动态sql的编写方面很方便。
三,项目所需要的jar信息
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.2</version> </dependency>
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> <exclusions> <exclusion> <groupId>org.junit.vintage</groupId> <artifactId>junit-vintage-engine</artifactId> </exclusion> </exclusions> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.68</version> </dependency> <!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter --> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.2.12</version> </dependency>
四,项目的基础类信息
package com.wpw.mybatisbatchoperationxml.entity;
import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;import lombok.experimental.Accessors;
import java.io.Serializable;import java.util.List;import java.util.Map;
/** * @author wpw */@AllArgsConstructor@NoArgsConstructor@Data@Builder@Accessors(chain = true)public class User implements Serializable { private Integer id; private String userName; private String passWord; private Integer age; private List<Integer> idList; private Integer pageNumber; private Integer pageSize; private Integer[] ids;}
五,前端控制器UserController
package com.wpw.mybatisbatchoperationxml.controller;
import com.alibaba.fastjson.JSONObject;import com.github.pagehelper.PageHelper;import com.wpw.mybatisbatchoperationxml.entity.User;import com.wpw.mybatisbatchoperationxml.mapper.UserMapper;import org.springframework.util.CollectionUtils;import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.RequestBody;import org.springframework.web.bind.annotation.RestController;
import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.stream.Collectors;import java.util.stream.Stream;
/** * @author wpw */@RestControllerpublic class UserController { private final UserMapper userMapper;
public UserController(UserMapper userMapper) { this.userMapper = userMapper; }
@PostMapping(value = "/batchInsert") public int batchInsert(@RequestBody JSONObject jsonObject) { List<User> userList = jsonObject.getJSONArray("userList").toJavaList(User.class); return userMapper.batchInsert(userList); }
@PostMapping(value = "/batchList") public List<User> batchList(@RequestBody User user) { return userMapper.batchList(user.getUserName(), user.getPassWord(), user.getIdList()); }
@PostMapping(value = "/list-pagination") public List<User> listPagination(@RequestBody User user) { PageHelper.startPage(user.getPageNumber(), user.getPageSize()); return userMapper.listPagination(user.getUserName(), user.getPassWord()); }
@PostMapping(value = "/listByArray") public List<User> batchListBaseArray(@RequestBody User user) { PageHelper.startPage(user.getPageNumber(), user.getPageSize()); return userMapper.batchListBaseArray(user.getIds()); }
@PostMapping(value = "/listByMap") public List<User> batchListBaseMap(@RequestBody(required = false) Map<String, Integer[]> map) { List<User> userList = preCheck(map); if (!CollectionUtils.isEmpty(userList)) { return userList; } HashMap<String, List<Integer>> hashMap = new HashMap<>(16, 0.75f); List<Integer> ids = Stream.of(map.get("ids")).collect(Collectors.toList()); hashMap.put("ids", ids); return userMapper.batchListBaseMap(hashMap); }
private List<User> preCheck(Map<String, Integer[]> map) { /** * 若获取的参数为null,则直接全表去查,默认第一页,pageNumber=1,pageSize=10 */ if (map == null) { PageHelper.startPage(1, 10); return userMapper.batchListBaseEmptyArgument(); } return null; }}
六,操作数据库的UserMapper代码
package com.wpw.mybatisbatchoperationxml.mapper;
import com.wpw.mybatisbatchoperationxml.entity.User;import org.apache.ibatis.annotations.Mapper;import org.springframework.stereotype.Repository;
import java.util.List;
/** * @author wpw */@Mapper@Repositorypublic interface UserMapper { /** * 批量插入用户信息 * * @param userList 用户信息 * @return 是否插入成功 */ int batchInsert(List<User> userList);
/** * 根据查询条件进行数据的查询 * * @param userName 用户名称 * @param passWord 用户密码 * @param idList 用户id集合信息 * @return 用户列表信息 */ List<User> batchList(String userName, String passWord, List<Integer> idList);
/** * 根据用户名和密码进行用户信息的查询 * * @param userName 用户名 * @param passWord 密码 * @return 用户列表信息 */ List<User> listPagination(String userName, String passWord);
/** * 根据数组进行用户列表数据的查询 * * @param ids 数组 * @return 用户数据信息 */ List<User> batchListBaseArray(Integer[] ids);
/** * 查询符合条件的用户信息 * * @param ids map参数信息 * @return 用户列表信息 */ List<User> batchListBaseMap(Object ids);
/** * 获取用户列表数据 * @return 用户列表数据 */ List<User> batchListBaseEmptyArgument();}
七,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,namespace的值习惯上设置成包名+sql映射文件名,这样就能够保证namespace的值是唯一的 --><mapper namespace="com.wpw.mybatisbatchoperationxml.mapper.UserMapper"> <insert id="batchInsert" parameterType="list"> <selectKey resultType="Integer" keyProperty="id" order="AFTER"> select last_insert_id(); </selectKey> insert into user(username,password,age) values <foreach collection="list" item="user" index="index" separator=","> (#{user.userName}, #{user.passWord}, #{user.age}) </foreach> </insert> <select id="batchList" resultMap="map"> select * from user where id in <foreach collection="idList" item="item" index="index" open="(" separator="," close=")"> #{item} </foreach> <if test="userName!=null"> and username=#{userName} </if> <if test="passWord!=null"> and password=#{passWord} </if> </select> <resultMap id="map" type="com.wpw.mybatisbatchoperationxml.entity.User"> <id property="id" column="id"/> <result property="userName" column="username"/> <result property="passWord" column="password"/> <result property="age" column="age"/> </resultMap> <select id="listPagination" resultMap="map"> select * from user <where> <if test="userName!=null"> username=#{userName} </if> <if test="passWord!=null"> and password=#{passWord} </if> </where> </select> <select id="batchListBaseArray" resultMap="map"> select * from user where id in <foreach collection="array" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> </select> <select id="batchListBaseMap" parameterType="java.util.HashMap" resultMap="map"> select * from user where id in <foreach collection="ids" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> </select> <select id="batchListBaseEmptyArgument" resultMap="map"> select * from user </select></mapper>
八,数据库配置文件信息
server: port: 8080spring: application: name: mybatis-batch-operation-xml datasource: url: jdbc:mysql://localhost:3306/study?serverTimezone=UTC username: root password: root driver-class-name: com.mysql.cj.jdbc.Drivermybatis: mapper-locations: classpath:/mapper/**.xmlpagehelper: reasonable: true
九,由于这里面用到了分页查询的实现,所以整合了pageHelper的jar和配置信息。
十,项目结构图
十一,项目的代码地址
github:https://github.com/myownmyway/mybatis-batch-operation-xml.git
十二,上面的sql文件
create database if not exists study;use study;select database();create table if not exists `user`( id int(11) auto_increment primary key comment "主键id", username varchar(255) comment '姓名', password varchar(255) comment '密码', age int(3) comment '年纪') engine = InnoDb charset = utf8;insert into user(username,password,age) values("zhangsan","123456",1);insert into user(username,password,age) values("lisi","123456",2);insert into user(username,password,age) values("wangwu","123456",3);insert into user(username,password,age) values("zhaoliu","123456",4);insert into user(username,password,age) values("dengchao","123456",5);insert into user(username,password,age) values("sunli","123456",6);insert into user(username,password,age) values("huangxiaoming","123456",7);insert into user(username,password,age) values("zhaowei","123456",8);insert into user(username,password,age) values("haungbo","123456",9);insert into user(username,password,age) values("shenteng","123456",10);insert into user(username,password,age) values("gaoyuanyuan","123456",11);insert into user(username,password,age) values("wanghan","123456",12);
整个项目的搭建和操作过程就结束了,这里采用的是postman进行测试的,这里截下图看下post传参的方式。
到这里就结束了,最后接下map接收参数的截图