文章详情页

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

浏览：94日期：2023-10-23 10:28:28

这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 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@Transactionalpublic 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;@Controllerpublic 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' /><link rel='stylesheet' type='text/css' href='https://www.haobala.com/static/h-ui/css/H-ui.min.css' rel='external nofollow' /><link rel='stylesheet' type='text/css' href='https://www.haobala.com/static/h-ui/css/H-ui.admin.css' rel='external nofollow' /><link rel='stylesheet' type='text/css' href='https://www.haobala.com/static/libs/Hui-iconfont/1.0.7/iconfont.css' rel='external nofollow' /><link rel='stylesheet' type='text/css' href='https://www.haobala.com/static/libs/icheck/icheck.css' rel='external nofollow' /><link rel='stylesheet' type='text/css' href='https://www.haobala.com/static/h-ui/skin/default/skin.css' rel='external nofollow' /><link rel='stylesheet' type='text/css' href='https://www.haobala.com/static/h-ui/css/style.css' rel='external nofollow' /><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'>></span> 模型和测试管理 <span class='c-gray en'>></span> 模型列表 <a href='javascript:location.replace(location.href);' rel='external nofollow'><i class='Hui-iconfont'></i></a> </nav> <div class='page-container'>   <div class='cl pd-5 bg-1 bk-gray mt-20'> <span class='l'> <a 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 onclick='data_refresh()'></a><input type='text' hidden='true' /> <input type='text' hidden='true' /></div> <div class='mt-20'> <table width=100%> <thead> <tr class='text-c'>  <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 tabindex='-1'> <article class='page-container'> <form action='' method='post' 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' value='' placeholder='' 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' value='' placeholder='' 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' value='' placeholder='' name='payCallBackURL'> </div> </div> <div class='row cl'> <div style='margin-left: 130px; width: 100px;'> <button value=''>  提交  </button> </div>  </div> </form> </article> </div> <script type='text/javascript' src='https://www.haobala.com/static/libs/jquery/1.9.1/jquery.min.js'></script> <script type='text/javascript' src='https://www.haobala.com/static/libs/layer/2.1/layer.js'></script> <script type='text/javascript' src='https://www.haobala.com/static/libs/My97DatePicker/WdatePicker.js'></script> <script type='text/javascript' src='https://www.haobala.com/static/libs/datatables/1.10.0/jquery.dataTables.min.js'></script> <script type='text/javascript' src='https://www.haobala.com/static/h-ui/js/H-ui.js'></script> <script type='text/javascript' src='https://www.haobala.com/static/h-ui/js/H-ui.admin.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 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+ ’') style='text-decoration:none'><i class='Hui-iconfont'></i></a>’; } else { return ’<a 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+ ’') style='text-decoration:none'><i class='Hui-iconfont'></i></a>’; } } else { if (e.isTrained == ’0’) { return ’<a 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+ ’') style='text-decoration:none'><i class='Hui-iconfont'></i></a>’; } else { return ’<a 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+ ’') 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 = 'https://www.haobala.com/bcjs/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多表联合查询内容请搜索好吧啦网以前的文章或继续浏览下面的相关文章希望大家以后多多支持好吧啦网！

上一条：MybatisPlus自定义Sql实现多表查询的示例下一条：MyBatis-Plus 如何实现连表查询的示例代码

相关文章：

1. 掌握SQL Server实战教程之SQL Server的安装指南2. Oracle如何对CLOB行字段来执行全文检索3. DB2 Web 服务提供者的安全性（1）4. ORACLE中常用的几种正则表达式小结5. SQL 嵌套查询的具体使用6. MySQL/MariaDB中如何支持全部的Unicode7. Oracle数据库SPFILE文件特点及具体操作8. MySQL 性能、监控与灾难恢复9. Access出现"所有记录中均未找到搜索关键字"的错误解决10. 详解MySQL批量入库的几种方式

排行榜

					
					Windwos下MySQL 64位压缩包的安装方法学习记录
Oracle数据库SPFILE文件特点及具体操作
MySQL/MariaDB中如何支持全部的Unicode
详解MySQL批量入库的几种方式
Access出现"所有记录中均未找到搜索关键字"的错误解决
ORACLE中常用的几种正则表达式小结
SQL 嵌套查询的具体使用
Oracle如何对CLOB行字段来执行全文检索
掌握SQL Server实战教程之SQL Server的安装指南
MySQL 性能、监控与灾难恢复
DB2 Web 服务提供者的安全性（1）
				

热门标签