mybatis/mybatisplus 一对多查询

最新推荐文章于 2025-06-20 14:12:20 发布

原创最新推荐文章于 2025-06-20 14:12:20 发布 · 809 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #java #mysql

本文介绍了一种使用MyBatis Plus实现分页查询的方法，并详细展示了如何通过定义Controller、Mapper接口及XML映射文件来完成复杂的数据查询任务。此外，还讨论了如何采用父子查询的方式来优化查询效率。

1.Controller 定义需要传入的参数

OntologyEntity param=new OntologyEntity();
param.setName(name);
Page<OntologyEntity> resultPage=new Page<>(pageNumber,pageSize);
// 查询分页数据：mybatisplus
// param：为查询需要传入的参数可以是任意java数据类型
// resultPage：分页参数
IPage<OntoBuildVertexLabelDto> iPage = entityService.pageByEntity(resultPage,param);

2.定义mapper接口

public interface OntologyEntityMapper extends BaseMapper<OntologyEntity> {
    /**
     * 分页查询
     * @param resultPage 分页参数
     * @param param 查询参数
     * @return
     */
    IPage<OntoBuildVertexLabelDto> pageByEntity(Page<OntologyEntity> resultPage,@Param("param")  OntologyEntity param);
}

2.定义实体类OntologyEntity，OntoBuildVertexLabelDto，OntologyEntityAttribute

public class OntoBuildVertexLabelDto {
   private String id;
   private String name;
   private OntologyEntityAttribute properties;
}
public class OntologyEntityAttribute{
    private String id;
    private String entityId;
    private String attributeId;
    private String attributeName;
}
public class OntologyEntity{
   private String id;
   private String name;
}

3.需要分页情况时2的方式就不能满足了，需要改为父子查询方式

// 定义返回的结果集结构
<?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.yunpi.doo.msvs.mapper.OntologyEntityMapper">
	/**主查询返回类型**/
    <resultMap id="BaseResultMapPage" type="com.yunpi.doo.msvs.model.dto.OntoBuildVertexLabelDto">
        <result column="id" property="id" />
        <result column="name"               property="name" />
        /**
        子查询定义：
        select:子查询查询语句id
        column：主表与子表关联字段信息：id主表id对应子表entityId
        property：主查询返回结果属性名称
        ofType：子查询返回类型
        **/
        <collection 
	        select="selectProperties" 
	        column="{entityId=id}"  
	        property="properties" 
	        ofType="com.yunpi.doo.msvs.bean.OntologyEntityAttribute">
        </collection>
    </resultMap>
    /**
     子查询
     id:子查询定义中的select值相等
     resultType：子查询定义中的ofType值相等
     **/
    <select id="selectProperties" resultType="com.yunpi.doo.msvs.bean.OntologyEntityAttribute">
        select
            doea.id AS id,
            doea.entity_id AS entityId,
            doea.attribute_id AS attributeId,
            doea.attribute_name AS attributeName 
        from
            doo_ontology_entity_attribute doea
        WHERE
            1=1 and  doea.entity_id=#{entityId}
    </select>
     /**
     主查询
     **/
    <select id="pageByEntity"  parameterType="com.yunpi.doo.msvs.bean.OntologyEntity" resultMap="BaseResultMapPage">
        SELECT
            doe.id,
            doe.target_type AS targetType,
            doe.ontology_base_info_id AS ontologyBaseInfoId,
            doe.name AS name
        FROM
            doo_ontology_target_bo doe
        WHERE 1=1
        <if test="param.name != null and param.name != ''">
            AND doe.name  = #{param.name }
        </if>
        order by doe.created_time desc
    </select>
</mapper>