MyBatis-Plus多表联合查询并且分页(3表联合)

这3张表的关系是模型表Model  ===> 训练表Training ===》应用表Application（大概的逻辑是：选择应用，然后训练，然后成为模型）

首先我们先建立实体Model（我使用的data注解不需要get set  @TableField(exist = false) 注解下的属性 是相关联表的属性）

package cn.com.befery.dataai.po;

import java.util.Date;
import org.springframework.boot.jackson.JsonComponent;
import com.baomidou.mybatisplus.annotations.TableField;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import com.baomidou.mybatisplus.enums.IdType;

import lombok.Data;

@JsonComponent()
@Data
@TableName("ai_model")
public class Model {
 @TableId(value = "model_id", type = IdType.AUTO)
 private Long modelID;
 private Long applicationId;
 private Long trainingId;
 private String modelName;
 // 描述
 private String modelDescribe;
 private String modelType;
 private Date createDate;
 private String filePath;
 private String fileName;
 private String daimension; //维度
 private Long status;

 @TableField(exist = false)
 private String applicationName;

 @TableField(exist = false)
 private String trainingName;

 @TableField(exist = false)
 private String order;

 @TableField(exist = false)
 private String orderdir; // 升序或降序
}

然后是第二个相关联的表 应用表application表

package cn.com.befery.dataai.po;

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

import com.baomidou.mybatisplus.annotations.TableField;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import com.baomidou.mybatisplus.enums.IdType;

import lombok.Data;

@Data
@TableName("ai_application")
public class Application implements Serializable{
 private static final long serialVersionUID = 1L;

 @TableId(value="application_id",type=IdType.AUTO)
 private Long applicationID;
 private String applicationName;
 private String filePath;
 private String fileName;
 private Long userId;
 private Date createDate;
 private Integer status;
 private String dimension; //维度

 @TableField(exist= false)
 private String userName; //关联用户表的名称字段

 @TableField(exist = false)
 private String order;

 @TableField(exist = false)
 private String modelName;

 @TableField(exist = false)
 private String trainingName;

 @TableField(exist = false)
 private String orderdir;  //升序或降序
}

然后是相关联的第3张表 训练表traning

package cn.com.befery.dataai.po;

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

import com.baomidou.mybatisplus.annotations.TableField;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import com.baomidou.mybatisplus.enums.IdType;

import lombok.Data;
@Data
@TableName("ai_training")
public class Training implements Serializable {
 private static final long serialVersionUID = 1L;

 @TableId(value = "training_id", type = IdType.AUTO)
 private Long trainingID;
 private Long serverId;  //服务器ID
 private Long applicationId; //应用ID
 private String trainingModel; //训练模型
 private String trainingName; //训练名称
 private String dimensionInput; //输入维度
 private String dimensionOutput; //输出维度
 private Date createDate;
 private Integer status;

 @TableField(exist = false)
 private String applicationName;

 @TableField(exist = false)
 private String serverName;

 @TableField(exist = false)
 private String modelName;

 @TableField(exist = false)
 private String order; //排序字段

 @TableField(exist = false)
 private String orderdir;  //升序或降序
}

然后是DAO层：

package cn.com.befery.dataai.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.baomidou.mybatisplus.plugins.pagination.Pagination;

import cn.com.befery.dataai.po.Model;

public interface ModelDao extends BaseMapper<Model> {

 List<Model> selectModelPage(Pagination page,@Param(value = "model") Model model);
}

然后是xml（sql语句使用了别名，别名和实体中的一致，包括之后的前后台交互，都取一致的名字，规范避免出错）【我之所以使用 $ 符号是因为 如果使用#号他会当作字符串识别，他不会当作关键字识别，我使用#号不行】

<?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="cn.com.befery.dataai.dao.ModelDao">
 <select id="selectModelPage"
 resultType="cn.com.befery.dataai.po.Model">
 SELECT
 model.`model_id`,
 model.`model_name` as modelName,
 model.`status`as status,
 t.`training_name`as trainingName,
 ap.`application_name` as applicationName,
 model.`create_date` as createDate
 FROM
 ai_model model
 LEFT JOIN ai_training t
 ON t.`training_id` = model.`training_id`
 LEFT JOIN ai_application ap
 ON ap.`application_id` = t.`application_id`
 <where>
  1 = 1
  <if test="model.modelName != null and model.modelName != ''">
  and model.`model_name` like '%${model.modelName}%'
  </if>
  order by ${model.order} ${model.orderdir}
 </where>
 </select>

</mapper>

然后就是service：

package cn.com.befery.dataai.service;

import javax.servlet.http.HttpServletRequest;

import org.springframework.web.multipart.MultipartFile;

import com.baomidou.mybatisplus.plugins.Page;
import com.baomidou.mybatisplus.service.IService;

import cn.com.befery.dataai.po.Model;
import cn.com.befery.dataai.vo.ResultCode;

public interface ModelService extends IService<Model>{
    //分页
 Page<Model> selectModelPage(int pageNo,int pageSize,Model model);

}

然后就是serviceImpl：（此处将接口中的  pageNo和pageSize封装成到  分页辅助类 page<T>中）

@Service
@Transactional
public class ModelServiceImpl extends ServiceImpl<ModelDao, Model> implements ModelService {

 @Autowired
 private ModelDao modelDao;

 @Override
 public Page<Model> selectModelPage(int pageNo, int pageSize, Model model) {
 // TODO Auto-generated method stub
 Page<Model> page = new Page<Model>(pageNo, pageSize);
 return page.setRecords(this.baseMapper.selectModelPage(page, model));
 }
}

然后就是Controller：

简单说一下下面的参数：
1.orderNO（排序用的）：是前台传过来的，根据orderNO（类似下标）找到前台定义好的数据库字段
2.order（排序用的）：根据orderNO（类似下标）找到前台定义好的数据库字段
3.orderdir（排序用的：是asc   还是desc）
4.search（前台模糊查询使用的）：前台传的名字，来进行模糊查询

/**
 * @author zhangxuewei 三表查询
 * @param param
 * @param request
 * @return
 */
 @ResponseBody
 @RequestMapping(value = "/modelPage")
 public ResponseData modlePage(SearchParam param, HttpServletRequest request) {
 logger.info("modlePage ...........");
 String orderNO = request.getParameter("order[0][column]");
 String order = request.getParameter("columns[" + orderNO + "][name]");
 String orderdir = request.getParameter("order[0][dir]");
 String search = request.getParameter("search[value]");

 int pageNo = param.getStart() / param.getLength() + 1;
 int pageSize = param.getLength();

 Model model = new Model();
 model.setModelName(search);
 model.setOrder(order);
 model.setOrderdir("asc".equals(orderdir) ? "asc" : "desc");
 Page<Model> pageDate = modelService.selectModelPage(pageNo, pageSize, model);

 return responseData(param.getDraw(), pageDate);
 }

这个是分页返回公共类

package cn.com.befery.dataai.controller;

import org.springframework.stereotype.Controller;

import com.baomidou.mybatisplus.plugins.Page;

import cn.com.befery.dataai.vo.ResponseData;

@Controller
public class BaseController {

  /**
   *
   * @param draw 重构次数
   * @param page 分页数据
   * @return
   */
  public ResponseData responseData(String draw,Page<?> page){
   ResponseData res = new ResponseData();
   res.setData(page.getRecords());
   res.setDraw(draw);
   res.setRecordsFiltered((int)page.getTotal());
   res.setRecordsTotal((int)page.getTotal());
   return res;
  }
}

这个是ResponseDate实体类

package cn.com.befery.dataai.vo;
import java.util.List;

//@JsonInclude(Include.NON_NULL)
public class ResponseData {

 /**
 *
 */
// private static final long serialVersionUID = 1L;

 private String draw;

 private int recordsTotal;

 private int recordsFiltered;

 @SuppressWarnings("rawtypes")
 private List data;
}

这是前端的html

<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<meta name="renderer" content="webkit|ie-comp|ie-stand">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<meta name="viewport"
 content="width=device-width,initial-scale=1,minimum-scale=1.0,maximum-scale=1.0,user-scalable=no" />
<meta http-equiv="Cache-Control" content="no-siteapp" />
<!--[if lt IE 9]>
<script type="text/javascript" src="lib/html5.js"></script>
<script type="text/javascript" src="lib/respond.min.js"></script>
<script type="text/javascript" src="lib/PIE_IE678.js"></script>
<![endif]-->
<link rel="stylesheet" type="text/css"
 href="../static/h-ui/css/H-ui.min.css" rel="external nofollow" />
<link rel="stylesheet" type="text/css"
 href="../static/h-ui/css/H-ui.admin.css" rel="external nofollow" />
<link rel="stylesheet" type="text/css"
 href="../static/libs/Hui-iconfont/1.0.7/iconfont.css" rel="external nofollow" />
<link rel="stylesheet" type="text/css"
 href="../static/libs/icheck/icheck.css" rel="external nofollow" />
<link rel="stylesheet" type="text/css"
 href="../static/h-ui/skin/default/skin.css" rel="external nofollow" id="skin" />
<link rel="stylesheet" type="text/css"
 href="../static/h-ui/css/style.css" rel="external nofollow" />
<!--[if IE 6]>
<script type="text/javascript" src="http://lib.h-ui.net/DD_belatedPNG_0.0.8a-min.js" ></script>
<script>DD_belatedPNG.fix('*');</script>
<![endif]-->
<title>应用列表</title>
<style type="text/css">
.dataTables_wrapper .dataTables_length {
 float: left;
 padding-bottom: 0px;
 padding-top: 10px;
 padding-left: 20px;
}

.table tbody tr td:FIRST-CHILD {
 text-align: center;
}

.table tbody tr td {
 text-align: center;
}

.dataTables_wrapper .dataTables_filter {
 padding-bottom: 10px;
}

.mt-20 {
 margin-top: 10px;
}

.page-container {
 padding: 20px;
 padding-top: 0px;
}

.form-horizontal .form-label {
 text-align: left;
 width: 140px;
 padding-right: 0px;
}
</style>
</head>
<body>
 <nav class="breadcrumb">
 <i class="Hui-iconfont"></i> 首页 <span class="c-gray en">&gt;</span>
 模型和测试管理 <span class="c-gray en">&gt;</span> 模型列表 <a
  class="btn btn-success radius r" id="refresh"
  style="line-height: 1.6em; margin-top: 3px"
  href="javascript:location.replace(location.href);" rel="external nofollow" title="刷新"><i
  class="Hui-iconfont"></i></a>
 </nav>
 <div class="page-container">
 <!-- <div class="text-c"> 日期范围：
 <input type="text" onfocus="WdatePicker({maxDate:'#F{$dp.$D(\'logmax\')||\'%y-%M-%d\'}',minDate:'#F{$dp.$D(\'logmax\',{M:-3})}'})" id="logmin" class="input-text Wdate" style="width:120px;">
 -
 <input type="text" onfocus="WdatePicker({maxDate:'#F{$dp.$D(\'logmin\',{M:3})||\'%y-%M-%d\'}',minDate:'#F{$dp.$D(\'logmin\')}'})" id="logmax" class="input-text Wdate" style="width:120px;">
 <input type="text" name="" id="search" placeholder=" 门店名称" style="width:250px" class="input-text">
 <button name="" id="search" class="btn btn-success" type="submit" onclick="search()"><i class="Hui-iconfont"></i> 搜账户</button>
 </div> -->

 <!-- <div style="display: &lt;#if(modelServer)??&gt;${modelServer"
  class="cl pd-5 bg-1 bk-gray mt-20">
  <span class="l"> <a class="btn btn-primary radius" id="addApp"
  onclick="modelServerServer_add('创建模型服务','/pages/modelServer-add.html')"
  href="javascript:;" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" ><i class="Hui-iconfont"></i> 创建模型服务</a><a
  id="datarefresh" onclick="data_refresh()"></a><input id="hiddentext"
  type="text" hidden="true" /> <input id="msgsecret" type="text"
  hidden="true" />
 </div> -->

 <div style="display: <#if (model)??>${model}<#else></#if>" class="cl pd-5 bg-1 bk-gray mt-20"> <span class="l"> <a class="btn btn-primary radius" id="addApp" onclick="model_testing('测试模型','/pages/model-testing.html')" href="javascript:;" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" ><i class="Hui-iconfont"></i> 测试模型</a><a id="datarefresh" onclick="data_refresh()"></a><input id="hiddentext" type="text" hidden="true" /> <input id="msgsecret" type="text" hidden="true" /></div>
 <div class="mt-20">
  <table
  class="table table-border table-bordered table-bg table-hover table-sort "
  width=100%>
  <thead>
   <tr class="text-c">
   <!-- <th width="40"><input id = "checkAll" name="checkAll" type="checkbox" value=""></th> -->
   <th width="80">序号</th>
   <th width="80">模型服务名称</th>
   <th width="100">训练名称</th>
   <th width="100">应用名称</th>
   <th width="100">创建时间</th>
   <th width="80">操作</th>
   </tr>
  </thead>
  <tbody>

  </tbody>
  </table>
 </div>
 </div>
 <div id="ajaxModal" class="modal hide fade" tabindex="-1">
 <article class="page-container">
  <form action="" method="post" class="form form-horizontal"
  id="form-modelServer-add">
  <div class="row cl">
   <label class="form-label col-xs-3 col-sm-3"><span
   class="c-red">*</span>App名称：</label>
   <div class="formControls col-xs-8 col-sm-9">
   <input type="text" class="input-text" value="" placeholder=""
    id="appName" name="appName">
   </div>
  </div>
  <div class="row cl">
   <label class="form-label col-xs-3 col-sm-3"><span
   class="c-red">*</span>图片地址：</label>
   <div class="formControls col-xs-8 col-sm-9">
   <input type="text" class="input-text" value="" placeholder=""
    id="adminMobile" name="adminMobile">
   </div>
  </div>

  <div class="row cl">
   <label class="form-label col-xs-3 col-sm-3"><span
   class="c-red">*</span>Domain(SaaS)：</label>
   <div class="formControls col-xs-8 col-sm-9">
   <input type="text" class="input-text" value="" placeholder=""
    id="payCallBackURL" name="payCallBackURL">
   </div>
  </div>

  <div class="row cl">
   <div class="col-xs-8 col-sm-9 col-xs-offset-4 col-sm-offset-3"
   style="margin-left: 130px; width: 100px;">
   <button class="btn btn-primary radius" value="">&nbsp;&nbsp;提交&nbsp;&nbsp;</button>
   </div>
   <!-- <div class="col-xs-8 col-sm-9 col-xs-offset-4 col-sm-offset-3" style="float: right; width: 250px;margin-left: 0px;">
  <input class="btn btn-primary radius" style="width: 80px;" onclick="javascript:;" value="&nbsp;&nbsp;取消&nbsp;&nbsp;">
  </div> -->
  </div>
  </form>
 </article>
 </div>
 <script type="text/javascript"
 src="../static/libs/jquery/1.9.1/jquery.min.js"></script>
 <script type="text/javascript" src="../static/libs/layer/2.1/layer.js"></script>
 <script type="text/javascript"
 src="../static/libs/My97DatePicker/WdatePicker.js"></script>
 <script type="text/javascript"
 src="../static/libs/datatables/1.10.0/jquery.dataTables.min.js"></script>
 <script type="text/javascript" src="../static/h-ui/js/H-ui.js"></script>
 <script type="text/javascript" src="../static/h-ui/js/H-ui.admin.js"></script>
 <!-- <script type="text/javascript" src="js/modelServer.js"></script> -->
 <script type="text/javascript">
 var table = $('.table-sort')
  .DataTable(
   {
    "processing" : true,
    "serverSide" : true,
    // "searching":true,
    'language' : {
    "search" : "按名称检索：",
    "sProcessing" : "<div style='position:absolute;margin-left:42%;margin-top:15%'><img src='static/h-ui/images/loading_072.gif'/>数据加载中...</div>",
    },

    "sDom" : '<"top"f<"clear">>rt<"bottom"ilp><"clear">',
    "order" : [
    [
     0, "asc"
     ]
    ],
    "ajax" : {
    "url" : "/model/modelPage",
    /* "data": function ( d ) {
     //添加额外的参数传给服务器
      d.extra_search = "canshu1";
     d.extra_search1 = "canshu11";
    }, */
    },
    "columnDefs" : [ {
    orderable : false,//禁用排序
    targets : [ 4 ]
    } ],
    "columns" : [
     /*{"sTitle": "<input type='checkbox'></input>","mDataProp": null, "sWidth": "20px", "sDefaultContent": "<input type='checkbox' ></input>", "bSortable": false,"sClass": "text-center",}, */

     {
     "data" : "modelID",
     "name" : "model_id",
     "class" : "modelID"
     },
     {
     "data" : "modelName",
     "name" : "model_name",
     "class" : "modelName"
     },
     {
     "data" : "trainingName",
     "name" : "training_name",
     "class" : "trainingName"
     },
     {
     "data" : "applicationName",
     "name" : "application_name",
     "class" : "applicationName"
     },
     {
     "data" : function(e) {
      if (e.createDate != null
       && e.createDate != "null") {
      return e.createDate;
      }
      return "";
     },
     "name" : "create_date",
     "class" : "createDate"
     },
     {
     "data" : function(e) {
      if (e.status == '0') {
      if (e.isTrained == '0') {
       return '<a style="display: <#if (modelServer_delete)??>${modelServer_delete}<#else></#if>" title="删除" href="javascript:;" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" onclick=modelServer_del(this,"'
        + e.modelID
        + '") class="ml-5" style="text-decoration:none"><i class="Hui-iconfont"></i></a>';
      } else {
       return '<a style="display: <#if (modelServer_delete)??>${modelServer_delete}<#else></#if>" title="删除" href="javascript:;" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" onclick=modelServer_del(this,"'
        + e.modelID
        + '") class="ml-5" style="text-decoration:none"><i class="Hui-iconfont"></i></a>';
      }
      } else {
      if (e.isTrained == '0') {
       return '<a style="display: <#if (modelServer_delete)??>${modelServer_delete}<#else></#if>" title="删除" href="javascript:;" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" onclick=modelServer_del(this,"'
        + e.modelID
        + '") class="ml-5" style="text-decoration:none"><i class="Hui-iconfont"></i></a>';
      } else {
       return '<a style="display: <#if (modelServer_delete)??>${modelServer_delete}<#else></#if>" title="删除" href="javascript:;" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" onclick=modelServer_del(this,"'
        + e.modelID
        + '") class="ml-5" style="text-decoration:none"><i class="Hui-iconfont"></i></a>';
      }
      }
     },
     "class" : "td-manage"
     } ],

   });

 table.on('draw.dt', function() {
 });

 $('.table-sort tbody').on('click', 'tr', function() {
  if ($(this).hasClass('selected')) {
  $(this).removeClass('selected');
  } else {
  table.$('tr.selected').removeClass('selected');
  $(this).addClass('selected');
  }
 });
 //});

 function refreshRow() {

 }

 function initComplete(data) {
  //选择时间后触发重新加载的方法
  $("#logmin").on('apply.input-text', function() {
  //当选择时间后，出发dt的重新加载数据的方法
  table.ajax.reload();
  //获取dt请求参数
  var args = table.ajax.params();
  console.log("额外传到后台的参数值extra_search为：" + args.extra_search);
  });
 }

 function filterGlobal() {
  alert($('#search').val());
  $('.table-sort').DataTable().search($('#search').val()).draw();
 }

 function search() {
  table.ajax.reload();
  //获取dt请求参数
  var args = table.ajax.params();
  console.log("额外传到后台的参数值extra_search为：" + args.extra_search);
  // filterGlobal();

 }
 /*账户-添加*/
 function modelServerServer_add(title, url) {
  layer_show(title, url, 550, 300);
 }
 function data_refresh() {
  var text = $('#hiddentext').val();
  if (text == 'add') {
  layer.alert($('#msgsecret').val());
  /* layer.msg('添加成功!',{icon: 6,time:1000}); */
  }
  if (text == 'update') {
  layer.msg('更新成功!', {
   icon : 6,
   time : 1000
  });
  }
  table.ajax.reload();
 }

 /*app-删除*/
 function modelServer_del(obj, id) {
  layer.confirm('确认要删除吗？', function(index) {
  //此处请求后台程序，下方是成功后的前台处理……
  if (index) {
   $.ajax({
   type : 'POST',
   url : '/model/deleteModel',
   data : {
    modelID : id,
   },
   success : function(result) {
    if (result.code == "0") {
    $(obj).parents("tr").remove();
    layer.msg('已删除!', {
     icon : 1,
     time : 1000
    });
    } else {
    layer.msg('删除失败!', {
     icon : 5,
     time : 1000
    });
    }
   },
   error : function(jqXHR, textStatus, errorThrown) {
    layer.msg(errorThrown, {
    icon : 5,
    time : 1000
    });
   }
   });
  } else {
   alert(33)
  }
  });
 }

 /*reset secret*/
 function reset_secret(obj, id) {

  layer.confirm('确认要重置Secret吗？', function(index) {
  //此处请求后台程序，下方是成功后的前台处理……
  if (index) {
   $.ajax({
   type : 'POST',
   url : 'modelServer/resetSecret',
   data : {
    appID : id,
   },
   success : function(result) {
    if (result.code == "0") {
    layer.alert('Secret is: ' + result.msg);
    } else {
    layer.alert('重置失败！');
    }
   },
   error : function(jqXHR, textStatus, errorThrown) {
    layer.msg(errorThrown, {
    icon : 5,
    time : 1000
    });
   }
   });
  } else {
   alert(33)
  }
  });
 }

 function modelServer_edit(obj, id) {
  var modelServerID = $(obj).parents("tr").find(".modelServerID").text();
  var appName = $(obj).parents("tr").find(".appName").text();
  var imageLibId = $(obj).parents("tr").find(".imageLibId").val();
  var domainId = $(obj).parents("tr").find(".domainId").val();
  /*var url = "pages/modelServer-update.html?te=66&tt=88";*/
  var url = "pages/modelServer-update.html?appName=" + appName
   + "&imageLibId=" + imageLibId + "&modelServerID=" + id
   + "&domainId=" + domainId;

  layer_show("修改App", encodeURI(url), 550, 350);

  /* var data = table.row( $(this).parents('tr') ).data();
  alert(data)
  var fields = $("#add-form").serializeArray();
  jQuery.each( fields, function(i, field){
    //jquery根据name属性查找
    $(":input[name='"+field.name+"']").val(data[i]);
  });
  $(":input[name='mark']").val("edit");
  $("#ajaxModal").modal("show");//弹出框show
  */

 }

 function modelServerServer_training(obj, id) {
  window.location.href = "pages/modelServer-train.html?appID=" + id;
 }

 function model_testing(title,url){
  layer_show(title,url,550,300);
 }
 </script>
</body>
</html>

到此这篇关于MyBatis-Plus多表联合查询并且分页(3表联合)的文章就介绍到这了,更多相关MyBatis-Plus多表联合查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！