Mybatis多对一一对多查询

原创

深漂小码哥 2022-04-16 09:18:41 ©著作权

文章标签 java mybatis sql xml apache 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者深漂小码哥的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、环境搭建

数据库表：

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

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http:///POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http:///POM/4.0.0 http:///xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <!--父工程-->
    <groupId>org.leo</groupId>
    <artifactId>mybatis_demo</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>mybatis_01</module>
    </modules>
    <!--导入依赖-->
    <dependencies>
        <!--mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>
        <!--mybatis-->
        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.9</version>
        </dependency>
        <!--junit-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <!--lombok-->
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.22</version>
        </dependency>
    </dependencies>
    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

</project>

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

<?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.leo.dao.StudentMapper">

</mapper>

<?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.leo.dao.TeacherMapper">

</mapper>

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

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="db.properties"></properties>
    <!--<settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>-->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper class="com.leo.dao.StudentMapper"/>
        <mapper class="com.leo.dao.TeacherMapper"/>
    </mappers>
</configuration>

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）执行结果

Mybatis多对一一对多查询_java

二、多对一查询

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

（1）mapper接口

package com.leo.dao;

import com.leo.pojo.Student;

import java.util.List;

public interface StudentMapper {
    public List<Student> getStudent();
}

（2） mapper 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.leo.dao.StudentMapper">
    <select id="getStudent" resultMap="StudentTeacher">
        select * from student
    </select>
    <resultMap id="StudentTeacher" type="com.leo.pojo.Student">
        <result column="id" property="id"></result>
        <result column="name" property="name"></result>
        <association column="tid" property="teacher" javaType="com.leo.pojo.Teacher" select="getTeacher"></association>
    </resultMap>
    <select id="getTeacher" resultType="com.leo.pojo.Teacher">
        select * from teacher where id = #{id}
    </select>
</mapper>

（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<Student> studentList = mapper.getStudent();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

（4）执行

Mybatis多对一一对多查询_apache_02

2.按照结果嵌套处理（连表查询）

（1）mapper接口

package com.leo.dao;

import com.leo.pojo.Student;

import java.util.List;

public interface StudentMapper {
    // 按照查询嵌套处理
    public List<Student> getStudent();
    // 按照结果嵌套处理
    public List<Student> getStudent2();
}

（2） mapper 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.leo.dao.StudentMapper">
    <select id="getStudent" resultMap="StudentTeacher">
        select * from student
    </select>
    <resultMap id="StudentTeacher" type="com.leo.pojo.Student">
        <result column="id" property="id"></result>
        <result column="name" property="name"></result>
        <association column="tid" property="teacher" javaType="com.leo.pojo.Teacher" select="getTeacher"></association>
    </resultMap>
    <select id="getTeacher" resultType="com.leo.pojo.Teacher">
        select * from teacher where id = #{id}
    </select>


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

（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<Student> 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<Student> studentList = mapper.getStudent2();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }
}

（4）执行

Mybatis多对一一对多查询_mybatis_03

三、一对多查询

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<Student> 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

<?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.leo.dao.StudentMapper">
</mapper>

<?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.leo.dao.TeacherMapper">


    <select id="getTeacher" resultMap="TeacherStudent">
        select s.id sid,  sname, tname, tid
        from student s,teacher t
        where s.tid =  and  = #{tid}
    </select>
    <resultMap id="TeacherStudent" type="com.leo.pojo.Teacher">
        <result column="tid" property="id"></result>
        <result column="tname" property="name"></result>
        <collection property="students" ofType="com.leo.pojo.Student">
            <result column="sid" property="id"></result>
            <result column="sname" property="name"></result>
            <result column="tid" property="tid"></result>
        </collection>
    </resultMap>
    <select id="getTeacher2" resultMap="TeacherStudent2">
        select * from teacher where id = #{tid}
    </select>
    <resultMap id="TeacherStudent2" type="com.leo.pojo.Teacher">
       <collection property="students" javaType="ArrayList" ofType="com.leo.pojo.Student">

       </collection>
    </resultMap>
    <select id="getStudentByTeacherId" resultType="com.leo.pojo.Student">
        select * from mybatis.student where tid = #{tid}
    </select>
</mapper>

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多对一一对多查询_xml_04