ibatis表关联查询小记

<sqlMap namespace="User">

	<typeAlias alias="User" type="com.zjapl.smsapp.domain.entity.User" />

	<resultMap class="User" id="UserResult">
		<result property="id" column="ID" jdbcType="DECIMAL" />
		<result property="loginId" column="LOGIN_ID" jdbcType="VARCHAR" />
		<result property="name" column="NAME" jdbcType="VARCHAR" />
		<result property="password" column="PASSWORD" jdbcType="VARCHAR" />
		<result property="gender" column="GENDER" jdbcType="DECIMAL" />
		<result property="phone" column="PHONE" jdbcType="VARCHAR" />
		<result property="status" column="STATUS" jdbcType="DECIMAL" />
		<result property="roleId" column="ROLE_ID" jdbcType="DECIMAL" />
		<result property="skin" column="SKIN" jdbcType="VARCHAR" />
		<result property="createDate" column="CREATE_DATE" jdbcType="TIMESTAMP" />
		<result property="updateDate" column="UPDATE_DATE" jdbcType="TIMESTAMP" />
		<result property="remark" column="REMARK" jdbcType="VARCHAR" />
	</resultMap>

	<resultMap class="User" id="UserWithRoleNameResult" extends="UserResult">
		<result property="roleName" column="ROLE_ID" select="Role.selectNameById"/>
	</resultMap>
	
	<!-- 判断条件：状态为(0为正常，1为不可用）以及模糊匹配用户名称 -->
	<sql id="whereByParam">
	<![CDATA[
		WHERE K.STATUS IN(0,1) AND K.ID <> 1
		]]>
		<isNotEmpty prepend="AND" property="param.searchloginId">
			<![CDATA[
			LOGIN_ID like '%$param.searchloginId$%' 
			]]>
		</isNotEmpty>
		<isNotEmpty prepend="AND" property="param.searName">
			<![CDATA[
				NAME LIKE '%$param.searName$%'
			]]>
		</isNotEmpty>
		<isNotEmpty prepend="AND" property="param.searRoleName">
			<![CDATA[
//---			ROLE_ID in (select id from mst_role where name LIKE '%$param.searRoleName$%')
			]]>
		</isNotEmpty>
	</sql>
	
	<!-- 用户列表 -->
	<select id="selectByParam" resultMap="UserWithRoleNameResult"
		parameterClass="DynamicQueryParam">
		<![CDATA[
			SELECT K.* FROM MST_USER K
		]]>
		<include refid="whereByParam" />
		<![CDATA[ ORDER BY K.UPDATE_DATE ASC ]]>
	</select>
</sqlMap>

---------------多表查询时

<!-- 			<tr> -->
<!-- 				<td align="right">角 色：</td> -->
<!-- 				<td><input name="user.searRoleName" type="text" class="search_text" value="${(user.searRoleName)!''}"/> -->
<!-- 				</td> -->
<!-- 			</tr> -->
		   <tr>
		    <td align="right">角色：</td>
		    <td>
		    	<select name="user.searRoleName"  class="search_text" >
		       		<option value="0" selected="selected">请选择角色</option>
		       		<#list roleList as role>
						<option value="${role.name}">${role.name}</option>
					</#list>
		        </select>
			</td>
		  </tr>

-----------------页面搜索框转换为下接列表框。

SQL LEFT JOIN 关键字
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
LEFT JOIN 关键字语法

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

注释：在某些数据库中， LEFT JOIN 称为 LEFT OUTER JOIN。