java mybatis连表查询

 

TeacherMapper.xml文件



<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--核心配置文件-->
<mapper namespace="com.kuang.dao.TeacherMapper">
    <select id="getTeacher" resultType="Teacher">
        select *
        from mybatis.teacher;
    </select>

    <!--    按照结果嵌套查询-->
    <select id="getTeacher2" resultMap="TeacherStudent">
        select s.s_id,s.s_name,t.t_name,t.t_id
        from teacher t,student s
        where s.t_id=t.t_id and t.t_id=#{t_id}
    </select>

    <resultMap id="TeacherStudent" type="Teacher">
        <result property="t_id" column="t_id"/>
        <result property="t_name" column="t_name"/>
        <!--复杂的属性我们要单独处理
            对象：association
            集合：collection

            javaType=""指定属性类型
            结合中的泛型信息，我们使用 ofTypeu获取
        -->
        <collection property="students" ofType="Student">
            <result property="s_id" column="s_id"/>
            <result property="s_name" column="s_name"/>
            <result property="t_id" column="t_id"/>
        </collection>
    </resultMap>


    <!--    ==========================================-->

    <select id="getTeacher3" resultMap="TeacherStudent2">
        select * from mybatis.teacher where t_id=#{t_id}
    </select>

    <resultMap id="TeacherStudent2" type="Teacher">
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId"
                    column="t_id"/>
    </resultMap>

    <select id="getStudentByTeacherId" resultType="Student">
        select * from mybatis.student where t_id=#{t_id}
    </select>

</mapper>

pojo实体类

package com.kuang.pojo;

import lombok.Data;

import java.util.List;

@Data
public class Teacher {
    private int t_id;
    private String t_name;
    //一个老师拥有多个学生
    private List<Student> students;
}

dao接口类

package com.kuang.dao;

import com.kuang.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface TeacherMapper {
    //获取老师
    List<Teacher> getTeacher();

    //获取指定老师下的所有学生以及老师信息
    Teacher getTeacher2(@Param("t_id") int t_id);

    //获取指定老师下的所有学生以及老师信息  子查询
    Teacher getTeacher3(@Param("t_id") int t_id);

}

MybatisUtils 工具类

package com.kuang.utils;


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 java.io.IOException;
import java.io.InputStream;

//工具类
//sqlSessionFactory -->sqlSession
public class MybatisUtils {

    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            //使用mybatis第一步，获取sqlSessionFactory对象
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //既然有了SqlSessionFactory，顾名思义，我们就可以从中获得SqlSeseion 的实例
    //SqlSession 完全包含了面向数据库执行SQL命令所需的所有方法
    public static SqlSession getSqlSession() {
        //sqlSession sqlSession=new sqlSessionFactory.openSession();
        //return sqlSession
        return sqlSessionFactory.openSession(true);
        // sqlSessionFactory.openSession();

    }

}

MyTest 测试类

import com.kuang.dao.TeacherMapper;
import com.kuang.pojo.Teacher;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

public class MyTest {

    @Test
    public void test() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        List<Teacher> teacher = sqlSession.getMapper(TeacherMapper.class).getTeacher();
        for (Teacher teacher1 : teacher) {
            System.out.println(teacher1);
        }

        sqlSession.close();
    }


    @Test
    public void getTeacher2() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        Teacher teacher = sqlSession.getMapper(TeacherMapper.class).getTeacher2(1);

        System.out.println(teacher);

//        Teacher(t_id=1, t_name=张老师, students=[Student(s_id=1, s_name=张三, t_id=1), Student(s_id=2, s_name=李四, t_id=1), Student(s_id=3, s_name=王五, t_id=1)])
        sqlSession.close();
    }

    @Test
    public void getTeacher3() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        Teacher teacher = sqlSession.getMapper(TeacherMapper.class).getTeacher3(1);

        System.out.println(teacher);

//        Teacher(t_id=1, t_name=张老师, students=[Student(s_id=1, s_name=张三, t_id=1), Student(s_id=2, s_name=李四, t_id=1), Student(s_id=3, s_name=王五, t_id=1)])
        sqlSession.close();
    }

}