Question

mybatis 根据不同的参数运行不同的SQL

<select id="getUserInfo" parameterType="java.util.Map" resultMap="userResult">
<if test="#{group == 'MANAGER'} || #{group == 'COMPANY'}">
SELECT TU.USERID, TU.USERNAME, TU.PASSWORD, TU.GROUPIDEN, TU.ROLEID
, TUI.REALNAME, TUI.GENDER, TUI.BIRTHDAY, TUI.PERSONID, TUI.EMAIL, TUI.MOBILENUMBER
, TUS.JOBNUMBER, TUS.COMPANYID FROM TAB_USER TU, TAB_USER_INFO TUI, TAB_USER_STAFF TUS
WHERE TU.USERID = TUI.USERID AND TU.USERID = TUS.USERID AND TUI.USERID = TUS.USERID AND TU.USERNAME = #{userName}
</if>
<if test="#{group == 'CONSUMER'}">
SELECT TU.USERID, TU.USERNAME, TU.PASSWORD, TU.GROUPIDEN, TU.ROLEID
, TUI.REALNAME, TUI.GENDER, TUI.BIRTHDAY, TUI.PERSONID, TUI.EMAIL, TUI.MOBILENUMBER
, TUC.ADDRESS, TUC.AREAID FROM TAB_USER TU, TAB_USER_INFO TUI, TAB_USER_CONSUMER TUC
WHERE TU.USERID = TUI.USERID AND TU.USERID = TUC.USERID AND TUI.USERID = TUC.USERID AND TU.USERNAME = #{userName}
</if>
</select>
根据group的值执行不同的SQL语句，运行报错，错误提示如下：
Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT TU.USERID, TU.USERNAME, TU.PASSWORD, TU.GROUPIDEN, TU.ROLEID
, TUI.RE' at line 7
如果我把上面的判断语句去掉只保留一个SQL运行是正确，请问我这种动态查询是不是写错了？具体应该怎么修改？
好吧，原因我找到了<if test="#{group == 'MANAGER'} || #{group == 'COMPANY'}">把判断语句中的#{}去掉就可以了，只是为什么#{}这种形式不可以呢，而且以#{}单独写一个判断的时候能运行成功，而且错误提示也没有提示我语法错误，希望大神能帮忙解答下

Answer 1

<select id="getUserInfo" parameterType="java.util.Map" resultMap="userResult">
SELECT TU.USERID, TU.USERNAME, TU.PASSWORD, TU.GROUPIDEN, TU.ROLEID
, TUI.REALNAME, TUI.GENDER, TUI.BIRTHDAY, TUI.PERSONID, TUI.EMAIL, TUI.MOBILENUMBER
<if test="#{group == 'MANAGER'} || #{group == 'COMPANY'}">
, TUS.JOBNUMBER, TUS.COMPANYID FROM TAB_USER TU,
TAB_USER_INFO TUI, TAB_USER_STAFF TUS
</if>
<if test="#{group == 'CONSUMER'}">
, TUC.ADDRESS, TUC.AREAID FROM TAB_USER TU,
TAB_USER_INFO TUI, TAB_USER_CONSUMER TUC
</if>
WHERE
<if test="#{group == 'MANAGER'} || #{group == 'COMPANY'}">
TU.USERID = TUI.USERID AND TU.USERID = TUS.USERID AND TUI.USERID = TUS.USERID AND TU.USERNAME = #{userName}
</if>
<if test="#{group == 'CONSUMER'}">
TU.USERID = TUI.USERID AND TU.USERID = TUC.USERID AND TUI.USERID = TUC.USERID AND TU.USERNAME = #{userName}
</if>
</select>

这样试试呢？

追问

好吧，原因我找到了把判断语句中的#{}去掉就可以了，只是为什么#{}这种形式不可以呢，而且以#{}单独写一个判断的时候能运行成功，而且错误提示也没有提示我语法错误，希望大神能帮忙解答下

追答

if条件里一般都是这样写的，你可以参考下：
delete from ***

and user_id = #{userId}
and school_id = #{schoolId}

1=0

这些标签写错了，只有在执行的时候才会抛出异常，没有那么智能的