PageHelper
PageHelper介绍
PageHelper 是mybatis 的通用插件,通过mybatis 的拦截器实现分页请求,拦截sql查询的请求,添加分页语句,最终实现分页查询功能.
在使用PageHelper实现Mybatis分页共功能开发,本项目使用springboot开发,
在spirngboot上集成pagehelper
PageHelper 的使用方法及原理如下:
在调用dao的service方法中设置分页参数:
PageHelper.startPage(page,size),分页参数会设置在ThreedLocal中PageHelp在mybatis执行sql前拦截,从ThreedLocal取出分页参数,修改当前执行的sql语句,添加分页sql.
最后执行添加了分页的sql的sql语句,实现分页查询
添加pagehelper依赖
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.4</version>
</dependency>
2.配置pagehelper
在application.yml中配置pageHelper操作的数据库类型:
pagehelper:
auto-dialect: mysql
在CourseMapper中配置方法
Page<CourseBase> findCourseList();
在CourseMapper.xml中
<select id="findCourseList" resultType="com.xuecheng.framework.domain.course.CourseBase">
select * from course_base
</select>
在test中测试方法
@Test
public void testPageHelper(){
// 设置分页参数
// 查询第一页,每一页显示十条
//没写分页语句,但是照样可以打印出来
PageHelper.startPage(1,10);//注意,pagehelper第一页不是0,是1
Page<CourseBase> courseList = courseMapper.findCourseList();
List<CourseBase> result = courseList.getResult();
long total = courseList.getTotal();
System.out.println(result);
}
在logback-spring日志中配置
level为debug
<root level="debug">
debug运行,成功,得到结果!
效果图:
在course_list.vue
<template>
<section>
<el-row >
<el-col :span="8" :offset=2 >
<el-card :body-style="{ padding: '10px' }">
<img src="/static/images/add.jpg" class="image" height="150px">
<div style="padding: 10px;">
<span>课程名称</span>
<div class="bottom clearfix">
<time class="time"></time>
<router-link class="mui-tab-item" :to="{path:'/course/add/base'}">
<el-button type="text" class="button" >新增课程</el-button>
</router-link>
</div>
</div>
</el-card>
</el-col>
<el-col :span="8" v-for="(course, index) in courses" :key="course.id" :offset="index > 0 ? 2 : 2">
<el-card :body-style="{ padding: '10px' }">
<img :src="course.pic!=null?imgUrl+course.pic:'/static/images/nonepic.jpg'" class="image" height="150px">
<div style="padding: 10px;">
<span>{{course.name}}</span>
<div class="bottom clearfix">
<time class="time"></time>
<el-button type="text" class="button" @click="handleManage(course.id)">管理课程</el-button>
</div>
</div>
</el-card>
</el-col>
<!--分页-->
<el-col :span="24" class="toolbar">
<el-pagination background layout="prev, pager, next" @current-change="handleCurrentChange" :page-size="size"
:total="total" :current-page="page"
style="float:right;">
</el-pagination>
</el-col>
</el-row>
</section>
</template>
<script>
import * as courseApi from '../api/course';
import utilApi from '../../../common/utils';
let sysConfig = require('@/../config/sysConfig')
export default {
data() {
return {
page:0,
size:8,
total: 0,
courses: [
{
}
],
sels: [],//列表选中列
imgUrl:sysConfig.imgUrl
}
},
methods: {
//分页方法
handleCurrentChange(val) {
this.page = val;
this.getCourse();
},
//获取课程列表
getCourse() {
courseApi.findCourseListPage(this.page,this.size,{}).then((res) => {
console.log(res);
if(res.success){
this.total = res.queryResult.total;
this.courses = res.queryResult.list;
}
});
},
handleManage: function (id) {
console.log(id)
this.$router.push({ path: '/course/manager/'+id})
}
},
created(){
// this.getCourse();
},
mounted() {
//查询我的课程
this.getCourse();
}
}
</script>
<style scoped>
.el‐col‐8{
width:20%
}
.el‐col‐offset‐2{
margin‐left:2%
}
.time {
font‐size: 13px;
color: #999;
}
.bottom {
margin‐top: 13px;
line‐height: 12px;
}
.button {
padding: 0;
float: right;
}
.image {
width: 100%;
display: block;
}
.clearfix:before,
.clearfix:after {
display: table;
content: "";
}
.clearfix:after {
clear: both
}
</style>
在api中添加
@ApiOperation("查询我的课程列表")
public QueryResponseResult findCourseList(
int page,
int size,
CourseListRequest courseListRequest
);
CourseController中继承api
public class CourseController implements CourseControllerApi {
实现方法
@Override
@GetMapping("/coursebase/list/{page}/{size}")
public QueryResponseResult findCourseList(@PathVariable("page")int page,@PathVariable("size") int size, CourseListRequest courseListRequest) {
return courseService.findCourseListPage( page, size,courseListRequest);
// return null;
}
引入
@Autowired
CourseService courseService;
Service中
public QueryResponseResult findCourseListPage(int page, int size, CourseListRequest courseListRequest) {
if (page <= 0) {
page = 1;
}
if (size <= 0) {
size = 20;
}
PageHelper.startPage(page, size);
Page<CourseInfo> courseListPage = courseMapper.findCourseListPage(courseListRequest);
QueryResult queryResult = new QueryResult();
queryResult.setList(courseListPage.getResult());
queryResult.setTotal(courseListPage.getTotal());
return new QueryResponseResult(CommonCode.SUCCESS, queryResult);
}
CourseMapper:
@Mapper
public interface CourseMapper {
CourseBase findCourseBaseById(String id);
Page<CourseBase> findCourseList();
Page<CourseInfo> findCourseListPage(CourseListRequest courseListRequest);
}
这个页面是点击新增course_add
页面中
<template>
<div>
<el-form :model="courseForm" label-width="80px" :rules="courseRules" ref="courseForm">
<el-form-item label="课程名称" prop="name">
<el-input v-model="courseForm.name" auto-complete="off" ></el-input>
</el-form-item>
<el-form-item label="适用人群" prop="users">
<el-input type="textarea" v-model="courseForm.users" auto-complete="off" ></el-input>
</el-form-item>
<el-form-item label="课程分类" prop="categoryActive">
<el-cascader
expand-trigger="hover"
:options="categoryList"
v-model="categoryActive"
:props="props">
</el-cascader>
</el-form-item>
<el-form-item label="课程等级" prop="grade">
<b v-for="grade in gradeList">
<el-radio v-model="courseForm.grade" :label="grade.sdId" >{{grade.sdName}}</el-radio>
</b>
</el-form-item>
<el-form-item label="学习模式" prop="studymodel">
<b v-for="studymodel_v in studymodelList">
<el-radio v-model="courseForm.studymodel" :label="studymodel_v.sdId" >{{studymodel_v.sdName}}</el-radio>
</b>
</el-form-item>
<el-form-item label="课程介绍" prop="description">
<el-input type="textarea" v-model="courseForm.description" ></el-input>
</el-form-item>
</el-form>
<div slot="footer" class="dialog-footer">
<el-button type="primary" @click.native="save" >提交</el-button>
</div>
</div>
</template>
<script>
import * as courseApi from '../api/course';
import utilApi from '../../../common/utils';
import * as systemApi from '../../../base/api/system';
export default {
data() {
return {
studymodelList:[],
gradeList:[],
props: {
value: 'id',
label:'name',
children:'children'
},
categoryList: [],
categoryActive:[],
courseForm: {
id:'',
name: '',
users: '',
grade:'',
studymodel:'',
mt:'',
st:'',
description: ''
},
courseRules: {
name: [
{required: true, message: '请输入课程名称', trigger: 'blur'}
],
category: [
{required: true, message: '请选择课程分类', trigger: 'blur'}
],
grade: [
{required: true, message: '请选择课程等级', trigger: 'blur'}
],
studymodel: [
{required: true, message: '请选择学习模式', trigger: 'blur'}
]
}
}
},
methods: {
//新增课程提交
save () {
this.$refs.courseForm.validate((valid)=>{
if (valid){
this.$confirm('确认提交么?','提示',{}).then(()=>{
// 当前选择的分类
//处理课程分类
// 选择课程分类存储到categoryActive
this.courseForm.mt= this.categoryActive[0]//大分类
this.courseForm.st= this.categoryActive[1]//小分类
//请求服务接口
courseApi.addCourseBase(this.courseForm).then(res=>{
if(res.success){
this.$message.success("提交成功")
//跳转到我的课程
this.$router.push({ path: '/course/list'})
}else{
if (res.message){
this.$message.error(res.message);
}else {
this.$message.error('提交失败');
}
}
})
})
}
})
}
},
created(){
},
mounted(){
// 查询课程分类
courseApi.category_findlist().then(res=>{
this.categoryList = res.children;
console.log(this.categoryList)
})
//查询数据字典
//查询课程等级
systemApi.sys_getDictionary("200").then(res=>{
this.gradeList = res.dvalue;
})
//查询学习模式
systemApi.sys_getDictionary("201").then(res=>{
this.studymodelList = res.dvalue;
})
}
}
</script>
<style scoped>
</style>
其中,课程等级和学习模式是活数据
在前端的代码关系图
export const sys_getDictionary= dType => {
return http.requestQuickGet(apiUrl+'/sys/dictionary/get/'+dType)
}
后端API
CategoryControllerApi
@Api(value = "课程分类查询", description = "课程分类管理",tags = {"课程分类管理"})
public interface CategoryControllerApi {
@ApiOperation("查询分类")
public CategoryNode findList();
}
}
Controller:
/**
* @author Andrewer
* @version 1.0
* @project xcEduService01
* @description
* @date 2022/12/4 19:44:10
*/
@RestController
@RequestMapping("/category")
public class CategoryController implements CategoryControllerApi {
@Autowired
CategoryService categoryService;
@Override
@GetMapping("/list")
public CategoryNode findList() {
return categoryService.findList();
}
}
Mapper:
/**
* @author Andrewer
* @version 1.0
* @project xcEduService01
* @description
* @date 2022/12/4 19:39:24
*/
@Mapper
public interface CategoryMapper {
// 查询分类
CategoryNode findList();
}
Service:
/**
* @author Andrewer
* @version 1.0
* @project xcEduService01
* @description
* @date 2022/12/4 19:50:03
*/
@Service
public class CategoryService {
@Autowired
CategoryMapper categoryMapper;
public CategoryNode findList() {
return categoryMapper.findList();
}
}
CatrgoryMapper.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.xuecheng.manage_course.dao.CategoryMapper" >
<resultMap type="com.xuecheng.framework.domain.course.ext.CategoryNode" id="categoryMap" >
<id property="id" column="one_id"/>
<result property="name" column="one_name"/>
<result property="label" column="one_label"/>
<result property="isshow" column="one_isshow"/>
<result property="isleaf" column="one_isleaf"/>
<result property="orderby" column="one_orderby"/>
<result property="parentid" column="one_parentid"/>
<collection property="children"
ofType="com.xuecheng.framework.domain.course.ext.CategoryNode">
<id property="id" column="two_id"/>
<result property="name" column="two_name"/>
<result property="label" column="two_label"/>
<result property="isshow" column="two_isshow"/>
<result property="isleaf" column="two_isleaf"/>
<result property="orderby" column="two_orderby"/>
<result property="parentid" column="two_parentid"/>
<collection property="children"
ofType="com.xuecheng.framework.domain.course.ext.CategoryNode">
<id property="id" column="three_id"/>
<result property="name" column="three_name"/>
<result property="label" column="three_label"/>
<result property="isshow" column="three_isshow"/>
<result property="isleaf" column="three_isleaf"/>
<result property="orderby" column="three_orderby"/>
<result property="parentid" column="three_parentid"/>
</collection>
</collection>
</resultMap>
<select id="findList" resultMap="categoryMap" >
SELECT
a.id one_id,
a.name one_name,
a.label one_label,
a.isshow one_isshow,
a.isleaf one_isleaf,
a.orderby one_orderby,
a.parentid one_parentid,
b.id two_id,
b.name two_name,
b.label two_label,
b.isshow two_isshow,
b.isleaf two_isleaf,
b.orderby two_orderby,
b.parentid two_parentid,
c.id three_id,
c.name three_name,
c.label three_label,
c.isshow three_isshow,
c.isleaf three_isleaf,
c.orderby three_orderby,
c.parentid three_parentid
FROM
category a LEFT JOIN category b
ON a.id = b.parentid
LEFT JOIN category c
ON b.id = c.parentid
WHERE a.parentid = '0'
ORDER BY a.orderby,
b.orderby,
c.orderby
</select>
</mapper>
数据字典:
API
SysDictionaryApi
/**
* @author Andrewer
* @version 1.0
* @project xcEduService01
* @description
* @date 2022/12/4 21:52:36
*/
@Api(value = "数据字典接口",description = "提供数据字典接口的管理、查询功能")
public interface SysDictionaryApi {
//数据字典
@ApiOperation(value="数据字典查询接口")
public SysDictionary getBydType(String dType);
}
Controller:
/**
* @author Andrewer
* @version 1.0
* @project xcEduService01
* @description
* @date 2022/12/4 21:35:12
*/
@RestController
@RequestMapping("/sys/dictionary")
public class SysDictionaryController implements SysDictionaryApi {
@Autowired
SysDictionaryService sysDictionaryService;
@Override
@GetMapping("/get/{dType}")
public SysDictionary getBydType( @PathVariable("dType") String dType) {
return sysDictionaryService.findDictionaryByType(dType);
}
}
Dao:
/**
* @author Andrewer
* @version 1.0
* @project xcEduService01
* @description
* @date 2022/12/4 21:31:38
*/
@Repository
public interface SysDictionaryDao extends MongoRepository<SysDictionary,String> {
// 根据字典分类查询信息
SysDictionary findBydType(String dType);
}
Service
/**
* @author Andrewer
* @version 1.0
* @project xcEduService01
* @description
* @date 2022/12/4 21:34:27
*/
@Service
public class SysDictionaryService {
@Autowired
SysDictionaryDao sysDictionaryDao;
public SysDictionary findDictionaryByType(String dType) {
return sysDictionaryDao.findBydType(dType);
}
}
剩下的懒得写了直接上代码
package com.xuecheng.api.course;
import com.xuecheng.framework.domain.course.CourseBase;
import com.xuecheng.framework.domain.course.CourseMarket;
import com.xuecheng.framework.domain.course.Teachplan;
import com.xuecheng.framework.domain.course.ext.CategoryNode;
import com.xuecheng.framework.domain.course.ext.CourseInfo;
import com.xuecheng.framework.domain.course.ext.TeachplanNode;
import com.xuecheng.framework.domain.course.request.CourseListRequest;
import com.xuecheng.framework.domain.course.response.AddCourseResult;
import com.xuecheng.framework.model.response.QueryResponseResult;
import com.xuecheng.framework.model.response.ResponseResult;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
/**
* @author Andrewer
* @version 1.0
* @project xcEduService01
* @description
* @date 2022/11/23 11:57:38
*/
@Api(value = "课程管理接口",description = "课程管理接口,提供页面的增,删,改,查")
public interface CourseControllerApi {
@ApiOperation("课程计划查询")
public TeachplanNode findTeachplanList(String courseId);
@ApiOperation("添加课程计划")
public ResponseResult addTeachplan(Teachplan teachplan);
@ApiOperation("查询我的课程列表")
public QueryResponseResult findCourseList(
int page,
int size,
CourseListRequest courseListRequest
);
@ApiOperation("添加课程基础信息")
public AddCourseResult addCourseBase(CourseBase courseBase);
@ApiOperation("获取课程基础信息")
public CourseBase getCourseBaseById(String courseId)throws RuntimeException;
@ApiOperation("修改课程信息")
public ResponseResult updateCourseBase(String id,CourseBase courseBase);
@ApiOperation("查询课程营销")
public CourseMarket getCourseMarketById(String id);
@ApiOperation("更新课程营销")
public ResponseResult updateCourseMarket(String id,CourseMarket courseMarket);
}
package com.xuecheng.manage_course.controller;
import com.xuecheng.api.course.CourseControllerApi;
import com.xuecheng.framework.domain.course.CourseBase;
import com.xuecheng.framework.domain.course.CourseMarket;
import com.xuecheng.framework.domain.course.Teachplan;
import com.xuecheng.framework.domain.course.ext.TeachplanNode;
import com.xuecheng.framework.domain.course.request.CourseListRequest;
import com.xuecheng.framework.domain.course.response.AddCourseResult;
import com.xuecheng.framework.model.response.QueryResponseResult;
import com.xuecheng.framework.model.response.ResponseResult;
import com.xuecheng.manage_course.service.CourseService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
/**
* @author Andrewer
* @version 1.0
* @project xcEduService01
* @description
* @date 2022/12/1 14:04:57
*/
@RestController
@RequestMapping("/course")
public class CourseController implements CourseControllerApi {
@Autowired
CourseService courseService;
@Override
@GetMapping("/teachplan/list/{courseId}")
public TeachplanNode findTeachplanList(@PathVariable("courseId") String courseId) {
return courseService.findTeachplanList(courseId);
}
@Override
@PostMapping("/teachplan/add")//post提交json数据
public ResponseResult addTeachplan(@RequestBody Teachplan teachplan) {
return courseService.addTeachplan(teachplan);
}
@Override
@GetMapping("/coursebase/list/{page}/{size}")
public QueryResponseResult findCourseList(@PathVariable("page")int page,@PathVariable("size") int size, CourseListRequest courseListRequest) {
return courseService.findCourseListPage( page, size,courseListRequest);
// return null;
}
@Override
@PostMapping("/coursebase/add")
public AddCourseResult addCourseBase(@RequestBody CourseBase courseBase) {
return courseService.addCourseBase(courseBase);
}
@Override
@GetMapping("coursebase/get/{courseId}")
public CourseBase getCourseBaseById(@PathVariable("courseId") String courseId) throws RuntimeException {
return courseService.getCourseBaseById(courseId);
}
@Override
@PutMapping("/coursebase/update/{id}")
public ResponseResult updateCourseBase(@PathVariable("id") String id, @RequestBody CourseBase courseBase) {
return courseService.updateCourseBase(id,courseBase);
}
@Override
@GetMapping("/coursemarket/get/{id}")
public CourseMarket getCourseMarketById(@PathVariable("id") String id) {
System.out.println("ssss");
return courseService.getCourseMarketById(id);
}
@Override
@PutMapping("/coursemarket/update/{id}")
public ResponseResult updateCourseMarket(String id, @RequestBody CourseMarket courseMarket) {
return courseService.updateCourseMarket(id,courseMarket);
}
}
package com.xuecheng.manage_course.dao;
import com.xuecheng.framework.domain.course.CourseBase;
import com.xuecheng.framework.domain.course.CourseMarket;
import org.springframework.data.jpa.repository.JpaRepository;
/**
* Created by Administrator.
*/
public interface CourseMarketRepository extends JpaRepository<CourseMarket,String> {
CourseMarket findCoursMarketById(String id);
}
package com.xuecheng.manage_course.dao;
import com.xuecheng.framework.domain.course.CourseBase;
import org.springframework.data.jpa.repository.JpaRepository;
/**
* Created by Administrator.
*/
public interface CourseBaseRepository extends JpaRepository<CourseBase,String> {
}
<?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.xuecheng.manage_course.dao.CourseMapper">
<select id="findCourseBaseById" parameterType="java.lang.String"
resultType="com.xuecheng.framework.domain.course.CourseBase">
select * from course_base where id = #{id}
</select>
<select id="findCourseList" resultType="com.xuecheng.framework.domain.course.CourseBase">
select * from course_base
</select>
<select id="findCourseListPage" resultType="com.xuecheng.framework.domain.course.ext.CourseInfo"
parameterType="com.xuecheng.framework.domain.course.request.CourseListRequest">
SELECT
course_base.*,
(SELECT pic FROM course_pic WHERE courseid = course_base.id) pic
FROM
course_base
</select>
</mapper>
前端:
import http from './../../../base/api/public'
import querystring from 'querystring'
import course_marketinfo from "../page/course_manage/course_marketinfo";
let sysConfig = require('@/../config/sysConfig')
let apiUrl = sysConfig.xcApiUrlPre;
//查询课程列表
//我的课程列表
export const findCourseListPage = (page,size,params) => {
//使用工具类将json对象转成key/value
let queries = querystring.stringify(params)
return http.requestQuickGet(apiUrl+"/course/coursebase/list/"+page+"/"+size+"?"+queries)
}
//获取课本基本信息
export const getCourseBaseById = id=>{
return http.requestQuickGet(apiUrl+"/course/coursebase/get/"+id)
}
//更新课程基本信息
export const updateCourseBase = (id,course) =>{
return http.requestPut(apiUrl+'/course/coursebase/update/'+id,course)
}
//查询课程分类
export const category_findlist= () => {
return http.requestQuickGet(apiUrl+'/category/list')
}
/*添加课程基础信息*/
export const addCourseBase = params => {
return http.requestPost(apiUrl+'/course/coursebase/add',params)
}
/*查询课程计划*/
export const findTeachplanList = courseid => {
return http.requestQuickGet(apiUrl+'/course/teachplan/list/'+courseid)
}
/*添加课程计划*/
export const addTeachplan = teachplah => {
return http.requestPost(apiUrl+'/course/teachplan/add',teachplah)
}
//保存课程图片地址到课程数据 库
export const addCoursePic= (courseId,pic) => {
return http.requestPost(apiUrl+'/course/coursepic/add?courseId='+courseId+"&pic="+pic)
}
//查询课程图片
export const findCoursePicList = courseId => {
return http.requestQuickGet(apiUrl+'/course/coursepic/list/'+courseId)
}
//删除课程图片
export const deleteCoursePic= courseId => {
return http.requestDelete(apiUrl+'/course/coursepic/delete?courseId='+courseId)
}
/*预览课程*/
export const preview = id => {
return http.requestPost(apiUrl+'/course/preview/'+id);
}
/*发布课程*/
export const publish = id => {
return http.requestPost(apiUrl+'/course/publish/'+id);
}
//查询课程信息
export const findCourseView = courseId => {
return http.requestQuickGet(apiUrl+'/course/courseview/'+courseId)
}
/*保存媒资信息*/
export const savemedia = teachplanMedia => {
return http.requestPost(apiUrl+'/course/savemedia',teachplanMedia);
}
export const getCourseMarketById = id =>{
return http.requestGet(apiUrl+"/course/coursemarket/get/"+id)
}
export const updateCourseMarket = (id,course_marketinfo) =>{
return http.requestPut(apiUrl+"/course/coursemarket/update/"+id,course_marketinfo)
}