Mybatis中联表查询实现

比如，我有两张表：wv_public_project（p表）、wv_public_service_apply（a表），其内容分别如下

 

 

其中p表中的apply_id与a表中的id字段关联。现在，我要条件查询p表中的全部属性与其所关联的部分属性，如何实现？

方法一：

以一个表为主体，在其上面添加另一个表中的字段：（我以p表为主体，在model中添加a表部分属性）

public class WvPublicServiceProject {    private Integer id;    private Integer applyId;    private String projectCode;    private String projectName;    private String projectType;    private String projectPerson;    private String phone;    private Float totalInvest;    private Float subsidyLimit;    private Integer households;private String impSubject;/** 以上为p表的全部属性，以下为a表的部分属性 **/    private String des;    private String year;    private String village;    private String town;    private String area;private String status;/** 省略get、set方法 **/}

Xml映射时根据上面的model字段进行映射：

<resultMap id="BaseResultMap"type="com.szht.agriculture.web.model.WvPublicServiceProject"><id column="id" property="id" jdbcType="INTEGER" /><result column="apply_id" property="applyId" jdbcType="INTEGER" /><result column="project_code" property="projectCode" jdbcType="VARCHAR" /><result column="project_name" property="projectName" jdbcType="VARCHAR" /><result column="project_type" property="projectType" jdbcType="VARCHAR" /><result column="project_person" property="projectPerson"jdbcType="VARCHAR" /><result column="phone" property="phone" jdbcType="VARCHAR" /><result column="total_invest" property="totalInvest" jdbcType="REAL" /><result column="subsidy_limit" property="subsidyLimit"jdbcType="REAL" /><result column="households" property="households" jdbcType="INTEGER" /><result column="imp_subject" property="impSubject" jdbcType="VARCHAR" /><result column="des" property="des" jdbcType="VARCHAR" /><result column="year" property="year" jdbcType="VARCHAR" /><result column="area" property="area" jdbcType="VARCHAR" /><result column="town" property="town" jdbcType="VARCHAR" /><result column="village" property="village" jdbcType="VARCHAR" /><result column="status" property="status" jdbcType="VARCHAR" /></resultMap>

（由此可见xml的映射不一定要和数据库的字段一一映射，但必须和model对应）

重点来了，在下面查询时直接用sql语句连表查询：

<select id="selectByParams" resultMap="BaseResultMap">select p.*, a.year as year, a.area as area, a.town as town, a.village as village, a.project_sta as statusfrom wv_public_service_project pinner join  wv_public_service_apply aonp.apply_id = a.id<where><if test="year !=null and year !='全部'">a.year = #{year,jdbcType=VARCHAR}</if><if test="town != null and town !='全部'">and a.town = #{town,jdbcType=VARCHAR}</if><if test="area != null and area !='全部'">and a.area = #{area,jdbcType=VARCHAR}</if><if test="village != null and village !='全部'">and a.village = #{village,jdbcType=VARCHAR}</if></where></select>

多表关联查询方法同上（活学活用sql连接查询语句）

方法二：网上多在xml文件中使用<association>（一对一）、<collection>（一对多）标签：http://www.cnblogs.com/yansum/p/5819973.html