vue+axios+mysql实现分页查询，条件查询以及批量删除
感觉里面的动态代理写的还行
·
vue学的不多直接上前端页面代码，前端的东西只有写进vue的范围内才能生效，能所的和解析全都在代码的注释中
<div id="app">
<!--搜索表单-->
<el-form :inline="true" :model="brand" class="demo-form-inline">
<el-form-item label="当前状态">
<el-select v-model="brand.status" placeholder="当前状态">
<el-option label="启用" value="1"></el-option>
<el-option label="禁用" value="0"></el-option>
</el-select>
</el-form-item>
<el-form-item label="企业名称">
<el-input v-model="brand.companyName" placeholder="企业名称"></el-input>
</el-form-item>
<el-form-item label="品牌名称">
<el-input v-model="brand.brandName" placeholder="品牌名称"></el-input>
</el-form-item>
<el-form-item>
<el-button type="primary" @click="onSubmit">查询</el-button>
</el-form-item>
</el-form>
<!--按钮-->
<el-row>
<el-button type="danger" plain @click="deleteByids">批量删除</el-button>
<el-button type="primary" plain @click="dialogVisible = true">新增</el-button>
</el-row>
<!--添加数据对话框表单-->
<el-dialog
title="编辑品牌"
:visible.sync="dialogVisible"
width="30%"
>
<el-form ref="form" :model="brands" label-width="80px">
<el-form-item label="品牌名称">
<el-input v-model="brands.brandName"></el-input>
</el-form-item>
<el-form-item label="企业名称">
<el-input v-model="brands.companyName"></el-input>
</el-form-item>
<el-form-item label="排序">
<el-input v-model="brands.ordered"></el-input>
</el-form-item>
<el-form-item label="备注">
<el-input type="textarea" v-model="brands.description"></el-input>
</el-form-item>
<el-form-item label="状态">
<el-switch v-model="brands.status"
active-value="1"
inactive-value="0"
></el-switch>
</el-form-item>
<el-form-item>
<el-button type="primary" @click="addBrand">提交</el-button>
<el-button @click="dialogVisible = false">取消</el-button>
</el-form-item>
</el-form>
</el-dialog>
<!--表格-->
<template>
<el-table
:data="tableData"
style="width: 100%"
:row-class-name="tableRowClassName"
@selection-change="handleSelectionChange">
<el-table-column
type="selection"
width="55">
</el-table-column>
<el-table-column
type="index"
width="50">
</el-table-column>
<el-table-column
prop="brandName"
label="品牌名称"
align="center"
>
</el-table-column>
<el-table-column
prop="companyName"
label="企业名称"
align="center"
>
</el-table-column>
<el-table-column
prop="ordered"
align="center"
label="排序">
</el-table-column>
<el-table-column
prop="statusStr"
align="center"
label="当前状态">
</el-table-column>
<el-table-column
align="center"
label="操作">
<el-row>
<el-button type="primary">修改</el-button>
<el-button type="danger">删除</el-button>
</el-row>
</el-table-column>
</el-table>
</template>
<!--分页工具条-->
<el-pagination
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
:current-page="currentPage"
:page-sizes="[5, 10, 15, 20]"
:page-size="5"
layout="total, sizes, prev, pager, next, jumper"
:total="totalCount">
</el-pagination>
</div>
<script src="js/vue.js"></script>
<script src="element-ui/lib/index.js"></script>
<link rel="stylesheet" href="element-ui/lib/theme-chalk/index.css">
<script src="js/axios-0.18.0.js"></script>
<!--vue+axios-->
<script>
//注意let vue变量的使用，在munted（）中的方法是没有办法使用vue变量的，因munted中的方法只有在vue框架初始化完后才会执行，所以vue变量还无法使用。
let vue = new Vue({
el: "#app",
mounted(){
//当页面加载完成后，发送异步请求，获取数据
this.selectAll();
/* var _this = this;
axios({
method:"get",
url:"http://localhost:8080/brand-case/selectAllServlet"
}).then(function (resp) {
_this.tableData = resp.data;
})*/
},
methods: {
//批量删除数据
deleteByids(){
//console.log("我在"+this.multipleSelection[0].id)
this.multipleSelection.forEach((item)=>{
this.ids.push(item.id)
})
console.log(this.ids)
if (this.ids.length==0){
this.$message({
type:'success',
message:'请先选择需要删除的信息'
})
}else{
this.$confirm('你确定吗？','提示',{
confirmButtonText:"确认删除？",
concelButtonText:"取消",
type:'warning'
}).then(function (resp){
axios({
method:"post",
url:"/day14Brand/brand/deleteByIdsServlet",
data:vue.ids
}).then(function (resp){
if (resp.data>0){
vue.selectAll();
}
})
})
}
},
// 查询所有数据.....
// 分页查询
selectAll(){
var _this = this;
/* axios({
method:"get",
url:"/day14Brand/brand/selectAllServlet"
}).then(function (resp) {
_this.tableData = resp.data;
})*/
axios({
// method:"get",
method:"post",
//url:"/day14Brand/brand/selectByPageServlet?currentPage="+_this.currentPage+"&pageSize="+_this.pageSize
url:"/day14Brand/brand/selectByPageandConditionServlet?currentPage="+_this.currentPage+"&pageSize="+_this.pageSize,
data:_this.brand,
}).then(function (resp){
//_this.tableData=resp.data.brandList
_this.tableData=resp.data.brandList
//设置总条目数
//_this.totalCount=resp.data.count
_this.totalCount=resp.data.count
console.log(resp.data)
})
},
tableRowClassName({row, rowIndex}) {
if (rowIndex === 1) {
return 'warning-row';
} else if (rowIndex === 3) {
return 'success-row';
}
return '';
},
// 复选框选中后执行的方法
handleSelectionChange(val) {
this.multipleSelection = val;
},
// 查询方法
onSubmit() {
// console.log(this.brand);
/* axios({
method:"post",
url:"/day14Brand/brand/selectByPageandConditionServlet?currentPage="+vue.currentPage+"&pageSize="+vue.pageSize,
data:vue.brand
}).then(function (resp){
vue.tableData=resp.data.brandList
//设置总条目数
vue.totalCount=resp.data.count
})*/
//直接将selectAll方法改进变为“post”，可以传递参数，如果使用了条件查询就将有内容的brand传给后台
//如果没有条件就传给后台一个null，相当于查询全部
vue.selectAll()
},
// 添加数据
addBrand() {
//console.log(this.brand);
var _this = this;
// 发送ajax请求，添加数据
axios({
method:"post",
url:"/day14Brand/brand/addBrandServlet",
data:_this.brands
}).then(function (resp) {
if(resp.data > 0){
//添加成功
//关闭窗口
_this.dialogVisible = false;
// 重新查询数据
_this.selectAll();
// 弹出消息提示
_this.$message({
message: '恭喜你，添加成功',
type: 'success'
});
}
if (resp.data == -1){
_this.$message({
message: '品牌名称不能为空',
type: 'success'
});
}
if (resp.data == -2){
_this.$message({
message: '品公司名称不能为空',
type: 'success'
});
}
})
},
//分页
handleSizeChange(val) {
// console.log(`每页 ${val} 条`);
//设置每一页的分页查询
vue.pageSize=val
vue.selectAll()
},
handleCurrentChange(val) {
//console.log(`当前页: ${val}`);
//重新设置当前页码
vue.currentPage=val
vue.selectAll()
}
},
data() {
return {
//每页显示数
pageSize:5,
//总条目数
totalCount:0,
// 当前页码
currentPage: 1,
// 添加数据对话框是否展示的标记
dialogVisible: false,
// 品牌模型数据
brand: {
status: '',
brandName: '',
companyName: '',
id: "",
ordered: "",
description: ""
},
brands: {
status: '',
brandName: '',
companyName: '',
id: "",
ordered: "",
description: ""
},
// 复选框选中数据集合，点击复选框后一行数据以对象为单位全部保存进该数组
multipleSelection:[],
//保存选中的id号
ids:[],
// 表格数据，表格中selectAll中的数据虽然很多但是用来显示的知识一部分
tableData: []
}
}
})
</script>
附上brandMapper.java代码
public interface BrandMapper {
List<Brand> selectAll();
int addBrand(Brand brand);
int deleteByIds(@Param("ids") int[] id);
@Select(" select * from tb_brand limit #{start},#{size}")
List<Brand> selectByPage(@Param("start") int start,@Param("size") int size);
@Select("select count(*) from tb_brand")
int selectCount();
// 分页条件查询
List<Brand> selectByPageandCondition(@Param("start") int start,@Param("size") int size,@Param("brand") Brand brand);
// 条件查询的总记录数
int selectCountByCondition(Brand brand);
}
下面附上brandMapper.xml的代码
<mapper namespace="cn.mapper.BrandMapper">
<select id="selectAll" resultType="cn.pojo.Brand">
select *
from tb_brand
</select>
<insert id="addBrand">
insert into tb_brand
values (null, #{brandName}, #{companyName}, #{ordered}, #{description}, #{status})
</insert>
<!--批量删除-->
<delete id="deleteByIds">
delete from tb_brand
<where>
<foreach collection="ids" item="id" open="id in (" separator="," close=")">
#{id}
</foreach>
</where>
</delete>
<!--条件查询-->
<!--brand.brandName这样写是因为写了@Param("brand")而brand是一个对象，里面有很多属性-->
<select id="selectByPageandCondition" resultType="cn.pojo.Brand">
select * from tb_brand
<where>
<if test="brand.brandName!=null and brand.brandName!=''">
and brand_name like concat("%",#{brand.brandName},"%")
</if>
<if test="brand.companyName!=null and brand.companyName!=''">
and company_name like concat("%",#{brand.companyName},"%")
</if>
<if test="brand.status !=null">
and status like concat("%",#{brand.status},"%")
</if>
</where>
limit #{start} ,#{size}
</select>
<!-- 条件查询，查询总记录数-->
<select id="selectCountByCondition" resultType="java.lang.Integer">
select count(*) from tb_brand
<where>
<if test="brandName!=null and brandName!=''">
and brand_name like concat("%",#{brandName},"%")
</if>
<if test="companyName!=null and companyName!=''">
and company_name like concat("%",#{companyName},"%")
</if>
<if test="status !=null">
and status like concat("%",#{status},"%")
</if>
</where>
</select>
</mapper>
Brand实体类就不贴了，附上一个用来辅助完成分页查询的PageBean实体类，为什么有这个类，是因为我们进行分页查询返回给前端的数据应该包含，一个Brand类型的集合存放查询出来的brand对象，以及一个数据库中的所有数据的总数count显示在页面下方，但是我们需要一起传给前端，所以我们应该将他们两个封装成一个类一起响应给前端，但是在前端需要注意，后端JSON传过去的格式：
所以前端的接收格式：
// 分页查询
selectAll(){
var _this = this;
/* axios({
method:"get",
url:"/day14Brand/brand/selectAllServlet"
}).then(function (resp) {
_this.tableData = resp.data;
})*/
axios({
// method:"get",
method:"post",
//url:"/day14Brand/brand/selectByPageServlet?currentPage="+_this.currentPage+"&pageSize="+_this.pageSize
url:"/day14Brand/brand/selectByPageandConditionServlet?currentPage="+_this.currentPage+"&pageSize="+_this.pageSize,
data:_this.brand,
}).then(function (resp){
//此处是接收后端传过来的pageBeanJson串
_this.tableData=resp.data.brandList
//设置总条目数
_this.totalCount=resp.data.count
console.log(resp.data)
})
},
为什么分页查询全部和条件查询用的是同一个方法：
因为其实两者的sql语句都差不多，只是条件查询需要传入一个brand对象作为参数，
我们把selectAll方法该为post的请求方式就可以传递参数，如果brand==null那么就和查询全部一样，如果brand !=null那么就是有参数就是条件查询。
下面解释批量删除的前端处理操作
，如果我们将选中的数据全部给后端，那么当数据量太大时就会给cpu造成大量的负担所以我们就只将选中的行的id变成集合传给后端即可：
this.multipleSelection.forEach((item)=>{
this.ids.push(item.id)
})
//data中的数据
// 复选框选中数据集合，点击复选框后一行数据以对象为单位全部保存进该数组
multipleSelection:[],
Service
该方法是为了让Servlet专心的做数据的接收和发送，所以将对数据操作分离出来，里面的MapperFactory，是为了省去重复性的创建session的操作写的动态代理类，BrandService.java：
public class BrandService {
//查询所有商品
public List<Brand> selectAll(){
/* SqlSession sqlSession;
BrandMapper mapper=null;
List<Brand> brands = null;
try {
sqlSession = SqlSessionFactorys.getSqlSession();
mapper = sqlSession.getMapper(BrandMapper.class);
brands = mapper.selectAll();
} catch (Exception e) {
e.printStackTrace();
}*/
List<Brand> brands = MapperFactory.getMapper(BrandMapper.class).selectAll();
return brands;
}
// 添加商品
public int addBrand(Brand brand){
int row = MapperFactory.getMapper(BrandMapper.class).addBrand(brand);
if (brand.getBrandName()==null||brand.getBrandName()==""){
return -1;
}
if (brand.getCompanyName()==null||brand.getCompanyName()==""){
return -2;
}
return row;
}
//批量删除商标
public int deleteByIds(int[] id){
int i = MapperFactory.getMapper(BrandMapper.class).deleteByIds(id);
return i;
}
// 分页查询
public List<Brand> selectByPage(int start,int size){
List<Brand> brands = MapperFactory.getMapper(BrandMapper.class).selectByPage(start, size);
return brands;
}
// 计算总数据
public int selectCount(){
int i = MapperFactory.getMapper(BrandMapper.class).selectCount();
return i;
}
//分页条件查询
public List<Brand> selectByPageandCondition(int start,int size,Brand brand){
List<Brand> brands = MapperFactory.getMapper(BrandMapper.class).selectByPageandCondition(start,size,brand);
return brands;
}
//分页条件查询总条目数
public int selectCountByCondition(Brand brand){
int i = MapperFactory.getMapper(BrandMapper.class).selectCountByCondition(brand);
return i;
}
}
Util：
MapperFactorys.java：
public class MapperFactory {
public static<T> T getMapper(final Class<T> clazz){
T o = (T) Proxy.newProxyInstance(clazz.getClassLoader(), new Class[]{clazz}, new InvocationHandler() {
public Object invoke(Object o, Method method, Object[] objects) throws Throwable {
//SqlSessionFactorys是自己写的工具类
SqlSession sqlSession = SqlSessionFactorys.getSqlSession();
T mapper = sqlSession.getMapper(clazz);
Object invoke = method.invoke(mapper,objects);
sqlSession.commit();
return invoke;
}
});
return o;
}
}
SqlSessionFactorys.java:
public class SqlSessionFactorys {
public static SqlSession getSqlSession() throws Exception{
//解析配置文件
InputStream in = Resources.getResourceAsStream("Mybatis-config.xml");
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(in);
//创建session
SqlSession sqlSession = sqlSessionFactory.openSession();
return sqlSession;
}
}
servlet的编写
为了减少servlet文件的个数，以前一个servlet文件只能完成一个操作，所以写一个父类，将子类的访问路径变成目录访问，用来帮助简化servlet。
BaseServlet.java:
public class BaseServlet extends HttpServlet {
@SneakyThrows
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//因为访问路径和方法名字一样
String requestURI = req.getRequestURI();
int i = requestURI.lastIndexOf("/");
String methodName = requestURI.substring(i + 1);
// 使用反射的方法进行方法的调用，this就是代指继承这个类的子类
Class clazz = this.getClass();
Method declaredMethod = clazz.getDeclaredMethod(methodName, HttpServletRequest.class, HttpServletResponse.class);
declaredMethod.setAccessible(true);
declaredMethod.invoke(this,req,resp);
}
}
继承BaseServlet.java的子类BrandServlet.java:
@WebServlet("/brand/*")
public class BrandServlet extends BaseServlet{
// 查询所有
public void selectAllServlet(HttpServletRequest req, HttpServletResponse resp) throws Exception {
req.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=utf-8");
BrandService brandService = new BrandService();
List<Brand> brands = brandService.selectAll();
String string = JSON.toJSONString(brands);
System.out.println("json"+string);
resp.getWriter().write(string);
}
// 添加用户
public void addBrandServlet(HttpServletRequest req, HttpServletResponse resp) throws Exception{
req.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=utf-8");
BufferedReader reader = req.getReader();
String s = reader.readLine();
Brand brand = JSON.parseObject(s, Brand.class);
System.out.println("brand"+brand);
BrandService brandService = new BrandService();
int i = brandService.addBrand(brand);
resp.getWriter().write(i+"");
}
// 批量删除
public void deleteByIdsServlet(HttpServletRequest req,HttpServletResponse resp) throws Exception{
req.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=utf-8");
BufferedReader reader = req.getReader();
String s = reader.readLine();
// 封装成类
int[] ids = JSON.parseObject(s, int[].class);
System.out.println("后端"+ids);
BrandService brandService = new BrandService();
int i = brandService.deleteByIds(ids);
resp.getWriter().write(i+"");
}
// 分页查询,有了分页条件查询这个就没用了
public void selectByPageServlet(HttpServletRequest req,HttpServletResponse resp) throws Exception{
req.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=utf-8");
//获取当前页和每页显示条目数
int currentPage = Integer.parseInt(req.getParameter("currentPage"));
int pageSize =Integer.parseInt(req.getParameter("pageSize"));
int start = (currentPage-1)*pageSize;
System.out.println("页码"+currentPage+"xianshi"+pageSize);
// 进行分页查询
BrandService brandService = new BrandService();
// 创建一个PageBean用来封装
PageBean<Brand> pageBean = new PageBean<Brand>();
List<Brand> brands = brandService.selectByPage(start, pageSize);
int count = brandService.selectCount();
// 封装
pageBean.setBrandList(brands);
pageBean.setCount(count);
// 将对象转成字节
String string = JSON.toJSONString(pageBean);
// 传给前端
resp.getWriter().write(string);
}
// 分页条件查询
public void selectByPageandConditionServlet(HttpServletRequest req,HttpServletResponse resp) throws Exception{
req.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=utf-8");
//获取当前页和每页显示条目数
int currentPage = Integer.parseInt(req.getParameter("currentPage"));
int pageSize =Integer.parseInt(req.getParameter("pageSize"));
int start = (currentPage-1)*pageSize;
System.out.println("页码"+currentPage+"xianshi"+pageSize);
// 接收前端的给的条件
BufferedReader reader = req.getReader();
String s = reader.readLine();
// 将前端传过来的条件封装成Brand对象
Brand brand = JSON.parseObject(s, Brand.class);
System.out.println("前端"+brand);
// 进行条件分页查询
BrandService brandService = new BrandService();
// 创建一个PageBean用来封装
PageBean<Brand> pageBean = new PageBean<Brand>();
List<Brand> brands = brandService.selectByPageandCondition(start,pageSize,brand);
int count = brandService.selectCountByCondition(brand);
System.out.println("当搜索条件为空时："+count+"搜索到的商标"+brands);
// 封装
pageBean.setBrandList(brands);
pageBean.setCount(count);
// 将对象转成字节
String string = JSON.toJSONString(pageBean);
System.out.println(string);
// 传给前端
resp.getWriter().write(string);
}
}
为开发者提供学习成长、分享交流、生态实践、资源工具等服务，帮助开发者快速成长。
更多推荐
1
0- 0
已为社区贡献2条内容
所有评论(0)