MyBatis if clause
14,203
Remove <![CDATA[
and ]]>
as this escapes all the query and mybatis doesn't process it at all so if
is passed verbatim as part of the query to database.
<select id="searchStudent" parameterType="hashmap" resultMap="StudentResult">
SELECT * FROM STUDENTS
WHERE 1 = 1
<if test="studId != null">
AND STUD_ID= #{studId}
</if>
<if test="name != null">
AND NAME like #{name}
</if>
</select>
Author by
user2241925
Updated on June 17, 2022Comments
-
user2241925 almost 2 years
I tried following if clause in MyBatis and got following exception please help me to identify the issue here..
public class Student{ private Integer studId; private String name; private String email; private Date dob; }
Mapping
<select id="searchStudent" parameterType="hashmap" resultMap="StudentResult"> <![CDATA[ SELECT * FROM STUDENTS WHERE 1 = 1 <if test="studId != null"> AND STUD_ID= #{studId} </if> <if test="name != null"> AND NAME like #{name} </if> ]]> </select>
Exception I get:
Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException: ### 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 'test="studId != null"> AND STUD_ID= 1 </if> <if test="name != null"> ' at line 4 ### The error may exist in StudentMapper.xml ### The error may involve com.maventest.mytest.StudentMapper.searchStudent-Inline ### The error occurred while setting parameters ### SQL: SELECT * FROM STUDENTS WHERE 1 = 1 <if test="studId != null"> AND STUD_ID= ? </if> <if test="name != null"> AND NAME like ? </if> ### 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 'test="studId != null"> AND STUD_ID= 1 </if> <if test="name != null"> ' at line 4 at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)