Shulou(Shulou.com)06/02 Report--

This article is about the difference between the values of # and $in MyBatis. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. Preface

Dynamic SQL is one of the main features of MyBatis. After the parameters defined in mapper are passed to XML, mybatis will dynamically parse them before query. MyBatis provides us with two syntax that support dynamic SQL: # {} and ${}.

II. Cases

Let's demonstrate the use of $and # through related cases.

The initial writing method of query statement in UserMapper.xml file

Select id, loginId, userName, role, note from t_user where id = # {id} and userName=# {userName}

The test console prints the SQL as follows:

2017-08-06 18 DEBUG 23390 [main] [com.queen.mybatis.mapper.UserMapper.findUserByMapParam]-[DEBUG] = > Preparing: select id, loginId, userName, role, note from t_user where id =? And userName=?

Now let's modify the statement to change where id = # {id} to ${id}, as follows:

Select id, loginId, userName, role, note from t_user where id = ${id} and userName=# {userName}

Test again, the console prints the SQL as follows:

2017-08-06 18 Preparing 27 main [com.queen.mybatis.mapper.UserMapper.findUserByMapParam]-[DEBUG] = > Preparing: select id, loginId, userName, role, note from t_user where id = 1 and userName=?

You can observe the difference between the two SQL statements, the following one takes the data directly into the SQL statement when getting the parameter value with the ${} statement, while we use # {} to get the value in the form of a placeholder.

# {}: setting parameters into SQL statements in a precompiled way, as we did when we learned JDBC, can prevent sql injection to a great extent

${}: if the extracted value is directly concatenated into the SQL statement, there will be a security problem and cannot prevent Sql injection.

In most cases, we use # {} to take the value of the parameter, but in some cases, like table names, we should be careful to use the order by dynamic parameter when sorting, using $instead of #.

Modify UserMapper.xml:

Select id, loginId, userName, role, note from t_user where id = # {id} and userName=# {userName} order by userName # {orderDesc}

The test console prints as follows:

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 'desc'' at line 1 thanks # The error may exist in UserMapper.xml### The error may involve com.queen.mybatis.mapper.UserMapper.findUserByMapParam-Inline### The error occurred while setting parameters### SQL: select id, loginId, userName, role, note from t_user where id =? And userName=? Order by userName? # 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''desc'' at line 1 at org.apache.ibatis.exceptions.ExceptionFactory.wrapException (ExceptionFactory.java:26)

Take the value in the way of # {}, and the console reports an error.

Let's modify the UserMapper.xml and change it to the value of ${}

Select id, loginId, userName, role, note from t_user where id = # {id} and userName=# {userName} order by userName ${orderDesc}

Test again, the console print SQL is normal:

2017-08-06 19 DEBUG 1702331 [main] [com.queen.mybatis.mapper.UserMapper.findUserByMapParam]-[DEBUG] = > Preparing: select id, loginId, userName, role, note from t_user where id = And userName=? Order by userName desc, thank you for your reading! This is the end of the article on "what's the difference between the value of # and $in MyBatis". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.