多对一映射

创建数据表

student是主表
class_id关联class表的id

class表
在这里插入图片描述
student表

创建pojo

Class类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Class {private Long id;private String name;private List<Student> students;
}

Student类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {private Long id;private String name;private Long classId;private Class clazz;
}

映射方式

1. 级联映射

StudentMapper写入如下代码

Student selectByIdResult(Long id);

StudentMapper.xml写入如下代码

<sql id="studentSql">s.id,s.name,s.class_id,c.id as classId,c.name as className
</sql><resultMap id="studentResultResultMap" type="Student"><id property="id" column="id"/><result property="name" column="name"/><result property="classId" column="classId"/><result property="clazz.id" column="classId"/><result property="clazz.name" column="className"/>
</resultMap><select id="selectByIdResult" resultMap="studentResultResultMap" >select<include refid="studentSql"/>from student s left join class c on s.class_id = c.idwhere s.id=#{id}
</select>

2. association映射

StudentMapper写入如下代码

Student selectByIdAssociation(Long id);

StudentMapper.xml写入如下代码

<sql id="studentSql">s.id,s.name,s.class_id,c.id as classId,c.name as className
</sql>
<resultMap id="studentAssociationResultMap" type="Student"><id property="id" column="id"/><result property="name" column="name"/><result property="classId" column="class_id"/><association property="clazz" javaType="Class"><id property="id" column="classId"/><result property="name" column="className"/></association>
</resultMap>
<select id="selectByIdAssociation" resultMap="studentAssociationResultMap">select <include refid="studentSql"/>from student s left join class c on s.class_id=c.idwhere s.id = #{id}
</select>

3. 分步查询

上述两种方法都是一条sql完成
分步查询需要俩条sql，可复用支持延迟加载

StudentMapper.xml写入如下代码

Student selectByIdStep(Long id);

ClassMapper写入如下代码

Class selectById(Long id);

StudentMapper.xml写入如下代码

<sql id="studentSql">id,name,class_id
</sql>
<resultMap id="studentStepResultMap" type="Student"><id property="id" column="id"/><result property="name" column="name"/><result property="classId" column="class_id"/><association property="clazz" select="org.example.mapper.ClassMapper.selectById" column="class_id"/>
</resultMap>
<select id="selectByIdStep" resultMap="studentStepResultMap">select <include refid="studentSql"/>from student where id = #{id}
</select>

ClassMapper.xml写入如下代码

<sql id="classSql">id,name
</sql>
<select id="selectById"  resultType="Class">select<include refid="classSql"/>from classwhere id=#{id}
</select>

上述俩种查询每次都要查询2条sql，有时我们不需要查询2条。并且联表查询会出现笛卡尔现象，性能很差

这可以是用延迟加载来提高性能，用到属性的时候查询不用就不会查询

局部设置 fetchType=“lazy” 。只会作用域这条sql

<resultMap id="studentStepResultMap" type="Student"><id property="id" column="id"/><result property="name" column="name"/><result property="classId" column="class_id"/><association property="clazz" fetchType="lazy" select="org.example.mapper.ClassMapper.selectById" column="class_id"/>
</resultMap>

全局开启延迟加载

<settings><setting name="lazyLoadingEnabled" value="true"/>
</settings>

局部关闭使用 fetchType=“eager”

    <resultMap id="studentStepResultMap" type="Student"><id property="id" column="id"/><result property="name" column="name"/><result property="classId" column="class_id"/><association property="clazz" fetchType="eager" select="org.example.mapper.ClassMapper.selectById" column="class_id"/></resultMap>

一对多映射

创建pojo

Class类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Class {private Long id;private String name;private List<Student> students;
}

Student类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {private Long id;private String name;private Long classId;private Class clazz;
}

映射方式

1. collection映射

ClassMapper写入如下代码

Class selectCollection(Long id);

ClassMapper.xml写入如下代码

<sql id="classSql">c.id,c.name,s.id as studentId,s.name as studentName,s.class_id as classId
</sql>
<resultMap id="selectCollectionResultMap" type="Class"><id property="id" column="id"/><result property="name" column="name"/><collection property="students" ofType="Student"><id property="id" column="studentId"/><result property="name" column="studentName"/><result property="classId" column="classId"/></collection>
</resultMap>
<select id="selectByIdCollection" resultMap="selectCollectionResultMap">select<include refid="classSql"/>from class c left join student s on c.id=s.class_idwhere c.id=#{id}
</select>

2. 分步查询

ClassMapper写入如下代码

Class selectByIdStep(Long id);

StudentMapper写入如下代码

Student selectById(Long classId);

ClassMapper.xml写入如下代码

<sql id="classSql">id,name
</sql>
<resultMap id="selectStepResultMap" type="Class"><id property="id" column="id"/><result property="name" column="name"/><collection property="students" select="org.example.mapper.StudentMapper.selectById" column="id"/>
</resultMap>
<select id="selectByIdStep" resultMap="selectStepResultMap">select<include refid="classSql"/>from class where id=#{id}
</select>

StudentMapper.xml写入如下代码

<sql id="studentSql">id,name,class_id
</sql>
<select id="selectById" resultType="Student">select<include refid="studentSql"/>from student where class_id = #{classId}
</select>