sqlMap文件中的resultMap:
<resultMap id="productAndPackageResult" class="productAndPackage" > <result column="ID" property="id" jdbcType="DECIMAL" /> <result column="NAME" property="name" jdbcType="VARCHAR" /> <result column="DISCTRIPTION" property="disctription" jdbcType="VARCHAR" /> <result column="DURATION" property="duration" jdbcType="DECIMAL" /> <result column="STATUS" property="status" jdbcType="DECIMAL" /> <result column="AD_COUNT" property="adCount" jdbcType="DECIMAL" /> <result column="BEGIN_TIME" property="beginTime" jdbcType="DATE" /> <result column="END_TIME" property="endTime" jdbcType="DATE" /> <result column="MARKET_PRICE" property="marketPrice" jdbcType="DECIMAL" /> <result column="PRICE" property="price" jdbcType="DECIMAL" /> <result column="RELEASE_TEL_NUM" property="releaseTelNum" jdbcType="DECIMAL" /> <result column="RELEVANCE_TEL_NUM" property="relevanceTelNum" jdbcType="DECIMAL" /> <result property="productPackageList" column="{productId=ID,areaId=areaId,status=status}" select="ProductPackageSQL.getByProductId"/> </resultMap>
注意productPackageList字段是,利用了select="ProductPackageSQL.getByProductId"来实现的。
getByProductId的select是这样的:
<select id="getByProductId" resultMap="productPackageResult" parameterClass="java.util.HashMap"> select * from TB_PRODUCT_PACKAGE where PRODUCT_ID = #productId# <isNotNull property="areaId" prepend="and"> AREA_ID = #areaId# </isNotNull> <isNotNull property="status" prepend="and"> STATUS = #status# </isNotNull> </select>
productAndPackageResult 用到的地方:
<select id="getList" resultMap="productAndPackageResult" parameterClass="java.util.Map"> select ID,NAME,DISCTRIPTION,DURATION,STATUS,PRICE,AD_COUNT,BEGIN_TIME,END_TIME, MARKET_PRICE,PRICE,RELEASE_TEL_NUM,RELEVANCE_TEL_NUM ,#areaId# as areaId,#status# as status from TB_PRODUCT <dynamic prepend="where"> <isNotEmpty prepend="and" property="productId"> ID = #productId# </isNotEmpty> </dynamic> order by ID </select>
应该注意的是这个字查询是有3个参数,一般来说一对多的情况是主键和外键的关系,这个方案就是为了解决这种可能的多参数的情况。
例子的关键是主表查询的时候使用 #areaId# as areaId,#status# as status 这样的方式,这样就可以在字查询中使用到status 和areaId这两个参数了。