I have been using the ORM framework Mybatis, and I have used some common functions in mybatis. Today, in project development, there is a business that requires restricting users from querying fields in certain tables and whether certain fields are displayed, such as certain fields in a certain table are not allowed to be queried by users. In this case, it is necessary to build SQL to dynamically pass table names and field names. Now I will summarize the solution, and I hope it will be helpful to my partners who encounter the same problem.
Dynamic SQL is one of the powerful features of mybatis. Before precompiling SQL statements, mybatis will dynamically parse SQL and parse it into a BoundSql object, which is also processed here. Let's first get familiar with the usage of #{} and ${} in mybatis:
In the dynamic SQL parsing process, the effects of #{} and ${} are different:
#{ } parsed as a parameter marker for a JDBC precompiled statement.
As shown in the following sql statement
select * from user where name = #{name};Will be parsed as:
select * from user where name = ?;
Can you see #{} being parsed into a parameter placeholder? .
${ } is just a pure string replacement. Variable replacement will be performed during the dynamic SQL parsing stage, such as the following SQL statement:
select * from user where name = ${name};When we pass the parameter "sprite", sql will parse as:
select * from user where name = "sprite";
You can see that the SQL statement before precompilation no longer contains variable name.
In summary, the replacement stage of the variable of ${ } is in the dynamic SQL parsing stage, while the replacement of the variable of #{ } is in the DBMS.
The difference between #{} and ${} can be summarized simply as follows:
#{} treats the passed parameter as a string and adds a double quote to the passed parameter
${} will directly display the passed parameters in SQL, and no quotes will be added
#{} can prevent sql injection in Chengdu, but ${} cannot prevent sql injection
${} has been replaced by variables before precompilation, which poses the risk of SQL injection. As follows sql
select * from ${tableName} where name = ${name}If the passed parameter tableName is user; delete user; --, then after sql dynamic parsing, the SQL before precompilation will become:
select * from user; delete user; -- where name = ?;
--The subsequent statements will not work as comments, and my friends and I were stunned! ! ! Did you see that the original query statement actually secretly contains a SQL that deletes table data, which is to delete, delete, delete! ! ! I said important things three times, and you can imagine how great this risk is.
${} is generally used to transfer the table name, field name, etc. of the database.
Try not to use ${} where you can use #{}
Going to the topic, through the above analysis, I believe you may have some ideas on how to dynamically call table names and field names. Examples are as follows:
<select id="getUser" resultType="java.util.Map" parameterType="java.lang.String" statementType="STATEMENT">select ${columns}from ${tableName}where COMPANY_REMARK = ${company}</select>To implement dynamic call table names and field names, precompilation cannot be used. You need to add statementType="STATEMENT"" .
statementType: any of STATEMENT (non-precompiled), PREPARED (precompiled) or CALLABLE, which tells MyBatis to use Statement, PreparedStatement or CallableStatement respectively. Default: PREPARED. Obviously, precompilation cannot be used here, it must be changed to non-precompilation.
Secondly, the value of the variable in sql is ${xxx}, not #{xxx}.
Because ${} is to directly display the passed parameters to generate SQL, for example, the parameters passed in ${xxx} are string data, quotation marks should be added before the parameter is passed in, such as:
String name = "sprite";name = "'" + name + "'";
Summarize
The above is the solution to the Mybatis dynamic call table name and field name introduced to you by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!
This article is reproduced from: http://www.yuanrengu.com/index.php/mybatis1021.html