新建数据库表
Course
@Data
@TableName("t_course")
public class Course implements Serializable {
private static final long serialVersionUID = 1L;
/**
* id
*/
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 课程名称
*/
private String name;
/**
* 学分
*/
private Integer score;
/**
* 上课时间
*/
private String times;
/**
* 是否开课
*/
private Boolean state;
/**
* 授课老师id
*/
private Integer teacherId;
}CourseController
@RestController
@RequestMapping("/course")
public class CourseController {
@Resource
private CourseService courseService;
//修改或增加
@PostMapping("/saveCourse")
public Result saveCourse(@RequestBody Course course) {
//新增或修改
return Result.success(courseService.saveOrUpdate(course));
}
@GetMapping("/findAll")
public Result findAll() {
return Result.success(courseService.list());
}
@DeleteMapping("/{id}")
public Result delete(@PathVariable("id") Integer id) {
return Result.success(courseService.removeById(id));
}
//批量删除
@PostMapping("/del/batch")
public Result deleteBatch(@RequestBody List<Integer> ids) {
return Result.success(courseService.removeBatchByIds(ids));
}
//分页查询 mybatis-plus方式
@GetMapping("/selectPage")
public Result selectPage(@RequestParam(defaultValue = "") String name,
@RequestParam Integer pageSize,
@RequestParam Integer pageNum) {
IPage<Course> page = new Page<>(pageNum, pageSize);
QueryWrapper<Course> queryWrapper = new QueryWrapper<>();
if (!"".equals(name)) {
queryWrapper.like("name", name);
}
return Result.success(courseService.page(page, queryWrapper));
}
}
Course.vue
<template>
<div>
<div>
<div style="margin: 10px 0">
<el-input style="width: 200px" placeholder="请输入名称" suffix-icon="el-icon-search"
v-model="name"></el-input>
<el-button class="ml-5" type="primary" @click="load">搜索</el-button>
<el-button type="warning" @click="reset">重置</el-button>
</div>
<div style="margin: 10px 0">
<el-button type="primary" @click="handleAdd">新增 <i class="el-icon-circle-plus-outline"></i></el-button>
<el-popconfirm
class="ml-5"
confirm-button-text='确定'
cancel-button-text='我再想想'
icon="el-icon-info"
icon-color="red"
title="您确定批量删除这些数据吗？"
@confirm="delBatch"
>
<el-button type="danger" slot="reference">批量删除 <i class="el-icon-remove-outline"></i></el-button>
</el-popconfirm>
</div>
<el-table :data="tableData" border stripe :header-cell-class-name="'headerBg'"
@selection-change="handleSelectionChange">
<el-table-column type="selection" width="55"></el-table-column>
<el-table-column prop="id" label="ID" width="80"></el-table-column>
<el-table-column prop="name" label="课程名"></el-table-column>
<el-table-column prop="score" label="学分"></el-table-column>
<el-table-column prop="times" label="课时"></el-table-column>
<el-table-column prop="teacher" label="授课老师"></el-table-column>
<el-table-column label="启用">
<template slot-scope="scope">
<el-switch v-model="scope.row.enable" active-color="#13ce66" inactive-color="#ccc"
@change="changeEnable(scope.row)"></el-switch>
</template>
</el-table-column>
<el-table-column label="操作" width="200" align="center">
<template slot-scope="scope">
<el-button type="success" @click="handleEdit(scope.row)">编辑 <i class="el-icon-edit"></i></el-button>
<el-popconfirm
class="ml-5"
confirm-button-text='确定'
cancel-button-text='我再想想'
icon="el-icon-info"
icon-color="red"
title="您确定删除吗？"
@confirm="del(scope.row.id)"
>
<el-button type="danger" slot="reference">删除 <i class="el-icon-remove-outline"></i></el-button>
</el-popconfirm>
</template>
</el-table-column>
</el-table>
<div style="padding: 10px 0">
<el-pagination
@size-change="handleSizeChange"
@current-change="handleCurrentChange"
:current-page="pageNum"
:page-sizes="[2, 5, 10, 20]"
:page-size="pageSize"
layout="total, sizes, prev, pager, next, jumper"
:total="total">
</el-pagination>
</div>
<el-dialog title="课程信息" :visible.sync="dialogFormVisible" width="30%">
<el-form label-width="80px" size="small">
<el-form-item label="课程名">
<el-input v-model="form.name" autocomplete="off"></el-input>
</el-form-item>
<el-form-item label="学分">
<el-input v-model="form.score" autocomplete="off"></el-input>
</el-form-item>
<el-form-item label="课时">
<el-input v-model="form.times" autocomplete="off"></el-input>
</el-form-item>
<el-form-item label="授课老师">
<el-select clearable v-model="form.teacherId" placeholder="请选择" style="width: 100%">
<el-option v-for="item in teachers" :key="item.id" :label="item.nickname" :value="item.id">
</el-option>
</el-select>
</el-form-item>
</el-form>
<div slot="footer" class="dialog-footer">
<el-button @click="dialogFormVisible = false">取 消</el-button>
<el-button type="primary" @click="save">确 定</el-button>
</div>
</el-dialog>
</div>
</div>
</template>
<script>
export default {
name: "Course",
data() {
return {
form: {},
tableData: [],
name: "",
multipleSelection: [],
pageNum: 1,
pageSize: 5,
total: 0,
dialogFormVisible: false,
teachers: [],
user: localStorage.getItem("user") ? JSON.parse(localStorage.getItem("user")) : {}
}
}, created() {
this.load()
},
methods: {
//图片回显
formatImg(value) {
if (value) {
return "<img src='" + value.substring(21) + "' style='hight:50px'>"
} else {
return null;
}
},
load() {
this.request.get("/course/selectPage", {
params: {
pageNum: this.pageNum,
pageSize: this.pageSize,
name: this.name
}
}).then(res => {
//注意data
this.tableData = res.data.records
this.total = res.data.total
})
},
changeEnable(row) {
this.request.post("/course/update", row).then(res => {
if (res.code === '200') {
this.$message.success("更新成功")
}
})
},
del(id) {
this.request.delete("/course/" + id).then(res => {
if (res.code === '200') {
this.$message.success("删除成功")
this.load()
} else {
this.$message.error("删除失败")
}
})
},
save() {
this.request.post("/course/saveCourse", this.form).then(res => {
if (res.code === '200') {
this.$message.success("保存成功")
this.dialogFormVisible = false
this.load()
} else {
this.$message.error("保存失败")
}
})
},
handleAdd() {
this.dialogFormVisible = true
this.form = {}
},
handleEdit(row) {
this.form = JSON.parse(JSON.stringify(row))
this.dialogFormVisible = true
},
handleSelectionChange(val) {
console.log(val)
this.multipleSelection = val
},
delBatch() {
let ids = this.multipleSelection.map(v => v.id) // [{}, {}, {}] => [1,2,3]
this.request.post("/course/del/batch", ids).then(res => {
if (res.data) {
this.$message.success("批量删除成功")
this.load()
} else {
this.$message.error("批量删除失败")
}
})
},
reset() {
this.name = ""
this.load()
},
handleSizeChange(pageSize) {
console.log(pageSize)
this.pageSize = pageSize
this.load()
},
handleCurrentChange(pageNum) {
console.log(pageNum)
this.pageNum = pageNum
this.load()
},
handleFileUploadSuccess(res) {
console.log(res)
this.load()
},
download(url) {
window.open(url)
}
}
}
</script>
<style>
</style>插入数据
一、实现授课老师查询
(关联查询)
一对一
UserController
@GetMapping("/role/{role}")
public Result findAllUserByRoleId(@PathVariable String role) {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("role", role);
List<User> list = userService.list(queryWrapper);
return success(list);
}
Course.vue
load() {
this.request.get("/course/selectPage", {
params: {
pageNum: this.pageNum,
pageSize: this.pageSize,
name: this.name
}
}).then(res => {
//注意data
this.tableData = res.data.records
this.total = res.data.total
})
this.request.get("/user/role/ROLE_TEACHER").then(res => {
//注意data
this.teachers = res.data
})
},
Course
@TableField(exist = false)
private String teacher;//睡一会
TimeUnit.SECONDS.sleep(20);CourseController
第一种方法
//分页查询 mybatis-plus方式
@GetMapping("/selectPage")
public Result selectPage(@RequestParam(defaultValue = "") String name,
@RequestParam Integer pageSize,
@RequestParam Integer pageNum) {
QueryWrapper<Course> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByDesc("id");
Page<Course> page = courseService.page(new Page<>(pageNum, pageSize), queryWrapper);
List<Course> records = page.getRecords();
for (Course record : records) {
User user = userService.getById(record.getTeacherId());
if (user != null) {
record.setTeacher(user.getNickname());
}
}
return Result.success(page);
}
第二种方法
CourseController
@GetMapping("/selectPage")
public Result selectPage(@RequestParam String name,
@RequestParam Integer pageSize,
@RequestParam Integer pageNum) {
Page<Course> page = courseService.findPage(new Page<>(pageNum, pageSize), name);
return Result.success(page);
}CourseService
Page<Course> findPage(Page<Course> page, String name);
CourseServiceImpl
@Resource
private CourseMapper courseMapper;
@Override
public Page<Course> findPage(Page<Course> page, String name) {
return courseMapper.findPage(page,name);
}CourseMapper
Page<Course> findPage(Page<Course> page, @Param("name") String name);CourseMapper.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.springboot.mapper.CourseMapper">
<select id="findPage" resultType="com.example.springboot.entity.Course">
select t_course.*,sys_user.nickname as teacher
from t_course
left join sys_user
on t_course.teacher_id = sys_user.id
<where>
<if test="name!=null and name !=''">
and name like concat('%',#{name},'%')
</if>
</where>
</select>
</mapper>
多对一：
查看老师教授课程
User.vue
//让页面好看
<el-table-column prop="role" label="角色" width="140">
<template slot-scope="scope">
<el-tag type="primary" v-if="scope.row.role==='ROLE_ADMIN'">管理员</el-tag>
<el-tag type="warning" v-if="scope.row.role==='ROLE_TEACHER'">老师</el-tag>
<el-tag type="success" v-if="scope.row.role==='ROLE_STUDENT'">学生</el-tag>
</template>
</el-table-column>
//添加按钮
<el-button type="primary" @click="lookCourse(scope.row.courses)" v-if="scope.row.role === 'ROLE_TEACHER'">
查看教授课程<i class="el-icon-document"></i>
</el-button>
//添加弹窗
<el-dialog title="课程信息" :visible.sync="vis" width="30%">
<el-table :data="courses" border stripe>
<el-table-column prop="name" label="课程名称"></el-table-column>
<el-table-column prop="score" label="学分"></el-table-column>
</el-table>
</el-dialog>
//添加变量
courses: [],
vis: false
//添加方法
lookCourse(courses) {
this.courses = courses
this.vis = true
},
User
@TableField(exist = false)
private List<Course> courses;UserController
@GetMapping("/selectPage")
public Result selectPage(@RequestParam(defaultValue = "") String username,
@RequestParam Integer pageSize,
@RequestParam Integer pageNum,
@RequestParam(defaultValue = "") String email,
@RequestParam(defaultValue = "") String address) {
return Result.success(userService.findPage(new Page<>(pageNum,pageSize),username,email,address));
}UserService
Page<User> findPage(Page<User> page, String username, String email, String address);UserServiceImpl
@Override
public Page<User> findPage(Page<User> page, String username, String email, String address) {
return userMapper.findPage(page,username,email,address);
}UserMapper
Page<User> findPage(Page<User> page, @Param("username") String username,@Param("email") String email,@Param("address") String address);
UserMapper.xml
<resultMap id="pageUser" type="com.example.springboot.entity.User">
<result column="id" property="id"/>
<result column="username" property="username"/>
<result column="nickname" property="nickname"/>
<result column="email" property="email"/>
<result column="phone" property="phone"/>
<result column="address" property="address"/>
<result column="create_time" property="createTime"/>
<result column="avatar_url" property="avatarUrl"/>
<result column="role" property="role"/>
<collection property="courses" javaType="java.util.ArrayList" ofType="com.example.springboot.entity.Course">
<result column="name" property="name"/>
<result column="score" property="score"/>
</collection>
</resultMap>
<select id="findPage" resultMap="pageUser">
select sys_user.*,
t_course.*
from sys_user left join t_course
on sys_user.id = t_course.teacher_id
<where>
<if test="username !=null and username!=''">
and sys_user.username like concat('%',#{username},'%')
</if>
<if test="email !=null and email!=''">
and sys_user.email like concat('%',#{email},'%')
</if>
<if test="address !=null and address!=''">
and sys_user.address like concat('%',#{address},'%')
</if>
</where>
</select>
多对多
学生选课功能
新建数据库表
CourseController
//修改或增加
@PostMapping("/studentCourse/{courseId}/{studentId}")
public Result StudentCourse(@PathVariable Integer courseId,@PathVariable Integer studentId ) {
//新增或修改
courseService.setStudentCourse(courseId,studentId);
return Result.success();
}CourseService
void setStudentCourse(Integer courseId, Integer studentId);CourseServiceImpl
@Override
@Transactional
public void setStudentCourse(Integer courseId, Integer studentId) {
courseMapper.deleteStudentCourse(courseId, studentId);
courseMapper.setStudentCourse(courseId, studentId);
}CourseMapper
void deleteStudentCourse(@Param("courseId") Integer courseId, @Param("studentId") Integer studentId);
void setStudentCourse(@Param("courseId") Integer courseId, @Param("studentId") Integer studentId);
CourseMapper.xml
<delete id="deleteStudentCourse">
delete
from student_course
where student_id = #{studentId}
and course_id = #{courseId}
</delete>
<insert id="setStudentCourse">
insert
into student_course(student_id, course_id)
values (#{studentId}, #{courseId})
</insert>Course.vue
//添加按钮
<el-button type="primary" @click="selectCourse(scope.row.id)">选课</el-button>
//添加方法
selectCourse(courseId) {
this.request.post("/course/studentCourse/" + courseId + "/" + this.user.id).then(res => {
if (res.code === '200') {
this.$message.success("选课成功")
} else {
this.$message.error(res.msg)
}
})
},查询已选课程功能实现
User.vue
//添加按钮
<el-button type="warning" @click="lookStuCourse(scope.row.stuCourses)" v-if="scope.row.role === 'ROLE_STUDENT'">
查看已选课程<i class="el-icon-document"></i>
</el-button>
//添加参数
stuVis:false,
stuCourses:[],
//添加功能
lookStuCourse(stuCourses) {
this.courses = stuCourses
this.stuVis = true
},
<el-dialog title="课程信息" :visible.sync="stuVis" width="30%">
<el-table :data="courses" border stripe>
<el-table-column prop="name" label="课程名称"></el-table-column>
<el-table-column prop="score" label="学分"></el-table-column>
</el-table>
</el-dialog>UserMapper.xml
<resultMap id="pageUser" type="com.example.springboot.entity.User">
<result column="id" property="id"/>
<result column="username" property="username"/>
<result column="nickname" property="nickname"/>
<result column="email" property="email"/>
<result column="phone" property="phone"/>
<result column="address" property="address"/>
<result column="create_time" property="createTime"/>
<result column="avatar_url" property="avatarUrl"/>
<result column="role" property="role"/>
<collection property="courses" javaType="java.util.ArrayList" ofType="com.example.springboot.entity.Course">
<result column="teacherCourseName" property="name"/>
<result column="teacherScore" property="score"/>
</collection>
<collection property="stuCourses" javaType="java.util.ArrayList" ofType="com.example.springboot.entity.Course">
<result column="stuCourseName" property="name"/>
<result column="stuScore" property="score"/>
</collection>
</resultMap>
<select id="findPage" resultMap="pageUser">
select sys_user.*,sc.name as stuCourseName,tc.name as teacherCourseName,tc.score as teacherScore,
sc.score as stuScore from sys_user
left join student_course
on sys_user.id = student_course.student_id
left join t_course sc
on student_course.course_id = sc.id
left join t_course tc
on sys_user.id = tc.teacher_id
<where>
<if test="username !=null and username!=''">
and sys_user.username like concat('%',#{username},'%')
</if>
<if test="email !=null and email!=''">
and sys_user.email like concat('%',#{email},'%')
</if>
<if test="address !=null and address!=''">
and sys_user.address like concat('%',#{address},'%')
</if>
</where>
</select>
