阅读:51
学习了SpringBoot分页查询的两种写法,一种是手动实现,另一种是使用框架实现。现在我将具体的实现流程分享一下。
首先是手动实现分页查询:
先复习一下,SQL中的limit关键字,下面一行sql语句的意思是从第二个数据开始查,查询出两条数据
SELECT * FROM sys_user limit 1,2;
使用limit前一个参数pageNum是从第几个数据开始查,后一个参数pageSize是查询多少条数据,
注意数据库查询pageNum=0代表第一个数据。
那么在Springboot中该如何写呢?
controller:
// 分页查询
// 接口路径:/user/page?pageNum=1&pageSize=10
// @RequestParam接收
// limit第一个参数= (pageNum-1)+pageSize
@GetMapping("/page")
public List<User> findPage(@RequestParam Integer pageNum,@RequestParam Integer pageSize){
pageNum = (pageNum-1);
return userMapper.selectPage(pageNum,pageSize);
}
}
mapper:
@Select("select * from sys_user limit #{pageNum},#{pageSize}")
List<User> selectPage(Integer pageNum, Integer pageSize);
这样就写好了,测试一下:
完善一下controller,添加查询 总条数的方法:
// 分页查询
// 接口路径:/user/page?pageNum=1&pageSize=10
// @RequestParam接收 前端url
// limit第一个参数= (pageNum-1)*pageSize
@GetMapping("/page")
public Map<String, Object> findPage(@RequestParam Integer pageNum, @RequestParam Integer pageSize){
pageNum = (pageNum-1)*pageSize;
List<User> data = userMapper.selectPage(pageNum,pageSize);
Integer total = userMapper.selectTotal();
Map<String,Object> res = new HashMap<>();
res.put("data",data);
res.put("total",total);
return res;
}
修改分页页面homeView:使用fetch()请求分页数据
created() {
//请求分页数据
fetch("localhost:9090/user/page?pageNum=1&pageSize=2").then(res =>res.json()).then(res =>{
console.log(res)
})
},
fetch得到的是一个字符串数据,需要将其转换成Json格式,使用console.log()打印数据
@JsonIgnore忽略某一个属性的注解
修改前端页面表格数据:
表格数据对应数据库的属性
<el-table :data="tableData" border stripe :header-cell-class-name="headerBg">
<el-table-column prop="id" label="ID" width="80">
</el-table-column>
<el-table-column prop="username" label="用户名" width="140">
</el-table-column>
<el-table-column prop="nickname" label="昵称" width="120">
</el-table-column>
<el-table-column prop="email" label="邮箱">
</el-table-column>
<el-table-column prop="phone" label="电话">
</el-table-column>
<el-table-column prop="address" label="地址">
</el-table-column>
修改前端数据内容:
data() {
return {
tableData: [], //表格的值默认为空数组
total: 0, //查询条数默认为0
msg: "hello 阿晴",
collapseBtnClass: 'el-icon-s-fold',
isCollapse: false,
sideWidth: 200,
logoTextShow: true,
headerBg: 'headerBg'
}
},
渲染页面:
created() {
//请求分页数据
fetch("http://localhost:9090/user/page?pageNum=1&pageSize=2").then(res =>res.json()).then(res =>{
console.log(res)
this.tableData = res.data
this.total = res.total
})
},
分页函数:
<el-pagination
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
:current-page="pageNum"
:page-sizes="[2, 5, 10, 20]"
:page-size="pageSize"
layout="total, sizes, prev, pager, next, jumper"
:total="total">
</el-pagination>
load() {
fetch("http://localhost:9090/user/page?pageNum="+this.pageNum+"&pageSize=" + this.pageSize)
.then(res => res.json()).then(res => {
console.log(res)
this.tableData = res.data
this.total = res.total
})
},
handleSizeChange(pageSize){
console.log(pageSize)
this.pageSize = pageSize
this.load()
},
handleCurrentChange(pageNum){
console.log(pageNum)
this.pageNum = pageNum
this.load()
}
}
实现分页功能:
controller:
@GetMapping("/page")
public Map<String, Object> findPage(@RequestParam Integer pageNum, @RequestParam Integer pageSize,@RequestParam String username){
pageNum = (pageNum-1)*pageSize;
List<User> data = userMapper.selectPage(pageNum,pageSize,username);
Integer total = userMapper.selectTotal();
Map<String,Object> res = new HashMap<>();
res.put("data",data);
res.put("total",total);
return res;
}
mapper:
@Select("select * from sys_user where username like concat('%', #{username}, '%') limit #{pageNum},#{pageSize}")
List<User> selectPage(Integer pageNum, Integer pageSize,String username);
测试一下:
在前端绑定:
<div style="margin: 10px 0">
<el-input style="width: 200px" placeholder="请输入名称" suffix-icon="el-icon-search" v-model="username"></el-input>
<el-input style="width: 200px" placeholder="请输入邮箱" suffix-icon="el-icon-message" class="ml-5"></el-input>
<el-input style="width: 200px" placeholder="请输入地址" suffix-icon="el-icon-position" class="ml-5"></el-input>
<el-button class="ml-5" type="primary" @click="load">搜索</el-button>
</div>
data() {
return {
tableData: [],
total: 0,
pageNum: 1,
pageSize: 2,
username: "",
msg: "hello 阿晴",
collapseBtnClass: 'el-icon-s-fold',
isCollapse: false,
sideWidth: 200,
logoTextShow: true,
headerBg: 'headerBg'
}
},
load() {
fetch("http://localhost:9090/user/page?pageNum="+this.pageNum+"&pageSize=" + this.pageSize+"&username="+this.username)
.then(res => res.json()).then(res => {
console.log(res)
this.tableData = res.data
this.total = res.total
})
},
结语:坚定目标,日日精进,必有所成。共勉! !