springboot 整合 mybatis plus postgresql

依赖

<!--mybatis-plus-->
    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.2.0</version>
    </dependency>

<!--postgresql-->
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <scope>runtime</scope>
    </dependency>

建表测试

数据库表

create table t_club
(
  id          serial primary key,
  name        varchar(32) not null,
  money       int,
  nick_name   varchar(32),
  birthday    TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  create_time TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  update_time TIMESTAMP WITHOUT TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

实体类

package com.my.mybatis.test.demo.po;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;

import java.util.Date;

/**
 * @author wangli66
 ***************************************************/
@Data
@TableName("t_club")
public class ClubPo {
    /**
     * 主键
     * @TableId中可以决定主键的类型,不写会采取默认值,默认值可以在yml中配置
     * AUTO: 数据库ID自增
     * INPUT: 用户输入ID
     * ID_WORKER: 全局唯一ID，Long类型的主键
     * ID_WORKER_STR: 字符串全局唯一ID
     * UUID: 全局唯一ID，UUID类型的主键
     * NONE: 该类型为未设置主键类型
     */
    @TableId(type = IdType.AUTO)
    private Integer id;
    private String name;
    private Double money;
    private String nickName;
    private Date birthday;
    private Date createTime;
    private Date updateTime;
}

mapper

package com.my.mybatis.test.demo.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.my.mybatis.test.demo.po.ClubPo;
/**
 * @author wangli66
 ***************************************************/
public interface ClubMapper extends BaseMapper<ClubPo> {
}

测试类

package com.my.mybatis.test.demo.test;

import com.my.mybatis.test.demo.dao.ClubMapper;
import com.my.mybatis.test.demo.dao.MoneyMapper;
import com.my.mybatis.test.demo.dao.StudentMapper;
import com.my.mybatis.test.demo.po.ClubPo;
import com.my.mybatis.test.demo.po.MoneyPo;
import com.my.mybatis.test.demo.po.StudentPo;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.Arrays;
import java.util.List;

/**
 * @author
 ***************************************************/
@SpringBootTest
public class TetMP {
    private Logger logger = LoggerFactory.getLogger(TetMP.class);
   
    @Autowired
    ClubMapper clubMapper;

    @Test
    public void test3() {
        String[] nameArr = {"xiaoming","xiaohong","xiaolan","xiaohua","xiaomei"};
        String[] nickArr = {"Tom","Tony","Lily","Marry","Jerry"};
        for(int i=0;i<5;i++) {
            ClubPo clubPo = new ClubPo();
            clubPo.setName(nameArr[i]);
            clubPo.setMoney(9999.0*(i+1));
            clubPo.setNickName(nickArr[i]);
            clubPo.setBirthday(new Date());
            clueMapper.insert(clubPo);
        }

        ClubPo clubPo = clubMapper.selectById(1);
        logger.info("按id查询:"+clubPo);

        List<ClubPo> clubPos = clubMapper.selectBatchIds(Arrays.asList(1, 2, 3));
        logger.info("按id批量查询:"+clubPos);

    }
}

数据库表
在这里插入图片描述

整体流程走一遍

controller

package com.my.mybatis.test.demo.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.my.mybatis.test.demo.po.ClubPo;
import com.my.mybatis.test.demo.service.ClubService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 
 ***************************************************/
@RestController
@RequestMapping("/club")
public class ClubController {

    @Autowired
    private ClubService clubService;

    /**
     * @Description: 按id查询
     * @param: [id]
     * @return: com.my.mybatis.test.demo.po.ClubPo
     * @Date: 2020/4/23
     */
    @RequestMapping("/getInfo")
    public ClubPo getInfo(String id) {
        return clubService.getById(id);
    }

    /**
     * @Description: 查询全量,不分页
     * @param: []
     * @return: java.util.List<com.my.mybatis.test.demo.po.ClubPo>
     * @Date: 2020/4/23
     */
    @RequestMapping("/getAll")
    public List<ClubPo> getAll() {
        return clubService.list();
    }

    /**
     * @Description: 分页查询
     * @param: [current, pageSize]
     * @return: com.baomidou.mybatisplus.core.metadata.IPage<com.my.mybatis.test.demo.po.ClubPo>
     * @Date: 2020/4/23
     */
    @RequestMapping("/page")
    public IPage<ClubPo> getPage(int current, int pageSize) {
        IPage<ClubPo> clubPoIPage = new Page<>();
        clubPoIPage.setCurrent(current);
        clubPoIPage.setSize(pageSize);
        IPage<ClubPo> page = clubService.page(clubPoIPage);
        return page;
    }

    /**
     * @Description: 条件查询
     * @param: [po]
     * @return: java.lang.Object
     * @Date: 2020/4/23
     */
    @RequestMapping("/getListMap")
    public Collection<ClubPo> getListMap(ClubPo po) {
        Map<String,Object> param = new HashMap<>();
        param.put("name",po.getName());
        param.put("nickName",po.getNickName());
        Collection<ClubPo> clubPos = clubService.listByMap(param);
        return clubPos;
    }
    
    /**
     * @Description: 保存
     * @param: [po]
     * @return: void
     * @Date: 2020/4/23
     */
    @RequestMapping("/save")
    public boolean save(ClubPo po) {
        boolean save = clubService.save(po);
        return save;
    }

    /**
     * @Description: 批量保存
     * @param: [pos]
     * @return: boolean
     * @Date: 2020/4/23
     */
    @RequestMapping("/batchSave")
    public boolean batchSave(List<ClubPo> pos) {
        boolean b = clubService.saveBatch(pos);
        return b;
    }

    /**
     * @Description: 按id进行更新,不需要更新的字段传值为null
     * @param: [po]
     * @return: boolean
     * @Date: 2020/4/23
     */
    @RequestMapping("/update")
    public boolean update(ClubPo po) {
        boolean b = clubService.updateById(po);
        return b;
    }

    /**
     * @Description: 按id进行批量更新,不需要更新的字段传值为null
     * @param: [po]
     * @return: boolean
     * @Date: 2020/4/23
     */
    @RequestMapping("/batchUpdate")
    public boolean batchUpdate(List<ClubPo> pos) {
        boolean b = clubService.updateBatchById(pos);
        return b;
    }

    /**
     * @Description: 不确定数据库中是否存在时
     * 入的实体类中ID为null就会新增(ID自增),
     * 实体类ID值存在,如果数据库存在ID就会更新,如果不存在就会新增
     * @param: [po]
     * @return: boolean
     * @Date: 2020/4/23
     */
    @RequestMapping("/saveOrUpdate")
    public boolean saveOrUpdate(ClubPo po) {
        boolean b = clubService.saveOrUpdate(po);
        return b;
    }

   /**
    * @Description: 通过id删除
    * @param: [userId]
    * @return: void
    * @Date: 2020/4/23
    */
    @RequestMapping("/delete")
    public void delete(String id){
        clubService.removeById(id);
    }

    /*-------------------------以下为复杂查询------------------------------*/

    /**
     * @Description: 条件查询
     * @param: [po]
     * @return: void
     * @Date: 2020/4/23
     */
    @RequestMapping("/queryByCondition")
    public void queryByCondition(ClubPo po) {
        // 构建warpper
        QueryWrapper<ClubPo> wrapper = new QueryWrapper<>();
        // 查名字叫:xiaoming
        // 同sql: select id,name,nickname,money,birthday,create_time,update_time from t_club where name = "xiaoming"
        wrapper.lambda().eq(ClubPo::getName,"xiaoming");
        clubService.list(wrapper);

        // 查生日在
        wrapper = new QueryWrapper<>();
        // 同sql: select id,name,nickname,money,birthday,create_time,update_time from t_club where birthday > "2020-04-23 14:27:37.317000" and birthday < "2020-04-23 14:27:37.317000"
        wrapper.lambda().gt(ClubPo::getBirthday,new Date(System.currentTimeMillis()-1000));
        wrapper.lambda().le(ClubPo::getBirthday,new Date(System.currentTimeMillis()+1000));
        clubService.list(wrapper);

        // 查名字中有xiao的用户,并按照创建时间降序排列
        wrapper = new QueryWrapper<>();
        // 同sql: select id,name,nickname,money,birthday,create_time,update_time from t_club where name like "%xiao%" orderby create_time desc
        wrapper.lambda().like(ClubPo::getName,"xiao");
        wrapper.lambda().orderByDesc(ClubPo::getCreateTime);
        clubService.list(wrapper);

		// 使用mapper查询所有, select * from t_club
		List<Club> clubs = clubMapper.selectList(null);// 传null可查
    }
}

service

package com.my.mybatis.test.demo.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.my.mybatis.test.demo.po.ClubPo;

/**
 * @author 
 ***************************************************/
public interface ClubService extends IService<ClubPo> {
}

impl

package com.my.mybatis.test.demo.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.my.mybatis.test.demo.dao.ClubMapper;
import com.my.mybatis.test.demo.po.ClubPo;
import com.my.mybatis.test.demo.service.ClubService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * @author 
 ***************************************************/
@Service
@Transactional
public class ClubServiceImpl extends ServiceImpl<ClubMapper, ClubPo> implements ClubService {
}

wrapper参数

当查询条件复杂的时候,我们可以使用MP的条件构造器,请参考下面的QueryWrapper条件参数说明

查询方式方法说明
setSqlSelect设置 SELECT 查询字段
whereWHERE 语句，拼接 + WHERE 条件
andAND 语句，拼接 + AND 字段=值
orOR 语句，拼接 + OR 字段=值
eq等于=
allEq基于 map 内容等于=
ne不等于<>
gt大于>
ge大于等于>=
lt小于<
le小于等于<=
like模糊查询 LIKE
notLike模糊查询 NOT LIKE
inIN 查询
notInNOT IN 查询
isNullNULL 值查询
isNotNullIS NOT NULL
groupBy分组 GROUP BY
havingHAVING 关键词
orderBy排序 ORDER BY
orderByAscASC 排序 ORDER BY
orderByDescDESC 排序 ORDER BY
existsEXISTS 条件语句
notExistsNOT EXISTS 条件语句
betweenBETWEEN 条件语句
notBetweenNOT BETWEEN 条件语句
addFilter自由拼接 SQL
last拼接在最后，例如：last(“LIMIT 1”)
posted @   初见洞洞拐  阅读(353)  评论(0)  编辑  收藏  举报
相关博文：
· springboot整合postgresql
· mybatis plus ActiveRecord 用法
· SpringBoot整合Mybaits-Plus
· MyBatis-Plus （SpringBoot2 版）看这篇就足够了，一发入魂
· MyBatisPlus
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 单线程的Redis速度为什么快？
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛