一、关联查询
例如:一个国家对应多个地区,一个地区对应多个城市,在查询国家的时候把地区及城市一并查出
//实体类,简单写一下
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别用错
<!-- 国家与地区的1对多映射 -->
<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>
<!-- 地区与城市的1对多映射 -->
<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之间的记录,如何将查询条件传递
<!-- 国家与地区的1对多映射 -->
<resultMap id="CountryResultMap" type="com.xxx.Country" >
<id column="id" property="id" />
<result column="name" property="name" />
<!-- column="用Map方式传递参数 Key值为定义传递的参数名,Value值为父查询中虚拟列的列名" -->
<collection property="areas" ofType="com.xxx.Area"
select="getAreas" column="{id=id,start=start,end=end}">
</collection>
</resultMap>
<!-- parameterType设置为map -->
<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>