Mybatis dynamically calls the solution for table and field names

1 use Mybatis, the ORM framework, which USES some common functions in mybatis. Today, in the project development, there is a business that needs to restrict each user to query the fields in some tables and whether some fields are displayed or not. For example, some fields in a certain table are not allowed to be queried by the user. In this case, you need to build sql to dynamically pass in table names and field names. Now, let's summarize the solutions and hopefully help others who are facing the same problem.

Dynamic SQL is one of the most powerful features of mybatis. Before precompiling sql statements, mybatis dynamically parses sql into an BoundSql object, where dynamic sql is also processed. Let's get familiar with the usage of #{} and ${} in mybatis:

In the dynamic sql parsing process, the effect of #{} and ${} is not the same:

#{} resolves to a parameter marker for an JDBC precompiled statement (prepared statement).

See the sql statement below

select * from user where name = #{name};

It will be interpreted as:

select * from user where name = ?;

You can see that #{} is resolved to 1 parameter placeholder? .

${} is only replaced with 1 pure broken string. The substitution is performed during the dynamic SQL parsing phase
For example, the following sql statement:

select * from user where name = ${name};

When we pass the parameter "sprite", sql will be resolved to:

select * from user where name = "sprite";

You can see that the sql statement before the precompilation no longer contains the variable name.

To sum up, the ${} variable substitution phase is in the dynamic SQL parsing phase, while the #{} variable substitution phase is in DBMS.

The difference between #{} and ${} can be summarized as follows:

#{} treats the incoming parameter as if it were a string, and puts a double quotation mark around it

${} displays the incoming parameters directly in sql without adding quotes

#{} greatly prevents sql injection, while ${} does not prevent sql injection

${} has been replaced before the precompile, and there is a risk of sql injection. The following sql

select * from ${tableName} where name = ${name}

If the parameter tableName passed in is user; delete user; --, then after sql dynamic parsing, sql before precompilation will be:

select * from user; delete user; -- where name = ?;

The following statement will not work as a comment, I and my friends were shocked!! See, the original query statement, actually secretly included a delete table data sql, is delete, delete, delete!! Say important things three times, and you can imagine how big the risk is.

${}1 is generally used to transfer database table names, field names, and so on

Avoid using ${} where you can use #{}

To get to the point, through the above analysis, I believe you may have some ideas on how to dynamically call table names and field names. Here's an example:

<select id="getUser" resultType="java.util.Map" parameterType="java.lang.String" statementType="STATEMENT">
select 
${columns}
from ${tableName}
where COMPANY_REMARK = ${company}
</select>

To make dynamic calls to table and field names, you can't use precompilation, you need to add statementType="STATEMENT"".

statementType: any one of STATEMENT (non-precompiled), PREPARED (precompiled), or CALLABLE, which tells MyBatis to use Statement, PreparedStatement, or CallableStatement, respectively. Default: PREPARED. Obviously you can't use precompiled here, you can change it to non-precompiled.

Second, the value of the variable in sql is ${xxx}, not #{xxx}.

Because ${} directly displays the incoming parameters to generate sql, if the parameters passed in ${xxx} are string data, please put quotation marks before the parameters are passed in, such as:

String name = "sprite";
name = "'" + name + "'";

conclusion

This article from: http: / / www yuanrengu. com/index php/mybatis1021 html