专栏首页后端Coderjava进阶|MyBatis系列文章(五)注解版批量操作的增删改查

java进阶|MyBatis系列文章(五)注解版批量操作的增删改查

一,Mybatis框架介绍

MyBatis是支持定制化SQL,存储过程以及高级映射的优秀的持久层框架。MyBatis避免了几乎所有的JDBC代码和手动设置参数以及获取结果集。MyBatis可以对配置和原生Map使用简单的XML或注解,将接口和Java的POJOS(Plian Old Java Objects,普通的Java对象)映射成数据库中的记录。

二,文章介绍

1,这篇文章主要基于MyBatis注解的形式进行单表项目的增删改查操作2,常用的注解为@Insert@Delete@Update@Select3,使用了MyBatis的高级特性Provider进行数据的批量操作,不过这里也只是用了起来,很简单,但是这里建议使用xml的形式进行操作,Provider形式拼接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>        <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->        <dependency>            <groupId>com.alibaba</groupId>            <artifactId>fastjson</artifactId>            <version>1.2.68</version>        </dependency>

四,项目连接数据库的配置信息

server:  port: 8080spring:  application:    name: mybatis-batch-operation  datasource:    url: jdbc:mysql://localhost:3306/study?serverTimezone=UTC    username: root    password: root    driver-class-name: com.mysql.cj.jdbc.Driver

五,涉及的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);

六,由于这里都是基于自己的测试操作,写法上不满足正规的流程,比如说这里直接在controller层调用了userMapper的方法操作数据库,正常的应该还有一个userService接口的操作,这里简单了一些。

七,先看下UserController接口的代码

package com.wpw.mybatisbatchoperation.controller;
import com.alibaba.fastjson.JSON;import com.alibaba.fastjson.JSONArray;import com.alibaba.fastjson.JSONObject;import com.wpw.mybatisbatchoperation.entity.User;import com.wpw.mybatisbatchoperation.mapper.UserMapper;import org.springframework.web.bind.annotation.*;
import java.util.ArrayList;import java.util.Arrays;import java.util.List;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;    }
    @GetMapping(value = "/select")    public User selectOne(@RequestParam(value = "userName") String userName, @RequestParam(value = "passWord") String passWord) {        return userMapper.selectOne(userName, passWord);    }
    @DeleteMapping(value = "/delete")    public boolean delete(@RequestParam(value = "id") Integer id) {        return userMapper.delete(id);    }
    @PutMapping(value = "/update")    public boolean update(@RequestBody User user) {        return userMapper.update(user);    }
    @PostMapping(value = "/insert")    public int insert(@RequestBody User user) {        return userMapper.insert(user);    }
    @GetMapping(value = "/selectList")    public List<User> selectList(@RequestParam(value = "userName") String userName) {        return userMapper.selectList(userName);    }
    @PostMapping(value = "/batchInsert")    public int batchInsert(@RequestBody JSONObject jsonObject) {        return userMapper.batchInsert(jsonObject.getJSONArray("userList").toJavaList(User.class));    }
    @DeleteMapping(value = "/batchDelete")    public boolean batchDelete(@RequestBody JSONObject jsonObject) {        return userMapper.batchDelete(jsonObject.getJSONArray("userList").toJavaList(User.class));    }
    @PostMapping(value = "/batchList")    public List<User> batchList(@RequestBody JSONObject jsonObject) {        return userMapper.batchList(jsonObject.getJSONArray("userList").toJavaList(User.class));    }
    @PutMapping(value = "/batchUpdate")    public boolean batchUpdate(@RequestBody JSONObject jsonObject) {        return userMapper.batchUpdate(jsonObject.getJSONArray("userList").toJavaList(User.class));    }}

八,UserMapper接口操作数据的代码如下:

package com.wpw.mybatisbatchoperation.mapper;
import com.wpw.mybatisbatchoperation.entity.User;import org.apache.ibatis.annotations.*;import org.springframework.stereotype.Repository;
import java.text.MessageFormat;import java.util.List;
/** * @author wpw */@Mapper@Repositorypublic interface UserMapper {    /**     * 保存用户信息     *     * @param user 用户信息     * @return 自增主键     */    @Insert(value = "insert into user(username,password,age) values(#{userName},#{passWord},#{age})")    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")    int insert(User user);
    /**     * 根据用户id删除用户信息     *     * @param id 用户id     * @return 是否删除成功     */    @Delete(value = "delete from user where id=#{id}")    boolean delete(Integer id);
    /**     * 根据用户id进行用户信息的更新     *     * @param user 用户信息     * @return 是否更新成功     */    @Update(value = "update user set username=#{userName},password=#{passWord} where id=#{id}")    boolean update(User user);
    /**     * 根据用户名和密码进行查找     *     * @param userName 用户名     * @param passWord 密码     * @return 用户信息     */    @Results(value = {            @Result(property = "id", column = "id"),            @Result(property = "userName", column = "username"),            @Result(property = "passWord", column = "password"),            @Result(property = "age", column = "age")    })    @Select(value = "select * from user where username=#{userName} and password=#{passWord}")    User selectOne(String userName, String passWord);
    /**     * 根据用户名称查询用户列表信息     *     * @param userName 用户名称     * @return 用户列表     */    @Results(value = {            @Result(property = "id", column = "id"),            @Result(property = "userName", column = "username"),            @Result(property = "passWord", column = "password"),            @Result(property = "age", column = "age")    })    @Select(value = "select * from user where username like #{userName}")    List<User> selectList(String userName);
    /**     * 批量增加数据     *     * @param userList 用户列表信息     * @return 是否保存成功     */    @InsertProvider(type = UserSqlProvider.class, method = "batchInsert")    int batchInsert(@Param(value = "userList") List<User> userList);
    /**     * 批量删除用户信息     *     * @param userList 用户列表信息     * @return 是否删除成功     */    @DeleteProvider(type = UserSqlProvider.class, method = "batchDelete")    boolean batchDelete(@Param(value = "userList") List<User> userList);
    /**     * 批量更新     *     * @param userList 用户列表信息     * @return 是否更新成功     */    @UpdateProvider(type = UserSqlProvider.class, method = "batchUpdate")    boolean batchUpdate(@Param(value = "userList") List<User> userList);
    /**     * 批量查询     *     * @param userList 用户列表     * @return 用户列表信息     */    @Results(value = {            @Result(property = "id", column = "id"),            @Result(property = "userName", column = "username"),            @Result(property = "passWord", column = "password"),            @Result(property = "age", column = "age")    })    @SelectProvider(type = UserSqlProvider.class, method = "batchList")    List<User> batchList(@Param(value="userList") List<User> userList);
    class UserSqlProvider {        /**         * 批量增加         *         * @param userList 用户列表         * @return str字符串         */        public String batchInsert(@Param(value = "userList") List<User> userList) {            StringBuilder stringBuilder = new StringBuilder();            stringBuilder.append("insert into user(username,password,age)values");            String message = "(''{0}'',''{1}'',{2})";            int i = 1;            for (User u : userList) {                String format = MessageFormat.format(message, u.getUserName(), u.getPassWord(), u.getAge());                stringBuilder.append(format);                if (i == userList.size()) {                    break;                }                stringBuilder.append(",");                i++;            }            return stringBuilder.toString();        }
        /**         * 批量删除         *         * @param userList 用户列表信息         * @return str字符串         */        public String batchDelete(@Param(value = "userList") List<User> userList) {            StringBuilder stringBuilder = new StringBuilder();            stringBuilder.append("delete from user where id in(");            int i = 1;            for (User u : userList            ) {                stringBuilder.append(u.getId());                if (i == userList.size()) {                    break;                }                stringBuilder.append(",");                i++;            }            stringBuilder.append(")");            return stringBuilder.toString();        }
        /**         * 批量更新         *         * @param userList 用户列表         * @return str字符串         */        public String batchUpdate(@Param(value = "userList") List<User> userList) {            StringBuilder stringBuilder = new StringBuilder();            stringBuilder.append("insert into user(id,name,password,age) values");            String message = "{0},''{1}'',''{2}'',{3}";            int i = 1;            for (User u : userList            ) {                String format = MessageFormat.format(message, u.getId(), u.getUserName(), u.getPassWord(), u.getAge());                stringBuilder.append(format);                if (i == userList.size()) {                    break;                }                stringBuilder.append(",");                i++;                stringBuilder.append("on duplicate key update id=values(id),username=values(username),password=values(password),age=values(age)");            }            return stringBuilder.toString();        }
        /**         * 批量查询         *         * @param userList 用户列表信息         * @return str字符串         */        public String batchList(@Param(value = "userList") List<User> userList) {            StringBuilder stringBuilder = new StringBuilder();            stringBuilder.append("select * from user where id in(");            int i = 1;            for (User u : userList            ) {                stringBuilder.append(u.getId());                if (i == userList.size()) {                    break;                }                stringBuilder.append(",");                i++;            }            stringBuilder.append(")");            return stringBuilder.toString();        }    }}

九,涉及的用户操作类User.class

package com.wpw.mybatisbatchoperation.entity;
import lombok.AllArgsConstructor;import lombok.Builder;import lombok.Data;import lombok.NoArgsConstructor;import lombok.experimental.Accessors;
import java.io.Serializable;
/** * @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;}

十,代码的地址放入到github了,地址如下

github:https://github.com/myownmyway/mybatis-batch-operation.git

十一:项目的结构图

本文分享自微信公众号 - WwpwW(gh_245290c1861a),作者:后端Coder

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-04-16

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • java进阶|Mybatis系列文章(三)表关联查询操作

    整个项目所需要的xml文件进行数据的增删改查操作ClassesMapper.xml文件

    后端Coder
  • java进阶|基于mybatis思考一些事情

    没什么好介绍这个框架的,一款半自动化sql的关系映射框架,之所以会写这篇文章还是为了标注一下自己在写CRUD操作过程中的一些思考和理解。

    后端Coder
  • java进阶|MyBatis系列文章(一)基础性文章的思考增删改查

    本篇文章主要是使用statetment最原生的方式操作数据库以及基于xml方式操作数据库,为什么自己又去写xml方式操作数据了呢,写了几篇示例程序觉得还是xml...

    后端Coder
  • mybatis 详解(二)------入门实例(基于XML)

      通过上一小节,mybatis 和 jdbc 的区别:https://cloud.tencent.com/developer/article/1012781,...

    IT可乐
  • MyBatis框架教程「实践与工具类封装」

    上一篇文章我们学习了MyBatis框架的环境搭建以及对sqlsessionfactory有个大致的了解,这篇文章就要运用搭建好的环境进行增删改查并且指出一些细节...

    用户1093975
  • 深入理解 Laravel Eloquent(三)——模型间关系(关联)

    Eloquent 是一个 ORM,全称为 Object Relational Mapping,翻译为 “对象关系映射”(如果只把它当成 Database A...

    貟王軍
  • MyBatis魔法堂:即学即用篇

    一、前言                                          本篇内容以理解MyBatis的基本用法和快速在项目中实践为目的,...

    ^_^肥仔John
  • spark sql多维分析优化——细节是魔鬼

    这是一张广告竞价的业务表,每一条请求 request_id 都会产生一条数据,一天下来,数据量是很大的(几十亿)。 然而,又要对 7个维度做成22个组合,分别...

    数据仓库践行者
  • UI5 xml view里control id的生成逻辑

    版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。

    Jerry Wang
  • UI5 xml view里control id的生成逻辑

    id的naming convention: +"–" + “<control id in xml view definition”, 例如:

    Jerry Wang

扫码关注云+社区

领取腾讯云代金券