实训三 mybatis第4天 一对多关系和多对多关系

目录

• mybatis第4天 一对多关系和多对多关系
• 1. 新建数据库表tb_student和tb_course和中间表tb_stu_course
  • 2. mapper层有接口CourseMapper和StudentMapper
  • 使用List<Map<String,Integer> > 封装key和value
  • Student类里封装List<Course>的方法:使用resultMap

mybatis第4天 一对多关系和多对多关系

1. 新建数据库表tb_student和tb_course和中间表tb_stu_course

2. mapper层有接口CourseMapper和StudentMapper

StudetnMapper.java

package com.icis.mapper;

import com.icis.pojo.Student;
import javafx.util.Pair;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface StudentMapper {

    Integer insertStudentByStudent(Student student);
    Student getStudentById(@Param("mystuid") Integer id);

    Integer insertStuCourseByList(List<Map<String, Integer>> list);
	
    //返回学生对象, 并封装学生所学课程到list
    Student getStuCourseById(@Param("stuid") Integer id);
}

StudentMapper.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">
<!--
namespace 相当于java语言中的包 (用以防止sql语句名称冲突)实现sql语句隔离
namespace 一定要和接口名UserDao.java相同
 -->
<mapper namespace="com.icis.mapper.StudentMapper">
    <insert id="insertStudentByStudent" parameterType="com.icis.pojo.Student">
        INSERT into tb_student VALUES (null, #{stuName}, #{stuBirthday})
    </insert>

    <select id="getStudentById" parameterType="int" resultType="com.icis.pojo.Student">
        SELECT  * FROM tb_student WHERE stuId=#{mystuid}
    </select>

    <insert id="insertStuCourseByList" parameterType="list">
        INSERT INTO tb_stu_course VALUES
        <foreach collection="list" item="mp" separator=",">
            (#{mp.stuId}, #{mp.courseId})
        </foreach>
    </insert>

    <resultMap id="stuMap" type="com.icis.pojo.Student">
        <result column="stuId" property="stuId"></result>
        <result column="stuName" property="stuName"></result>
        <result column="stuBirthday" property="stuBirthday"></result>

        <collection property="courses" column="courseId" javaType="list" ofType="com.icis.pojo.Course">
            <result column="courseId" property="courseId"></result>
            <result column="courseName" property="courseName"></result>
            <result column="courseTime" property="courseTime"></result>
        </collection>
    </resultMap>
    <!--返回学生对象, 并封装学生所学课程到list-->
    <select id="getStuCourseById" parameterType="int" resultMap="stuMap">
        SELECT
          stu.`stuId`,
          stu.`stuName`,
          stu.`stuBirthday`,
          co.`courseId`,
          co.`courseName`,
          co.`courseTime`
        FROM
          tb_student stu,
          tb_course co,
          tb_stu_course sc
        WHERE stu.`stuId`=sc.`stuId`
            AND co.`courseId`=sc.`courseId` and stu.`stuId`=#{stuid};
    </select>

</mapper>

使用List<Map<String,Integer> > 封装key和value

@Test
public void test3() {
    Student stu = studentMapper.getStudentById(3);
    Map<String, Integer> mp[] = new HashMap[3];
    for (int i=0; i<3; i++) 
        mp[i] = new HashMap<String, Integer>();
    mp[0].put("stuId", 5); mp[0].put("courseId", 1);
    mp[1].put("stuId", 5); mp[1].put("courseId", 2);
    mp[2].put("stuId", 5); mp[2].put("courseId", 3);
    Integer ret = studentMapper.insertStuCourseByList(Arrays.asList(mp));
    System.out.println(ret);
}

在xml里

<insert id="insertStuCourseByList" parameterType="list">
    INSERT INTO tb_stu_course VALUES
    <foreach collection="list" item="mp" separator=",">
        (#{mp.stuId}, #{mp.courseId})
    </foreach>
</insert>

Student类里封装List<Course>的方法:使用resultMap

xml里

<resultMap id="stuMap" type="com.icis.pojo.Student">
    <result column="stuId" property="stuId"></result>
    <result column="stuName" property="stuName"></result>
    <result column="stuBirthday" property="stuBirthday"></result>

    <collection property="courses" column="courseId" javaType="list" ofType="com.icis.pojo.Course">
        <result column="courseId" property="courseId"></result>
        <result column="courseName" property="courseName"></result>
        <result column="courseTime" property="courseTime"></result>
    </collection>
</resultMap>
<!--返回学生对象, 并封装学生所学课程到list-->
<select id="getStuCourseById" parameterType="int" resultMap="stuMap">
    SELECT
    stu.`stuId`,
    stu.`stuName`,
    stu.`stuBirthday`,
    co.`courseId`,
    co.`courseName`,
    co.`courseTime`
    FROM
    tb_student stu,
    tb_course co,
    tb_stu_course sc
    WHERE stu.`stuId`=sc.`stuId`
    AND co.`courseId`=sc.`courseId` and stu.`stuId`=#{stuid};
</select>

student实体类

package com.icis.pojo;


import java.sql.Timestamp;
import java.util.List;

public class Student {

  private long stuId;
  private String stuName;
  private java.sql.Timestamp stuBirthday;

  List<Course> courses;

  @Override
  public String toString() {
    return "Student{" +
            "stuId=" + stuId +
            ", stuName='" + stuName + '\'' +
            ", stuBirthday=" + stuBirthday +
            ", courses=" + courses +
            '}';
  }

  public List<Course> getCourses() {
    return courses;
  }

  public void setCourses(List<Course> courses) {
    this.courses = courses;
  }

  public Student() {
  }

  public Student(long stuId, String stuName, Timestamp stuBirthday) {
    this.stuId = stuId;
    this.stuName = stuName;
    this.stuBirthday = stuBirthday;
  }

  public long getStuId() {
    return stuId;
  }

  public void setStuId(long stuId) {
    this.stuId = stuId;
  }


  public String getStuName() {
    return stuName;
  }

  public void setStuName(String stuName) {
    this.stuName = stuName;
  }


  public java.sql.Timestamp getStuBirthday() {
    return stuBirthday;
  }

  public void setStuBirthday(java.sql.Timestamp stuBirthday) {
    this.stuBirthday = stuBirthday;
  }

}

Course.java

package com.icis.pojo;

import java.sql.Timestamp;

public class Course {

  private long courseId;
  private String courseName;
  private java.sql.Timestamp courseTime;

  @Override
  public String toString() {
    return "Course{" +
            "courseId=" + courseId +
            ", courseName='" + courseName + '\'' +
            ", courseTime=" + courseTime +
            '}';
  }

  public Course() {
  }

  public Course(long courseId, String courseName, Timestamp courseTime) {
    this.courseId = courseId;
    this.courseName = courseName;
    this.courseTime = courseTime;
  }

  public long getCourseId() {
    return courseId;
  }

  public void setCourseId(long courseId) {
    this.courseId = courseId;
  }


  public String getCourseName() {
    return courseName;
  }

  public void setCourseName(String courseName) {
    this.courseName = courseName;
  }


  public java.sql.Timestamp getCourseTime() {
    return courseTime;
  }

  public void setCourseTime(java.sql.Timestamp courseTime) {
    this.courseTime = courseTime;
  }

}