1. Navicat查询数据
- Navicat中查询所有数据
SELECT * FROM sys_user;
- Navicat中查询前两条数据（俩种方式）
SELECT * FROM sys_user LIMIT 2;
//从0开始，第一个参数是起始位置即(pageNum-1)*pageSize，第二个参数是步长
SELECT * FROM sys_user LIMIT 0,2;
2. 分页查询最主要的就是对pageNum的处理
3. 操作：
- 传入pageNum、pageSize，并使用postman测试成功与否。
//UserController.java传入pageNum、pageSize
@GetMapping("/page")
public Map<String,Object> findPage(@RequestParam Integer pageNum, @RequestParam Integer pageSize) {
pageNum = (pageNum - 1) * pageSize;
return (Map<String, Object>) userMapper.selectPage(pageNum,pageSize);//UserMapper.java传入pageNum、pageSize
@Select("select * from sys_user limit #{pageNum}, #{pageSize}")
List<User> selectPage(@Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize);- 使用findAll接口查询总条数
UserController.java写一个新方法selectPage查询总条数，封装一个结果参数Map并返回。
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.seleceTotal();
Map<String,Object> res = new HashMap<>();
res.put("data",data);
res.put("total",total);
return res;
}- 结合前端页面查看需要绑定的数据
分页数据total和表格数据data要相关联
3.1 前后关联Home.vue添加create函数
created(){
//请求分页查询数据
this.load()
},
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
})
},- 前端报错，这是因为不同端口请求数据出现了跨域
解决方法：后端设置跨域。
新建包config–>new一个java class–>添加代码(包名不用粘贴)
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.cors.CorsConfiguration;
import org.springframework.web.cors.UrlBasedCorsConfigurationSource;
import org.springframework.web.filter.CorsFilter;
@Configuration
public class CorsConfig {
// 当前跨域请求最大有效时长。这里默认1天
private static final long MAX_AGE = 24 * 60 * 60;
@Bean
public CorsFilter corsFilter() {
UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource();
CorsConfiguration corsConfiguration = new CorsConfiguration();
corsConfiguration.addAllowedOrigin("http://localhost:8080"); // 1 设置访问源地址
corsConfiguration.addAllowedHeader("*"); // 2 设置访问源请求头
corsConfiguration.addAllowedMethod("*"); // 3 设置访问源请求方法
corsConfiguration.setMaxAge(MAX_AGE);
source.registerCorsConfiguration("/**", corsConfiguration); // 4 对接口配置跨域设置
return new CorsFilter(source);
}
}- 数据有了，改表格渲染
<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="电话" width="120"></el-table-column>
<el-table-column prop="address" label="地址" width="120"></el-table-column>- 拼接绑定前端传来的pageNum、pageSize 及后端数据请求完后的total
- 关键一步：触发
@size-change="handleSizeChange"
@current-change="handleCurrentChange"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
})
},
handleSizeChange(pageSize){
console.log(pageSize)
this.pageSize = pageSize
this.load()
},
handleCurrentChange(pageNum){
console.log(pageNum)
this.pageNum = pageNum
this.load()
}修改后的各部分
UserMapper.java
package com.qingge.springboot.mapper;
import com.qingge.springboot.entity.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface UserMapper {
@Select("SELECT * from sys_user")
List<User> findAll();
@Insert("INSERT into sys_user(username, password,nickname,email,phone,address) VALUES (#{username}, #{password}," +
" #{nickname}, #{email},#{phone}, #{address})")
int insert(User user);
int update(User user);
@Delete("delete from sys_user where id = #{id}")
Integer deleteById(@Param("id") Integer id);
@Select("select * from sys_user where username like #{username} limit #{pageNum}, #{pageSize}")
List<User> selectPage(@Param("pageNum") Integer pageNum, @Param("pageSize") Integer pageSize, @Param("username") String username);
@Select("select count(*) from sys_user where username like concat('%',#{username},'%')")
Integer seleceTotal(String username);
}UserController.java
package com.qingge.springboot.controller;
import com.qingge.springboot.entity.User;
import com.qingge.springboot.mapper.UserMapper;
import com.qingge.springboot.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserMapper userMapper;
@Autowired
private UserService userService;
// 新增和修改
@PostMapping
public Integer save(@RequestBody User user) {
// 新增或者更新
return userService.save(user);
}
// 查询所有数据
@GetMapping
public List<User> findall() {
List<User> all = userMapper.findAll();
return all;
}
@DeleteMapping("/{id}")
public Integer delete(@PathVariable Integer id) {
return userMapper.deleteById(id);
}
// 分页查询
// 接口路径：/user/page?pageNum=1&pageSize=10
// @RequestParam接受
// limit第一个参数 = (pageNum - 1) * pageSize
// pageSize
@GetMapping("/page")
public Map<String,Object> findPage(@RequestParam Integer pageNum,
@RequestParam Integer pageSize,
@RequestParam String username) {
pageNum = (pageNum - 1) * pageSize;
username = "%" + username + "%";
List<User> data = userMapper.selectPage(pageNum,pageSize,username);
Integer total = userMapper.seleceTotal(username);
Map<String,Object> res = new HashMap<>();
res.put("data",data);
res.put("total",total);
return res;
}
}User.java
package com.qingge.springboot.entity;
import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
public class User {
private Integer id;
private String username;
@JsonIgnore
private String password;
private String nickname;
private String email;
private String phone;
private String address;
}CorsConfig.java
package com.qingge.springboot.config;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.cors.CorsConfiguration;
import org.springframework.web.cors.UrlBasedCorsConfigurationSource;
import org.springframework.web.filter.CorsFilter;
@Configuration
public class CorsConfig {
// 当前跨域请求最大有效时长。这里默认1天
private static final long MAX_AGE = 24 * 60 * 60;
@Bean
public CorsFilter corsFilter() {
UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource();
CorsConfiguration corsConfiguration = new CorsConfiguration();
corsConfiguration.addAllowedOrigin("http://localhost:8080"); // 1 设置访问源地址
corsConfiguration.addAllowedHeader("*"); // 2 设置访问源请求头
corsConfiguration.addAllowedMethod("*"); // 3 设置访问源请求方法
corsConfiguration.setMaxAge(MAX_AGE);
source.registerCorsConfiguration("/**", corsConfiguration); // 4 对接口配置跨域设置
return new CorsFilter(source);
}
}HomeView.vue
<template>
<el-container style="min-height: 100vh">
<el-aside :width="sideWidth + 'px'" style="box-shadow: 2px 0 6px rgb(0 21 41 );">
<el-menu :default-openeds="['1', '3']" style="min-height: 100%; overflow-x: hidden"
background-color="rgb(48, 65, 86)"
text-color="#fff"
active-text-color="#ffd04b"
:collapse-transition="false"
:collapse="isCollapse"
>
<div style="height: 60px; line-height: 60px; text-align: center">
<img src="../assets/logo.png" alt="" style="width: 20px; position: relative; top: 5px; right: 5px">
<b style="color: white" v-show="logoTextShow">后台管理系统</b>
</div>
<el-submenu index="1">
<template slot="title">
<i class="el-icon-message"></i>
<span slot="title">导航一</span>
</template>
<el-menu-item-group title="分组2">
<el-menu-item index="1-3">选项3</el-menu-item>
</el-menu-item-group>
<el-submenu index="1-4">
<template slot="title">选项4</template>
<el-menu-item index="1-4-1">选项4-1</el-menu-item>
</el-submenu>
</el-submenu>
<el-submenu index="2">
<template slot="title">
<i class="el-icon-menu"></i>
<span slot="title">导航二</span>
</template>
<el-submenu index="2-4">
<template slot="title">选项4</template>
<el-menu-item index="2-4-1">选项4-1</el-menu-item>
</el-submenu>
</el-submenu>
<el-submenu index="3">
<template slot="title">
<i class="el-icon-setting"></i>
<span slot="title">导航三</span>
</template>
<el-submenu index="3-4">
<template slot="title">选项4</template>
<el-menu-item index="3-4-1">选项4-1</el-menu-item>
</el-submenu>
</el-submenu>
</el-menu>
</el-aside>
<el-container>
<el-header style="font-size: 12px; border-bottom: 1px solid #ccc; line-height: 60px; display: flex">
<div style="flex: 1; font-size: 20px">
<span :class="collapseBtnClass" style="cursor: pointer" @click="collapse"></span>
</div>
<el-dropdown style="width: 70px; cursor: pointer">
<span>王小虎</span><i class="el-icon-arrow-down" style="margin-left: 5px"></i>
<el-dropdown-menu slot="dropdown" >
<el-dropdown-item style="font-size: 14px; padding: 5px 0">个人信息</el-dropdown-item>
<el-dropdown-item style="font-size: 14px; padding: 5px 0">退出</el-dropdown-item>
</el-dropdown-menu>
</el-dropdown>
</el-header>
<el-main>
<div style="margin-bottom: 30px">
<el-breadcrumb separator="/">
<el-breadcrumb-item :to="{ path: '/' }">首页</el-breadcrumb-item>
<el-breadcrumb-item>用户管理</el-breadcrumb-item>
</el-breadcrumb>
</div>
<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>
<div style="margin: 10px 0">
<el-button type="primary">新增 <i class="el-icon-circle-plus-outline"></i></el-button>
<el-button type="danger">批量删除 <i class="el-icon-remove-outline"></i></el-button>
<el-button type="primary">导入 <i class="el-icon-bottom"></i></el-button>
<el-button type="primary">导出 <i class="el-icon-top"></i></el-button>
</div>
<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="电话" width="120"></el-table-column>
<el-table-column prop="address" label="地址" width="120"></el-table-column>
<el-table-column label="操作" width="200" align="center">
<template slot-scope="scope">
<el-button type="success">编辑 <i class="el-icon-edit"></i></el-button>
<el-button type="danger">删除 <i class="el-icon-remove-outline"></i></el-button>
</template>
</el-table-column>
</el-table>
<div style="padding: 10px 0">
<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>
</div>
</el-main>
</el-container>
</el-container>
</template>
<script>
export default {
name: 'Home',
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'
}
},
created(){
//请求分页查询数据
this.load()
},
methods: {
collapse() { // 点击收缩按钮触发
this.isCollapse = !this.isCollapse
if (this.isCollapse) { // 收缩
this.sideWidth = 64
this.collapseBtnClass = 'el-icon-s-unfold'
this.logoTextShow = false
} else { // 展开
this.sideWidth = 200
this.collapseBtnClass = 'el-icon-s-fold'
this.logoTextShow = true
}
},
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
})
},
handleSizeChange(pageSize){
console.log(pageSize)
this.pageSize = pageSize
this.load()
},
handleCurrentChange(pageNum){
console.log(pageNum)
this.pageNum = pageNum
this.load()
}
}
}
</script>
<style>
.headerBg {
background: #eee!important;
}
</style>
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
