MyBatis Plus實(shí)現(xiàn)多表聯(lián)接、分頁(yè)查詢的方法

發(fā)布時(shí)間：2020-11-03 15:21:12 來(lái)源：億速云閱讀：1150 作者：Leah 欄目：開(kāi)發(fā)技術(shù)

今天就跟大家聊聊有關(guān)MyBatis Plus實(shí)現(xiàn)多表聯(lián)接、分頁(yè)查詢的方法，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

代碼實(shí)現(xiàn)

entity、mapper、service、controller 使用了 MyBatisPlus 的代碼生成器，自動(dòng)生成大部分基礎(chǔ)的代碼，操作方法見(jiàn)之前的文章：

在 SpringBoot 中引入 MyBatisPlus 之常規(guī)操作

1.實(shí)體

① Question

// import 省略

@TableName("t_question")
public class Question implements Serializable {

  private static final long serialVersionUID = 1L;

  @ApiModelProperty(value = "問(wèn)答主鍵id")
  @TableId(value = "id", type = IdType.AUTO)
  private Integer id;

  @ApiModelProperty(value = "學(xué)生外鍵id")
  @TableField("student_id")
  private Integer studentId;

  @ApiModelProperty(value = "問(wèn)題內(nèi)容")
  private String content;

  @ApiModelProperty(value = "問(wèn)題發(fā)布時(shí)間，發(fā)布的時(shí)候后臺(tái)自動(dòng)生成")
  private Date date;

  @ApiModelProperty(value = "問(wèn)題懸賞的積分")
  private Integer value;

	// getter、setter 省略
}

② Student

// import 省略

@TableName("t_student")
public class Student implements Serializable {

  private static final long serialVersionUID = 1L;

  @ApiModelProperty(value = "學(xué)生主鍵id")
  @TableId(value = "id", type = IdType.AUTO)
  private Integer id;

  @ApiModelProperty(value = "學(xué)生名稱")
  private String name;

  @ApiModelProperty(value = "學(xué)生密碼")
  private String password;

  @ApiModelProperty(value = "學(xué)生積分?jǐn)?shù)")
  private Integer points;

  @ApiModelProperty(value = "學(xué)生郵件地址")
  private String email;

  @ApiModelProperty(value = "學(xué)生手機(jī)號(hào)碼")
  private String phone;

  @ApiModelProperty(value = "學(xué)生學(xué)號(hào)")
  private String num;

  @ApiModelProperty(value = "學(xué)生真實(shí)姓名")
  @TableField("true_name")
  private String trueName;

	// getter、setter 省略
}

2.mapper

① StudentMapper

// import 省略
public interface StudentMapper extends BaseMapper<Student> {
}

② QuestionMapper

// import 省略
public interface QuestionMapper extends BaseMapper<Question> {
  /**
   *
   * @param page 翻頁(yè)對(duì)象，可以作為 xml 參數(shù)直接使用，傳遞參數(shù) Page 即自動(dòng)分頁(yè)
   * @return
   */
  @Select("SELECT t_question.*,t_student.`name` FROM t_question,t_student WHERE t_question.student_id=t_student.id")
  List<QuestionStudentVO> getQuestionStudent(Pagination page);

}

3、service

① StudentService

// import 省略
public interface StudentService extends IService<Student> {
}

② QuestionService

// import 省略
public interface QuestionService extends IService<Question> {

  Page<QuestionStudentVO> getQuestionStudent(Page<QuestionStudentVO> page);

}

4、serviceImpl

① StudentServiceImpl

// import 省略
@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements StudentService {

}

② QuestionServiceImpl

// 省略 import

@Service
public class QuestionServiceImpl extends ServiceImpl<QuestionMapper, Question> implements QuestionService {

  @Override
  public Page<QuestionStudentVO> getQuestionStudent(Page<QuestionStudentVO> page) {
    return page.setRecords(this.baseMapper.getQuestionStudent(page));
  }

}

5、controller

// 省略 import

@RestController
@RequestMapping("/common")
@EnableSwagger2
public class CommonController {

  @Autowired
  QuestionService questionService;

  @Autowired
  StudentService studentService;

  @GetMapping("/getAllQuestionByPage/{page}/{size}")
  public Map<String, Object> getAllQuestionByPage(@PathVariable Integer page, @PathVariable Integer size) {
    Map<String, Object> map = new HashMap<>();
    Page<Question> questionPage = questionService.selectPage(new Page<>(page, size));
    if (questionPage.getRecords().size() == 0) {
      map.put("code", 400);
    } else {
      map.put("code", 200);
      map.put("data", questionPage);
    }
    return map;
  }

  @GetMapping("/getAllQuestionWithStudentByPage/{page}/{size}")
  public Map<String, Object> getAllQuestionWithStudentByPage(@PathVariable Integer page, @PathVariable Integer size) {
    Map<String, Object> map = new HashMap<>();
    Page<QuestionStudentVO> questionStudent = questionService.getQuestionStudent(new Page<>(page, size));
    if (questionStudent.getRecords().size() == 0) {
      map.put("code", 400);
    } else {
      map.put("code", 200);
      map.put("data", questionStudent);
    }
    return map;
  }

}

6、MyBatisPlus 配置

// 省略 import

@EnableTransactionManagement
@Configuration
@MapperScan("com.cun.app.mapper")
public class MybatisPlusConfig {

  /**
   * 分頁(yè)插件
   */
  @Bean
  public PaginationInterceptor paginationInterceptor() {
    return new PaginationInterceptor();
  }

  /**
   * 打印 sql
   */
  @Bean
  public PerformanceInterceptor performanceInterceptor() {
    PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
    //格式化sql語(yǔ)句
    Properties properties = new Properties();
    properties.setProperty("format", "true");
    performanceInterceptor.setProperties(properties);
    return performanceInterceptor;
  }
}

7、關(guān)聯(lián)查詢 VO 對(duì)象

// import 省略

public class QuestionStudentVO implements Serializable {

  @ApiModelProperty(value = "問(wèn)答主鍵id")
  @TableId(value = "id", type = IdType.AUTO)
  private Integer id;

  @ApiModelProperty(value = "學(xué)生外鍵id")
  @TableField("student_id")
  private Integer studentId;

  private String name;

  @ApiModelProperty(value = "問(wèn)題內(nèi)容")
  private String content;

  @ApiModelProperty(value = "問(wèn)題發(fā)布時(shí)間，發(fā)布的時(shí)候后臺(tái)自動(dòng)生成")
  private Date date;

  @ApiModelProperty(value = "問(wèn)題懸賞的積分")
  private Integer value;

	// getter、setter 省略

五、測(cè)試接口

MyBatis Plus實(shí)現(xiàn)多表聯(lián)接、分頁(yè)查詢的方法

1、沒(méi)有關(guān)聯(lián)的分頁(yè)查詢接口

http://localhost/common/getAllQuestionByPage/1/2

① json 輸出

{
 "code": 200,
 "data": {
  "total": 10,
  "size": 2,
  "current": 1,
  "records": [
   {
    "id": 1,
    "studentId": 3,
    "content": "唐代，渝州城里，有一個(gè)性格開(kāi)朗、樂(lè)觀的小伙子，名叫景天。",
    "date": 1534497561000,
    "value": 5
   },
   {
    "id": 2,
    "studentId": 1,
    "content": "雪見(jiàn)從小父母雙亡，由爺爺唐坤撫養(yǎng)成人。",
    "date": 1533201716000,
    "value": 20
   }
  ],
  "pages": 5
 }
}

② sql 執(zhí)行

MyBatis Plus實(shí)現(xiàn)多表聯(lián)接、分頁(yè)查詢的方法

2、多表關(guān)聯(lián)、分頁(yè)查詢接口

http://localhost/common/getAllQuestionWithStudentByPage/1/2

① json 輸出

{
 "code": 200,
 "data": {
  "total": 10,
  "size": 2,
  "current": 1,
  "records": [
   {
    "id": 1,
    "studentId": 3,
    "name": "vv",
    "content": "唐代，渝州城里，有一個(gè)性格開(kāi)朗、樂(lè)觀的小伙子，名叫景天。",
    "date": 1534497561000,
    "value": 5
   },
   {
    "id": 2,
    "studentId": 1,
    "name": "cun",
    "content": "雪見(jiàn)從小父母雙亡，由爺爺唐坤撫養(yǎng)成人。",
    "date": 1533201716000,
    "value": 20
   }
  ],
  "pages": 5
 }
}

② sql 執(zhí)行

MyBatis Plus實(shí)現(xiàn)多表聯(lián)接、分頁(yè)查詢的方法

六、小結(jié)

寫(xiě)本文的原因：

①網(wǎng)上有做法不合時(shí)宜的文章（自定義page類、配置版）②官方文檔使用的是配置版的，筆者采用注解版的

MyBatis 配置版	MyBatis 注解版
① 動(dòng)態(tài) sql 靈活、② xml 格式的 sql，可拓展性好	① 少一個(gè)設(shè)置，少一個(gè)錯(cuò)誤爆發(fā)點(diǎn)、② 代碼清晰優(yōu)雅

看完上述內(nèi)容，你們對(duì)MyBatis Plus實(shí)現(xiàn)多表聯(lián)接、分頁(yè)查詢的方法有進(jìn)一步的了解嗎？如果還想了解更多知識(shí)或者相關(guān)內(nèi)容，請(qǐng)關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。

向AI問(wèn)一下細(xì)節(jié)

MyBatis Plus實(shí)現(xiàn)多表聯(lián)接、分頁(yè)查詢的方法

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標(biāo)簽

MyBatis Plus實(shí)現(xiàn)多表聯(lián)接、分頁(yè)查詢的方法