.XML文件
<resultMap id="doorPage" type="org.jsola.pms.vo.HouseListVO">
<id column="houseid" jdbcType="BIGINT" property="id"/>
<result column="housepurpose" jdbcType="VARCHAR" property="purpose"/>
<result column="housename" jdbcType="VARCHAR" property="name"/>
<result column="househouseCard" jdbcType="VARCHAR" property="houseCard"/>
<result column="housecategory" jdbcType="VARCHAR" property="category"/>
<result column="housetype" jdbcType="VARCHAR" property="type"/>
<result column="houseareaStructure" jdbcType="BIGINT" property="areaStructure"/>
<result column="houseareaInner" jdbcType="BIGINT" property="areaInner"/>
<result column="houseareaShare" jdbcType="BIGINT" property="areaShare"/>
<result column="housestatus" jdbcType="INTEGER" property="status"/>
<result column="housecommunityId" jdbcType="BIGINT" property="communityId"/>
<result column="housecheckIn" jdbcType="TIMESTAMP" property="checkIn"/>
<result column="houseisDecorate" jdbcType="BOOLEAN" property="isDecorate"/>
<result column="houseisElevator" jdbcType="BOOLEAN" property="isElevator"/>
<result column="houseisIdentification" jdbcType="BOOLEAN" property="isIdentification"/>
<result column="houseremark" jdbcType="VARCHAR" property="remark"/>
<result column="houseownerRemark" jdbcType="VARCHAR" property="ownerRemark"/>
<result column="houseparentId" jdbcType="BIGINT" property="parentId"/>
<result column="housesort" jdbcType="INTEGER" property="sort"/>
<result column="housegmtCreate" jdbcType="TIMESTAMP" property="gmtCreate"/>
<result column="housegmtModified" jdbcType="TIMESTAMP" property="gmtModified"/>
<collection property="houseOwnerVOList" ofType="org.jsola.pms.vo.HouseOwnerVO" column="houseid" select="selecthowner" >
<id column="hownerid" jdbcType="BIGINT" property="id"/>
<result column="howneruserImageUrl" jdbcType="VARCHAR" property="userImageUrl"/>
<result column="howneruserImageId" jdbcType="VARCHAR" property="userImageId"/>
<result column="hownerlabel" jdbcType="VARCHAR" property="label"/>
<result column="hownerownerName" jdbcType="VARCHAR" property="ownerName"/>
<result column="hownerownerPhone" jdbcType="VARCHAR" property="ownerPhone"/>
<result column="hownerownerPic" jdbcType="VARCHAR" property="ownerPic"/>
<result column="hownerownerType" jdbcType="INTEGER" property="ownerType"/>
<result column="hownerstatus" jdbcType="INTEGER" property="status"/>
<result column="hownerhouseId" jdbcType="BIGINT" property="houseId"/>
<result column="hownercommunityId" jdbcType="BIGINT" property="communityId"/>
<result column="hownerproprietorName" jdbcType="VARCHAR" property="proprietorName"/>
<result column="hownerproprietorPhone" jdbcType="VARCHAR" property="proprietorPhone"/>
<result column="hownerproprietorRelation" jdbcType="INTEGER" property="proprietorRelation"/>
<result column="hownersex" jdbcType="INTEGER" property="sex"/>
<result column="hownergmtCreate" jdbcType="TIMESTAMP" property="gmtCreate"/>
<result column="hownergmtModified" jdbcType="TIMESTAMP" property="gmtModified"/>
</collection>
<collection property="houseMemberVOList" ofType="org.jsola.pms.vo.HouseOwnerVO" column="houseid" select="selecthtenants">
<id column="hmemberid" jdbcType="BIGINT" property="id"/>
<result column="hmemberuserImageUrl" jdbcType="VARCHAR" property="userImageUrl"/>
<result column="hmemberuserImageId" jdbcType="VARCHAR" property="userImageId"/>
<result column="hmemberlabel" jdbcType="VARCHAR" property="label"/>
<result column="hmemberownerName" jdbcType="VARCHAR" property="ownerName"/>
<result column="hmemberownerPhone" jdbcType="VARCHAR" property="ownerPhone"/>
<result column="hmemberownerPic" jdbcType="VARCHAR" property="ownerPic"/>
<result column="hmemberownerType" jdbcType="INTEGER" property="ownerType"/>
<result column="hmemberstatus" jdbcType="INTEGER" property="status"/>
<result column="hmemberhouseId" jdbcType="BIGINT" property="houseId"/>
<result column="hmembercommunityId" jdbcType="BIGINT" property="communityId"/>
<result column="hmemberproprietorName" jdbcType="VARCHAR" property="proprietorName"/>
<result column="hmemberproprietorPhone" jdbcType="VARCHAR" property="proprietorPhone"/>
<result column="hmemberproprietorRelation" jdbcType="INTEGER" property="proprietorRelation"/>
<result column="hmembersex" jdbcType="INTEGER" property="sex"/>
<result column="hmembergmtCreate" jdbcType="TIMESTAMP" property="gmtCreate"/>
<result column="hmembergmtModified" jdbcType="TIMESTAMP" property="gmtModified"/>
</collection>
<collection property="houseTenantsVOList" ofType="org.jsola.pms.vo.HouseOwnerVO" column="houseid" select="selecthmember">
<id column="htenantsid" jdbcType="BIGINT" property="id"/>
<result column="htenantsuserImageUrl" jdbcType="VARCHAR" property="userImageUrl"/>
<result column="htenantsuserImageId" jdbcType="VARCHAR" property="userImageId"/>
<result column="htenantslabel" jdbcType="VARCHAR" property="label"/>
<result column="htenantsownerName" jdbcType="VARCHAR" property="ownerName"/>
<result column="htenantsownerPhone" jdbcType="VARCHAR" property="ownerPhone"/>
<result column="htenantsownerPic" jdbcType="VARCHAR" property="ownerPic"/>
<result column="htenantsownerType" jdbcType="INTEGER" property="ownerType"/>
<result column="htenantsstatus" jdbcType="INTEGER" property="status"/>
<result column="htenantshouseId" jdbcType="BIGINT" property="houseId"/>
<result column="htenantscommunityId" jdbcType="BIGINT" property="communityId"/>
<result column="htenantsproprietorName" jdbcType="VARCHAR" property="proprietorName"/>
<result column="htenantsproprietorPhone" jdbcType="VARCHAR" property="proprietorPhone"/>
<result column="htenantsproprietorRelation" jdbcType="INTEGER" property="proprietorRelation"/>
<result column="htenantssex" jdbcType="INTEGER" property="sex"/>
<result column="htenantsgmtCreate" jdbcType="TIMESTAMP" property="gmtCreate"/>
<result column="htenantsgmtModified" jdbcType="TIMESTAMP" property="gmtModified"/>
</collection>
</resultMap>
<resultMap id="htenantsmap" type="org.jsola.pms.vo.HouseOwnerVO">
<id column="htenantsid" jdbcType="BIGINT" property="id"/>
<result column="htenantsuserImageUrl" jdbcType="VARCHAR" property="userImageUrl"/>
<result column="htenantsuserImageId" jdbcType="VARCHAR" property="userImageId"/>
<result column="htenantslabel" jdbcType="VARCHAR" property="label"/>
<result column="htenantsownerName" jdbcType="VARCHAR" property="ownerName"/>
<result column="htenantsownerPhone" jdbcType="VARCHAR" property="ownerPhone"/>
<result column="htenantsownerPic" jdbcType="VARCHAR" property="ownerPic"/>
<result column="htenantsownerType" jdbcType="INTEGER" property="ownerType"/>
<result column="htenantsstatus" jdbcType="INTEGER" property="status"/>
<result column="htenantshouseId" jdbcType="BIGINT" property="houseId"/>
<result column="htenantscommunityId" jdbcType="BIGINT" property="communityId"/>
<result column="htenantsproprietorName" jdbcType="VARCHAR" property="proprietorName"/>
<result column="htenantsproprietorPhone" jdbcType="VARCHAR" property="proprietorPhone"/>
<result column="htenantsproprietorRelation" jdbcType="INTEGER" property="proprietorRelation"/>
<result column="htenantssex" jdbcType="INTEGER" property="sex"/>
<result column="htenantsgmtCreate" jdbcType="TIMESTAMP" property="gmtCreate"/>
<result column="htenantsgmtModified" jdbcType="TIMESTAMP" property="gmtModified"/>
</resultMap>
<resultMap id="hownermap" type="org.jsola.pms.vo.HouseOwnerVO">
<id column="hownerid" jdbcType="BIGINT" property="id"/>
<result column="howneruserImageUrl" jdbcType="VARCHAR" property="userImageUrl"/>
<result column="howneruserImageId" jdbcType="VARCHAR" property="userImageId"/>
<result column="hownerlabel" jdbcType="VARCHAR" property="label"/>
<result column="hownerownerName" jdbcType="VARCHAR" property="ownerName"/>
<result column="hownerownerPhone" jdbcType="VARCHAR" property="ownerPhone"/>
<result column="hownerownerPic" jdbcType="VARCHAR" property="ownerPic"/>
<result column="hownerownerType" jdbcType="INTEGER" property="ownerType"/>
<result column="hownerstatus" jdbcType="INTEGER" property="status"/>
<result column="hownerhouseId" jdbcType="BIGINT" property="houseId"/>
<result column="hownercommunityId" jdbcType="BIGINT" property="communityId"/>
<result column="hownerproprietorName" jdbcType="VARCHAR" property="proprietorName"/>
<result column="hownerproprietorPhone" jdbcType="VARCHAR" property="proprietorPhone"/>
<result column="hownerproprietorRelation" jdbcType="INTEGER" property="proprietorRelation"/>
<result column="hownersex" jdbcType="INTEGER" property="sex"/>
<result column="hownergmtCreate" jdbcType="TIMESTAMP" property="gmtCreate"/>
<result column="hownergmtModified" jdbcType="TIMESTAMP" property="gmtModified"/>
</resultMap>
<resultMap id="hmembermap" type="org.jsola.pms.vo.HouseOwnerVO">
<id column="hmemberid" jdbcType="BIGINT" property="id"/>
<result column="hmemberuserImageUrl" jdbcType="VARCHAR" property="userImageUrl"/>
<result column="hmemberuserImageId" jdbcType="VARCHAR" property="userImageId"/>
<result column="hmemberlabel" jdbcType="VARCHAR" property="label"/>
<result column="hmemberownerName" jdbcType="VARCHAR" property="ownerName"/>
<result column="hmemberownerPhone" jdbcType="VARCHAR" property="ownerPhone"/>
<result column="hmemberownerPic" jdbcType="VARCHAR" property="ownerPic"/>
<result column="hmemberownerType" jdbcType="INTEGER" property="ownerType"/>
<result column="hmemberstatus" jdbcType="INTEGER" property="status"/>
<result column="hmemberhouseId" jdbcType="BIGINT" property="houseId"/>
<result column="hmembercommunityId" jdbcType="BIGINT" property="communityId"/>
<result column="hmemberproprietorName" jdbcType="VARCHAR" property="proprietorName"/>
<result column="hmemberproprietorPhone" jdbcType="VARCHAR" property="proprietorPhone"/>
<result column="hmemberproprietorRelation" jdbcType="INTEGER" property="proprietorRelation"/>
<result column="hmembersex" jdbcType="INTEGER" property="sex"/>
<result column="hmembergmtCreate" jdbcType="TIMESTAMP" property="gmtCreate"/>
<result column="hmembergmtModified" jdbcType="TIMESTAMP" property="gmtModified"/>
</resultMap>
<sql id="result">
house.id as houseid , house.purpose as housepurpose , house.name as housename , house.house_card as househouseCard ,
house.category as housecategory , house.type as housetype , house.area_structure as houseareaStructure , house.area_inner as houseareaInner ,
house.area_share as houseareaShare , house.status as housestatus , house.community_id as housecommunityId , house.check_in as housecheckIn ,
house.is_decorate as houseisDecorate , house.is_elevator as houseisElevator , house.is_identification as houseisIdentification ,
house.remark as houseremark , house.owner_remark as houseownerRemark , house.parent_id as houseparentId ,
house.sort as housesort , house.gmt_create as housegmtCreate , house.gmt_modified as housegmtModified
</sql>
<sql id="htenants">
htenants.id as htenantsid , htenants.user_image_url as htenantsuserImageUrl , htenants.user_image_id as htenantsuserImageId ,
htenants.label as htenantslabel , htenants.owner_name as htenantsownerName , htenants.owner_phone as htenantsownerPhone ,
htenants.owner_pic as htenantsownerPic , htenants.owner_type as htenantsownerType , htenants.status as htenantsstatus ,
htenants.house_id as htenantshouseId , htenants.community_id as htenantscommunityId , htenants.proprietor_name as htenantsproprietorName ,
htenants.proprietor_phone as htenantsproprietorPhone , htenants.proprietor_relation as htenantsproprietorRelation ,
htenants.sex as htenantssex , htenants.gmt_create as htenantsgmtCreate , htenants.gmt_modified as htenantsgmtModified
</sql>
<sql id="howner">
howner.id as hownerid , howner.user_image_url as howneruserImageUrl , howner.user_image_id as howneruserImageId ,
howner.label as hownerlabel , howner.owner_name as hownerownerName , howner.owner_phone as hownerownerPhone ,
howner.owner_pic as hownerownerPic , howner.owner_type as hownerownerType , howner.status as hownerstatus ,
howner.house_id as hownerhouseId , howner.community_id as hownercommunityId , howner.proprietor_name as hownerproprietorName ,
howner.proprietor_phone as hownerproprietorPhone , howner.proprietor_relation as hownerproprietorRelation ,
howner.sex as hownersex , howner.gmt_create as hownergmtCreate , howner.gmt_modified as hownergmtModified
</sql>
<sql id="hmember">
hmember.id as hmemberid , hmember.user_image_url as hmemberuserImageUrl , hmember.user_image_id as hmemberuserImageId ,
hmember.label as hmemberlabel , hmember.owner_name as hmemberownerName , hmember.owner_phone as hmemberownerPhone ,
hmember.owner_pic as hmemberownerPic , hmember.owner_type as hmemberownerType , hmember.status as hmemberstatus ,
hmember.house_id as hmemberhouseId , hmember.community_id as hmembercommunityId , hmember.proprietor_name as hmemberproprietorName ,
hmember.proprietor_phone as hmemberproprietorPhone , hmember.proprietor_relation as hmemberproprietorRelation ,
hmember.sex as hmembersex , hmember.gmt_create as hmembergmtCreate , hmember.gmt_modified as hmembergmtModified
</sql>
<select id="selectCountStatistics" resultType="java.lang.Integer">
select count(0) from h_house where is_valid = 1 and community_id = #{communityId} and site_id = #{siteId} and level =4
</select>
<select id="selectDoorPage" resultMap="doorPage" parameterType="org.jsola.pms.query.HouseQuery">
select <include refid="result"/>
FROM
h_house house
<if test="houseQuery.ownerName != null">
INNER JOIN h_house_owner hownz ON hownz.owner_name LIKE CONCAT('%', #{houseQuery.ownerName}, '%')
AND house.id = hownz.house_id
AND hownz.is_valid = TRUE
AND hownz.owner_type = 1
</if>
<if test="houseQuery.ownerPhone != null">
INNER JOIN h_house_owner hownzs ON hownzs.owner_phone LIKE CONCAT('%', #{houseQuery.ownerPhone}, '%')
AND house.id = hownzs.house_id
AND hownzs.is_valid = TRUE
AND hownzs.owner_type = 1
</if>
WHERE
house.is_valid = TRUE
AND house.`level` = 4
AND house.community_id = #{houseQuery.communityId}
<if test="houseQuery.houseIds != null and houseQuery.houseIds.size > 0">
AND house.parent_id in
<foreach collection="houseQuery.houseIds" item="id" index="index" open="(" close=")" separator=",">
#{id}
</foreach>
</if>
<if test="houseQuery.doorName != null">
AND house.`name` LIKE CONCAT('%', #{houseQuery.doorName}, '%')
</if>
<if test="houseQuery.status != null">
AND house.`status` = #{houseQuery.status}
</if>
<if test="houseQuery.category != null">
AND house.`category` = #{houseQuery.category}
</if>
ORDER BY
house.parent_id,
house.gmt_create
</select>
<select id="selecthowner" resultMap="hownermap">
select <include refid="howner"/> from
h_house_owner howner where howner.house_id = #{houseid}
AND howner.owner_type = 1
AND howner.is_valid = TRUE
</select>
<select id="selecthtenants" resultMap="htenantsmap">
select <include refid="htenants"/> from
h_house_owner htenants where htenants.house_id = #{houseid}
AND htenants.owner_type = 2
AND htenants.is_valid = TRUE
</select>
<select id="selecthmember" resultMap="hmembermap">
select <include refid="hmember"/> from
h_house_owner hmember where hmember.house_id = #{houseid}
AND hmember.owner_type = 3
AND hmember.is_valid = TRUE
</select>
重点就是
collection property=“houseMemberVOList” ofType=“org.jsola.pms.vo.HouseOwnerVO” column=“houseid” select=“selecthtenants”
在collection的时候 加个select=“selecthtenants” select中就是你需要的一对多中多的数据 select中就是查询的方法 column=“houseid” 就是你相关联的id。 在子查询(selecthtenants)中 #{houseid}来获取
DAO层
/**
* 查询小区下的房屋业主,家属及租客
* @param houseQuery 查询条件
* @return 展示列表
*/
List<HouseListVO> selectDoorPage (@Param("houseQuery") HouseQuery houseQuery);
impl
PageInfo<HouseListVO> pageInfo = PageHelper.startPage(houseQuery.getPageNo(), houseQuery.getPageSize()).doSelectPageInfo(() -> houseDAO.selectDoorPage(houseQuery));
Page<HouseListVO> resultPage = new Page<>(pageInfo.getList(), pageInfo.getPageNum(), pageInfo.getPageSize(), (int) pageInfo.getTotal());
这样就能正常的配合PageHelper工具类了