实例:根据classId查询对应的班级信息,包括查询出附带学生和老师的信息
1.创建表和数据集
新建一个学生表,class_id表示班级id,一个班级下有多个学生
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 ) ;
2.定义实体类:
2.1 创建Student类
package com. dhl. beyond. bean;
public class Student {
private int id;
private String name;
@Override
public String toString ( ) {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
'}' ;
}
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;
}
}
2.2 在之前创建的Classes类中添加 students属性(集合实现),并生成相应的get和set方法.
改后的Classes类
package com. dhl. beyond. bean;
import java. util. List;
public class Classes {
private int id;
private String name;
private Teacher teacher;
private List< Student> students;
public List< Student> getStudents ( ) {
return students;
}
public void setStudents ( List< Student> students) {
this . students = students;
}
@Override
public String toString ( ) {
return "Classes{" +
"id=" + id +
", name='" + name + '\'' +
", teacher=" + teacher +
", students=" + students +
'}' ;
}
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;
}
}
添加的代码介绍
3.创建Mapper文件
3.1 源码
< ? 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.dhl.beyond.shiyan7.classMapper" >
< select id= "getClass" parameterType= "int" resultMap= "getClassMap" >
select * from class c , teacher t, student s where c. teacher_id = t. t_id and s. class_id = c. c_id and c. c_id = #{ id}
< / select>
< resultMap id= "getClassMap" type= "Classes" >
< 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= "students" ofType= "Student" >
< id property= "id" column= "s_id" / >
< result property= "name" column= "s_name" / >
< / collection>
< / resultMap>
< / mapper>
3.2 源码介绍
4.注册并且编写测试类
4.1注册
4.2 测试类
package com. dhl. beyond. shiyan7;
import com. dhl. beyond. bean. Classes;
import com. dhl. beyond. util. MybatisUtil;
import org. apache. ibatis. session. SqlSession;
import org. junit. Test;
import java. io. IOException;
public class ClassesTest {
@Test
public void getClasses ( ) throws IOException {
SqlSession sqlSession = MybatisUtil. getSqlSession ( ) ;
Classes o = sqlSession. selectOne ( "com.dhl.beyond.shiyan7.classMapper.getClass" , 2 ) ;
System. out. println ( o) ;
}
}
4.3 结果集
5.补充:第二种方式实现(三条 sql语句实现)
5.1 对Mapper文中添加第二种方式,即用三条 sql语句实现, 在上面的mapper文件基础上添加如下语句:
< select id= "getClass2" parameterType= "int" resultMap= "getClassMap2" >
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= "getStudents" parameterType= "int" resultType= "Student" >
select s_id id, s_name name from student where class_id = #{ id}
< / select>
< resultMap id= "getClassMap2" type= "Classes" >
< id property= "id" column= "c_id" / >
< result property= "name" column= "c_name" / >
< association property= "teacher" select= "getTeacher" column= "teacher_id" >
< / association>
< collection property= "students" select= "getStudents" column= "c_id" >
< / collection>
< / resultMap>
代码介绍
5.2 测试类中写测试语句
@Test
public void getClasses2 ( ) throws IOException {
SqlSession sqlSession = MybatisUtil. getSqlSession ( ) ;
Classes o = sqlSession. selectOne ( "com.dhl.beyond.shiyan7.classMapper.getClass2" , 1 ) ;
System. out. println ( o) ;
}
5.3 结果集