The use posture of the escape character of SpringBoot series Mybatis

[DB Series] The use posture of the escape character in Mybatis

It is more convenient and concise to write sql directly in the xml file of mybatis, but it should be noted that in the xml file, some scenarios that need to be escaped are often encountered, such as querying id < xxx , the less than sign cannot be written directly in the sql Next, we will take a look at the escape characters in mybatis and how to deal with the escape problem

<!-- more -->

I. Escaping

1. escape character

In the xml file of mybatis, our most common escape character is the less than sign, such as querying data whose id is less than 100

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

Note that in the above sql, the less-than sign actually uses < , and you cannot use < directly. For example, if you use the less-than sign directly, there will be the following error message in idea

In addition to the above less than sign in daily development, another common one is & and operator. If there is a bit operation scenario in sql, it also needs to be escaped

<select id="xxx">
  -- select * from `money` where id & 1 = 1 的sql，需要如下转义
  select * from `money` where id &amp; 1 = 1
</select>

The mapping relations of several common escape character tables in mybatis are as follows (the escaping of mybatis actually completely follows the xml escape rules, mainly the following ones)

symbol	Escape	illustrate
<	<	Less than
>	>	more than the
&	&	and
'	'	apostrophe
"	"	Double quotes

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

Although the way of escaping is simple, one problem is that it is not intuitive enough. When reading sql, you need to invert the meaning in your mind. This is not very friendly. Fortunately, xml provides CDATA syntax, which is wrapped in The statements inside 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 need to pay attention to:

• Does not support nested wording
• The ending character ]]> used in conjunction with the starting character

III. Source code and related knowledge points not to be missed

0. Project

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

Series of blog posts:

• [DB Series] The basic use posture of CURD in Mybatis series of tutorials
• [DB series] Mybatis series of tutorials CURD basic use posture-annotations
• 【DB series】Several postures for parameter transfer of

1. WeChat public account: Yi Hui Hui Blog

It is not as good as the letter. The above content is purely a family statement. Due to limited personal ability, it is inevitable that there will be omissions and errors. If you find a bug or have a better suggestion, you are welcome to criticize and correct, and I am grateful.

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

• Blog Personal Blog 1615865edb1026 https://blog.hhui.top
• Blog-Spring Special Blog 1615865edb105c http://spring.hhui.top