MyBatis註解實現動態SQL問題

@Select("SELECT title from book_tbl where id = #{id}")

@Select("<script>"+
			"SELECT id " +
            "from book_tbl" +
            "<where>" +
            "<if test 'company_id != null'>" +
            "and company_id = #{company_id}" +
            "</if>" +
            "<if test 'title != null'>" +
            "and title like CONCAT('%',#{title},'%')" +
            "</if>" +
            "</where>" +
            "ORDER BY create_time desc,rank desc" +
        "</script>")

plateNumber2 = plateNumber2 + "," + plateNumber;
String[] split = plateNumber2.split(",");
StringBuffer stringBuffer = new StringBuffer();
stringBuffer.append("SELECT plate_number from p_park_record where park_key = '"+parkDO.getParkKey()+"'");
stringBuffer.append(" and (");
for (int i = 0; i < split.length; i++) {
    stringBuffer.append(" plate_number = "+split[i]);
    if (i != split.length-1){
        stringBuffer.append(" OR ");
    }
}
stringBuffer.append(" )");
stringBuffer.append(" order by sensor_exit_time desc");
stringBuffer.append(" limit 1");

plateNumber2 = plateNumber2 + "," + plateNumber;
ParkRecordDO parkRecordDO = new ParkRecordDO();
parkRecordDO.setPlateNumber(plateNumber2);
parkRecordDO.setParkKey(parkDO.getParkKey());
String onlyPlateNum = parkRecordMapper.getOnlyPlateNum(parkRecordDO);

    <select id="getOnlyPlateNum" parameterType="ParkRecordDO" resultType="java.lang.String">
        SELECT plate_number from p_park_record where park_key = #{parkKey} and sensor_exit_time is not null and
        <foreach collection="plateNumber.split(',')" index="index" item="id" open="(" separator=" or " close=")">
            plate_number = #{id}
        </foreach>
        order by sensor_exit_time desc
        limit 1
    </select>

<!--
        foreach
        collection：指定要遍歷的集合
                    list類型的參數會特殊處理封裝在map中，map的key就叫list
              item：將當前遍歷出的元素賦值給指定的變量
              separator：元素之間的分割符
              open: 遍歷出的所有結果拼接一個開始字符
              close：遍歷出的所有結果拼接一個結束字符
              index：索引，遍歷list的時候是索引，item就是當前值；
                          遍歷map時index標識的就是map的key，item就是map的值
              #{變量名}: 就能取出變量的值就是當前遍歷出的元素
-->
<select id="queryUserByForEach" resultType="user">
    select * from easybuy_user where id in
    <foreach collection="ids" item="item_id" separator=","
             open="(" close=")">
        #{item_id}
    </foreach>
</select>


<insert id="batchSaveUser" parameterType="user">
    insert into easybuy_user(loginName, userName, password) values
    <foreach collection="userList" item="user" separator=",">
        (#{user.loginName}, #{user.userName}, #{user.password})
    </foreach>
</insert>

<!--
        使用set標簽或者trim標簽與if標簽相結合
        實現動態更新sql語句的拼接
-->
<update id="updateUserByCondition" parameterType="user">
    update easybuy_user
    <set>
        <if test="userName != null">
            username = #{userName},
        </if>
        <if test="email != null">
            email = #{email},
        </if>
    </set>
    where id = 26;
</update>

<select id="queryUserByChoose" resultType="com.unistart.entity.User">
    select * from easybuy_user
    <where>
        <choose>
            <when test="id != null">
                id=#{id}
            </when>
            <when test="userName != null">
                userName like #{userName}
            </when>
            <when test="sex != null">
                sex = #{sex}
            </when>
            <otherwise>
                1=1;
            </otherwise>
        </choose>
    </where>
</select>

<!--
        trim標簽的使用：解決後面多出的and或or
        prefix=""：前綴，trim標簽體中是整個字符串拼串後的結果
                prefix給拼串後的整個字符串加一個前綴
        prefixOverrides=""：前綴覆蓋：去掉整個字符串前面多餘的字符
        suffix=""：後綴，給拼串後的整個字符串加一個後綴
        suffixOverrides=""：後綴覆蓋，去掉整個字符串後面多餘的字符
-->
<select id="queryUserByTrim" resultType="user">
    select * from easybuy_user
    <trim prefix="where" suffixOverrides="and">
        <if test="id != null">
            id = #{id} and
        </if>
        <if test="userName != null and userName!=&quot;&quot;">
            userName like #{userName} and
        </if>
        <if test="email != null and email!=&quot;&quot;">
            email = #{email} and
        </if>
    </trim>
</select>

<!--
        查詢用戶：要求，攜帶哪些字段查詢條件就按這些字段進行查詢
        使用的OGNL，類似與EL表達式

        從參數中取值進行判斷，遇見特殊符號使用轉義字符
        使用where標簽時要註意，它隻能去除第一個多出來的and或or
-->
<select id="queryUserByCondition" resultType="user">
    select * from easybuy_user
    <where>
        <if test="id != null">
            and id = #{id}
        </if>
        <if test="userName != null and userName!=&quot;&quot;">
            and userName like #{userName}
        </if>
        <if test="email != null and email!=&quot;&quot;">
            and email = #{email}
        </if>
    </where>
</select>