Mybatis多对一一对多查询

一、环境搭建

数据库表：

CREATE TABLE `teacher` (
  `id` int(10) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` 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;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '小明', '1');
INSERT INTO `student` VALUES ('2', '小红', '1');
INSERT INTO `student` VALUES ('3', '小张', '1');
INSERT INTO `student` VALUES ('4', '小李', '1');
INSERT INTO `student` VALUES ('5', '小王', '1');

1.导入lombok



    4.0.0
    
    org.leo
    mybatis_demo
    pom
    1.0-SNAPSHOT
    
        mybatis_01
    
    
    
        
        
            mysql
            mysql-connector-java
            8.0.27
        
        
        
        
            org.mybatis
            mybatis
            3.5.9
        
        
        
            junit
            junit
            4.12
        
        
        
        
            org.projectlombok
            lombok
            1.18.22
        
    
    
        
            
                src/main/resources
                
                    ***.xml
                
                true
            
            
                src/main/java
                
                    ***.xml
                
                true
            
        
    
    
        8
        8

2.新建实体类Student、Mapper

package com.leo.pojo;

import lombok.Data;

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

package com.leo.pojo;

import lombok.Data;

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

3.建立Mapper接口

package com.leo.dao;

public interface StudentMapper {
}

package com.leo.dao;

import com.leo.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

5.在核心配置文件中绑定注册Mapper接口或文件

6.测试查询是否成功

（1）工具类

package com.leo.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;

// mybatis工具类
public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        String resource = "mybatis-config.xml";
        try {
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 既然有了 SqlSessionFactory，顾名思义，我们可以从中获得 SqlSession 的实例。SqlSession 提供了在数据库执行SQL命令所需的所有方法。
    // 你可以通过 SqlSession 实例来直接执行已映射的 SQL 语句。例如：
    public static SqlSession getSqlSession() {
        return sqlSessionFactory.openSession();
    }
}

（2）测试类

package com.leo.dao;

import com.leo.pojo.Teacher;
import com.leo.utils.MybatisUtils;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class TeacherMapperTest {

    @Test
    public void test() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }
}

（3）执行结果

二、多对一查询

1.按照查询嵌套处理（子查询）

（1）mapper接口

package com.leo.dao;

import com.leo.pojo.Student;

import java.util.List;

public interface StudentMapper {
    public List getStudent();
}

（2） mapper xml




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

（3）测试代码

package com.leo.dao;

import com.leo.pojo.Student;
import com.leo.pojo.Teacher;
import com.leo.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

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

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

（4）执行

三、一对多查询

1.实体pojo

package com.leo.pojo;

import lombok.Data;

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

package com.leo.pojo;

import lombok.Data;

import java.util.List;

@Data
public class Teacher {
    private int id;
    private String name;
    // 一个老师拥有多个学生
    private List students;
}

2.dao

package com.leo.dao;

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

public interface TeacherMapper {
   // 获取指定老师下的所有学生及老师的信息（嵌套查询）
   Teacher getTeacher(@Param("tid") int id);
   // 获取指定老师下的所有学生及老师的信息（子查询）
   Teacher getTeacher2(@Param("tid") int id);
}

package com.leo.dao;

public interface StudentMapper {

}

3.mapper.xml






    
        select * from teacher where id = #{tid}

4.测试代码

package com.leo.dao;

import com.leo.pojo.Teacher;
import com.leo.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class TeacherMapperTest {

    @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();
    }

}

5.执行

Mybatis多对一一对多查询

Java相关栏目本月热门文章