Mybatis的一对多查询、多条件查询、递归

素颜马尾好姑娘i 2021-06-11 15:13 644阅读 0赞

一、关联查询

例如：一个国家对应多个地区，一个地区对应多个城市，在查询国家的时候把地区及城市一并查出

//实体类,简单写一下
    class Country{
    
        String id;
        String name;
        List<Area> areas;
        
    }
    
    class Area{
    
        String id;
        String name;
        String countryId;
        List<City> citys;
    }
    
    class City{
    
        String id;
        String name;
        String areaId;
        
    }

XML配置

注意select标签的 resultType和resultMap别用错

<resultMap id="CountryResultMap" type="com.xxx.Country" >
       <id column="id" property="id" />
       <result column="name" property="name"  />
       <collection property="areas" ofType="com.xxx.Area" 
                   select="getAreas" column="id">
       </collection>
    </resultMap>
    
      
    <resultMap id="AreaResultMap" type="com.xxx.Area" >
       <id column="id" property="id" />
       <id column="country_id" property="countryId" />
       <result column="name" property="name"  />
       <collection property="citys" ofType="com.xxx.City" 
                   select="getCitys" column="id">
       </collection>
    </resultMap>
    
    <select id="selectCountryById" parameterType="java.lang.String"     
          resultMap="CountryResultMap">
      select 
            c.id,c.name
            from country c
            where  c.id = #{id}
    </select>
    <select id="getAreas" parameterType="java.lang.String"     
          resultMap="AreaResultMap">
      select 
            a.id,a.name,a.country_id
            from area a
            where  a.country_id = #{id}
    </select>
    <select id="getCitys" parameterType="java.lang.String"     
          resultType="com.xxx.City">
      select 
            t.id,t.name,t.area_id as areaId
            from city t
            where  t.area_id = #{id}
    </select>

二、查询条件传递

例如，每个记录都有创建时间，想查询创建时间在startDate和endDate之间的记录，如何将查询条件传递

<resultMap id="CountryResultMap" type="com.xxx.Country" >
       <id column="id" property="id" />
       <result column="name" property="name"  />
        
       <collection property="areas" ofType="com.xxx.Area" 
                   select="getAreas" column="{id=id,start=start,end=end}">
       </collection>
    </resultMap>
    
    
    <select id="selectCountryById" parameterType="java.util.Map"     
          resultMap="CountryResultMap">
      select 
        
            #{startDate} start,
            #{endDate} end,
            c.id,c.name
            from country c
            where  c.id = #{id}
            <if test="startDate != null" >
               and createDate <![CDATA[>= DATE_FORMAT(#{startDate},'%Y-%m-%d %H:%i:%s') ]]> 
            </if>
             <if test="endDate != null" >
               and createDate <![CDATA[<= DATE_FORMAT(#{endDate},'%Y-%m-%d %H:%i:%s') ]]> 
            </if>
    </select>
    <select id="getAreas" parameterType="java.util.Map"     
          resultMap="AreaResultMap">
      select 
            a.id,a.name,a.country_id
            from area a
            where  a.country_id = #{id}
             <if test="start != null and start != ''  " >
               and createDate <![CDATA[>= DATE_FORMAT(#{start},'%Y-%m-%d %H:%i:%s') ]]> 
            </if>
             <if test="end != null and end != ''  " >
               and createDate <![CDATA[<= DATE_FORMAT(#{end},'%Y-%m-%d %H:%i:%s') ]]> 
            </if>
    </select>

三、tree结构递归查询全部子节点

<resultMap id="NodeResultMap" type="com.xxx.Node" >
       <id column="id" property="id" />
       <result column="parentid" property="parentid"/>
       <result column="name" property="name"  />
       <collection property="children" ofType="com.xxx.Node" 
                   select="findNodesByParentid" column="id">
       </collection>
    </resultMap>
    
    
    <select id="findAllNodes" resultMap="NodeResultMap">
      SELECT id, name, parentid FROM area WHERE parentid = '' or parent_id is NULL
    </select>
    <select id="findNodesByParentid" resultMap="NodeResultMap">
      SELECT id, name, parentid FROM area WHERE parentid = #{id}
    </select>