编写:HorinJsor
前言
介绍分页插件,SQL映射文件的标准增删改查。
提示:以下是本篇文章正文内容,下面案例可供参考
一、SQL映射文件
可以当一个模板使用。
<?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.mapper.LeavesMapper">
<!--请假模块
编写:HorinJsor
时间:2022/04/30 14:23:25
-->
<!--映射集合-->
<resultMap id="BaseResultMap" type="com.domain.po.LeavesPo">
<id property="leaveId" column="leave_id" jdbcType="INTEGER"/>
<result property="leaveType" column="leave_type" jdbcType="INTEGER"/>
<result property="leaveDetails" column="leave_details" jdbcType="VARCHAR"/>
<result property="leaveAddress" column="leave_address" jdbcType="VARCHAR"/>
<result property="leaveStart" column="leave_start" jdbcType="TIMESTAMP"/>
<result property="leaveEnd" column="leave_end" jdbcType="TIMESTAMP"/>
<result property="leaveSubmit" column="leave_submit" jdbcType="TIMESTAMP"/>
<result property="leaveApproval" column="leave_approval" jdbcType="INTEGER"/>
<result property="leaveReview" column="leave_review" jdbcType="VARCHAR"/>
<result property="studentId" column="student_id" jdbcType="BIGINT"/>
<result property="teacherId" column="teacher_id" jdbcType="BIGINT"/>
<result property="createUser" column="create_user" jdbcType="VARCHAR"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<result property="modifiedUser" column="modified_user" jdbcType="VARCHAR"/>
<result property="modifiedTime" column="modified_time" jdbcType="TIMESTAMP"/>
<result property="leaveState" column="leave_state" jdbcType="INTEGER"/>
<!--以下为衍生字段-->
<result property="studentName" column="student_name" jdbcType="VARCHAR"/>
<result property="studentDepartments" column="student_departments" jdbcType="VARCHAR"/>
<result property="studentProfessional" column="student_professional" jdbcType="VARCHAR"/>
<result property="studentClass" column="student_class" jdbcType="BIGINT"/>
<result property="classGrade" column="class_grade" jdbcType="VARCHAR"/>
<result property="teacherName" column="teacher_name" jdbcType="VARCHAR"/>
</resultMap>
<!--增加请假信息-->
<insert id="addLeaves" parameterType="com.domain.Leaves" keyProperty="leaveId" useGeneratedKeys="true">
INSERT INTO leaves(leave_type,leave_details,leave_address,leave_start,leave_end,
leave_submit,leave_approval,leave_review,
create_user,create_time,modified_user,modified_time,teacher_id,student_id,leave_state)
VALUES(#{leaveType}, #{leaveDetails}, #{leaveAddress}, #{leaveStart}, #{leaveEnd}, #{leaveSubmit}, #{leaveApproval}, #{leaveReview}, #{createUser}, #{createTime}, #{modifiedUser}, #{modifiedTime}, #{teacherId}, #{studentId}, #{leaveState} )
</insert>
<!--删除请假信息-->
<delete id="deleteLeave" parameterType="java.lang.Integer" >
DELETE from leaves WHERE leave_id = #{leaveId}
</delete>
<!--修改请假信息-->
<update id="updateLeaves" parameterType="com.domain.Leaves" >
UPDATE `leaves`
<set>
<if test="leaveType != null ">
leave_type = #{leaveType},
</if>
<if test=" leaveDetails !=null and leaveDetails !=''">
leave_details = #{leaveDetails},
</if>
<if test=" leaveAddress !=null and leaveAddress !=''">
leave_address = #{leaveAddress},
</if>
<if test=" leaveStart !=null ">
leave_start = #{leaveStart},
</if>
<if test=" leaveEnd !=null ">
leave_end = #{leaveEnd},
</if>
<if test=" leaveSubmit !=null ">
leave_submit = #{leaveSubmit},
</if>
<if test=" leaveApproval !=null ">
leave_approval = #{leaveApproval},
</if>
<if test=" leaveReview !=null and leaveReview !=''">
leave_review = #{leaveReview},
</if>
<if test=" createUser !=null and createUser !=''">
create_user = #{createUser},
</if>
<if test=" createTime !=null">
create_time = #{createTime},
</if>
<if test=" modifiedUser !=null and modifiedUser !=''">
modified_user = #{modifiedUser},
</if>
<if test=" modifiedTime !=null">
modified_time = #{modifiedTime},
</if>
<if test=" teacherId !=null ">
teacher_id = #{teacherId},
</if>
<if test=" studentId !=null ">
student_id = #{studentId},
</if>
<if test=" leaveState !=null ">
leave_state = #{leaveState},
</if>
</set>
WHERE `leave_id` =#{leaveId};
</update>
<!--老师请假审批-->
<update id="updateApproval" parameterType="com.domain.Leaves" >
UPDATE `leaves`
<set>
<if test="leaveApproval != null ">
leave_approval = #{leaveApproval},
</if>
<if test="leaveReview != null and leaveReview !=''">
leave_Review = #{leaveReview},
</if>
</set>
WHERE `leave_id` =#{leaveId};
</update>
<!--查询所有请假记录-->
<select id="selectLeaves" resultMap="BaseResultMap">
SELECT
`leaves`.*,
student.student_name,
student.student_departments,
student.student_professional,
student.student_class,
student.student_id,
classes.class_grade,
teacher.teacher_name
FROM
`leaves`
INNER JOIN
student
ON
`leaves`.student_id = student.student_id
INNER JOIN
classes
ON
classes.class_id = student.student_class
INNER JOIN
teacher
ON
`leaves`.teacher_id = teacher.teacher_id
</select>
<!--请假模块多条件查询-->
<select id="selectLeaveByCondition" parameterType="com.domain.vo.LeavesVo" resultMap="BaseResultMap">
SELECT
`leaves`.*,
student.student_name,
student.student_departments,
student.student_professional,
student.student_class,
student.student_id,
classes.class_grade,
teacher.teacher_name
FROM
`leaves`
INNER JOIN
student
ON
`leaves`.student_id = student.student_id
INNER JOIN
classes
ON
classes.class_id = student.student_class
INNER JOIN
teacher
ON
`leaves`.teacher_id = teacher.teacher_id
<where>
<!--条件:提交时间(范围)-->
<!--特别注意:Leave类中定义了数据库不存在的字段leaveQueryStart和leaveQueryEnd,用于查询提交时间范围-->
<if test="leaveQueryStart !=null">
AND DATE_FORMAT(leave_submit, '%Y-%m-%d %H:%i:%s') >= DATE_FORMAT(#{leaveQueryStart}, '%Y-%m-%d %H:%i:%s')
</if>
<if test="leaveQueryEnd !=null">
AND DATE_FORMAT(leave_submit, '%Y-%m-%d %H:%i:%s') <= DATE_FORMAT(#{leaveQueryEnd}, '%Y-%m-%d %H:%i:%s')
</if>
<!--条件:学生院系-->
<if test="studentDepartments !=null and studentDepartments !=''">
AND student.student_departments = #{studentDepartments}
</if>
<!--条件:学生专业-->
<if test="studentProfessional !=null and studentProfessional !=''">
AND student.student_professional = #{studentProfessional}
</if>
<!--条件:学生年级-->
<if test="classGrade !=null and classGrade !=''">
AND classes.class_grade = #{classGrade}
</if>
<!--条件:学生班级-->
<if test="studentClass !=null ">
AND student.student_class = #{studentClass}
</if>
<!--条件:学生学号-->
<if test="studentId!=null ">
AND leaves.student_id = #{studentId}
</if>
<!--条件:学生姓名-->
<if test="studentName !=null and studentName !=''">
AND student.student_name = #{studentName}
</if>
<!--条件:请假类型-->
<if test="leaveType !=null ">
AND leaves.leave_Type = #{leaveType}
</if>
<!--条件:请假审批状态-->
<if test="leaveApproval !=null ">
AND leaves.leave_approval = #{leaveApproval}
</if>
<!--条件:老师姓名-->
<if test="teacherName !=null ">
AND teacher.teacher_name = #{teacherName}
</if>
<!--条件:提交时间-->
<if test="leaveSubmit !=null">
AND leaves.leave_submit = #{leaveSubmit}
</if>
</where>
</select>
<!--请假详情查看-->
<select id="selectDetail" parameterType="java.lang.Integer" resultType="com.domain.po.LeavesPo">
SELECT
`leaves`.*,
student.student_name,
student.student_departments,
student.student_professional,
student.student_class,
student.student_id,
classes.class_grade,
teacher.teacher_name
FROM
`leaves`
INNER JOIN
student
ON
`leaves`.student_id = student.student_id
INNER JOIN
classes
ON
classes.class_id = student.student_class
INNER JOIN
teacher
ON
`leaves`.teacher_id = teacher.teacher_id
WHERE `leave_id` = #{leaveId};
</select>
<!--请假模块下拉框:学生院系-->
<select id="selectDepartDownList" resultType="String">
SELECT
depart_name
FROM
depart
</select>
<!--请假模块下拉框:审批人-->
<select id="selectTeacherDownList" resultType="String">
SELECT
teacher_name
FROM
teacher
</select>
<!--请假模块下拉框:学生专业-->
<select id="selectMajor" resultType="String">
SELECT
major_name
FROM
major
</select>
<!--请假模块下拉框:学生年级-->
<select id="selectGrade" resultType="String">
SELECT
class_grade
FROM
classes
</select>
<!--请假模块下拉框:学生班级-->
<select id="selectClass" resultType="String">
SELECT
class_id
FROM
classes
</select>
<!--通过学号查询审批老师工号techerId-->
<select id="selectTeacher" parameterType="java.lang.Long" resultType="java.lang.Long">
SELECT
teacher.teacher_id
FROM
student
INNER JOIN
group_student
ON
student.student_id = group_student.student_id
INNER JOIN
group_teacher
ON
group_student.group_id = group_teacher.group_id
INNER JOIN
teacher
ON
teacher.teacher_id = group_teacher.teacher_id
where student.student_id = #{stuId}
</select>
<!--通过请假编号leaveId查询审批老师工号teacherId-->
<select id="selectTeacher2" parameterType="java.lang.Integer" resultType="java.lang.Long">
SELECT
`leaves`.teacher_id
FROM
`leaves`
WHERE leave_id = #{leaveId}
</select>
</mapper>
二、分页插件
1.引入依赖
代码如下(示例):
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.4.2</version>
</dependency>
2.逻辑层
代码如下(示例):
@GetMapping("/query")
public Result selectLeaveByCondition(@RequestBody LeavesVo leavesVo,
@RequestParam(value = "pageNum",defaultValue ="1") Integer pageNum,
@RequestParam(value ="pageSize",defaultValue ="10") Integer pageSize) {
PageHelper.startPage(pageNum, pageSize);//启动
List<LeavesPo> a = leavesMapper.selectLeaveByCondition(leavesVo);
PageInfo<LeavesPo> leavesPoPageInfo = new PageInfo<>(a);//导入结果对象
System.out.println("leavesPoPageInfo = " + leavesPoPageInfo);//控制台测试
if (a != null){
Result<PageInfo<LeavesPo>> result = Result.success(200,"查询成功", leavesPoPageInfo);
return result;
}else {
Result result = Result.error(500,"查询失败");
return result;
}
}
总结
分页插件最新版本查看
https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter