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

2023-07-18 03:12:03 69

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

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

packagecn.com.befery.dataai.po;

importjava.util.Date;
importorg.springframework.boot.jackson.JsonComponent;
importcom.baomidou.mybatisplus.annotations.TableField;
importcom.baomidou.mybatisplus.annotations.TableId;
importcom.baomidou.mybatisplus.annotations.TableName;
importcom.baomidou.mybatisplus.enums.IdType;

importlombok.Data;

@JsonComponent()
@Data
@TableName("ai_model")
publicclassModel{
@TableId(value="model_id",type=IdType.AUTO)
privateLongmodelID;
privateLongapplicationId;
privateLongtrainingId;
privateStringmodelName;
//描述
privateStringmodelDescribe;
privateStringmodelType;
privateDatecreateDate;
privateStringfilePath;
privateStringfileName;
privateStringdaimension;//维度
privateLongstatus;

@TableField(exist=false)
privateStringapplicationName;

@TableField(exist=false)
privateStringtrainingName;

@TableField(exist=false)
privateStringorder;

@TableField(exist=false)
privateStringorderdir;//升序或降序
}

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

packagecn.com.befery.dataai.po;

importjava.io.Serializable;
importjava.util.Date;

importcom.baomidou.mybatisplus.annotations.TableField;
importcom.baomidou.mybatisplus.annotations.TableId;
importcom.baomidou.mybatisplus.annotations.TableName;
importcom.baomidou.mybatisplus.enums.IdType;

importlombok.Data;

@Data
@TableName("ai_application")
publicclassApplicationimplementsSerializable{
privatestaticfinallongserialVersionUID=1L;

@TableId(value="application_id",type=IdType.AUTO)
privateLongapplicationID;
privateStringapplicationName;
privateStringfilePath;
privateStringfileName;
privateLonguserId;
privateDatecreateDate;
privateIntegerstatus;
privateStringdimension;//维度

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

@TableField(exist=false)
privateStringorder;

@TableField(exist=false)
privateStringmodelName;

@TableField(exist=false)
privateStringtrainingName;

@TableField(exist=false)
privateStringorderdir;//升序或降序
}

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

packagecn.com.befery.dataai.po;

importjava.io.Serializable;
importjava.util.Date;

importcom.baomidou.mybatisplus.annotations.TableField;
importcom.baomidou.mybatisplus.annotations.TableId;
importcom.baomidou.mybatisplus.annotations.TableName;
importcom.baomidou.mybatisplus.enums.IdType;

importlombok.Data;
@Data
@TableName("ai_training")
publicclassTrainingimplementsSerializable{
privatestaticfinallongserialVersionUID=1L;

@TableId(value="training_id",type=IdType.AUTO)
privateLongtrainingID;
privateLongserverId;//服务器ID
privateLongapplicationId;//应用ID
privateStringtrainingModel;//训练模型
privateStringtrainingName;//训练名称
privateStringdimensionInput;//输入维度
privateStringdimensionOutput;//输出维度
privateDatecreateDate;
privateIntegerstatus;

@TableField(exist=false)
privateStringapplicationName;

@TableField(exist=false)
privateStringserverName;

@TableField(exist=false)
privateStringmodelName;

@TableField(exist=false)
privateStringorder;//排序字段

@TableField(exist=false)
privateStringorderdir;//升序或降序
}

然后是DAO层：

packagecn.com.befery.dataai.dao;

importjava.util.List;

importorg.apache.ibatis.annotations.Param;

importcom.baomidou.mybatisplus.mapper.BaseMapper;
importcom.baomidou.mybatisplus.plugins.pagination.Pagination;

importcn.com.befery.dataai.po.Model;

publicinterfaceModelDaoextendsBaseMapper{

ListselectModelPage(Paginationpage,@Param(value="model")Modelmodel);
}

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





SELECT
model.`model_id`,
model.`model_name`asmodelName,
model.`status`asstatus,
t.`training_name`astrainingName,
ap.`application_name`asapplicationName,
model.`create_date`ascreateDate
FROM
ai_modelmodel
LEFTJOINai_trainingt
ONt.`training_id`=model.`training_id`
LEFTJOINai_applicationap
ONap.`application_id`=t.`application_id`

1=1

andmodel.`model_name`like'%${model.modelName}%'

orderby${model.order}${model.orderdir}

然后就是service：

packagecn.com.befery.dataai.service;

importjavax.servlet.http.HttpServletRequest;

importorg.springframework.web.multipart.MultipartFile;

importcom.baomidou.mybatisplus.plugins.Page;
importcom.baomidou.mybatisplus.service.IService;

importcn.com.befery.dataai.po.Model;
importcn.com.befery.dataai.vo.ResultCode;


publicinterfaceModelServiceextendsIService{
//分页
PageselectModelPage(intpageNo,intpageSize,Modelmodel);

}

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

@Service
@Transactional
publicclassModelServiceImplextendsServiceImplimplementsModelService{

@Autowired
privateModelDaomodelDao;

@Override
publicPageselectModelPage(intpageNo,intpageSize,Modelmodel){
//TODOAuto-generatedmethodstub
Pagepage=newPage(pageNo,pageSize);
returnpage.setRecords(this.baseMapper.selectModelPage(page,model));
}
}

然后就是Controller：

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

/**
*@authorzhangxuewei三表查询
*@paramparam
*@paramrequest
*@return
*/
@ResponseBody
@RequestMapping(value="/modelPage")
publicResponseDatamodlePage(SearchParamparam,HttpServletRequestrequest){
logger.info("modlePage...........");
StringorderNO=request.getParameter("order[0][column]");
Stringorder=request.getParameter("columns["+orderNO+"][name]");
Stringorderdir=request.getParameter("order[0][dir]");
Stringsearch=request.getParameter("search[value]");

intpageNo=param.getStart()/param.getLength()+1;
intpageSize=param.getLength();

Modelmodel=newModel();
model.setModelName(search);
model.setOrder(order);
model.setOrderdir("asc".equals(orderdir)?"asc":"desc");
PagepageDate=modelService.selectModelPage(pageNo,pageSize,model);

returnresponseData(param.getDraw(),pageDate);
}

这个是分页返回公共类

packagecn.com.befery.dataai.controller;

importorg.springframework.stereotype.Controller;

importcom.baomidou.mybatisplus.plugins.Page;

importcn.com.befery.dataai.vo.ResponseData;

@Controller
publicclassBaseController{

/**
*
*@paramdraw重构次数
*@parampage分页数据
*@return
*/
publicResponseDataresponseData(Stringdraw,Pagepage){
ResponseDatares=newResponseData();
res.setData(page.getRecords());
res.setDraw(draw);
res.setRecordsFiltered((int)page.getTotal());
res.setRecordsTotal((int)page.getTotal());
returnres;
}
}

这个是ResponseDate实体类

packagecn.com.befery.dataai.vo;
importjava.util.List;

//@JsonInclude(Include.NON_NULL)
publicclassResponseData{

/**
*
*/
//privatestaticfinallongserialVersionUID=1L;

privateStringdraw;

privateintrecordsTotal;

privateintrecordsFiltered;

@SuppressWarnings("rawtypes")
privateListdata;
}

这是前端的html

















应用列表

.dataTables_wrapper.dataTables_length{
float:left;
padding-bottom:0px;
padding-top:10px;
padding-left:20px;
}

.tabletbodytrtd:FIRST-CHILD{
text-align:center;
}

.tabletbodytrtd{
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;
}




首页>
模型和测试管理>模型列表






${model}<#else>"class="clpd-5bg-1bk-graymt-20">测试模型

序号模型服务名称训练名称应用名称创建时间操作

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

热门推荐

随机推荐