MyBatis一对多和多对一联表查询

最新推荐文章于 2024-09-10 11:07:06 发布

烟雨一平生

最新推荐文章于 2024-09-10 11:07:06 发布

阅读量173

点赞数

本文链接：https://blog.csdn.net/yisuyanyu/article/details/111992212

版权

首先测试一对多关系

举得例子：标签组和标签的关系（一个标签组对应多个标签）

标签组：QywxTagGroup 表名：uo_qywx_tag_group

标签：QywxTag 表名：uo_qywx_tag

实体类：

 /**
   * 标签组实体类
   */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class QywxTagGroup {
    /**
     * 标签组ID
     */
    private String  groupId;
    /**
     * 标签组名称
     */
    private String groupName;

    /**
     * 标签组次序
     */
    private long groupOrder;
    /**
     * 创建时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm", timezone = "GMT+8")
    private Date createTime;
    /**
     * 标签集合（标签的集合，将标签类放入集合中。）
     */
    private List<QywxTag> tagList;

}

 /**
   * 标签标签的实体类
   */
@Data
public class QywxTag {
    /**
     * 标签ID
     */
    private String tagId;
    /**
     * 标签名称
     */
    private String tagName;
    /**
     * 标签创建时间
     */
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm", timezone = "GMT+8")
    private Date tagCreateTime;
    /**
     * 标签次序
     */
    private long tagOrder;
    /**
     * 标签组ID，此处含有标签组的主键
     */
    private String groupId;
}

Dao:

public interface QywxTagMapper {
    //获取标签组集合（里面含有标签组下面的标签）
    List<QywxTagGroup> getTagGroup();
}

Mapper:

<?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.linksteady.qywx.dao.QywxTagMapper">

    <resultMap id="tagGroup" type="com.linksteady.qywx.domain.QywxTagGroup">
        <result property="groupId" jdbcType="VARCHAR" column="group_id"/>
        <result property="groupName" jdbcType="VARCHAR" column="group_name"/>
        <result property="groupOrder" jdbcType="DECIMAL" column="group_order"/>
        <result property="createTime" jdbcType="TIMESTAMP" column="create_time"/>
        <!--利用collection标签，将标签类的信息和标签组做绑定-->
        <collection property="tagList" ofType="com.linksteady.qywx.domain.QywxTag">
            <result column="tag_id" jdbcType="VARCHAR" property="tagId"/>
            <result column="tag_name" jdbcType="VARCHAR" property="tagName"/>
            <result column="tag_create_time" jdbcType="TIMESTAMP" property="tagCreateTime"/>
            <result column="tag_order" jdbcType="DECIMAL" property="tagOrder"/>
            <result column="group_id" jdbcType="VARCHAR" property="groupId"/>
        </collection>
    </resultMap>


    <!--查询语句-->
    <select id="getTagGroup" resultMap="tagGroup">
        select t.tag_id,t.tag_name,g.group_id,g.group_name from uo_qywx_tag t,uo_qywx_tag_group g where t.group_id=g.group_id
    </select>

</mapper>

上述方法虽说能简单有效的解决一对多的问题。查询数据库只需一次。但如果业务存在分页的情况下，上述情况就不适合了。

存在分页的一对多情况：

就是将两个语句分别查出，然后通过关联的ID，进行关联。

Dao:

public interface QywxTagMapper {
    //查询主表，里面带分页参数
    List<QywxTagGroup> getTagGroup(int limit,int offset);
    //查询从表
    List<QywxTag> selectTag();
}

Mapper:

<?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.linksteady.qywx.dao.QywxTagMapper">

    <resultMap id="tagGroup" type="com.linksteady.qywx.domain.QywxTagGroup">
        <result property="groupId" jdbcType="VARCHAR" column="group_id"/>
        <result property="groupName" jdbcType="VARCHAR" column="group_name"/>
        <result property="groupOrder" jdbcType="DECIMAL" column="group_order"/>
        <result property="createTime" jdbcType="TIMESTAMP" column="create_time"/>
        <!--这里注意：比上面增加了select和column-->
        <collection property="tagList" ofType="com.linksteady.qywx.domain.QywxTag" select="selectTag" column="group_id">
            <!--这里增加了外键关联-->
            <id property="groupId" column="group_id"/>
            <result column="tag_id" jdbcType="VARCHAR" property="tagId"/>
            <result column="tag_name" jdbcType="VARCHAR" property="tagName"/>
            <result column="tag_create_time" jdbcType="TIMESTAMP" property="tagCreateTime"/>
            <result column="tag_order" jdbcType="DECIMAL" property="tagOrder"/>
            <result column="group_id" jdbcType="VARCHAR" property="groupId"/>
        </collection>
    </resultMap>
     
    <!--查询主表，带分页参数-->
    <select id="getTagGroup" resultMap="tagGroup">
        select group_id,group_name,group_order,create_time from uo_qywx_tag_group order by create_time desc
        <if test="limit!=0 and limit !=''">
            limit #{limit} offset #{offset}
        </if>
    </select>

    <!--查询从表，不带分页。是根据ID查询-->
    <select id="selectTag" resultType="com.linksteady.qywx.domain.QywxTag">
        select tag_id,tag_name,tag_create_time,tag_order,group_id from uo_qywx_tag where group_id=#{groupId}
    </select>

</mapper>

此种情况会产生效率问题。从控制台输出的查询语句可以看出流程是：先查询主表，查询出符合的条数，然后通过groupID,逐个去查询从表的数据。主表查询多少条数据，从表就查询多少次。

测试多对一

举得例子：学生和老师的关系（对应关系就是多个学生对应一个老师）

学生类：Student 表名：student

老师类：Teacher 表名：teacher

实体类：

//学生类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    //学生ID
    private int id;
    //学生姓名
    private String name;
    //对应老师的实体类
    private Teacher teacher;

}

//老师的实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    //老师ID
    private int id;
    //老师姓名
    private String name;

}

Dao:


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

Mapper:

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

    <resultMap id="resultBase" type="com.linksteady.qywx.domain.Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <!--用association标签，将老师信息和学生做绑定-->
        <association property="teacher" javaType="com.linksteady.qywx.domain.Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

    <!--查询语句--> 
    <select id="getStudent" resultMap="resultBase">
        select s.id sid, s.name sname, t.id tid, t.name tname from student s, teacher where s.tid = t.id
    </select>
 
</mapper>