Mybatis——复杂查询环境的搭建（多对一的处理&一对多的处理）

多对一的处理

多对一：

多个学生，对应一个老师
对于学生而言，多个学生关联一个老师（关联关系，多对一，association）
对于老师而言，一个老师集合了多个学生（集合关系，一对多，collection）

SQL:

create table `teacher`(
	`id` int(10) not null,
	`name` varchar(30) default null,
primary key (`id`)
)engine=INNODB DEFAULT CHARSET=utf8;

insert into teacher(`id`,`name`) values(1,'秦老师')


create table `student`(
	`id` int(10) not null,
	`name` varchar(30) default null,
	`tid` int(10) default null,
primary key (`id`),
key `fktid` (`tid`),
constraint `fktid` foreign key (`tid`) references `teacher` (`id`)
)engine=INNODB DEFAULT CHARSET=utf8;


insert into `student` (id,name,tid) values('1','夏明','1');
insert into `student` (id,name,tid) values('2','晓晓','1');
insert into `student` (id,name,tid) values('3','江南','1');
insert into `student` (id,name,tid) values('4','卡卡','1');
insert into `student` (id,name,tid) values('5','帆帆','1');

查询所有的学生信息，以及对应的老师信息

select s.id,s.name,t.name from student s,teacher t where s.tid=t.id

按照查询嵌套处理


        select * from teacher where id = #{id}


    
        select * from teacher where id = #{id}

按照结果嵌套处理


    
        select * from student
    

    


        
    

    
        select s.id sid,s.name sname,t.name tname from student s,teacher t where s.tid=t.id

8.创建测试类——StudentMapperTest

package com.kuang.dao;

import com.kuang.pojo.Student;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.Test;

import java.util.List;

public class StudentMapperTest {

    static Logger logger = Logger.getLogger(StudentMapperTest.class);

    @Test
    public void getStudent(){
        SqlSession session = MybatisUtils.getSqlSession();

        StudentMapper mapper = session.getMapper(StudentMapper.class);
        List student = mapper.getStudent();

        for (Student s:student) {
            System.out.println(s);
        }

        session.close();
    }

    @Test
    public void getStudent2(){
        SqlSession session = MybatisUtils.getSqlSession();

        StudentMapper mapper = session.getMapper(StudentMapper.class);
        List student = mapper.getStudent2();

        for (Student s:student) {
            System.out.println(s);
        }

        session.close();
    }
}

9.各文件位置

一对多处理

比如：一个老师拥有多个学生
对于老师而言，就是一对多的关系

SQL:

select s.id sid,s.name sname, t.name tname,t.id tid from student s,teacher t
        where s.tid=t.id and t.id=#{tid}

按照查询嵌套处理


        select * from teacher where id=#{tid}
    
    
        
    
    
        select s.id sid,s.name sname, t.name tname,t.id tid from student s,teacher t
        where s.tid=t.id and t.id=#{tid}

代码流程 1.创建模板mybatis-07 2.创建数据库配置信息——db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&useEncoding=false&characterEncoding=UTF-8&serverTimezone=GMT
username=root
password=root

3.创建mybatis配置信息——mybatis-config.xml

4.创建mybatis工具类——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 {

    //提升SqlSessionFactory的作用域
    private static SqlSessionFactory sqlSessionFactory;

    static{
        try {
//            使用Mybatis第一步获取sqlSessionFactory对象
            String resource = "mybatis-config.xml";
//            通过Resources读取配置文件
            InputStream inputStream = Resources.getResourceAsStream(resource);
//          通过SqlSessionFactoryBuilder加载一个流，构建一个sqlSession工厂
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        }catch (IOException e){
            e.printStackTrace();
        }
    }
    //    既然有了 SqlSessionFactory，顾名思义，我们可以从中获得 SqlSession 的实例。
//    SqlSession 提供了在数据库执行 SQL 命令所需的所有方法。
    public static SqlSession getSqlSession(){

        return sqlSessionFactory.openSession(true);

    }
}

5.创建实体类

Student

package com.kuang.pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;
   private  int tid;
}

Teacher

package com.kuang.pojo;

import lombok.Data;

import java.util.List;

@Data
public class Teacher {
    private int id;
    private String name;

    //一个老师拥有多个学生
    private List students;
}

6.创建接口——TeacherMapper

package com.kuang.dao;

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

import java.util.List;

public interface TeacherMapper {

    //获取老师
//    List getTeacher();

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

    Teacher getTeacher2(@Param("tid") int id);
}

7.创建接口配置类——TeacherMapper.xml






    
        select * from teacher where id=#{tid}

8.创建测试类——TeacherMapperTest

package dao;

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

import java.util.List;

public class TeacherMapperTest {

    static Logger logger = Logger.getLogger(TeacherMapperTest.class);

    @Test
    public  void getTeacher(){
        SqlSession session = MybatisUtils.getSqlSession();
        TeacherMapper t = (TeacherMapper)session.getMapper(TeacherMapper.class);
        Teacher teacher = t.getTeacher(1);
        System.out.println(teacher);

        session.close();
    }

    @Test
    public  void getTeacher2(){
        SqlSession session = MybatisUtils.getSqlSession();
        TeacherMapper t = (TeacherMapper)session.getMapper(TeacherMapper.class);
        Teacher teacher = t.getTeacher2(1);
        System.out.println(teacher);

        session.close();
    }

}

9.各文件位置

小结

1.关联-association
2.集合-collection
3.javaType&ofType
javaType：用来指定实体类中属性的类型
ofType：用来指定映射到List或者集合中的pojo类型，泛型中的约束类型

注意点

保证SQL的可读性，尽量保证通俗易懂
注意一对多和多对一中，属性名和字段的问题
如果问题不好排查错误，建议使用log4j

参考：

Mybatis——配置解析
Invalid bound statement (not found)解决方法

Mybatis——复杂查询环境的搭建（多对一的处理&一对多的处理）

Java相关栏目本月热门文章