采用XML映射文件的形式来映射sql语句；采用动态sql语句的方式，实现条件查询的分页。
controller
@Slf4j
@RestController
@RequestMapping("supermarket111")
public class SupermarketFenyeController {
@Autowired
SupermarketFenyeService supermarketFenyeService;
@RequestMapping ("/supermarket/{page}/{pageSize}")
public Result findAll(@PathVariable Integer page,
@PathVariable Integer pageSize){
PageBean pageBean = supermarketFenyeService.list(page,pageSize);
return Result.seccess(pageBean);
}
//查询所有+分页
@RequestMapping("/supermarket1/{page}/{pageSize}")
public Result findAll_chaxun(@PathVariable Integer page,
@PathVariable Integer pageSize, String merchID, String factoryID){
//记录日志
log.info("分页查询，参数：{},{},{},{},{},{}", page, pageSize,merchID, factoryID);
PageBean pageBean = supermarketFenyeService.list_chaxun(page,pageSize,merchID,factoryID);
return Result.seccess(pageBean);
}
//条件分页查询
@GetMapping("/{page}/{pageSize}")
public Result page(@PathVariable Integer page,
@PathVariable Integer pageSize,
String merchID, String factoryID) {
//记录日志
log.info("分页查询，参数：{},{},{},{},{},{}", page, pageSize,merchID, factoryID);
//调用业务层分页查询功能
PageBean pageBean = supermarketFenyeService.list_chaxun(page, pageSize, merchID, factoryID);
//响应
return Result.seccess(pageBean);
}
mapper
public List<MerchInfo> list();
//带条件的查询
public List<MerchInfo> list_chaxun(@Param("merchID") String merchID, @Param("factoryID") String factoryID);
service
@Autowired
private SupermarketMapper supermarketMapper;
@Override
public PageBean list(Integer page,Integer pageSize) {
//问：PageHelper.startPage(page, pageSize); 请解释一下
// 设置分页参数
PageHelper.startPage(page, pageSize);
// 执行分页查询
List<MerchInfo> supermarketList = supermarketMapper.list();
// 获取分页结果
PageInfo<MerchInfo> p = new PageInfo<>(supermarketList);
//封装PageBean
PageBean pageBean = new PageBean(p.getTotal(), p.getList());
return pageBean;
}
@Override
public PageBean list_chaxun(Integer page,Integer pageSize,String merchID,String factoryID) {
// 设置分页参数
PageHelper.startPage(page, pageSize);
// 执行分页查询
List<MerchInfo> supermarketList = supermarketMapper.list_chaxun(merchID, factoryID);
// 获取分页结果
PageInfo<MerchInfo> p = new PageInfo<>(supermarketList);
//封装PageBean
PageBean pageBean = new PageBean(p.getTotal(), p.getList());
return pageBean;
}public PageBean list(Integer page, Integer pageSize);
public PageBean list_chaxun(Integer page, Integer pageSize,String merchID,String factoryID);
xml
<?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="com.example.git.mapper.SupermarketMapper">
<!-- 查询 -->
<select id="list" resultType="com.example.git.pojo.MerchInfo">
select * from merchinfo
</select>
<!-- 条件分页查询 -->
<select id="list_chaxun" resultType="com.example.git.pojo.MerchInfo">
select * from merchinfo
<where>
<if test="merchID != null and merchID != ''">
merchID like concat('%',#{merchID},'%')
</if>
<if test="factoryID != null and factoryID != ''">
and factoryID like concat('%',#{factoryID},'%')
</if>
</where>
</select>
</mapper>html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>超市管理</title>
<link rel="stylesheet" href="js/element.css">
</head>
<body style="margin: 0">
<div id="app" style="width: 100%;height: 100%">
<!--头导航栏-->
<el-container >
<el-header style="height: 60px;width: 100%;margin-top: 0;background-color: #545c64 ">
<el-menu
:default-active="activeIndex"
class="el-menu-demo"
mode="horizontal"
@select="handleSelect"
background-color="#545c64"
text-color="#fff"
active-text-color="#ffd04b">
<el-menu-item index="1" style="float: left">
<template slot="title">超市管理</template>
</el-menu-item>
<el-menu-item index="1" style="float: right">处理中心</el-menu-item>
<el-submenu index="2" style="float: right">
<template slot="title">我的工作台</template>
<el-menu-item index="2-1">选项1</el-menu-item>
<el-menu-item index="2-2">选项2</el-menu-item>
<el-menu-item index="2-3">选项3</el-menu-item>
<el-submenu index="2-4">
<template slot="title">选项4</template>
<el-menu-item index="2-4-1">选项1</el-menu-item>
<el-menu-item index="2-4-2">选项2</el-menu-item>
<el-menu-item index="2-4-3">选项3</el-menu-item>
</el-submenu>
</el-submenu>
<el-menu-item index="3" disabled style="float: right">消息中心</el-menu-item>
<el-menu-item index="4" style="float: right"><a href="https://www.ele.me" target="_blank">订单管理</a></el-menu-item>
</el-menu>
</el-header>
<!--左导航栏-->
<el-container style="height: 900px;">
<el-aside width="300px" height="900px" style="background-color:#545c64">
<el-col style="height: 100%;width: 300px;color:#545c64">
<el-menu
default-active="2"
class="el-menu-vertical-demo"
@open="handleOpen"
@close="handleClose"
background-color="#545c64"
text-color="#fff"
active-text-color="#ffd04b">
<el-submenu index="1">
<template slot="title">
<i class="el-icon-location"></i>
<span>功能管理</span>
</template>
<el-menu-item-group>
<template slot="title">核心功能</template>
<el-menu-item index="1-1">商品信息管理</el-menu-item>
<el-menu-item index="1-2">厂商管理</el-menu-item>
<el-menu-item index="1-3">供货商管理</el-menu-item>
<el-menu-item index="1-4">销售管理</el-menu-item>
</el-menu-item-group>
</el-submenu>
<el-submenu index="2">
<template slot="title">
<i class="el-icon-location"></i>
<span>统计分析</span>
</template>
<el-menu-item-group>
<template slot="title">图表统计</template>
<el-menu-item index="2-1">统计分析</el-menu-item>
</el-menu-item-group>
</el-submenu>
</el-menu>
</el-col>
</el-aside>
<!--主表格页面-->
<el-main height="900px">
<!--查询栏-->
<el-form :inline="true" :model="formInline" class="demo-form-inline" style="font-size: 15px">
<el-form-item label="商品编号">
<el-input v-model="formInline.merchID" placeholder="商品编号" size="mini"></el-input>
</el-form-item>
<el-form-item label="厂商编号">
<el-input v-model="formInline.factoryID" placeholder="厂商编号" size="mini"></el-input>
</el-form-item>
<el-form-item>
<el-button type="primary" @click="onSubmit" size="mini">查询</el-button>
</el-form-item>
<el-form-item>
<el-button type="success" @click="gotoInsert" size="mini" icon="el-icon-circle-plus-outline">新增</el-button>
</el-form-item>
</el-form>
<!--表格-->
<el-table
:data="tableData.filter(data => !search || data.merchID.toLowerCase().includes(search.toLowerCase()))">
<el-table-column align="center"
label="商品编号"
prop="merchID">
</el-table-column>
<el-table-column align="center"
label="商品名称"
prop="merchName">
</el-table-column>
<el-table-column align="center"
label="价格"
prop="merchPrice">
</el-table-column>
<el-table-column align="center"
label="库存数量"
prop="merchNum">
</el-table-column>
<el-table-column align="center"
label="厂商编号"
prop="factoryID">
</el-table-column>
<el-table-column align="center"
label="供货商编号"
prop="provideID">
</el-table-column>
<el-table-column align="center" label="操作">
<template slot-scope="scope">
<el-button
size="mini"
@click="gotoEdit(scope.row.merchID)">Edit
</el-button>
<el-button
size="mini"
type="danger"
@click="deleteById(scope.row.merchID)">Delete
</el-button>
</template>
</el-table-column>
</el-table>
<p align="center">
<el-pagination
layout="total, sizes, prev, pager, next, jumper"
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
:current-page="currentPage"
:page-sizes="[2, 3, 4, 10]"
:page-size="pageSize"
:total="total">
</el-pagination>
</p>
</el-main>
</el-container>
</el-container>
</div>
<!-- 引入组件库 -->
<script src="js/jquery.min.js"></script>
<script src="js/vue.js"></script>
<script src="js/element.js"></script>
<script src="js/axios-0.18.0.js"></script>
<script>
new Vue({
el: "#app",
data: {
activeIndex:'1',
search: '',
currentPage: 1,
pageSize: 4,
total: null,
formInline: {
merchID: '',
factoryID: '',
},
tableData: [],
formLabelWidth: '120px'
},
methods: {
handleEdit(index, row) {
console.log(index, row);
},
handleDelete(index, row) {
console.log(index, row);
},
handleSizeChange(val) {
this.pageSize = val;
this.findAll();
console.log(`每页 ${val} 条`);
},
handleCurrentChange(val) {
this.currentPage = val;
this.findAll();
console.log(`当前页: ${val}`);
},
//查询所有
onSubmit() {
var url = `/supermarket111/${this.currentPage}/${this.pageSize}?merchID=${encodeURIComponent(this.formInline.merchID)}&factoryID=${encodeURIComponent(this.formInline.factoryID)}`
console.log(this.formInline.merchID);
console.log(this.formInline.factoryID);
axios.get(url)
.then(res =>{
this.tableData = res.data.data.rows;
this.total=res.data.data.total;
console.log(this.tableData);
console.log(this.total);
})
.catch(error=>{
console.error(error);
})
},
findAll() {
var url = `/supermarket111/${this.currentPage}/${this.pageSize}`
axios.get(url)
.then(res =>{
this.tableData = res.data.data.rows;
this.total=res.data.data.total;
console.log(this.tableData);
console.log(this.total);
})
.catch(error=>{
console.error(error);
})
},
deleteById:function (merchID) {
var _this= this;
if (window.confirm("确定要删除该条数据吗？？？")){
axios.delete('/delete2/'+merchID)
.then(function (response) {
alert("删除成功")
_this.findAll(1);
})
.catch(function (error) {
console.log(error);
});
}
},
gotoInsert(){
location.href='insert.html';
},
gotoEdit(merchID){
location.href='edit.html?merchID='+merchID;
}
},
created(){
this.findAll();
}
})
</script>
</body>
</html>
