mybais-多对一查询

准备阶段(多个学生对应一个老师)

1.建表

2.编写实体类

package com.lv.pojo;
 
import lombok.Data;
 
@Data
public class Student {
    private int id;
    private String name;
    //学生需要关联一个老师
    private Teacher teacher;
}

package com.lv.pojo;
 
import lombok.Data;
 
@Data
public class Teacher {
    private int id;
    private String name;
}

3.编写Mapper接口

package com.lv.dao;
 
public interface StudentMapper {
}

package com.lv.dao;
 
import com.lv.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
 
public interface TeacherMapper {
    @Select("select * from teacher where id = #{tid}")
    Teacher getTeacher(@Param("tid") int id);
}

4.编写Mapper.xml配置文件

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lv.dao.StudentMapper">
 
</mapper>

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lv.dao.TeacherMapper">
 
</mapper>

5.在mybatis-config.xml中添加映射

<mappers>
    <mapper class="com.lv.dao.TeacherMapper"/>
    <mapper class="com.lv.dao.StudentMapper"/>
</mappers>

6.编写测试文件进行测试

package com.lv.dao;
 
import com.lv.pojo.Teacher;
import com.lv.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
 
public class MyTest {
    @Test
    public void getTeacher(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }
}

7.测试结果

方式一:按查询嵌套处理(相当于SQL中的子查询)

1.在StudentMapper接口中编写方法

//查询所有的学生信息,以及对应的老师信息!
public List<Student> getStudent();

2.编写对应的Mapper.xml文件

<resultMap id="StudentTeacher" type="Student">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <!--复杂的属性,我们需要单独处理 对象:association 集合:collection-->
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getStudent" resultMap="StudentTeacher">
    select * from student
</select>
<select id="getTeacher" resultType="Teacher">
    select * from teacher where id = #{id}
</select>

3.测试

@Test
public void getStudent(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> studentList = mapper.getStudent();
    for (Student student : studentList) {
        System.out.println(student);
    }
    sqlSession.close();
}

方式二:按结果嵌套处理(相当于SQL中的连表查询)

1.在StudentMapper接口中编写方法

//查询所有的学生信息,以及对应的老师信息!
public List<Student> getStudent2();

2.编写对应的Mapper.xml文件

<resultMap id="StudentTeacher2" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="name" column="tname"/>
    </association>
</resultMap>
<select id="getStudent2" resultMap="StudentTeacher2">
    select s.id sid,s.name sname,t.name tname
    from student s,teacher t
    where s.tid = t.id;
</select>

3.测试

@Test
public void getStudent2(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> studentList = mapper.getStudent2();
    for (Student student : studentList) {
        System.out.println(student);
    }
    sqlSession.close();
}