springboot xml 数据库访问 联表查询

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<Student> getStudents(String stuNo,String stuName,String stuSex,String stuDept);

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

<select id="getStudents"
     resultType="Student">
  SELECT * FROM student
  WHERE 
  <if test="title != null">
    stu_no = #{stuNo}
  </if>
  <if test="title != null">
    AND stu_name = #{stuName}
  </if>
  <if test="title != null">
    AND stu_sex = #{stuSex}
  </if>
  <if test="title != null">
    AND stu_dept = #{stuDept}
  </if>
</select>

xml联表查询

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

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

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

application主启动类加

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

扫描自己建立的Mapper接口类

resourses下建文件夹存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开始就是自己定义的了-->
<mapper namespace="com.example.sqltest.Mapper.CourseMapper">    <!--对应的mapper类的包路径-->

    <resultMap type="com.example.sqltest.enity.Course" id="courseMap">
        <id column="id" property="id"/>
        <result column="course_no" property="courseNo"/>
        <result column="course_name" property="courseName"/>
        <result column="course_prior_id" property="coursePriorId"/>
        <result column="course_credit" property="courseCredit"/>
        <association property="score" javaType="com.example.sqltest.enity.Score">
            <id column="id" property="id"/>
            <result column="course_id" property="courseId"/>
            <result column="student_id" property="stuId"/>
        </association>
    </resultMap>
    <select id="getAllChoose" parameterType="java.util.List" resultMap="courseMap">
        SELECT A.stu_id, A.course_id, A.grade, B.*
        from sc as A,
             course as B
        where A.course_id = B.id
    </select>
</mapper>

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 {
    @Autowired
    CourseMapper courseMapper;

    @Test
    void 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("已选课");
        }
    }

    @Test
    void getStudents() {
        String stuNo = null;
        String stuName = null;
        String stuSex = "女";
        String stuDept = "CS";
        List<Student> 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);
            }
        }
    }

    @Test
    void 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;

最后

以上就是无奈巨人为你收集整理的springboot xml 数据库访问 联表查询的全部内容，希望文章能够帮你解决springboot xml 数据库访问 联表查询所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。