preface
After consulting the official documents of mybatis plus, I found that there were only a few words and no examples for multi table joint query. I had to figure out how to use mybatis plus for multi table joint query. The way of using xml is too redundant, so after checking the use of annotations, we decisively choose to use annotations.
Annotation learning
@Select annotation: in fact, the corresponding sql statement is added after the annotation, and the writing method is consistent with that in xml.
@Results annotation: there is an id attribute, which can be understood as defining a method that can be referenced by id. the reference method is ResultMap ("")@ Results can be understood as a mapping of a Result set, in which multiple @ results can be added.
@Result annotation: similar to result / in xml. The main explanation is that many=@Many(), the attribute of this class specified by this attribute corresponds to multiple data, that is, it is wrapped in a list. There is also one=@One (), which is a single object as the name suggests.
Practice produces true knowledge. I won't say much. Learn directly through examples!
Problems need to be solved
You need to query all the courses selected by this student. The query result is:
Student id, student name, list collection of Course objects
And query all the courses taught by this teacher. The query result is:
Teacher id, teacher name, list collection of Course objects
The above problems are actually consistent, which can be solved by solving one of them and then by C+V
Now let's take the students to solve it.
In fact, the Student class Student has only two attributes, that is
id, name (the database is consistent with it)
The Course class has only two properties, which are
id, name (the database is consistent with it)
We need to query the list set of Course objects we want through the relationship table between them
This relationship table is called Tasks, which is also called Tasks class. Its properties are
id, studentId, courseId (the database is consistent with it)
After the problem description, let's start to practice.
Database part:
Student list
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int NOT NULL,
`name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('18120107', 'Student nine');
INSERT INTO `student` VALUES ('18120108', 'Student number ten');
INSERT INTO `student` VALUES ('18120101', 'Zhang San');
INSERT INTO `student` VALUES ('18120102', 'Li Si');
INSERT INTO `student` VALUES ('18120103', 'Wang Wu');
INSERT INTO `student` VALUES ('18120105', 'pseudo-ginseng');
INSERT INTO `student` VALUES ('18120104', 'Zhao Liu');
INSERT INTO `student` VALUES ('18120106', 'Chen Ba');
Class Schedule Card
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`id` int NOT NULL,
`name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('87508', 'JavaWeb development');
INSERT INTO `course` VALUES ('87502', 'operating system');
INSERT INTO `course` VALUES ('87505', 'Database principle');
INSERT INTO `course` VALUES ('87501', 'data structure');
INSERT INTO `course` VALUES ('87506', 'Algorithm design and analysis');
INSERT INTO `course` VALUES ('87504', 'Computer composition');
INSERT INTO `course` VALUES ('87503', 'computer network');
INSERT INTO `course` VALUES ('87509', 'Software collaborative design');
INSERT INTO `course` VALUES ('87507', 'software engineering');
StudentCourse
(in fact, the course_name attribute here is redundant, and it is reserved for the time being)
-- ----------------------------
-- Table structure for takes
-- ----------------------------
DROP TABLE IF EXISTS `takes`;
CREATE TABLE `takes` (
`id` int NOT NULL AUTO_INCREMENT,
`student_id` int NOT NULL,
`course_id` int NOT NULL,
`course_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_takes_student_id` (`student_id`),
KEY `fk_takes_course_id` (`course_id`),
KEY `fk_takes_course_name` (`course_name`),
CONSTRAINT `fk_takes_course_id` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_takes_course_name` FOREIGN KEY (`course_name`) REFERENCES `course` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_takes_student_id` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of takes
-- ----------------------------
INSERT INTO `takes` VALUES ('1', '18120101', '87501', 'data structure');
INSERT INTO `takes` VALUES ('2', '18120101', '87502', 'operating system');
INSERT INTO `takes` VALUES ('3', '18120101', '87503', 'computer network');
INSERT INTO `takes` VALUES ('4', '18120102', '87501', 'data structure');
INSERT INTO `takes` VALUES ('5', '18120102', '87502', 'operating system');
INSERT INTO `takes` VALUES ('6', '18120102', '87503', 'computer network');
INSERT INTO `takes` VALUES ('7', '18120103', '87509', 'Software collaborative design');
INSERT INTO `takes` VALUES ('8', '18120103', '87508', 'JavaWeb development');
INSERT INTO `takes` VALUES ('9', '18120103', '87507', 'software engineering');
INSERT INTO `takes` VALUES ('10', '18120104', '87503', 'computer network');
INSERT INTO `takes` VALUES ('11', '18120104', '87506', 'Algorithm design and analysis');
INSERT INTO `takes` VALUES ('12', '18120104', '87507', 'software engineering');
INSERT INTO `takes` VALUES ('13', '18120105', '87502', 'operating system');
INSERT INTO `takes` VALUES ('14', '18120105', '87504', 'Computer composition');
INSERT INTO `takes` VALUES ('15', '18120105', '87505', 'Database principle');
INSERT INTO `takes` VALUES ('16', '18120106', '87501', 'data structure');
INSERT INTO `takes` VALUES ('17', '18120106', '87503', 'computer network');
INSERT INTO `takes` VALUES ('18', '18120106', '87505', 'Database principle');
INSERT INTO `takes` VALUES ('19', '18120107', '87509', 'Software collaborative design');
INSERT INTO `takes` VALUES ('20', '18120107', '87506', 'Algorithm design and analysis');
INSERT INTO `takes` VALUES ('21', '18120107', '87504', 'Computer composition');
INSERT INTO `takes` VALUES ('22', '18120108', '87506', 'Algorithm design and analysis');
INSERT INTO `takes` VALUES ('23', '18120108', '87507', 'software engineering');
INSERT INTO `takes` VALUES ('24', '18120108', '87508', 'JavaWeb development');
Entity class
lombok plug-in is used to automatically generate relevant methods, getter s, setter s, constructors, etc
Student class
Here's an explanation. The * * @ TableField(exist = false) * * annotation above the Course of the List indicates that the attribute is not a field in the database (in fact, it is written directly into the entity class for the convenience of the diagram. In actual development, you should write an additional Vo class, inherit from Student, and then write this attribute)
@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
public class Student implements Serializable {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.NONE)
private Integer id;
private String name;
@TableField(exist = false)
private List<Course> courses;
}
Courses
@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
public class Course implements Serializable {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.NONE)
private Integer id;
private String name;
}
Course selection
Still, the courseName can be ignored, but it is actually unnecessary
@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
public class Takes implements Serializable {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
private int studentId;
private int courseId;
private String courseName;
}
Here's the point!
Mapper layer
CourseMapper
It can be clearly seen here that it is to find out all the courses of this student and return to a course list
public interface CourseMapper extends BaseMapper<Course> {
@Select("select * from course where id in(select course_id from takes where student_id=#{stuId})")
List<Course> queryCoursesByStuId(Integer stuId);
}
StudentMapper
This is the key of the full text
First, the student object is queried externally, and the returned actual result set is represented in the @ Results annotation,
The first two @ results are student id and name. Needless to say here
The third one is @ list. Javaresult Class, many=@Many to specify the attribute of the Result (query the list collection through the method in CourseMapper). Note: write the full name of the method name, package name + class name + method name
@Select("select * from student where id=#{id}")
@Results({
@Result(column="id",property="id",id=true),
@Result(column = "name",property = "name"),
@Result(column="id",property="courses",javaType= List.class,
many=@Many(
select="com.usst.mapper.CourseMapper.queryCoursesByStuId"
)
)
})
Student queryStuById(@Param("id") Integer id);
In fact, when I write here, the Service layer comes out without saying, but I'd better write it again
Service layer
StudentService
public interface StudentService extends IService<Student> {
Student queryStuById(@Param("id") int id);
}
StudentServiceImpl
@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements StudentService {
@Autowired
private StudentMapper studentMapper;
@Override
public Student queryStuById(int id) {
return this.studentMapper.queryStuById(id);
}
}
Result test
@Autowired
private StudentService studentService;
@Test
void test(){
System.out.println(studentService.queryStuById(18120101).toString());
}
Screenshot of test results:
So that's all for this study. I hope it can be helpful to you!
emmmm, little hand praise, pay attention and support! There will be more useful articles in the follow-up!