<?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.hx.sscs.mapper.OfficeMapper">
<!--
1. 定义联查结果映射VO（办公室+教师） -->
<resultMap id="OfficeTeacherVO" type="com.hx.sscs.vo.OfficeTeacherVO">
<id column="course_id" property="courseId" />
<result column="task" property="task" />
<result column="classroom" property="classroom" />
<result column="class_time" property="classTime" />
<result column="teacher_id" property="teacherId" />
<result column="teacher_name" property="teacherName" />
<result column="department" property="department" />
</resultMap>
<!--
2. 双表联查：根据教师ID查询办公室任务及教师信息 -->
<select id="selectOfficeByTeacherId" parameterType="java.lang.Integer" resultMap="OfficeTeacherVO">
SELECT
o.course_id, o.task, o.classroom, o.class_time,
t.teacher_id, t.teacher_name, t.department
FROM
office o
INNER JOIN
teachers t ON o.teacher_id = t.teacher_id
WHERE
o.logical_delete = 0 AND t.logical_delete = 0
AND o.teacher_id = #{teacherId}
</select>
<!--
2. 双表联查：根据教师教室查询办公室任务及教师信息 -->
<select id="selectOfficeByName" parameterType="java.lang.String" resultMap="OfficeTeacherVO">
SELECT
o.course_id, o.task, o.classroom, o.class_time,
t.teacher_id, t.teacher_name, t.department
FROM
office o
INNER JOIN
teachers t ON o.teacher_id = t.teacher_id
WHERE
o.logical_delete = 0 AND t.logical_delete = 0
AND t.teacher_name LIKE CONCAT(&#
39;%&#
39;, #{TeacherName}, &#
39;%&#
39;)
</select>
<!--
3. 扩展：查询所有办公室任务及对应教师信息（不带条件） -->
<select id="selectAllOfficeWithTeacher" resultMap="OfficeTeacherVO">
SELECT
o.course_id, o.task, o.classroom, o.class_time,
t.teacher_id, t.teacher_name, t.department
FROM
office o
INNER JOIN
teachers t ON o.teacher_id = t.teacher_id
WHERE
o.logical_delete = 0 AND t.logical_delete = 0
</select>
</mapper>
package com.hx.sscs.mapper;package com.hx.sscs.service.impl;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.hx.sscs.entity.Office;
import com.hx.sscs.entity.TaskList;
import com.hx.sscs.entity.Teachers;
import com.hx.sscs.mapper.OfficeMapper;
import com.hx.sscs.service.OfficeService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.hx.sscs.vo.OfficeTeacherVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* <p>
* 服务实现类
* </p>
*
* @author testjava
* @since
20
25-06-05
*/
@Service
public class OfficeServiceImpl extends ServiceImpl<OfficeMapper, Office> implements OfficeService {
@Autowired
private OfficeMapper officeMapper;
@Override
public List<Office> searchClass(String taskName) {
QueryWrapper<Office> queryWrapper = new QueryWrapper<>();
queryWrapper.like("task", taskName);//模糊查询姓名
return baseMapper.selectList(queryWrapper);
}
@Override
public Office taskroom(String classroom) {
QueryWrapper<Office> queryWrapper=new QueryWrapper<>();
queryWrapper.eq("classroom",classroom);
return baseMapper.selectOne(queryWrapper);
}
@Override
public List<OfficeTeacherVO> searchTeacher(String teacherName) {//根据姓名查询老师的教职工号，根据教职工号查询教室
List<OfficeTeacherVO> list=officeMapper.selectOfficeByName(teacherName);
return list;
}
}
package com.hx.sscs.controller;
import com.hx.sscs.commonutils.R;
import com.hx.sscs.entity.Office;
import com.hx.sscs.entity.TaskList;
import com.hx.sscs.entity.Teachers;
import com.hx.sscs.handler.EduException;
import com.hx.sscs.service.OfficeService;
import com.hx.sscs.service.TaskListService;
import com.hx.sscs.service.TeachersService;
import com.hx.sscs.vo.OfficeTeacherVO;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
/**
* <p>
* 前端控制器
* </p>
*
* @author testjava
* @since
20
25-06-05
*/
@RestController
@RequestMapping("/sscs/office")
public class OfficeController {
//根据任务查询教室//模糊查询
@Resource
private OfficeService officeService;
@Resource
private TeachersService teachersService;
@ApiOperation(value = "模糊查询")
@PostMapping("searchClass")
public R searchClass(
@ApiParam(name = "taskName",value = "任务名",required = true)
@RequestParam String taskName
){
try{
//调用模糊查询方法
List<Office> offices=new ArrayList<>();//建立集合装填返回数据
offices=officeService.searchClass(taskName);
//找到taskList的子节点，若子节点有自己的子节点则继续找出子节点;
if (offices == null) {
return R.error().code(6000
1).message("未查询到任务: " + taskName);
}
System.out.println(offices);
return R.ok().data("tasks", offices).message("查看成功");
}catch (EduException e) {
return R.error().code(e.getCode()).message(e.getMsg());
} catch (Exception e){
System.out.println(e.getMessage());
return R.error().code(6000
2).message("查看失败："+e.getMessage());
}
}
//根据教室查询信息
@ApiOperation(value = "教室查询")
@PostMapping("Class")
public R Class(
@ApiParam(name = "classroom",value = "教室号",required = true)
@RequestParam String classroom){
try{
Office office=officeService.taskroom(classroom);
System.out.println(office);
return R.ok().data("office",office).message("登录成功");
}catch (EduException e) {
return R.error().code(e.getCode()).message(e.getMsg());
} catch (Exception e){
System.out.println(e.getMessage());
return R.error().code(
200055).message("登录失败："+e.getMessage());
}
}
//根据老师查询教室
@ApiOperation(value = "任课老师查询")
@PostMapping("ClassTeacher")
private R ClassTeacher(
@ApiParam(name = "TeacherName",value = "教师名",required = true)
@RequestParam String TeacherName
){
//根据老师姓名查询id，得到多表联查的关于该id的数据//改为直接多表联查姓名
try{
List<OfficeTeacherVO> teachers=officeService.searchTeacher(TeacherName);
System.out.println(teachers);
return R.ok().data("OfficeTeacherVO",teachers).message("多表查询老师姓名");
}catch (EduException e) {
return R.error().code(e.getCode()).message(e.getMsg());
} catch (Exception e){
System.out.println(e.getMessage());
return R.error().code(
200055).message("多表查询老师姓名失败："+e.getMessage());
}
//教师号查询id
//id查询Off
}
//根据教室查询老师
}
package com.hx.sscs;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;
import springfox.documentation.swagger
2.annotations.EnableSwagger
2;
@SpringBootApplication
@EnableSwagger
2
@ComponentScan(basePackages = {"com.hx"})
@MapperScan("com.hx.sscs.mapper")
public class Boot
3Application {
public static void main(String[] args) {
SpringApplication.run(Boot
3Application.class,args);
}
}
package com.hx.sscs.vo;
import lombok.Data;
import java.io.Serializable;
/**
* 办公室表与教师表联查结果VO
*/
@Data
public class OfficeTeacherVO implements Serializable {
// 办公室表字段
private Integer courseId;
private String task;
private String classroom;
private String classTime;
// 教师表字段
private Integer teacherId;
private String teacherName;
private String department;
// getter 和 setter 方法
public Integer getCourseId() { return courseId; }
public void setCourseId(Integer courseId) { this.courseId = courseId; }
public String getTask() { return task; }
public void setTask(String task) { this.task = task; }
public String getClassroom() { return classroom; }
public void setClassroom(String classroom) { this.classroom = classroom; }
public String getClassTime() { return classTime; }
public void setClassTime(String classTime) { this.classTime = classTime; }
public Integer getTeacherId() { return teacherId; }
public void setTeacherId(Integer teacherId) { this.teacherId = teacherId; }
public String getTeacherName() { return teacherName; }
public void setTeacherName(String teacherName) { this.teacherName = teacherName; }
public String getDepartment() { return department; }
public void setDepartment(String department) { this.department = department; }
@Override
public String toString() {
return "OfficeTeacherVO{" +
"courseId=" + courseId +
", task=&#
39;" + task + &#
39;\&#
39;&#
39; +
", classroom=&#
39;" + classroom + &#
39;\&#
39;&#
39; +
", classTime=&#
39;" + classTime + &#
39;\&#
39;&#
39; +
", teacherId=" + teacherId +
", teacherName=&#
39;" + teacherName + &#
39;\&#
39;&#
39; +
", department=&#
39;" + department + &#
39;\&#
39;&#
39; +
&#
39;}&#
39;;
}
}
Invalid bound statement (not found): com.hx.sscs.mapper.OfficeMapper.selectOfficeByName
import com.hx.sscs.entity.Office;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hx.sscs.vo.OfficeTeacherVO;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
/**
* <p>
* Mapper 接口
* </p>
*
* @author testjava
* @since
20
25-06-05
*/
@Mapper
public interface OfficeMapper extends BaseMapper<Office> {
/**
* 根据教师ID联查办公室任务及教师信息
*/
List<OfficeTeacherVO> selectOfficeByTeacherId(Integer teacherId);
List<OfficeTeacherVO> selectOfficeByName(String TeacherName);
/**
* 查询所有办公室任务及对应教师信息
*/
List<OfficeTeacherVO> selectAllOfficeWithTeacher();
}
package com.hx.sscs.service;
import com.hx.sscs.entity.Office;
import com.baomidou.mybatisplus.extension.service.IService;
import com.hx.sscs.entity.TaskList;
import com.hx.sscs.vo.OfficeTeacherVO;
import java.io.Serializable;
import java.util.List;
/**
* <p>
* 服务类
* </p>
*
* @author testjava
* @since
20
25-06-05
*/
public interface OfficeService extends IService<Office> {
List<Office> searchClass(String taskName);//模糊查询
Office taskroom(String classroom);
List<OfficeTeacherVO> searchTeacher(String teacherName);//教师名查询
}
扫一扫
2579
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
