Use posture of escape character of mybatis in springboot series

[DB series] use posture of escape character of mybatis

It is convenient and concise to write SQL directly in the XML file of mybatis, but it should be noted that in the XML file, some scenes that need escape, such as query, are often encounteredid < xxxThe less than sign cannot be written directly in SQL. Next, we will look at the escape characters in mybatis and how to deal with the escape problem

<!– more –>

1. Escape

1. Escape character

In mybatis XML files, the most common escape character is the less than sign, such as querying data with ID less than 100

<select id="xxx">
  select * from `money` where id &lt; #{id}
</select>

Note the SQL above. The less than sign actually uses<, cannot be used directly<For example, if the less than sign is used directly, the following error message will appear in idea

In addition to the less than sign above, another common problem in daily development is&Like the operator, if there is a bit operation scenario in SQL, it also needs to be escaped

<select id="xxx">
  --Select * from ` money ` where id & the SQL with 1 = 1 needs to be escaped as follows
  select * from `money` where id &amp; 1 = 1
</select>

The mapping relationships of several common escape character tables in mybatis are shown in the following table (the escape of mybatis actually completely follows the XML escape rules, mainly including the following)

Symbol	Escape	explain
<	<	less than
>	>	greater than
&	&	And
‘	‘	Single quotation mark
“	“	Double quotation mark

2. <! [CDATA []] > how to write

Although the escape method is simple, there is a problem that it is not intuitive enough. When reading SQL, you need to reverse the escape in your mind, which is not very friendly. Fortunately, XML provides CDATA syntax, and the statements wrapped in it will not be parsed by the XML parser

For example, write and operate through the following writing method

<select id="queryBitCondition" resultType="long">
    select id from money where  <![CDATA[ `money` & #{bit} = #{bit} ]]>
</select>

When using this method, you should pay attention to:

• Nested writing is not supported
• Terminator]]>Note that it should be used with the start character

3. Can not miss the source code and related knowledge points

0. Project

• Project: https://github.com/liuyueyi/spring-boot-demo
• Source code: https://github.com/liuyueyi/spring-boot-demo/tree/master/spring-boot/103-mybatis-xml

Series blog posts:

• [DB series] basic curd posture in mybatis series tutorials
• [DB series] mybatis series tutorial: curd basic use posture – Annotation
• [DB series] several postures of parameter transfer of mybatis

1. WeChat official account: a gray Blog

The above contents are only the words of one family. Due to limited personal ability, it is inevitable that there are omissions and mistakes. If you find a bug or have better suggestions, you are welcome to criticize and correct and be grateful

The following is a gray personal blog, which records all blog posts in study and work. Welcome to visit

• A personal blog https://blog.hhui.top
• A gray blog spring special blog http://spring.hhui.top