springboot xml 数据库访问 联表查询

练习数据库表：

course表

student表

sc表

建立Spring boot 项目，并初始化对应依赖

实体类

Course：

package com.example.sqltest.enity;import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;@Data
@TableName("course")
public class Course {@TableId("id")private Integer id;private String courseNo;private String courseName;private Integer coursePriorId;private Integer courseCredit;private Score score;// 为了联表查询
}

sc：

package com.example.sqltest.enity;import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;@Data
@TableName("sc")
public class Score {private Integer id;private Integer stuId;private Integer courseId;private Integer grade;
}

student:

package com.example.sqltest.enity;import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;@Data
@TableName("student")
public class Student {private Integer id;private String stuNo;private String stuName;@TableField("stu_sex")private String stuSex;private Integer stuAge;@TableField("stu_dept")private String stuDept;
}

基本查询和插入

完成这类操作，不需要联表，使用注解@Mapper,@Select,@Insert基本可以实现。

例如：实现选课信息表的添加功能，要求一个学生一门课程只能选修一次。

@Insert("insert into sc(stu_id,course_id) values (#{stu_id},#{course_id})")
void insertCourse(Integer stu_id,Integer course_id);

动态SQL

这一类部分也可以使用注解来完成相对应的功能。

例如:实现学生信息表的查询功能，要求：查询条件中包含学号，姓名，性别以及院系，返回符合查询条件的学生信息。只要完成AND条件的组合即可。例如：输入学号为NULL，姓名为NULL，性别为女，院系为SS，则返回SS系的所有女生信息

@Select("select * from student where (stu_no = #{stuNo} or #{stuNo} is null) and (stu_name = #{stuName} or #{stuName} is null) and (stu_sex= #{stuSex} or #{stuSex} is null)  and (stu_dept = #{stuDept} or #{stuDept} is null)")List getStudents(String stuNo,String stuName,String stuSex,String stuDept);

当然，也可以使用xml动态SQL：

SELECT * FROM studentWHERE stu_no = #{stuNo}AND stu_name = #{stuName}AND stu_sex = #{stuSex}AND stu_dept = #{stuDept}

xml联表查询

使用SQL语句对数据库的两个或多个表进行联表查询

例如：返回所有课程的选修情况，要求结果中包含课程信息，以及选课信息。不需要把选课学生的详情列出，必须使用关联映射实现。

mybatis官网:https://mybatis.net.cn/sqlmap-xml.html

application主启动类加

@MapperScan(basePackages = "com.example.sqlTest.Mapper")

扫描自己建立的Mapper接口类

resourses下建文件夹存xml

    SELECT A.stu_id, A.course_id, A.grade, B.*from sc as A,course as Bwhere A.course_id = B.id

application.yml加配置项：

mybatis:mapper-locations: classpath:mapper/*Mapper.xml

Junit测试

在测试类里面写测试方法，写上一些逻辑判断，调用

package com.example.sqltest;import com.example.sqltest.Mapper.CourseMapper;
import com.example.sqltest.enity.Course;
import com.example.sqltest.enity.CourseScore;
import com.example.sqltest.enity.Student;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;import java.util.List;@SpringBootTest
class SqlTestApplicationTests {@AutowiredCourseMapper courseMapper;@Testvoid insertCourse() {Integer stu_id = 1;String course_name = "计算机网络";Integer course_id = courseMapper.getCourseNameByName(course_name);if(course_id == null) {System.out.println("课程不存在");}System.out.println(course_id);System.out.println(courseMapper.getScById(course_id));if(courseMapper.getScById(course_id).size() == 0){courseMapper.insertCourse(stu_id,course_id);System.out.println("选课成功");}else{System.out.println("已选课");}}@Testvoid getStudents() {String stuNo = null;String stuName = null;String stuSex = "女";String stuDept = "CS";List students = courseMapper.getStudents(stuNo,stuName,stuSex,stuDept);if(students.size() == 0){System.out.println("学生不存在");}else{System.out.println("学生如下：");for (Student student : students) {System.out.println(student);}}}@Testvoid getAllChoose() {List list = courseMapper.getAllChoose();if(list.size() == 0){System.out.println("没有选课");return;}for (Object o : list) {System.out.println(o);}}
}

SQL脚本：

create database  exam1;
use exam1;-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (`id` int(0) NOT NULL AUTO_INCREMENT,`stu_no` char(9),`stu_name` varchar(20) ,`stu_sex` char(2) ,`stu_age` int(0) NULL DEFAULT NULL,`stu_dept` char(15) ,PRIMARY KEY (`id`) 
) ;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '201815001', '李晨', '女', 22, 'SS');
INSERT INTO `student` VALUES (2, '201815002', '张毅', '男', 20, 'SS');
INSERT INTO `student` VALUES (3, '201815003', '杨磊', '女', 20, 'SS');
INSERT INTO `student` VALUES (4, '201815004', '张丰毅', '男', 19, 'SS');
INSERT INTO `student` VALUES (5, '201816001', '王敏', '女', 22, 'CS');
INSERT INTO `student` VALUES (6, '201816002', '李蕾', '女', 21, 'CS');
INSERT INTO `student` VALUES (7, '201817001', '李贵', '男', 21, 'MA');
INSERT INTO `student` VALUES (8, '201817002', '严丽', '女', 20, 'MA');
INSERT INTO `student` VALUES (9, '201817003', '沈菁菁', '女', 21, 'MA');-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (`id` int(0) NOT NULL AUTO_INCREMENT,`course_no` char(8),`course_name` varchar(40) ,`course_prior_id` int(0) NULL DEFAULT NULL,`course_credit` smallint(0) NULL DEFAULT NULL,PRIMARY KEY (`id`) ,INDEX `Cpno`(`course_prior_id`) ,CONSTRAINT `course_ibfk_1` FOREIGN KEY (`course_prior_id`) REFERENCES `course` (`id`) 
) ;-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, '6324567', '数据处理', NULL, 4);
INSERT INTO `course` VALUES (2, '6234322', '数学', NULL, 2);INSERT INTO `course` VALUES (4, '6256774', '操作系统', 1, 4);
INSERT INTO `course` VALUES (7, '6203183', 'pascal语言', 1, 4);
INSERT INTO `course` VALUES (5, '6228723', '数据结构', 7, 4);
INSERT INTO `course` VALUES (6, '6203456', '数据库系统概论', 5, 2);INSERT INTO `course` VALUES (8, '6203752', '大学英语', NULL, 4);
INSERT INTO `course` VALUES (9, '6203496', '计算机网络', NULL, 4);
INSERT INTO `course` VALUES (3, '6254374', '信息系统', 6, 3);-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (`id` int(0) NOT NULL AUTO_INCREMENT,`stu_id` int(0) NOT NULL,`course_id` int(0) NOT NULL,`grade` int(0) NULL DEFAULT NULL,PRIMARY KEY (`id`),INDEX `FK_sc_course`(`course_id`) ,INDEX `stu_id`(`stu_id`),CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `student` (`id`) ,CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) 
) ;-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES (1, 1, 1, 90);
INSERT INTO `sc` VALUES (2, 1, 2, 85);
INSERT INTO `sc` VALUES (3, 2, 1, 86);
INSERT INTO `sc` VALUES (4, 2, 2, 90);
INSERT INTO `sc` VALUES (5, 2, 4, 67);
INSERT INTO `sc` VALUES (6, 3, 1, 85);select * from student;
select * from sc;
select * from course;