本文主要通过学生选课的场景,进行Mybatis多对多的案例介绍。
开发环境
- idea
- mysql
工程代码下载地址 https://github.com/psp0001060/06_Mybatis_Many2Many.git
代码介绍
1.sql
CREATE TABLE `course` (
`id` int(11) NOT NULL,
`course_code` varchar(10) NOT NULL,
`course_name` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Create Table
CREATE TABLE `student` (
`id` int(11) NOT NULL auto_increment,
`name` tinytext,
`sex` tinytext,
`age` int(4) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `student_course` (
`id` decimal(3,0) NOT NULL,
`student_id` int(11) default NULL,
`course_id` int(3) default NULL,
PRIMARY KEY (`id`),
KEY `stuFK` (`student_id`),
KEY `courseFK` (`course_id`),
CONSTRAINT `courseFK` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `stuFK` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2.model
Course:
package test.model;
import java.util.List;
public class Course {
private Integer id;
private String courseCode;
private String courseName;
private List<Student> students;// 选课学生
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getCourseCode() {
return courseCode;
}
public void setCourseCode(String courseCode) {
this.courseCode = courseCode == null ? null : courseCode.trim();
}
public String getCourseName() {
return courseName;
}
public void setCourseName(String courseName) {
this.courseName = courseName == null ? null : courseName.trim();
}
}
Student:
package test.model;
import java.util.List;
public class Student {
private Integer id;
private String name;
private String sex;
private Integer age;
private List<Course> courses;// 所选的课程
public List<Course> getCourses() {
return courses;
}
public void setCourses(List<Course> courses) {
this.courses = courses;
}
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 == null ? null : name.trim();
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex == null ? null : sex.trim();
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
}
3.Mapper
CourseMapper:
package test.dao;
import test.model.Course;
public interface CourseMapper {
int deleteByPrimaryKey(Integer id);
int insert(Course record);
int insertSelective(Course record);
Course selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(Course record);
int updateByPrimaryKey(Course record);
//根据课程id,获取课程信息及所选学生信息
Course selectCourseWithStudents(Integer id);
}
StudentMapper:
package test.dao;
import test.model.Course;
import test.model.Student;
public interface StudentMapper {
int deleteByPrimaryKey(Integer id);
int insertSelective(Student record);
Student selectByPrimaryKey(Integer id);
int updateByPrimaryKeySelective(Student record);
//学生x选课y(向student_course表插入记录)
int studentChooseCourse(Integer id,Student student, Course course);
//查询student级联查询出所选的course并且组装成完整的对象
Student getStudentByIdWithCourses(Integer id);
}
4.Mapper.xml
CourseMapper:
<?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 namespace="test.dao.CourseMapper" >
<resultMap id="courseResultMap" type="test.model.Course" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="course_code" property="courseCode" jdbcType="VARCHAR" />
<result column="course_name" property="courseName" jdbcType="VARCHAR" />
</resultMap>
<sql id="Base_Column_List" >
id, course_code, course_name
</sql>
<select id="selectByPrimaryKey" resultMap="courseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from course
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from course
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="test.model.Course" >
insert into course (id, course_code, course_name
)
values (#{id,jdbcType=INTEGER}, #{courseCode,jdbcType=VARCHAR}, #{courseName,jdbcType=VARCHAR}
)
</insert>
<insert id="insertSelective" parameterType="test.model.Course" >
insert into course
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
id,
</if>
<if test="courseCode != null" >
course_code,
</if>
<if test="courseName != null" >
course_name,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=INTEGER},
</if>
<if test="courseCode != null" >
#{courseCode,jdbcType=VARCHAR},
</if>
<if test="courseName != null" >
#{courseName,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="test.model.Course" >
update course
<set >
<if test="courseCode != null" >
course_code = #{courseCode,jdbcType=VARCHAR},
</if>
<if test="courseName != null" >
course_name = #{courseName,jdbcType=VARCHAR},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="test.model.Course" >
update course
set course_code = #{courseCode,jdbcType=VARCHAR},
course_name = #{courseName,jdbcType=VARCHAR}
where id = #{id,jdbcType=INTEGER}
</update>
</mapper>
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" >
<mapper namespace="test.dao.StudentMapper" >
<resultMap id="studentResultMap" type="test.model.Student" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="sex" property="sex" jdbcType="VARCHAR" />
<result column="age" property="age" jdbcType="INTEGER" />
</resultMap>
<sql id="Base_Column_List" >
id, name, sex, age
</sql>
<select id="selectByPrimaryKey" resultMap="studentResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from student
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from student
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insertSelective" parameterType="test.model.Student" >
insert into student
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
id,
</if>
<if test="name != null" >
name,
</if>
<if test="sex != null" >
sex,
</if>
<if test="age != null" >
age,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=INTEGER},
</if>
<if test="name != null" >
#{name,jdbcType=VARCHAR},
</if>
<if test="sex != null" >
#{sex,jdbcType=VARCHAR},
</if>
<if test="age != null" >
#{age,jdbcType=INTEGER},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="test.model.Student" >
update student
<set >
<if test="name != null" >
name = #{name,jdbcType=VARCHAR},
</if>
<if test="sex != null" >
sex = #{sex,jdbcType=VARCHAR},
</if>
<if test="age != null" >
age = #{age,jdbcType=INTEGER},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<insert id="studentChooseCourse">
<!-- param1代表方法中第一个参数 以此类推 -->
insert into
student_course(id,student_id,course_id)
values
(#{param1},#{param2.id},#{param3.id})
</insert>
<!-- 继承上面那个基本的映射,再扩展出级联查询 -->
<resultMap id="StudentResultWithCourses" type="Student" extends="studentResultMap">
<collection property="courses" ofType="Course">
<id property="id" column="cid"></id>
<result property="courseCode" column="course_code"/>
<result property="courseName" column="course_name"/>
</collection>
</resultMap>
<select id="getStudentByIdWithCourses" parameterType="int" resultMap="StudentResultWithCourses">
select s.id,s.name,c.id as cid,c.course_code,c.course_name,sc.id,sc.student_id,sc.course_id
from student s,course c,student_course sc
where
s.id=#{id}
and
s.id=sc.student_id
and
sc.course_id=c.id
</select>
</mapper>
5.Test
package mybatis_helloworld;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import test.dao.StudentMapper;
import test.model.Course;
import test.model.Student;
import java.io.IOException;
import java.io.InputStream;
public class StudentTest {
private String filename = "mybatis-config.xml";
@Test
public void testInsertStudent() {
InputStream is;
try {
is = Resources.getResourceAsStream(filename);
SqlSessionFactory factory = new SqlSessionFactoryBuilder()
.build(is);
SqlSession session = factory.openSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
Student student = new Student();
student.setName("zs");
student.setAge(23);
student.setSex("man");
mapper.insertSelective(student);
session.commit();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 学生选择课程
*/
@Test
public void testStudentChooseCourse(){
InputStream is;
try {
is = Resources.getResourceAsStream(filename);
SqlSessionFactory factory = new SqlSessionFactoryBuilder()
.build(is);
SqlSession session = factory.openSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
Student student = new Student();
student.setId(1);
Course course = new Course();
course.setId(2);
mapper.studentChooseCourse(1, student, course);
session.commit();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 查询某个学生的个人信息及所选的课程信息
*/
@Test
public void testGetStudentByIdWithCourses(){
InputStream is;
try {
is = Resources.getResourceAsStream(filename);
SqlSessionFactory factory = new SqlSessionFactoryBuilder()
.build(is);
SqlSession session = factory.openSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
Student student =mapper.getStudentByIdWithCourses(1);
System.out.println("姓名:"+student.getName());
for (Course course:
student.getCourses()) {
System.out.println(course.getCourseCode()+"--"+course.getCourseName());
}
} catch (IOException e) {
e.printStackTrace();
}
}
}