共计 2984 个字符,预计需要花费 8 分钟才能阅读完成。
1、多表查询(多对多)
1.1、使用多表查询前的准备
教师表(teacher):
教师学生表 (tors):
1.2、需求分析
查看所有老师下面的学生信息
老师信息与学生信息为多对多的关系,一个学生有多个老师,一个老师同样可以有多个学生。要想知道老师下的学生信息,不能直接查到我们的学生信息,只能通过中间表(tors)才能关联到学生信息。
SQL 如下:
select t.tid,t.tname,s.* from teacher t left join tors ts on ts.tid=t.tid left join students s on s.sid=ts.sid order by t.tid
2、案例实现
2.1、创建 Teacher.java 文件
import java.io.Serializable;
import java.util.List;
public class Teacher implements Serializable {private int tid;
private String tname;
private List<StudentsNew> students;
public int getTid() {return tid;
}
public void setTid(int tid) {this.tid = tid;
}
public String getTname() {return tname;
}
public void setTname(String tname) {this.tname = tname;
}
public List<StudentsNew> getStudents() {return students;
}
public void setStudents(List<StudentsNew> students) {this.students = students;
}
@Override
public String toString() {return "Teacher{" +
"tid=" + tid +
", tname='" + tname + '\'' +
'}';
}
}
2.2、创建 ITeacherDao.java 文件
package com.tyschool.mb005.students.dao;
import com.tyschool.mb005.javabean.Teacher;
import java.util.List;
public interface ITeacherDao {List<Teacher> findAll();
}
2.3、创建 ITeacherDao.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.tyschool.mb005.students.dao.ITeacherDao">
<resultMap id="teacherMap" type="Teacher">
<id column="tid" property="tid"></id>
<result column="tname" property="tname"></result>
<collection property="students" ofType="StudentsNew">
<id column="sid" property="sid"></id>
<result column="sname" property="sname"></result>
<result column="ssex" property="ssex"></result>
<result column="sage" property="sage"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="teacherMap">
select s.*,t.tid,t.tname from teacher t left join tors ts on ts.tid=t.tid left join students s on s.sid=ts.sid order by t.tid
</select>
</mapper>
<mappers>
<mapper resource="com/tyschool/mb005/students/dao/ITeacherDao.xml"></mapper>
</mappers>
2.4、创建 MbTeacherTest.java 文件
import com.tyschool.mb005.javabean.Classes;
import com.tyschool.mb005.javabean.Teacher;
import com.tyschool.mb005.students.dao.IClassesDao;
import com.tyschool.mb005.students.dao.ITeacherDao;
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.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class MbTeacherTest {private InputStream in;
private SqlSession session;
private ITeacherDao teacherDao;
@Test
public void findAll(){List<Teacher> list=teacherDao.findAll();
for(Teacher t:list){System.out.println(t+":"+t.getStudents());
}
}
@Before
public void init()throws IOException {in= Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
SqlSessionFactory factory=builder.build(in);
session=factory.openSession();
teacherDao=session.getMapper(ITeacherDao.class);
}
@After
public void destroy() throws IOException {session.commit();;
session.close();
in.close();}
}
正文完
星哥玩云-微信公众号