Small knowledge, big challenge! This paper is participating in theEssentials for programmers”Creative activities

【DB series 】 The use of escape characters in Mybatis

It is convenient and concise to write SQL directly in mybatis XML file, but it needs to be noted that in XML file, there are often some scenarios that need to escape, such as querying data with ID < XXX, the < sign cannot be written directly in SQL, next we will look at. What are the escape characters in Mybatis and how to deal with the escape problem

I. escape

1. The escape character

In the XML file of Mybatis, the most common escape character is less than sign, such as query data with id less than 100

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

Note that in the SQL above, the < sign actually uses < , do not use <. For example, if < is used, the following error message may be displayed in IDEA

In addition to the less-than sign above, another common operation in daily development is the & and operator, if there is an operation in THE SQL scenario, also need to escape

<select id="xxx">Select * from 'money' where id = 1&amp; 1 = 1
</select>
Copy the code

The mapping relationship of several common escape characters in Mybatis is shown in the following table (The escape of Mybatis completely follows XML escape rules, mainly including the following ones).

symbol escape instructions
< < Less than
> > Is greater than
& & with
‘ ‘ Single quotes
“ “ Double quotation marks

2. Writing

The escape method is simple, but one problem is that it is not intuitive. When reading SQL, you need to reverse escape in your mind, which is not very friendly. Fortunately, XML provides the syntax of CDATA

For example, write and operate by writing the following way

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

When using this approach, note:

  • Nesting is not supported
  • At the end of operator]] >Note that it is used with the start character

III. Can’t miss the source code and related knowledge points

0. Project

  • Project: github.com/liuyueyi/sp…
  • Source: github.com/liuyueyi/sp…

Series of blog posts:

  • 【DB series 】Mybatis series of CURD basic use posture
  • 【DB series 】Mybatis series of CURD basic use posture – notes
  • 【DB series 】 Several posture of Mybatis parameter transfer

1. Wechat official account: Yash Blog

As far as the letter is not as good, the above content is purely one’s opinion, due to the limited personal ability, it is inevitable that there are omissions and mistakes, if you find bugs or have better suggestions, welcome criticism and correction, don’t hesitate to appreciate

Below a gray personal blog, record all the study and work of the blog, welcome everyone to go to stroll

  • A grey Blog Personal Blog blog.hhui.top
  • A Grey Blog-Spring feature Blog Spring.hhui.top