多对多：
多对多映射
对于在mybatis中的多对多的处理,其实我们可以参照一对多来解决
【注意】在这个例子中有三个字段都是一样的:id,这种情况一定要小心,
要给列起别名的(上面的一对一和一对多中如果出现这种情况也是一样的处理方式)
建表语句:
drop table course;
drop table student;
drop table student_course;
如果需要可以使用 cascade constraints;
create table course (
id number primary key,
course_code varchar2(30) not null,
course_name varchar2(30) not null
);
create table student (
id number primary key,
name varchar2(10) not null,
gender varchar2(10) ,
major varchar2(10) ,
grade varchar2(10)
);
create table student_course (
id number primary key,
student_id number references student(id),
course_id number references course(id)
);
java代码:
public class Course {
private Integer id;
private String courseCode; // 课程编号
private String courseName;// 课程名称
private List<Student> students;// 选课学生
get/set
}
public class Student {
private Integer id;
private String name; // 姓名
private String gender; // 性别
private String major; // 专业
private String grade; // 年级
private List<Course> courses;// 所选的课程
get/set
}
Many2ManyMapper.java:
public interface Many2ManyMapper {
//插入student数据
public void insertStudent(Student student);
//插入course数据
public void insertCourse(Course course);
//通过id查询学生
public Student getStudentById(Integer id);
//通过id查询课程
public Course getCourseById(Integer id);
//学生x选课y
public void studentSelectCourse(Student student, Course course);
//查询比指定id值小的学生信息
public List<Student> getStudentByIdOnCondition(Integer id);
//查询student级联查询出所选的course并且组装成完整的对象
public Student getStudentByIdWithCourses(Integer id);
}
Many2ManyMapper.xml:
<insert id="insertStudent" parameterType="Student">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select my_seq.nextval from dual
</selectKey>
insert into
student(id,name,gender,major,grade)
values
(#{id},#{name},#{gender},#{major},#{grade})
</insert>
<insert id="insertCourse" parameterType="Course">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select my_seq.nextval from dual
</selectKey>
insert into
course(id,course_code,course_name)
values
(#{id},#{courseCode},#{courseName})
</insert>
<select id="getStudentById" parameterType="int" resultType="Student">
select id,name,gender,major,grade
from student
where id=#{id}
</select>
<select id="getCourseById" parameterType="int" resultType="Course">
select id,course_code as courseCode,course_name as courseName
from course
where id=#{id}
</select>
<!-- param1代表方法中第一个参数 以此类推 -->
<insert id="studentSelectCourse">
insert into
student_course(id,student_id,course_id)
values
(my_seq.nextval,#{param1.id},#{param2.id})
</insert>
<!-- 如果有特殊符号的话 需要用 <![CDATA[ 特殊符号 ]]> 例如 < & 等等 -->
<select id="getStudentByIdOnCondition" parameterType="int" resultType="Student">
select *
from student
where id <![CDATA[ < ]]> #{id}
</select>
<!--
这里使用了嵌套结果ResultMap的方式进行级联查询
当然也可以使用嵌套查询select
-->
<!-- 映射一个基本的Student查询结果 -->
<resultMap id="StudentResult" type="Student">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="gender" column="gender"/>
<result property="major" column="major"/>
<result property="grade" column="grade"/>
</resultMap>
<!-- 继承上面那个基本的映射,再扩展出级联查询 -->
<resultMap id="StudentResultWithCourses" type="Student" extends="StudentResult">
<collection property="courses" resultMap="CourseResult"></collection>
</resultMap>
<!-- 这里特别要是的是column="cid" 这是和select语句中的 c.id as cid对应的 一定一定一定要对应起来 -->
<resultMap id="CourseResult" type="Course">
<id property="id" column="cid"/>
<result property="courseCode" column="course_code"/>
<result property="courseName" column="course_name"/>
</resultMap>
<!--
注意:查询语句的中的c.id as cid这个地方,避免名字相同出现查询结果不正确的情况
同时在id="CourseResult"的resultMap中也有与这里对应的设置要特别特别注意
-->
<select id="getStudentByIdWithCourses" parameterType="int" resultMap="StudentResultWithCourses">
select s.id,s.name,s.gender,s.major,s.grade,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>
测试代码:
@Test
public void test_insertStudent(){
SqlSession session = null;
try {
session = MyBatisSqlSessionFactory.openSession();
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
mapper.insertStudent(new Student("张三","男","计算机","大四"));
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}finally {
if(session!=null)session.close();
}
}
@Test
public void test_insertCourse(){
SqlSession session = null;
try {
session = MyBatisSqlSessionFactory.openSession();
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
mapper.insertCourse(new Course("001","corejava"));
mapper.insertCourse(new Course("002","oracle"));
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}finally {
if(session!=null)session.close();
}
}
@Test
public void test_studentSelectCourse(){
SqlSession session = null;
try {
session = MyBatisSqlSessionFactory.openSession();
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
Student student = mapper.getStudentById(58);
Course course = mapper.getCourseById(59);
mapper.studentSelectCourse(student, course);
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}finally {
if(session!=null)session.close();
}
}
@Test
public void test_getStudentByIdOnCondition(){
SqlSession session = null;
try {
session = MyBatisSqlSessionFactory.openSession();
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
List<Student> list = mapper.getStudentByIdOnCondition(100);
for(Student s:list){
System.out.println(s);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if(session!=null)session.close();
}
}
@Test
public void test_getStudentByIdWithCourses(){
SqlSession session = null;
try {
session = MyBatisSqlSessionFactory.openSession();
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
Student student = mapper.getStudentByIdWithCourses(58);
System.out.println(student);
} catch (Exception e) {
e.printStackTrace();
}finally {
if(session!=null)session.close();
}
}
注:这是从student这边出发所做的一些操作,从course一边开始操作是一样的,因为俩者的关系是多对多(对称的).
同时不论是一对一还是一对多还是多对多,都不能在mybatis中进行级联保存、更新、删除,我们需要使用sql语句控制每一步操作
网友评论