文章詳情頁

MyBatis-Plus多表聯合查詢并且分頁(3表聯合)

瀏覽：91日期：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='http://www.aoyou183.cn/static/h-ui/css/H-ui.min.css' rel='external nofollow' /><link rel='stylesheet' type='text/css' href='http://www.aoyou183.cn/static/h-ui/css/H-ui.admin.css' rel='external nofollow' /><link rel='stylesheet' type='text/css' href='http://www.aoyou183.cn/static/libs/Hui-iconfont/1.0.7/iconfont.css' rel='external nofollow' /><link rel='stylesheet' type='text/css' href='http://www.aoyou183.cn/static/libs/icheck/icheck.css' rel='external nofollow' /><link rel='stylesheet' type='text/css' href='http://www.aoyou183.cn/static/h-ui/skin/default/skin.css' rel='external nofollow' /><link rel='stylesheet' type='text/css' href='http://www.aoyou183.cn/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='http://www.aoyou183.cn/static/libs/jquery/1.9.1/jquery.min.js'></script> <script type='text/javascript' src='http://www.aoyou183.cn/static/libs/layer/2.1/layer.js'></script> <script type='text/javascript' src='http://www.aoyou183.cn/static/libs/My97DatePicker/WdatePicker.js'></script> <script type='text/javascript' src='http://www.aoyou183.cn/static/libs/datatables/1.10.0/jquery.dataTables.min.js'></script> <script type='text/javascript' src='http://www.aoyou183.cn/static/h-ui/js/H-ui.js'></script> <script type='text/javascript' src='http://www.aoyou183.cn/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 = 'http://www.aoyou183.cn/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. 如何正確的進行Oracle數據庫性能完全保護2. 講解Oracle優化器的優化方式和優化模式3. 國內學院派專家對DB2 9新產品贊不絕口4. SQL SERVER – 檢測LOCK、終止會話 ID/UOW 的用戶進程5. ubuntu下使用SQLite3的基本命令6. DB2 V9.5工作負載管理之工作負載管理簡介7. SQLite教程（四）：內置函數8. MySQL存儲過程in、out和inout參數示例和總結9. 教你使用智能優化器提高Oracle性能極限10. SQL Server數據庫的三種創建方法匯總

排行榜

					
					如何正確的進行Oracle數據庫性能完全保護
MySQL存儲過程in、out和inout參數示例和總結
使用DB2look 重新創建優化器訪問計劃(3)（1）
ubuntu下使用SQLite3的基本命令
國內學院派專家對DB2 9新產品贊不絕口
教你使用智能優化器提高Oracle性能極限
SQLite教程（四）：內置函數
DB2 V9.5工作負載管理之工作負載管理簡介
SQL Server數據庫的三種創建方法匯總
講解Oracle優化器的優化方式和優化模式
SQL SERVER – 檢測LOCK、終止會話 ID/UOW 的用戶進程
				

熱門標簽

亚洲精品久久久中文字幕-亚洲精品久久片久久-亚洲精品久久青草-亚洲精品久久婷婷爱久久婷婷-亚洲精品久久午夜香蕉

MyBatis-Plus多表聯合查詢并且分頁(3表聯合)