MyBatis 学习笔记

[bingoogolapple]

LIKE 查询

这种形式不能用 #，会报错

<if test="roleName != null and roleName!=''">
    AND r.roleName LIKE '%#{roleName, jdbcType=VARCHAR}%'
</if>

用 $ 不能防 SQL 注入

<if test="roleName != null and roleName!=''">
    AND r.roleName LIKE '%${roleName}%'
</if>

这种是以 ? 的形式

<if test="roleName != null and roleName!=''">
    AND r.roleName LIKE concat('%',#{roleName,jdbcType=VARCHAR},'%')
</if>

这种也是以 ? 的形式「必须用双引号来包裹百分号，否则查不出来数据」

<if test="roleName != null and roleName!=''">
    AND r.roleName LIKE "%"#{roleName,jdbcType=VARCHAR}"%"
</if>

[bingoogolapple]

一对多

LEFT JOIN

<resultMap id="functionMap" type="FunctionModel">
    <id property="functionID" column="functionID"/>
    <result property="functionKey" column="functionKey"/>
    <result property="functionName" column="functionName"/>
</resultMap>

<resultMap id="menuInfoMap" type="MenuInfoModel">
    <id property="menuID" column="menuID"/>
    <result property="menuDisplayName" column="menuDisplayName"/>
    <result property="authKey" column="authKey"/>
    <result property="sortIndex" column="sortIndex"/>
    <result property="parentID" column="parentID"/>
    <collection property="functionList" resultMap="functionMap"/>
</resultMap>

<select id="queryMenuAndFunctionList" resultMap="menuInfoMap">
    SELECT
    mi.menuID,
    mi.menuDisplayName,
    mi.authKey,
    mi.sortIndex,
    mi.parentID,
    f.functionID,
    f.functionKey,
    f.functionName
    FROM CP_MenuInfo mi
    LEFT JOIN CP_Function f
    ON mi.authKey != '' AND f.functionKey LIKE CONCAT(mi.authKey, '%')
</select>

LEFT JOIN - 可以在 collection 节点指定 autoMapping 属性为 true 来省掉 collection 节点中 resultMap 属性关联的 resultMap，但是 resultMap 节点不能指定 autoMapping 为 true，否则「多」的那一方有多少条结果就有多少条

<resultMap id="menuInfoMap" type="MenuInfoModel">
    <id property="menuID" column="menuID"/>
    <result property="menuDisplayName" column="menuDisplayName"/>
    <result property="authKey" column="authKey"/>
    <result property="sortIndex" column="sortIndex"/>
    <result property="parentID" column="parentID"/>
    <collection property="functionList" autoMapping="true" ofType="FunctionModel"/>
</resultMap>

<select id="queryMenuAndFunctionList" resultMap="menuInfoMap">
    SELECT
    mi.menuID,
    mi.menuDisplayName,
    mi.authKey,
    mi.sortIndex,
    mi.parentID,
    f.functionID,
    f.functionKey,
    f.functionName
    FROM CP_MenuInfo mi
    LEFT JOIN CP_Function f
    ON mi.authKey != '' AND f.functionKey LIKE CONCAT(mi.authKey, '%')
</select>

子查询，不建议

<resultMap id="menuInfoMap" type="MenuInfoModel" autoMapping="true">
    <collection property="functionList" autoMapping="true" column="authKey" select="queryFunctionListByAuthKey"/>
</resultMap>

<select id="test" resultMap="menuInfoMap">
    SELECT
    menuID,
    menuDisplayName,
    authKey,
    sortIndex,
    parentID
    FROM CP_MenuInfo
</select>

<select id="queryFunctionListByAuthKey" parameterType="java.lang.String" resultType="FunctionModel">
    SELECT
    functionID,
    functionKey,
    functionName
    FROM CP_Function
    WHERE
    #{authKey, jdbcType=VARCHAR} != '' AND functionKey LIKE CONCAT(#{authKey, jdbcType=VARCHAR}, '%')
    ORDER BY functionID ASC
</select>