MyBatis一对多关系实战

最新推荐文章于 2022-03-22 20:33:59 发布

chengqiuming

最新推荐文章于 2022-03-22 20:33:59 发布

阅读量176

点赞数

文章标签： MyBatis

本文链接：https://blog.csdn.net/chengqiuming/article/details/100176679

版权

一数据表

CREATE TABLE tb_clazz(
id INT PRIMARY KEY AUTO_INCREMENT,
CODE VARCHAR(18),
NAME VARCHAR(18)
);
INSERT INTO tb_clazz(CODE,NAME) VALUES('j1601','Java就业班');
CREATE TABLE tb_student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(18),
sex VARCHAR(18),
age INT,
clazz_id INT,
FOREIGN KEY (clazz_id) REFERENCES tb_clazz(id)
);
INSERT INTO tb_student(NAME,sex,age,clazz_id) VALUES('jack','男',23,1);
INSERT INTO tb_student(NAME,sex,age,clazz_id) VALUES('rose','女',18,1);
INSERT INTO tb_student(NAME,sex,age,clazz_id) VALUES('tom','男',21,1);
INSERT INTO tb_student(NAME,sex,age,clazz_id) VALUES('alice','女',20,1);

二领域模型

1 Clazz

package org.fkit.domain;

import java.io.Serializable;
import java.util.List;

public class Clazz implements Serializable {
    
    private static final long serialVersionUID = 1L;
    
    private Integer id; // 班级id，主键
    private String code; // 班级编号
    private String name; // 班级名称
    
    // 班级和学生是一对多的关系，即一个班级可以有多个学生
    private List<Student> students;
    
    public Clazz() {
        super();
        // TODO Auto-generated constructor stub
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getCode() {
        return code;
    }
    public void setCode(String code) {
        this.code = code;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public List<Student> getStudents() {
        return students;
    }
    public void setStudents(List<Student> students) {
        this.students = students;
    }
    @Override
    public String toString() {
        return "Clazz [id=" + id + ", code=" + code + ", name=" + name + "]";
    }
}

2 Student

package org.fkit.domain;
import java.io.Serializable;

public class Student implements Serializable {
     private static final long serialVersionUID = 1L;
     
     private Integer id; // 学生id，主键
     private String name; // 姓名
     private String sex;  // 性别
     private Integer age; // 年龄
     
     // 学生和班级是多对一的关系，即一个学生只属于一个班级
     private Clazz clazz;
     public Student() {
           super();
           // TODO Auto-generated constructor stub
     }
     public Integer getId() {
           return id;
     }
     public void setId(Integer id) {
           this.id = id;
     }
     public String getName() {
           return name;
     }
     public void setName(String name) {
           this.name = name;
     }
     public String getSex() {
           return sex;
     }
     public void setSex(String sex) {
           this.sex = sex;
     }
     public Integer getAge() {
           return age;
     }
     public void setAge(Integer age) {
           this.age = age;
     }
     public Clazz getClazz() {
           return clazz;
     }
     public void setClazz(Clazz clazz) {
           this.clazz = clazz;
     }
     @Override
     public String toString() {
           return "Student [id=" + id + ", name=" + name + ",  sex=" + sex
                     + ", age=" + age + "]";
     }
     
}

三会话工厂

package org.fkit.factory;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class FKSqlSessionFactory {
    
    private static SqlSessionFactory sqlSessionFactory = null;
    
    // 初始化创建SqlSessionFactory对象
    static{
        try (// 读取mybatis-config.xml文件
                InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            ){
            sqlSessionFactory = new SqlSessionFactoryBuilder()
                    .build(is);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    // 获取SqlSession对象的静态方法
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }

    // 获取SqlSessionFactory的静态方法
    public static SqlSessionFactory getSqlSessionFactory() {
        return sqlSessionFactory;
    }

}

四 mapper

1 ClazzMapper

package org.fkit.mapper;
import org.fkit.domain.Clazz;
public interface ClazzMapper {
     // 根据id查询班级信息
     Clazz selectClazzById(Integer id);
     
}

2 StudentMapper

package org.fkit.mapper;
import org.fkit.domain.Student;
public interface StudentMapper {
     // 根据id查询学生信息
     Student selectStudentById(Integer id);
     
}

3 ClazzMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace指用户自定义的命名空间。 -->
<mapper namespace="org.fkit.mapper.ClazzMapper">
     <!-- 根据id查询班级信息，返回resultMap -->
       <select id="selectClazzById" parameterType="int"  resultMap="clazzResultMap">
          SELECT * FROM tb_clazz  WHERE id = #{id}
       </select>
       
        <!-- 映射Clazz对象的resultMap -->
     <resultMap type="org.fkit.domain.Clazz"  id="clazzResultMap">
           <id property="id" column="id"/>
           <result property="code" column="code"/>
           <result property="name" column="name"/>
           <!-- 一对多关联映射:collection fetchType="lazy"表示懒加载  -->
           <collection property="students" javaType="ArrayList"
       column="id" ofType="org.fkit.domain.Student"
        select="org.fkit.mapper.StudentMapper.selectStudentByClazzId"
       fetchType="lazy">
          <id property="id" column="id"/>
          <result property="name" column="name"/>
          <result property="sex" column="sex"/>
          <result property="age" column="age"/>
       </collection>
     </resultMap>
     
     
  
</mapper>

4 StudentMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace指用户自定义的命名空间。 -->
<mapper namespace="org.fkit.mapper.StudentMapper">
     <!-- 根据id查询学生信息，多表连接，返回resultMap -->
  <select id="selectStudentById" parameterType="int"  resultMap="studentResultMap">
      SELECT * FROM tb_clazz c,tb_student s
      WHERE c.id = s.clazz_id
       AND s.id = #{id}
  </select>
  
  <!-- 根据班级id查询学生信息，返回resultMap -->
  <select id="selectStudentByClazzId" parameterType="int"
  resultMap="studentResultMap">
      SELECT * FROM tb_student WHERE clazz_id = #{id}
  </select>
  
   <!-- 映射Student对象的resultMap -->
     <resultMap type="org.fkit.domain.Student"  id="studentResultMap">
           <id property="id" column="id"/>
          <result property="name" column="name"/>
          <result property="sex" column="sex"/>
          <result property="age" column="age"/>
           <!-- 多对一关联映射:association   -->
           <association property="clazz"  javaType="org.fkit.domain.Clazz">
                <id property="id" column="id"/>
                <result property="code" column="code"/>
                <result property="name" column="name"/>
           </association>
     </resultMap>
</mapper>

五配置文件

1 db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mybatis
username=root
password=123456

2 log4j.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">  
<log4j:configuration  xmlns:log4j="http://jakarta.apache.org/log4j/">  
    <appender name="STDOUT"  class="org.apache.log4j.ConsoleAppender">  
        <layout class="org.apache.log4j.PatternLayout">  
            <param name="ConversionPattern" value="%5p [%t] %m%n"  />  
        </layout>  
    </appender>   
     <logger name="org.fkit.mapper">  
        <level value="DEBUG" />  
    </logger>
    <root>  
        <level value="ERROR" />  
        <appender-ref ref="STDOUT" />  
    </root>  
</log4j:configuration>

3 mybatis-config.xml

<?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">
  <!--  XML 配置文件包含对 MyBatis 系统的核心设置 -->
<configuration>
     <properties resource="db.properties"/>
     <!-- 指定 MyBatis 所用日志的具体实现 -->
     <settings>
           <setting name="logImpl" value="LOG4J"/>
           <!-- 要使延迟加载生效必须配置下面两个属性 -->
           <setting name="lazyLoadingEnabled" value="true"/>
           <setting name="aggressiveLazyLoading" value="false"/>
     </settings>
     <environments default="mysql">
     <!-- 环境配置，即连接的数据库。 -->
    <environment id="mysql">
    <!--  指定事务管理类型，type="JDBC"指直接简单使用了JDBC的提交和回滚设置 -->
      <transactionManager type="JDBC"/>
      <!--  dataSource指数据源配置，POOLED是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告诉了MyBatis去哪里找持久化类的映射文件 -->
  <mappers>
      <mapper resource="org/fkit/mapper/ClazzMapper.xml"/>
      <mapper resource="org/fkit/mapper/StudentMapper.xml"/>
  </mappers>
</configuration>

六测试

package org.fkit.test;

import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.fkit.domain.Clazz;
import org.fkit.domain.Student;
import org.fkit.factory.FKSqlSessionFactory;
import org.fkit.mapper.ClazzMapper;
import org.fkit.mapper.StudentMapper;

public class OneToManyTest {

    public static void main(String[] args) {
        
        // 定义SqlSession变量
        SqlSession sqlSession = null;
        try {
            // 创建SqlSession实例
            sqlSession = FKSqlSessionFactory.getSqlSession();
            OneToManyTest t = new OneToManyTest();
            t.testSelectClazzById(sqlSession);
//            t.testSelectStudentById(sqlSession);
            // 提交事务
            sqlSession.commit();
        } catch (Exception e) {
            // 回滚事务
            sqlSession.rollback();
            e.printStackTrace();
        }finally {
            // 关闭SqlSession
            if(sqlSession != null)
                sqlSession.close();
        }
    }
    
    // 测试一对多，查询班级Clazz（一）的时候级联查询学生Student（多）  
    public void testSelectClazzById(SqlSession sqlSession){
        // 获得ClazzMapper接口的代理对象
        ClazzMapper cm = sqlSession.getMapper(ClazzMapper.class);
        // 调用selectClazzById方法
        Clazz clazz = cm.selectClazzById(1);
        // 查看查询到的clazz对象信息
        System.out.println(clazz.getId() + " "+ clazz.getCode() + " "+clazz.getName());
        // 查看clazz对象关联的学生信息
        List<Student> students = clazz.getStudents();
        students.forEach(stu -> System.out.println(stu));
    }
    
    // 测试多对一，查询学生Student（多）的时候级联查询 班级Clazz（一）
    public void testSelectStudentById(SqlSession sqlSession){
        // 获得StudentMapper接口的代理对象
        StudentMapper sm = sqlSession.getMapper(StudentMapper.class);
        // 调用selectStudentById方法
        Student stu = sm.selectStudentById(1);
        // 查看查询到的Student对象信息
        System.out.println(stu);
        // 查看Student对象关联的班级信息
        System.out.println(stu.getClazz());
    }

}

七测试结果

1 测试一对多

DEBUG [main] ==>  Preparing: SELECT * FROM tb_clazz WHERE id = ?
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 1
1 j1601 Java就业班
DEBUG [main] ==>  Preparing: SELECT * FROM tb_student WHERE  clazz_id = ?
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 4
Student [id=1, name=jack, sex=男, age=23]
Student [id=2, name=rose, sex=女, age=18]
Student [id=3, name=tom, sex=男, age=21]
Student [id=4, name=alice, sex=女, age=20]

2 测试多对一

DEBUG [main] ==>  Preparing: SELECT * FROM tb_clazz c,tb_student  s WHERE c.id = s.clazz_id AND s.id = ?
DEBUG [main] ==> Parameters: 1(Integer)
DEBUG [main] <==      Total: 1
Student [id=1, name=Java就业班, sex=男, age=23]
Clazz [id=1, code=j1601, name=Java就业班]