首页 > 软件编程 > Java > Mybatis实现一对一、一对多关联查询的方法(示例详解)

Mybatis实现一对一、一对多关联查询的方法(示例详解)

福牛 4月 01, 2023 36 0

MyBATis实现一对一、一对多关联查询，关联查询：多个表联合查询，只查询一次，通过resultMap里面的<association>、<collection>标签配置一对一、一对多。

涉及的sql文件、idea工程文件：Mybatis实现一对一、一对多关联查询资源

本篇文章涉及的一对一、一对多关系

  • 班级classes、班主任teacher是一对一的关系
  • 班级classes、学生student是一对多的关系

-1

使用IDEA快速构建一个MyBatis工程

-2

pom.XML

  1. <?xml version=“1.0” encoding=“UTF-8”?>
  2. <project xmlns=“http://maven.apache.org/POM/4.0.0”
  3.              xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance”
  4.              xsi:schemaLocation=“http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd”>
  5.      <modelVersion>4.0.0</modelVersion>
  6.  
  7.      <groupId>com</groupId>
  8.      <artifactId>sun</artifactId>
  9.      <version>1.0-SNAPSHOT</version>
  10.      <name>mybatisTest</name>
  11.  
  12.      <properties>
  13.          <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  14.          <maven.compiler.target>1.8</maven.compiler.target>
  15.          <maven.compiler.source>1.8</maven.compiler.source>
  16.          <junit.version>5.8.2</junit.version>
  17.      </properties>
  18.  
  19.      <dependencies>
  20.          <dependency>
  21.              <groupId>org.mybatis</groupId>
  22.              <artifactId>mybatis</artifactId>
  23.              <version>3.2.2</version>
  24.          </dependency>
  25.          <dependency>
  26.              <groupId>mysql</groupId>
  27.              <artifactId>mysql-connector-Java</artifactId>
  28.              <version>8.0.30</version>
  29.          </dependency>
  30.          <dependency>
  31.              <groupId>org.projectlombok</groupId>
  32.              <artifactId>lombok</artifactId>
  33.              <version>1.18.24</version>
  34.          </dependency>
  35.          <dependency>
  36.              <groupId>log4j</groupId>
  37.              <artifactId>log4j</artifactId>
  38.              <version>1.2.17</version>
  39.          <;/dependency>
  40.          <dependency>
  41.              <groupId>org.apache.logging.log4j</groupId>
  42.              <artifactId>log4j-core</artifactId>
  43.              <version>2.13.3</version>
  44.          </dependency>
  45.          <dependency>
  46.              <groupId>org.junit.jupiter</groupId>
  47.              <artifactId>junit-jupiter-api</artifactId>
  48.              <version>${junit.version}</version>
  49.              <scope>test</scope>
  50.          </dependency>
  51.          <dependency>
  52.              <groupId>org.junit.jupiter</groupId>
  53.              <artifactId>junit-jupiter-engine</artifactId>
  54.              <version>${junit.version}</version>
  55.              <scope>test</scope>
  56.          </dependency>
  57.          <dependency>
  58.              <groupId>junit</groupId>
  59.              <artifactId>junit</artifactId>
  60.              <version>4.13.2</version>
  61.              <scope>test</scope>
  62.          </dependency>
  63.      </dependencies>
  64.      <build>
  65.          <resources>
  66.              <resource>
  67.                  <!– directory：指定资源文件的位置 –>
  68.                  <directory>src/main/java</directory>
  69.                  <includes>
  70.                      <!– “**” 表示任意级目录 “*”表示任意任意文件 –>
  71.                      <!– mvn resources:resources　：对资源做出处理，先于compile阶段 –>
  72.                      <include>**/*.properties</include>
  73.                      <include>**/*.xml</include>
  74.                  </includes>
  75.                  <!– filtering：开启过滤，用指定的参数替换directory下的文件中的参数(eg. ${name}) –>
  76.                  <filtering>false</filtering>
  77.              </resource>
  78.              <resource>
  79.                  <directory>src/main/resources</directory>
  80.              </resource>
  81.          </resources>
  82.      </build>
  83. </project>
  84.  

mybatis-config.xml

  1. <?xml version=“1.0” encoding=“UTF-8” ?>
  2. <!DOCTYPE configuration
  3.          PUBLIC “-//mybatis.org//DTD Config 3.0//EN”
  4.          “http://mybatis.org/dtd/mybatis-3-config.dtd”>
  5.  
  6. <!– 通过这个配置文件完成mybatis与数据库的连接 –>
  7. <configuration>
  8.      <!– 引入 database.properties 文件–>
  9.      <properties resource=“database.properties”/>
  10.  
  11.      <!– 配置mybatis的log实现为LOG4J –>
  12.      <settings>
  13.          <setting name=“logImpl” value=“LOG4J” />
  14.      </settings>
  15.  
  16.      <environments default=“development”>
  17.          <environment id=“development”>
  18.              <!–配置事务管理，采用JDBC的事务管理 –>
  19.              <transactionManager type=“JDBC”></transactionManager>
  20.              <!– POOLED:mybatis自带的数据源，JNDI:基于tomcat的数据源 –>
  21.              <dataSource type=“POOLED”>
  22.                  <property name=“driver” value=“${driver}”/>
  23.                  <property name=“url” value=“${url}”/>
  24.                  <property name=“username” value=“${user}”/>
  25.                  <property name=“password” value=“${password}”/>
  26.              </dataSource>
  27.          </environment>
  28.      </environments>
  29.  
  30.      <!– 将mapper文件加入到配置文件中 –>
  31.      <mappers>
  32.          <mapper resource=“com/sun/mapper/ClassesMapper.xml”/>
  33.      </mappers>
  34. </configuration>
  35.  

database.properties

  1. driver=com.mysql.cj.jdbc.Driver
  2. url=jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
  3. user=root
  4. password=root

MybatisUtil.java

  1. package com.sun.utils;
  2.  
  3. import org.apache.ibatis.io.Resources;
  4. import org.apache.ibatis.session.SqlSession;
  5. import org.apache.ibatis.session.SqlSessionFactory;
  6. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  7.  
  8. import java.io.InputStream;
  9.  
  10. /**
  11.      * @author JumperSun
  12.      * @date 2023-03-26-14:46
  13.      */
  14. public class MyBatisUtil {
  15.      private static SqlSessionFactory factory;
  16.  
  17.      static {
  18.          try {
  19.              InputStream is = Resources.getResourceAsStream(“mybatis-config.xml”);
  20.              factory = new SqlSessionFactoryBuilder().build(is);
  21.          } catch (Exception e) {
  22.              e.printStackTrace();
  23.          }
  24.      }
  25.  
  26.      public static SqlSession createSqlSession(){
  27.          return factory.openSession(false); // true为自动提交事务
  28.      }
  29.  
  30.      public static void closeSqlSession(SqlSession sqlSession){
  31.          if(null != sqlSession)
  32.              sqlSession.close();
  33.      }
  34. }
  35.  

Teacher.java

  1. package com.sun.pojo;
  2.  
  3. import lombok.Data;
  4.  
  5. /**
  6.      * @author JumperSun
  7.      * @date 2023-03-26-14:51
  8.      */
  9. @Data
  10. public class Teacher {
  11.      private int id;
  12.      private String name;
  13. }

Student.java

  1. package com.sun.pojo;
  2.  
  3. import lombok.Data;
  4.  
  5. /**
  6.      * @author JumperSun
  7.      * @date 2023-03-26-14:53
  8.      */
  9. @Data
  10. public class Student {
  11.      private int id;
  12.      private String name;
  13. }

Classes.java

  1. package com.sun.pojo;
  2.  
  3. import lombok.Data;
  4.  
  5. import java.util.List;
  6.  
  7. /**
  8.      * @author JumperSun
  9.      * @date 2023-03-26-14:52
  10.      */
  11. @Data
  12. public class Classes {
  13.      private int id;
  14.      private String name;
  15.      private Teacher teacher;
  16.      private List<Student> studentList;
  17. }

classMapper.java

  1. package com.sun.mapper;
  2.  
  3. import com.sun.pojo.Classes;
  4. import org.apache.ibatis.annotations.Param;
  5.  
  6. import java.util.List;
  7.  
  8. /**
  9.      * @author JumperSun
  10.      * @date 2023-03-26-14:54
  11.      */
  12. public interface ClassesMapper {
  13.      List<Classes> getClassesTeacher(@Param(“id”) int id);
  14.  
  15.      List<Classes> getClassesTeacherStudentList(@Param(“id”) int id);
  16. }

classMapper.xml

  1. <?xml version=“1.0” encoding=“UTF-8”?>
  2. <!DOCTYPE mapper PUBLIC “-//mybatis.org//DTD Mapper 3.0//EN” “http://mybatis.org/dtd/mybatis-3-mapper.dtd”>
  3. <mapper namespace=“com.sun.mapper.ClassesMapper”>
  4.  
  5.      <!– 一对一关联查询 –>
  6.      <select id=“getClassesTeacher” parameterType=“int” resultMap=“ClassesResultMap”>
  7.          select * from classes c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
  8.      </select>
  9.  
  10.      <resultMap type=“com.sun.pojo.Classes” id=“ClassesResultMap”>
  11.          <!– 实体类的字段名和数据表的字段名映射 –>
  12.          <id property=“id” column=“c_id”/>
  13.          <result property=“name” column=“c_name”/>
  14.          <association property=“teacher” javaType=“com.sun.pojo.Teacher”>
  15.              <id property=“id” column=“t_id”/>
  16.              <result property=“name” column=“t_name”/>
  17.          </association>
  18.      </resultMap>
  19.  
  20.      <!– 一对多关联查询 –>
  21.      <select id=“getClassesTeacherStudentList” parameterType=“int” resultMap=“ClassesResultMap2”>
  22.          select * from classes c,teacher t,student s where c.teacher_id=t.t_id and c.c_id=s.class_id and c.c_id=#{id}
  23.      </select>
  24.  
  25.      <resultMap type=“com.sun.pojo.Classes” id=“ClassesResultMap2”>
  26.          <id property=“id” column=“c_id”/>
  27.          <result property=“name” column=“c_name”/>
  28.          <association property=“teacher” javaType=“com.sun.pojo.Teacher”>
  29.              <id property=“id” column=“t_id”/>
  30.              <result property=“name” column=“t_name”/>
  31.          </association>
  32.          <collection property=“studentList” ofType=“com.sun.pojo.Student”>
  33.              <id property=“id” column=“s_id”/>
  34.              <result property=“name” column=“s_name”/>
  35.          </collection>
  36.      </resultMap>
  37.  
  38. </mapper>
  39.  

MapperTest.java

  1. import com.sun.mapper.ClassesMapper;
  2. import com.sun.pojo.Classes;
  3. import com.sun.utils.MyBatisUtil;
  4. import org.apache.ibatis.session.SqlSession;
  5. import org.junit.Test;
  6.  
  7. import java.util.List;
  8.  
  9. /**
  10.      * @author JumperSun
  11.      * @date 2023-03-26-15:10
  12.      */
  13. public class MapperTest {
  14.      /**
  15.          * 测试班级与班主任老师一对一的关联关系
  16.          */
  17.      @Test
  18.      public void test1() {
  19.          SqlSession sqlSession = MyBatisUtil.createSqlSession();
  20.          ClassesMapper classesMapper = sqlSession.getMapper(ClassesMapper.class);
  21.          List<Classes> classes = classesMapper.getClassesTeacher(1);
  22.          for (Classes Classes : classes) {
  23.              System.out.println(classes);
  24.          }
  25.  
  26.      }
  27.  
  28.      /**
  29.          * 测试班级与学生一对多关联关系
  30.          */
  31.      @Test
  32.      public void test2() {
  33.          SqlSession sqlSession = MyBatisUtil.createSqlSession();
  34.          ClassesMapper classesMapper = sqlSession.getMapper(ClassesMapper.class);
  35.          List<Classes> classes = classesMapper.getClassesTeacherStudentList(1);
  36.          for (Classes Classes : classes) {
  37.              System.out.println(classes);
  38.          }
  39.  
  40.      }
  41. }
  42.  

test1:

-3

test2:

-4

总结：一对一关联查询需要在resultMap里面配置association，一对多关联查询需要在resultMap里面配置collection；

  1. List classes = classesMapper.getClassesTeacherStudentList(1);
  2. for (Classes Classes : classes) {
  3. System.out.println(classes);
  4. }}
  5.  
  6. }
  7. test1:
  8. [外链图片转存中…(img–qon5qYiR–1680248446105)]
  9. test2:
  10. [外链图片转存中…(img–dzayGx8q–1680248446107)]
  11. 总结：一对一关联查询需要在resultMap里面配置association，一对多关联查询需要在resultMap里面配置collection；

到此这篇关于Mybatis实现一对一、一对多关联查询的方法(示例详解)的文章就介绍到这了,更多相关Mybatis关联查询内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

标签

相关推荐

发表评论