【无标题】

最新推荐文章于 2024-01-18 23:25:36 发布

qq_41126680

最新推荐文章于 2024-01-18 23:25:36 发布

阅读量33

点赞数

文章标签： mybatis

本文链接：https://blog.csdn.net/qq_41126680/article/details/132853687

版权

MybatisPlus 多表联合查询自定义SQL+分页+条件查询

章节实体类

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import javax.validation.constraints.Pattern;
import java.util.Date;
import java.io.Serializable;
ince 2023-09-11 17:33:36
 */
@Data
@ApiModel("章节实体类")
public class Article implements Serializable {
    private static final long serialVersionUID = -74512898752821314L;

    @TableId(type = IdType.AUTO)  //调用 save 方法插入数据，自增主键会存入实体类的 id 属性中
    private Integer id;
    /**
     * 课程ID
     */
    @ApiModelProperty(value = "课程id")
    @Pattern(regexp = "^\\d+$")
    private Integer courseId;
    /**
     * 标题
     */
    @ApiModelProperty(value = "章节标题")
    private String title;
    /**
     * 父章节ID
     */
    @Pattern(regexp = "^\\d+$")
    @ApiModelProperty(value = "父章节ID")
    private Integer pid;
    /**
     * 描述
     */
    private String description;
    /**
     * 章节文件url
     */
    private String fileUrl;

    /**
     * 排序
     */
    private int sort;
    /**
     * 课时
     */
    private Double classHour;

    private Date createTime;

    private Date updateTime;


}

课程实体类


import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import org.hibernate.validator.constraints.Length;

import javax.validation.constraints.NotBlank;
import java.util.Date;

@Data
@TableName("course")
public class Course {

    private Integer id;
    //课程名称
    @NotBlank(message = "课程名称不能为空")
    @Length(max = 30)
    private String name;
    //课程标题
    @NotBlank(message = "课程标题不能为空")
    private String title;
    //图片
    private String picture;
    //学分
    private Double credit;
    //课程描述
    private String description;

    @TableField(fill = FieldFill.INSERT)
    private Date createTime;
    @TableField(fill = FieldFill.UPDATE)
    private Date updateTime;

}

ArticleVo

查询Article的时候需要把课程名称一起查询出来

package com.itentbase.smartteaching.pojo.vo;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.itentbase.smartteaching.pojo.Article;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import javax.validation.constraints.Pattern;
import java.io.Serializable;
import java.util.Date;


@Data
public class ArticleVo extends Article implements Serializable {
    private String courseName;
}

ArticleMapper

注意返回值和参数

${ew.customSqlSegment} queryWrapper提供的值

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.itentbase.smartteaching.pojo.Article;
import com.itentbase.smartteaching.pojo.vo.ArticleVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

public interface ArticleMapper extends BaseMapper<Article> {

    @Select("select a.*,c.name as courseName" +
            "  from article a" +
            "  left join course c on a.course_id = c.id" +
            "  ${ew.customSqlSegment}")
    IPage<ArticleVo> queryArticleVoByPage(IPage<ArticleVo> page,
                                          @Param(Constants.WRAPPER) Wrapper<ArticleVo> queryWrapper);

}

测试

这样就会应用到分页+queryWrapper+自定义sql

注意

（1）因为是多表联合查询，那么传递参数的时候，需要使用到表别名+字段

例如：a.title=网络，这样才能正确查询出结果。

（2）会自动执行2条sql语句，一条查询总记录数，一条分页查询数据，如下：

JsqlParserCountOptimize sql=select a.,c.name as courseName from article a left join course c on a.course_id = c.id WHERE (a.title LIKE ?)
> Preparing: SELECT COUNT(1) FROM article a WHERE (a.title LIKE ?)
> Parameters: %网络%(String)
< Columns: COUNT(1)
< Row: 6
==> Preparing: select a.,c.name as courseName from article a left join course c on a.course_id = c.id WHERE (a.title LIKE ?) LIMIT ?,?
> Parameters: %网络%(String), 0(Long), 10(Long)
< Columns: id, course_id, title, pid, description, file_url, class_hour, sort, create_time, update_time, courseName
<== Row: 1, 1, 第1章网络空间安全概述, 0, 33, null, 11.0, 22, null, null, 网络安全技术应用
<== Row: 3, 1, 1.1.1 网络安全相关概念, 2, 333, null, 11.0, 22, null, null, 网络安全技术应用
<== Row: 4, 1, 1.1.2 网络安全体系, 2, 测试, null, 1.0, 1, null, null, 网络安全技术应用
<== Row: 5, 1, 1.2 网络安全法律法规, 1, 成都市, null, 1.0, 1, null, null, 网络安全技术应用
<== Row: 6, 1, 第2章网络空间, 0, 1, null, 1.0, 1, null, null, 网络安全技术应用
<== Row: 7, 2, 第1章计算机网络安全概述, 0, 222, Article/16945344723537.docx, 11.0, 2, null, null, 网络安全实践
<== Total: 6
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@46f7cd30]