mytibis-plus踩坑记

于 2022-01-04 23:11:42 发布
阅读量585 收藏
点赞数
文章标签： mariadb mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_52534711/article/details/122313566
版权
本文记录了在使用mytibis-plus过程中遇到的问题，包括首相pojo类、mapper.xml、dao层、service层及controller的实现。在测试中，通过postman发现@RequestParam与@RequestBody注解的请求参数传递方式不同，以及如何解决Invalid bound statement异常，其中异常可能是数据库映射错误或IDEA缓存问题导致。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

一、实现内容

1.首相pojo类

package com.sql.slow.query.po;

import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.enums.IdType;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

@Data
public class NodeWhiteList implements Serializable {
    private static final long serialVersionUID = 1L;

    @TableId(value="id", type= IdType.AUTO)
    private Integer id;

    private String hostIp;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date createTime;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date updateTime;

}

2.接着写mapper.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="com.sql.slow.query.dao.NodeWhiteListMapper">
<!--    和数据库映射-->
    <resultMap id="BaseResultMap" type="com.sql.slow.query.po.NodeWhiteList">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="host_ip" jdbcType="VARCHAR" property="hostIp" />
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime" />
    </resultMap>
<!--基本返回字段-->
    <sql id="Base_Column_List">
        id,host_ip,create_time,update_time
    </sql>

<!--通过传入的条件查询节点白名单集合-->
    <select id="selectNodeWhiteListByCondition" resultType="com.sql.slow.query.po.NodeWhiteList" >
        select
         <include refid="Base_Column_List" />
        from
            node_white_list
        where
        host_ip like "%"#{hostIp}"%"
        order by create_time desc
    </select>

<!--    添加指定节点白名单数据-->
    <insert id="insertNodeWhiteList" parameterType="com.sql.slow.query.po.NodeWhiteList" keyColumn="id" keyProperty="id" useGeneratedKeys="true">
        insert into node_white_list
        <trim prefix="(" suffix=")" suffixOverrides=",">
            id,
            <if test="hostIp != null">
                host_ip,
            </if>
            create_time,
            update_time,
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            null,
            <if test="hostIp != null">
                #{hostIp,jdbcType=VARCHAR},
            </if>
            now(),
            now(),
        </trim>
    </insert>

<!--    修改指定白名单数据-->
    <update id="updateByPrimaryKey" parameterType="com.sql.slow.query.po.NodeWhiteList">
        update node_white_list
        <set>
            <if test="hostIp != null">
                host_ip = #{hostIp,jdbcType=VARCHAR},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>

<!--    删除指定白名单数据-->
    <delete id="deleteByPrimaryKey" parameterType="com.sql.slow.query.po.NodeWhiteList">
        delete from node_white_list
        where id = #{id,jdbcType=INTEGER}
    </delete>

</mapper>

3.dao层的mapper接口

package com.sql.slow.query.dao;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.baomidou.mybatisplus.plugins.Page;
import com.sql.slow.query.po.NodeWhiteList;

import java.util.List;

public interface NodeWhiteListMapper extends BaseMapper<NodeWhiteList> {
    public List<NodeWhiteList> selectNodeWhiteListByCondition(Page page, String hostIp);

    public Integer insertNodeWhiteList(NodeWhiteList nodeWhiteList);

    public Integer updateByPrimaryKey(NodeWhiteList nodeWhiteList);

    public Integer deleteByPrimaryKey(NodeWhiteList nodeWhiteList);
}

4.service层接口

package com.sql.slow.query.service;

import com.baomidou.mybatisplus.plugins.Page;
import com.baomidou.mybatisplus.service.IService;
import com.sql.slow.query.po.NodeWhiteList;

public interface NodeWhiteListService extends IService<NodeWhiteList> {
    public Page selectNodeWhiteListByCondition(int pageNum, int pageSize, String hostIp);

    public boolean insertNodeWhiteList(NodeWhiteList nodeWhiteList);

    public boolean updateByPrimaryKey(NodeWhiteList nodeWhiteList);

    public boolean deleteByPrimaryKey(NodeWhiteList nodeWhiteList);
}

5.service层实现类

package com.sql.slow.query.service.impl;

import com.baomidou.mybatisplus.plugins.Page;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import com.sql.slow.query.dao.NodeWhiteListMapper;
import com.sql.slow.query.po.NodeWhiteList;
import com.sql.slow.query.service.NodeWhiteListService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

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

@Service("NodeWhiteListService")
@Slf4j
public class NodeWhiteListServiceImpl extends ServiceImpl<NodeWhiteListMapper,NodeWhiteList> implements NodeWhiteListService {
    @Resource
    NodeWhiteListMapper nodeWhiteListMapper;

    @Override
    public Page selectNodeWhiteListByCondition(int pageNum, int pageSize, String hostIp) {
        Page page = new Page(pageNum, pageSize);
        List<NodeWhiteList> nodeWhiteLists = nodeWhiteListMapper.selectNodeWhiteListByCondition(page, hostIp);
        page.setRecords(nodeWhiteLists);
//        System.out.println(nodeWhiteLists);
        return page;
    }

    @Override
    public boolean insertNodeWhiteList(NodeWhiteList nodeWhiteList) {
        return nodeWhiteListMapper.insertNodeWhiteList(nodeWhiteList) > 0 ? true:false;
    }

    @Override
    public boolean updateByPrimaryKey(NodeWhiteList nodeWhiteList) {
        return nodeWhiteListMapper.updateByPrimaryKey(nodeWhiteList) > 0 ? true:false;
    }

    @Override
    public boolean deleteByPrimaryKey(NodeWhiteList nodeWhiteList) {
        return nodeWhiteListMapper.deleteByPrimaryKey(nodeWhiteList) > 0 ? true:false;
    }

}

6.controller请求

package com.sql.slow.query.controller;

import com.baomidou.mybatisplus.plugins.Page;
import com.sql.slow.query.po.NodeWhiteList;
import com.sql.slow.query.service.NodeWhiteListService;
import com.sql.slow.query.vo.ResultVO;
import io.swagger.annotations.ApiOperation;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

/**
 * @author luxh
 * @desc 节点白名单
 * @create 2022/1/4 10:00
 */
@RestController
@RequestMapping("/slowquery/nodeWhiteList")
@ApiOperation("操作节点白名单")
public class NodeWhiteListController {
    @Resource
    NodeWhiteListService nodeWhiteListService;

    /**
     * @desc: 获取节点白名单
     * @author: luxh
     * @create: 2022/1/4 10:00
     */
    @RequestMapping(value = "/getNodeWhitListByCondition",method = RequestMethod.POST)
    public ResultVO<Page> getNodeWhitListByCondition(@RequestParam int pageNum, @RequestParam int pageSize, @RequestParam String hostIp){
        Page page = null;
        try{
            page = nodeWhiteListService.selectNodeWhiteListByCondition(pageNum, pageSize, hostIp);
            return ResultVO.success(page);
        }catch(Exception e){
            e.printStackTrace();
            return ResultVO.failure(e.getMessage());
        }
    }

    /**
     * @desc: 添加节点白名单
     * @author: luxh
     * @create: 2022/1/4 10:00
     */
    @RequestMapping(value = "/insertNodeWhitList",method = RequestMethod.POST)
    public ResultVO insertNodeWhitList(@RequestParam String hostIp){
        try{
            NodeWhiteList nodeWhiteList = new NodeWhiteList();
            nodeWhiteList.setHostIp(hostIp);
            boolean b = nodeWhiteListService.insertNodeWhiteList(nodeWhiteList);
            return ResultVO.success(b);
        }catch (DuplicateKeyException e){
            return ResultVO.failure("添加失败！输入的hostIp已经存在");
        }catch(Exception e){
            e.printStackTrace();
            return ResultVO.failure(e.getMessage());
        }
    }
    /**
     * @desc: 修改节点白名单
     * @author: luxh
     * @create: 2022/1/4 10:00
     */
    @RequestMapping(value = "/updateByPrimaryKey",method = RequestMethod.POST)
    public ResultVO updateByPrimaryKey(@RequestParam int id,@RequestParam String hostIp){
        try{
            NodeWhiteList nodeWhiteList = new NodeWhiteList();
            nodeWhiteList.setId(id);
            nodeWhiteList.setHostIp(hostIp);
            boolean b = nodeWhiteListService.updateByPrimaryKey(nodeWhiteList);
            return ResultVO.success(b);
        }catch (DuplicateKeyException e){
            return ResultVO.failure("修改失败！修改的hostIp已经存在");
        }catch(Exception e){
            e.printStackTrace();
            return ResultVO.failure(e.getMessage());
        }
    }

    /**
     * @desc: 删除节点白名单
     * @author: luxh
     * @create: 2022/1/4 10:00
     */
    @RequestMapping(value = "/deleteByPrimaryKey",method = RequestMethod.POST)
    public ResultVO deleteByPrimaryKey(@RequestParam int id){
        try{
            NodeWhiteList nodeWhiteList = new NodeWhiteList();
            nodeWhiteList.setId(id);
            boolean b = nodeWhiteListService.deleteByPrimaryKey(nodeWhiteList);
            return ResultVO.success(b);
        }catch(Exception e){
            e.printStackTrace();
            return ResultVO.failure(e.getMessage());
        }
    }


}

二、遇到的问题

1.使用postman测试

如果请求接收值有注释@RequestParam则直接传递参数即可。即使是post请求被@RequestParam注释的属性也不能通过body的方式传递

如果请求接收值有注释@RequestBody则需要传递json字符串。

注意：此处报了一个异常，我这边的原因是项目里面写了对应的过滤器，导致传递请求体时被过滤了。

2.Invalid bound statement(not found)异常

 原因一、数据库和pojo对象映射出现问题，字段写错了什么的

原因二、检查了都没错，但是还是报这个异常，最后清空idea缓存解决了问题

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值