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]