mybatis一对多查询

来源：互联网发布：大数据产业链包括编辑：程序博客网时间：2024/04/30 13:49

要求：根据classId查询对应的班级信息,包括学生,老师

根据上一遍博文

CREATE TABLE student(s_id INT PRIMARY KEY AUTO_INCREMENT, s_name VARCHAR(20), class_id INT);INSERT INTO student(s_name, class_id) VALUES('xs_A', 1);INSERT INTO student(s_name, class_id) VALUES('xs_B', 1);INSERT INTO student(s_name, class_id) VALUES('xs_C', 1);INSERT INTO student(s_name, class_id) VALUES('xs_D', 2);INSERT INTO student(s_name, class_id) VALUES('xs_E', 2);INSERT INTO student(s_name, class_id) VALUES('xs_F', 2);

Classes.java

package com.lx.bean;import java.util.List;public class Classes {private int id;private String name;private Teacher teacher;private List<Student> list;public Classes(int id, String name, Teacher teacher) {super();this.id = id;this.name = name;this.teacher = teacher;}public Classes(int id, String name, Teacher teacher, List<Student> list) {super();this.id = id;this.name = name;this.teacher = teacher;this.list = list;}public Classes() {super();}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Teacher getTeacher() {return teacher;}public void setTeacher(Teacher teacher) {this.teacher = teacher;}public List<Student> getList() {return list;}public void setList(List<Student> list) {this.list = list;}@Overridepublic String toString() {return "Classes [id=" + id + ", name=" + name + ", teacher=" + teacher+ ", list=" + list + "]";}}

Student.java

package com.lx.bean;public class Student {private int id;private String name;public Student(int id, String name) {super();this.id = id;this.name = name;}public Student() {super();}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}@Overridepublic String toString() {return "Student [id=" + id + ", name=" + name + "]";}}

ClassMapper.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="com.lx.test5.ClassMapper">                   <!--                                 方式一：一对多          -->                    <select id="getClass" parameterType="int" resultMap="getClassMap">              select * from class c,teacher t,student s where c.teacher_id=c.c_id and c.c_id = s.class_id and c.c_id=#{id}          </select>                      <resultMap type="Classes" id="getClassMap">             <id property="id" column="c_id"/>             <result property="name" column="c_name"/>             <association property="teacher" javaType="Teacher">                 <id property="id" column="t_id"/>              <result property="name" column="t_name"/>             </association>             <collection property="list" ofType="Student">                <id property="id" column="s_id"/>                <result property="name" column="s_name"/>             </collection>          </resultMap>                     <!--                                    方式二：一对多           -->          <select id="getClass2" parameterType="int" resultMap="getClass2Map">               select * from class where c_id=#{id}          </select>          <select id="getTeacher" parameterType="int" resultType="Teacher">             select t_id id,t_name name from teacher where t_id=#{id}           </select>          <select id="getStudent" parameterType="int" resultType="Student">            select s_id id,s_name name from student where class_id=#{id}          </select>          <resultMap type="Classes" id="getClass2Map">             <id property="id" column="c_id"/>             <result property="name" column="c_name"/>              <association property="teacher" column="teacher_id" select="getTeacher">             </association>             <collection property="list" column="c_id" select="getStudent">             </collection>                       </resultMap></mapper>

测试类：

package com.lx.test5;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.junit.Test;import com.lx.bean.Classes;import com.lx.utils.FactoryUtil;public class Test5 {@Testpublic void test1(){SqlSessionFactory factory = FactoryUtil.getFactory();SqlSession session = factory.openSession();String statement = "com.lx.test5.ClassMapper.getClass";Classes classes = session.selectOne(statement, 1);System.out.println(classes);session.close();}@Testpublic void test2(){SqlSessionFactory factory = FactoryUtil.getFactory();SqlSession session = factory.openSession();String statement = "com.lx.test5.ClassMapper.getClass2";Classes classes = session.selectOne(statement, 1);System.out.println(classes);session.close();}}

0 0