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 status
	from wv_public_service_project p
	inner join  
	wv_public_service_apply a
	on
	p.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