采用XML映射文件的形式来映射sql语句
controller
import com.example.service.EquipService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
@RestController
public class EquipFenyeController {
@Autowired
EquipFenyeService equipFenyeService;
@GetMapping("/equip/{page}/{pageSize}")
public Result findAll(@PathVariable Integer page,
@PathVariable Integer pageSize){
PageBean pageBean = equipFenyeService.list(page,pageSize);
return Result.success(pageBean);
}
@GetMapping("/equip2/{page}/{pageSize}")
public Result findAll_chaxun(@PathVariable Integer page,
@PathVariable Integer pageSize,
@PathVariable String eno,
@PathVariable String ename){
PageBean pageBean = equipFenyeService.list_chaxun(page,pageSize,eno,ename);
return Result.success(pageBean);
}
}
mapper
package com.example.mapper;
import com.example.pojo.Equip;
import org.apache.ibatis.annotations.*;
import java.util.List;
@Mapper
public interface EquipMapper {
@Select("select * from equip")
public List<Equip> findAll() ;
//带条件的查询
@Select("select * from equip where eno like concat('%',#{eno},'%') and ename like concat('%',#{ename},'%')")
public List<Equip> list_chaxun(String eno, String ename);}
mapper.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.mapper.EquipMapper">
<!-- 查询 -->
<select id="list" resultType="com.example.pojo.Equip">
select * from equip
</select>
<!-- 条件分页查询 -->
<select id="list_chaxun" resultType="com.example.pojo.Equip">
select * from equip
<where>
<if test="eno != null and eno != ''">
eno like concat('%',#{eno},'%')
</if>
<if test="ename != null and ename != ''">
and ename like concat('%',#{ename},'%')
</if>
</where>
</select>
</mapper>
pagebean
package com.example.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;
/**
* 分页查询结果封装类
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class PageBean {
private Long total;//总记录数
private List rows;//数据列表
}
impl.EquipServiceB
package com.example.service.impl;
import com.example.mapper.EquipMapper;
import com.example.pojo.PageBean;
import com.example.pojo.Equip;
import com.example.service.EquipFenyeService;
import com.example.service.EquipService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
// 分页查询
@Service
public class EquipServiceB implements EquipFenyeService {
@Autowired
private EquipMapper equipMapper;
@Override
public PageBean list(Integer page,Integer pageSize) {
//问:PageHelper.startPage(page, pageSize); 请解释一下
// 设置分页参数
PageHelper.startPage(page, pageSize);
// 执行分页查询
List<Equip> equipList = equipMapper.findAll();
// System.out.println(equipList);
// 获取分页结果
PageInfo<Equip> p = new PageInfo<>(equipList);
//封装PageBean
PageBean pageBean = new PageBean(p.getTotal(), p.getList());
return pageBean;
}
@Override
public PageBean list_chaxun(Integer page,Integer pageSize,String eno,String ename) {
// 设置分页参数
PageHelper.startPage(page, pageSize);
// 执行分页查询
List<Equip> equipList = equipMapper.list_chaxun(eno,ename);
// System.out.println(equipList);
// 获取分页结果
PageInfo<Equip> p = new PageInfo<>(equipList);
//封装PageBean
PageBean pageBean = new PageBean(p.getTotal(), p.getList());
return pageBean;
}
}
service
package com.example.service;
import com.example.pojo.PageBean;
import com.example.pojo.Equip;
import java.util.List;
public interface EquipFenyeService {
public PageBean list(Integer page,Integer pageSize);
public PageBean list_chaxun(Integer page, Integer pageSize,String eno,String ename);
}
html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>5分页加搜索,两个条件可填可不填</title>
<link rel="stylesheet" href="js/element.css">
</head>
<body>
<div id="app">
<h1 align="center">器材表</h1>
<p align="center">
<el-form :inline="true" :model="formInline" class="demo-form-inline">
<el-form-item label="器材号">
<el-input v-model="formInline.eno" placeholder="器材号"></el-input>
</el-form-item>
<el-form-item label="器材名称">
<el-input v-model="formInline.ename" placeholder="器材名称"></el-input>
</el-form-item>
<el-form-item>
<el-button type="primary" @click="onSubmit">查询</el-button>
</el-form-item>
</el-form>
</p>
<el-table
:data="equipList.filter(equip => !search || equip.eno.includes(search))"
style="width: 100%">
<el-table-column
label="id"
prop="id">
</el-table-column>
<el-table-column
label="器材号"
prop="eno">
</el-table-column>
<el-table-column
label="器材名"
prop="ename">
</el-table-column>
<el-table-column
label="课程号"
prop="cno">
</el-table-column>
<el-table-column
align="right">
<template slot="header" slot-scope="scope">
<el-input
v-model="search"
size="mini"
placeholder="输入关键字搜索"/>
</template>
<template slot-scope="scope">
<el-button
size="mini"
@click="handleEdit(scope.$index, scope.row)">Edit</el-button>
<el-button
size="mini"
type="danger"
@click="handleDelete(scope.$index, scope.row)">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="[3, 5, 10, 20]"
:page-size="pageSize"
:total="total">
</el-pagination>
</p>
</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: {
search: '',
currentPage: 1,
pageSize: 3,
total: null,
equipList: [],
formInline: {
eno: '',
ename: ''
}
},
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 = `/equip2/${this.currentPage}/${this.pageSize}?eno=${encodeURIComponent(this.formInline.eno)}&ename=${encodeURIComponent(this.formInline.ename)}`
console.log(this.formInline.eno);
console.log(this.formInline.ename);
axios.get(url)
.then(res =>{
this.equipList = res.data.data.rows;
this.total=res.data.data.total;
console.log(this.equipList);
console.log(this.total);/*
console.log(this.formInline.eno);
console.log(this.formInline.ename);*/
})
.catch(error=>{
console.error(error);
})
},
findAll() {
var url = `/equip2/${this.currentPage}/${this.pageSize}`
axios.get(url)
.then(res => {
this.equipList = res.data.data.rows;
console.log(this.equipList);
this.total=res.data.data.total;
console.log(this.total);
})
.catch(error => {
console.error(error);
})
}
},
created() {
this.findAll();
}
})
</script>
</body>
</html>
git