springboot+mybatis+plus个人常用增删改查代码

TestXmlController:在mybatis的xml文件中写sql以实现增删改查

TestBaseMapperController.java:BaseMapper方式进行增删改查

TestServerImplController:通过通过ServiceImpl实现增删改查

package com.gree;


import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.gree.Entity.order.Order;
import com.gree.Entity.order.OrderForm;
import com.gree.Entity.order.OrderFormBatch;
import com.gree.Entity.order.OrderQuery;
import com.gree.mapper.OrderMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.LinkedHashMap;
import java.util.List;


/**
 * 在mybatis的xml文件中写sql以实现增删改查
 * 以下以订单表为例子，进行增删改查
 */
@RestController
@RequestMapping(path = "/order")
public class TestXmlController {


    @Autowired
    OrderMapper orderMapper;


    /**
     * 新增数据接口
     * xml方式
     */
    @PostMapping("/insertOne")
    public Object insertOne(@RequestBody OrderForm orderForm) {
        Integer num = orderMapper.insertOne(orderForm);
        return orderForm;
    }


    /**
     * 批量保存接口
     * xml方式
     */
    @PostMapping("/batchSave")
    public Object batchSave(@RequestBody OrderFormBatch form) {
        Integer num = orderMapper.batchSave(form.getItems());
        return form;
    }


    /**
     * 删除数据接口
     * xml方式
     */
    @PostMapping("/deleteOne")
    public Object deleteOne(@RequestParam(required = true) Long id) {
        Integer num = orderMapper.deleteOne(id);
        return num;
    }


    /**
     * 更新接口
     * xml方式
     * 全字段更新，如果请求的json中不包含某个字段或者某个字段为null，那么数据库中该字段的数值也会被更新为null
     */
    @GetMapping("/updatefull")
    public Object update(@RequestBody OrderForm form) {
        if (form.getOrderId() == null) {
            return "id不能为null";
        }
        Integer update = orderMapper.updateOrder(form);
        return update;
    }


    /**
     * 更新接口-
     * xml方式
     * 只更新字段值不为null的数据
     */
    @GetMapping("/updateNotNull")
    public Object updateNotNull(@RequestBody OrderForm form) {
        if (form.getOrderId() == null) {
            return "id不能为null";
        }
        Integer update = orderMapper.updateNotNull(form);
        return update;
    }


    /**
     * 只更新字段存在的数据
     * xml方式
     * 字段在请求体的json中存在则进行更新，字段在请求体的json中不存在则不更新，存在字段但是字段值为null将会把对应数据库字段赋值为null
     */
    @PostMapping("/updateExitField")
    public Object updateExitField(@RequestBody LinkedHashMap reqMap) {
        if (reqMap.get("orderId") == null) {
            return "id不能为null";
        }
        orderMapper.partUpdateOrder(reqMap);
        return reqMap;
    }


    /**
     * 批量更新接口
     * xml方式
     */
    @PostMapping("/batchUpdate")
    public Object batchUpdate(@RequestBody OrderFormBatch form) {
        long count = form.getItems().stream().filter(item -> item.getOrderId() == null).count();
        if (count > 0) {
            return "有id的值为空";
        }
        Integer update = orderMapper.updateBatch(form);
        return update;
    }


    /**
     * 分页查询
     * xml方式
     */
    @GetMapping("/pagelist")
    public Object pagelist(@RequestBody OrderQuery orderQuery) {
        PageHelper.startPage(orderQuery.getPageNum(), orderQuery.getPageSize());
        List<Order> list = orderMapper.pagelist(orderQuery);
        Page page = (Page) list;
        long total = page.getTotal();
        return page;
    }


}

package com.gree;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.gree.Entity.PageBean;
import com.gree.Entity.user.User;
import com.gree.Entity.user.UserQuery;
import com.gree.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;


/**
 * BaseMapper方式进行增删改查
 * 注意：BaseMapper方式只支持单表
 * 以下以用户表为例子，进行增删改查
 */
@RestController
@RequestMapping("/user")
public class TestBaseMapperController {

    @Autowired
    UserMapper userMapper;

    /**
     * 新增数据接口
     * BaseMapper方式
     */
    @PostMapping("/insertOne")
    public Object insertOne(@RequestBody User user) {
        int insert = userMapper.insert(user);
        return user;
    }


    /**
     * 删除数据接口
     * BaseMapper方式
     */
    @PostMapping("/delete")
    public Object delete(@RequestParam(name = "id") String id) {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("id", id);
        int delete = userMapper.delete(queryWrapper);
        return delete;
    }


    /**
     * 更新数据接口
     * 如果字段为null则不更新
     * BaseMapper方式
     */
    @PostMapping("/update")
    public Object update(@RequestBody User user) {
        UpdateWrapper<User> wrapper = new UpdateWrapper<>();
        wrapper.eq("id", user.getId());
        int num = userMapper.update(user, wrapper);
        return num;
    }


    /**
     * 查询数据接口
     * BaseMapper方式
     */
    @PostMapping("/select")
    public Object select(@RequestBody UserQuery userQuery) {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        if (userQuery.getId() != null) {
            queryWrapper.eq("id", userQuery.getId());
        }
        if (userQuery.getAge() != null) {
            queryWrapper.eq("age", userQuery.getAge());//如果字段为null，则最终sql会拼接成 = null
        }
        if (userQuery.getBirthday() != null) {
            queryWrapper.eq("birthday", userQuery.getBirthday());
        }
        PageHelper.startPage(userQuery.getPageNum(), userQuery.getPageSize());
        List<User> users = userMapper.selectList(queryWrapper);
        Page<User> page = (Page) users;
        long total = page.getTotal();
        PageBean<User> pageBean = new PageBean(page);
        return pageBean;
    }


}

package com.gree;


import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.github.pagehelper.PageHelper;
import com.gree.Entity.student.Student;
import com.gree.server.TestIBaseServiceServer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;


/**
 * 通过通过ServiceImpl实现增删改查
 * ServiceImpl底层部分实现基于BaseMapper实现，因此也只适合单表操作
 * 下面以学生表为例写增删改查代码
 */
@RestController
@RequestMapping("/student")
public class TestServerImplController {

    @Autowired
    TestIBaseServiceServer server;


    /**
     * 新增数据接口
     * 通过ServiceImpl实现
     */
    @PostMapping("/save")
    public Object insertOne(@RequestBody Student student) {
        server.save(student);
        return student;
    }


    /**
     * 删除数据接口
     * 通过ServiceImpl实现
     * 如果id传入为null,则最后sql为DELETE FROM student WHERE (id = null)
     */
    @PostMapping("/delect")
    public Object delect(@RequestBody Student student) {
        UpdateWrapper<Student> wrapper = new UpdateWrapper<>();
        wrapper.eq("id", student.getId());
        boolean update = server.remove(wrapper);
        return update;
    }


    /**
     * 更新数据接口
     * 通过ServiceImpl实现
     * 只更新不为null的字段，如果字段为null则不更新
     */
    @PostMapping("/update")
    public Object update(@RequestBody Student student) {
        UpdateWrapper<Student> wrapper = new UpdateWrapper<>();
        wrapper.eq("id", student.getId());
        boolean update = server.update(student, wrapper);
        return update;
    }


    /**
     * 查询数据接口
     * 通过ServiceImpl实现
     */
    @PostMapping("/select")
    public Object select(
            @RequestParam Integer pageNum,
            @RequestParam Integer pageSize,
            @RequestBody Student student
    ) {
        QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
        if (student.getId() != null) {
            queryWrapper.eq("id", student.getId());
        }
        if (student.getAge() != null) {
            queryWrapper.eq("age", student.getAge());//如果字段为null，则最终sql会拼接成 = null，将查不出任何数据
        }
        PageHelper.startPage(pageNum, pageSize);
        List<Student> list = server.list(queryWrapper);
        return list;
    }


    /**
     * 批量更新
     * 通过ServiceImpl实现
     * ServiceImpl还有批量保存，批量保存或更新的方法
     * 为null的字段不回更新
     */
    @PostMapping("/updateBatchById")
    public Object updateBatchById(@RequestBody List<Student> arr) {
        server.updateBatchById(arr);
        return arr;
    }


}

package com.gree.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.gree.Entity.user.User;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserMapper extends BaseMapper<User> {

}

package com.gree.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.gree.Entity.student.Student;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface StudentMapper extends BaseMapper<Student> {

}

package com.gree.mapper;

import com.gree.Entity.order.Order;
import com.gree.Entity.order.OrderForm;
import com.gree.Entity.order.OrderFormBatch;
import com.gree.Entity.order.OrderQuery;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;


@Mapper
public interface OrderMapper {

    List<Order> pagelist(@Param("orderQuery") OrderQuery orderQuery);

    Integer updateOrder(@Param("form") OrderForm form);

    Integer updateNotNull(@Param("mapOfParam") OrderForm form);

    Integer partUpdateOrder(@Param("mapOfParam") Map orderQuery);

    Integer insertOne(OrderForm orderForm);

    Integer deleteOne(@Param("id") Long id);

    Integer updateBatch(@Param("form") OrderFormBatch form);

    Integer batchSave(@Param("items") List<OrderForm> items);
}

package com.gree.server;

import com.baomidou.mybatisplus.extension.service.IService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.gree.Entity.student.Student;
import com.gree.mapper.StudentMapper;
import org.springframework.stereotype.Service;

@Service
public class TestIBaseServiceServer extends ServiceImpl<StudentMapper, Student> {

}

OrderMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.gree.mapper.OrderMapper">


    <select id="pagelist" resultType="com.gree.Entity.order.Order">
        select * from t_order
        <where>
            <if test="orderQuery.userId != null ">
                and user_id =#{orderQuery.userId}
            </if>
            <if test="orderQuery.userCode != null and orderQuery.userCode!=''">
                AND user_id like CONCAT('%',#{orderQuery.userCode},'%')
            </if>
            <if test="orderQuery.userIdList != null and orderQuery.userIdList.size > 0 ">
                and user_id in
                <foreach collection="orderQuery.userIdList" index="index" open="(" close=")" item="item" separator=",">
                    #{item}
                </foreach>
            </if>
        </where>
    </select>


    <update id="updateOrder">
        update t_order
        <set>
            price = #{form.price},
            user_id = #{form.userId}
        </set>
        where order_Id = #{form.orderId}
    </update>


    <update id="updateNotNull">
        update t_order
        <set>
            <if test="mapOfParam.userId != null ">
                user_id = #{mapOfParam.userId},
            </if>
            <if test="mapOfParam.price != null ">
                price = #{mapOfParam.price},
            </if>
        </set>
        where order_Id = #{mapOfParam.orderId}
    </update>


    <update id="partUpdateOrder">
        update t_order
        <set>
            <if test="mapOfParam.containsKey('userId')">
                user_id = #{mapOfParam.userId},
            </if>
            <if test="mapOfParam.containsKey('price')">
                price = #{mapOfParam.price},
            </if>
        </set>
        where order_Id = #{mapOfParam.orderId}
    </update>



    <update id="updateBatch">
        UPDATE t_order
        SET user_id = (
        CASE order_Id
        <foreach collection="form.items" index="index" item="item" separator=" ">
            WHEN #{item.orderId} THEN #{item.userId}
        </foreach>
        END
        ),
        price = (
        CASE order_Id
        <foreach collection="form.items" index="index" item="item" separator=" ">
            WHEN #{item.orderId} THEN #{item.price}
        </foreach>
        END
        )
        WHERE
        order_Id IN (
        <foreach collection="form.items" index="index" item="item" separator=",">
            #{item.orderId}
        </foreach>
        )
    </update>


    <insert id="insertOne">
        INSERT INTO `t_order`(`order_id`, `price`, `user_id`, `status`)
        VALUES
        (#{form.orderId}, #{form.price},#{form.userId}, #{form.status});
    </insert>


    <insert id="batchSave" useGeneratedKeys="true" keyProperty="orderId">
        INSERT INTO `t_order`( `price`, `user_id`)
        VALUES
        <foreach collection="items" index="index" item="item" separator=",">
            ( #{item.price},#{item.userId})
        </foreach>
    </insert>


    <delete id="deleteOne">
        DELETE FROM `m0`.`t_order` WHERE `order_id` = #{id}
    </delete>


</mapper>

application.yml

server:
  port: 8752

spring:
  application:
    name: springcloudclientTest
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/m0?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password: 1234

logging:
  level:
    com.gree.mapper: trace