Question

mysql里面 ibatis的like模糊查询问题, 谢谢，请问我的sql语句是否有错，问题是 查询条件可输入可不输入

<!-- 根据用户名搜索用户 -->
<select id="selectUserByUsername" parameterClass="Users" resultMap="resultUsers">
SELECT
a.USER_ID,
a.LOGIN_NAME,
a.SHOW_NAME,
a.PASS_WORD,
a.CREATE_TIME,
a.GROUP_ID,
a.ADMIN_FLG
FROM
table_user AS a
<dynamic prepend="WHERE">
<isNotEmpty property="loginName">
a.LOGIN_NAME LIKE '%$loginName$%'
</isNotEmpty>
<isNotEmpty prepend="AND" property="showName">
a.SHOW_NAME LIKE '%$showName$%'
</isNotEmpty>
<isNotEmpty prepend="AND" property="adminFlag">
a.GROUP_ID = #groupId#
</isNotEmpty>
<isNotEmpty prepend="AND" property="adminFlag">
a.ADMIN_FLG = #adminFlag#
</isNotEmpty>
</dynamic>
ORDER BY
a.USER_ID DESC
</select>
请问我的语句是否有错

Answer 1

有错的，你要这样
<dynamic prepend="WHERE">
<isNotEmpty property="loginName" prepend="AND">
a.LOGIN_NAME LIKE '%$loginName$%'
</isNotEmpty>
就对了。还是要加prepend="AND"的

追问

第一句也要加prepend="AND"这个吗？

Answer 2

<dynamic prepend="WHERE">
<isNotEmpty property="loginName">
a.LOGIN_NAME LIKE '%$loginName$%' AND
</isNotEmpty>
<isNotEmpty property="showName">
a.SHOW_NAME LIKE '%$showName$%' AND
</isNotEmpty>
<isNotEmpty property="adminFlag">
a.GROUP_ID = #groupId# AND
</isNotEmpty>
<isNotEmpty property="adminFlag">
a.ADMIN_FLG = #adminFlag#
</isNotEmpty>
</dynamic>

Answer 3

where 1=1
然后在第一个模糊查询中也加"AND"