Mybatis 07 一对多表关联查询
程序员文章站
2024-03-23 09:06:10
...
实例:根据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 结果集
上一篇: oracle 备份