多对一关联

一、创建数据库

create table students(
    sid char(5) primary key,
    sname varchar(20) not null,
    sage int not null,
    scid int not null
);

create table classes(
    cid int primary key AUTO_INCREMENT,
    cname varchar(30) not null unique,
    cdesc varchar(100)
)

二、创建实体类

Student1.java

package com.qfedu.pojo.moreToOne;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Student1 {
    private String stuId;
    private String stuName;
    private int stuAge;
    private Clazz1 clazz1; // 学生所在班级
}

Clazz1.java

package com.qfedu.pojo.moreToOne;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Clazz1 {
    private int classId;
    private String className;
    private String classDesc;
}

三、创建持久层

Student1DAO.java

package com.qfedu.dao.moreToOne;

import com.qfedu.pojo.moreToOne.Student1;

public interface Student1DAO {
    public Student1 queryStudentBySid(String sid);
}

四、多对一查询（关联查询）

4.1、创建映射层

Student1Mapper.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">
<mapper namespace="com.qfedu.dao.moreToOne.Student1DAO">

    <resultMap id="student1Map" type="Student1">
        <id column="sid" property="stuId" />
        <result column="sname" property="stuName" />
        <result column="sage" property="stuAge" />
        <result column="cid" property="clazz1.classId" />
        <result column="cname" property="clazz1.className" />
        <result column="cdesc" property="clazz1.classDesc" />
    </resultMap>
    <select id="queryStudentBySid" resultMap="student1Map">
        select sid,sname,sage,scid,cid,cname,cdesc
        from students s INNER JOIN classes c
        ON s.scid = c.cid
        where s.sid=#{sid}
    </select>
</mapper>

4.2、测试

    @Test
    public void testQueryStudentBySid(){
        Student1DAO student1DAO = MyBatisUtil.getMapper(Student1DAO.class);
        Student1 student1 = student1DAO.queryStudentBySid("10004");
        System.out.println(student1);
    }

五、多对一查询（子查询）

5.1 持久层

Clazz1.java

package com.qfedu.dao.moreToOne;
import com.qfedu.pojo.moreToOne.Clazz1;

public interface Class1DAO {
    /**
     * 根据班级编号 查询班级信息
     */
    public Clazz1 queryClass(int classId);
}

5.2 映射层

Class1Mapper.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">
<mapper namespace="com.qfedu.dao.moreToOne.Class1DAO">

    <resultMap id="class1Map" type="Clazz1">
        <id column="cid" property="classId" />
        <result column="cname" property="className" />
        <result column="cdesc" property="classDesc" />
    </resultMap>
    <select id="queryClass" resultMap="class1Map">
        select cid, cname, cdesc
        from classes
        where cid=#{cid}
    </select>
</mapper>

Student1Mapper.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">
<mapper namespace="com.qfedu.dao.moreToOne.Student1DAO">

    <resultMap id="student1Map" type="Student1">
        <id column="sid" property="stuId" />
        <result column="sname" property="stuName" />
        <result column="sage" property="stuAge" />
        <association property="clazz1" select="com.qfedu.dao.moreToOne.Class1DAO.queryClass" column="scid"/>
    </resultMap>
    <select id="queryStudentBySid" resultMap="student1Map">
        select sid,sname,sage,scid
        from students where sid=#{sid}
    </select>
</mapper>