I have used mybatis recently. I have used ibatis before. Overall, it is similar, but I still encountered many problems. I will record it again.
For example, the difference between using #{} and ${} parameter transmission,
Use # to pass the parameter, and the SQL statement parsing will add "", for example, select * from table where name = #{name}, the passed name is Xiao Li, then the last printout is
select * from table where name = 'Xiao Li', it will be parsed as a string. This is obviously better than $. The #{} parameter passing can prevent SQL injection. If the parameter you pass in is single quotes', then if you use ${}, this method will report an error.
Another scenario is that if you want to do dynamic sorting, such as order by column, be sure to use ${} at this time, because if you use #{}, then the printed one will be
select * from table order by 'name' , this is useless,
At present, if you can use #, don’t use $.
Difference between # and $ in mybatis
1. # Treat all the incoming data as a string, and add double quotes to the automatically incoming data. For example: order by #user_id#, if the value passed in is 111, then the value when parsing into sql is order by "111". If the value passed in is id, the parsed into sql is order by "id".
2. $Displays the passed data directly and generates it in SQL. For example: order by $user_id$, if the value passed in is 111, then the value when parsed into sql is order by user_id. If the value passed in is id, the parsed into sql is order by id.
3. The # method can greatly prevent SQL injection.
4. The $ method cannot prevent Sql injection.
5. The $ method is generally used to pass in database objects, such as passing in table names.
6. Generally, if you can use #, don’t use $.
When using order by dynamic parameters when sorting MyBatis, you need to pay attention to using $ instead of #
String replacement
By default, using the #{} format syntax causes MyBatis to create a preprocessed statement property and set a safe value with it as the background (such as?). This is safe and quick, and sometimes you just want to insert a string that doesn't change directly into the SQL statement. For example, like ORDER BY, you can use it like this:
ORDER BY ${columnName}
Here MyBatis will not modify or escape strings.
Important: It is not safe to accept content output from the user and provide it to an unchanged string in the statement. This can lead to potential SQL injection attacks, so you should not allow users to enter these fields, or usually escape and check them yourself.
A brief summary of the difference between $ and # in Mybatis
Not long ago, someone came to our company for an interview. Our manager asked this question. I had only a little understanding of it, so I went to Baidu.
In fact, the difference is very simple. You will understand it with an example. Write a sentence SQL-for example: select * from user_role where user_code = "100";
In this sentence, it needs to be written as select * from ${tableName} where user_code = #{userCode}
Therefore, the $ character is spelled into SQL directly, while the # character will be spelled with SQL in the form of a string.