The usage scenario of for update has been introduced in Spring Date JPA for update. For more information, see the blog: https://blog.csdn.net/zc_ad/article/details/83578487 , we will introduce the usage scenario in mybatis here. Mybatis plus is used. Some basic tutorials have been done before. Address: https://blog.csdn.net/zc_ad/article/details/83301911
As with the use of row level locks in Spring Date JPA, transactions need to be added, and for update needs to be added when querying. Direct code:
Database table:
create table t_pub_student( id int PRIMARY key auto_increment, code VARCHAR(50) COMMENT 'Student CODE', name VARCHAR(50) COMMENT 'Student name' ) create table t_course_detail( id int PRIMARY key auto_increment, name VARCHAR(50) COMMENT 'Course title', teacher_name VARCHAR(50) COMMENT 'Teacher's name', elective_total int COMMENT 'Optional total', elective_num int COMMENT 'Selected quantity' ) create table t_course_detail( id int PRIMARY key auto_increment, student_code varchar(50) COMMENT 'Student code', course_id int COMMENT 'curriculum ID' )
Code directory structure:
Define entity:
package course.entity;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import com.baomidou.mybatisplus.enums.IdType;
import javax.persistence.*;
/**
* Created by Xichuan on 2018-10-31.
*/
@TableName("t_pub_student")
public class Student {
@TableId(value = "id",type = IdType.AUTO)
private Integer id;
@Column(name = "code")
private String code;
@Column(name = "name")
private String name;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
package course.entity;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import com.baomidou.mybatisplus.enums.IdType;
import javax.persistence.*;
/**
* Created by Xichuan on 2018-10-31.
*/
@TableName("t_pub_course")
public class Course {
@TableId(value = "id",type = IdType.AUTO)
private Integer id;
@Column(name = "name")
private String name;
@Column(name = "teacher_name")
private String teacherName;
@Column(name = "elective_total")
private Integer electiveTotal;
@Column(name = "elective_num")
private Integer electiveNum;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getTeacherName() {
return teacherName;
}
public void setTeacherName(String teacherName) {
this.teacherName = teacherName;
}
public Integer getElectiveTotal() {
return electiveTotal;
}
public void setElectiveTotal(Integer electiveTotal) {
this.electiveTotal = electiveTotal;
}
public Integer getElectiveNum() {
return electiveNum;
}
public void setElectiveNum(Integer electiveNum) {
this.electiveNum = electiveNum;
}
}package course.entity;
import com.baomidou.mybatisplus.annotations.TableId;
import com.baomidou.mybatisplus.annotations.TableName;
import com.baomidou.mybatisplus.enums.IdType;
import javax.persistence.*;
/**
* Created by Xichuan on 2018-10-31.
*/
@TableName("t_course_detail")
public class CourseDetail {
@TableId(value = "id",type = IdType.AUTO)
private Integer id;
@Column(name = "course_id")
private Integer courseId;
@Column(name = "student_code")
private String studentCode;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getCourseId() {
return courseId;
}
public void setCourseId(Integer courseId) {
this.courseId = courseId;
}
public String getStudentCode() {
return studentCode;
}
public void setStudentCode(String studentCode) {
this.studentCode = studentCode;
}
}DAO layer code:
package course.mapper;
import com.baomidou.mybatisplus.mapper.BaseMapper;
import course.entity.Student;
import org.apache.ibatis.annotations.Mapper;
/**
* Created by XiChuan on 2018-10-31.
*/
@Mapper
public interface StudentMapper extends BaseMapper<Student> {
}
package course.mapper;
import com.baomidou.mybatisplus.mapper.BaseMapper;
import course.entity.CourseDetail;
import org.apache.ibatis.annotations.Mapper;
@Mapper
public interface CourseDetailMapper extends BaseMapper<CourseDetail> {
}
package course.mapper;
import com.baomidou.mybatisplus.mapper.BaseMapper;
import course.entity.Course;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
/**
* Created by Xichuan on 2018-10-31.
*/
public interface CourseMapper extends BaseMapper<Course> {
/**Lock the data in this row, and the transaction will not be unlocked until the whole method commits the transaction*/
@Select(value = "select t from t_pub_course t where t.id = #{courseId} ")
Course queryAllById(@Param("courseId") Integer courseId);
/**Add 1 to the electiveNum in the course table*/
@Update("update t_pub_course t set t.elective_num = t.elective_num + 1 where t.id = #{courseId}")
void addElectiveNumByCourseId(@Param("courseId") Integer courseId);
}
Define interface:
package course.controller;
import course.service.CourseService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
/**
* Created by XiChuan on 2018-10-31.
*/
@RestController
public class CourseController {
@Autowired
CourseService courseService;
@PostMapping("/course/choose")
public Object chooseCourse(@RequestParam("student_code")String studentCode,
@RequestParam("course_id")Integer courseId){
return courseService.chooseCourse(studentCode,courseId);
}
}
service layer code:
public interface CourseService {
Object chooseCourse(String studentCode,Integer courseId);
}package course.service.impl;
import course.entity.Course;
import course.entity.CourseDetail;
import course.mapper.CourseDetailMapper;
import course.mapper.CourseMapper;
import course.mapper.StudentMapper;
import course.service.CourseService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;
import java.util.Objects;
/**
* Created by XiChuan on 2018-10-31.
*/
@Service
public class CourseServiceImpl implements CourseService {
private Logger logger = LoggerFactory.getLogger(CourseServiceImpl.class);
@Autowired
StudentMapper studentMapper;
@Autowired
CourseMapper courseMapper;
@Autowired
CourseDetailMapper courseDetailMapper;
/**This transaction must be added when using for update
* After the transaction is completed, for update will release the row level lock*/
@Transactional(isolation = Isolation.READ_COMMITTED)
// @Transactional(value = "testTransactionManager") / / if it is a multi data source, you need to specify a data source
@Override
public Object chooseCourse(String studentCode, Integer courseId) {
/** courseRepository.queryAllById(courseId)A row level lock will be added to the selected record, and other threads will queue here to unlock after the transaction is committed*/
Course course = courseMapper.queryAllById(courseId);
int electiveNum = course.getElectiveNum();
int totalNum = course.getElectiveTotal();
logger.info("After Lock Step 1, Thread: {},courseId{}, studentId: {}, electiveNum: {}, total: {}", Thread.currentThread(),courseId,studentCode, electiveNum, totalNum);
if (Objects.isNull(course)){
return "Course does not exist";
}
if (electiveNum >= totalNum) {
return "This course has been selected";
}
/**Save this student's course selection information to the course selection details*/
CourseDetail courseDetail = new CourseDetail();
courseDetail.setCourseId(courseId);
courseDetail.setStudentCode(studentCode);
courseDetailMapper.insert(courseDetail);
/**Add 1 to the electiveNum in the course table
* It is safer to use sql for accumulation, because the electronum in the course of using method to start query is not necessarily the value stored in the database*/
courseMapper.addElectiveNumByCourseId(courseId);
return "Successful course selection";
}
}