MyBatis a workaround for dynamic invocation of table names and field names _java

All the time using the ORM framework of MyBatis is to use some of the common features in MyBatis. Today, there is a business in project development that needs to limit the number of field queries that individual users have on certain tables and whether certain fields are displayed, such as some fields in a table that are not queried by the user. In this case, you need to build SQL to dynamically pass in the table name and field name. Now to summarize the solution, hopefully it will be helpful to partners who have the same problem.

Dynamic SQL is one of the powerful features of MyBatis, MyBatis dynamically parses SQL before compiling it, resolves it to a Boundsql object, and also handles dynamic SQL here. Let's first familiarize ourselves with the use of MyBatis #{} and ${}:

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

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

such as the following SQL statement

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

will be resolved to:

SELECT * from user where name =?;

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

${} replaces only a solid string, which is substituted in the dynamic SQL parsing phase
Like the SQL statement:

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

When we pass the parameter "sprite", SQL resolves to:

SELECT * from user where name = "Sprite";

You can see that the SQL statement before precompiling does not already contain the variable name.

In summary, the replacement phase of the ${} variable is in the dynamic SQL parsing phase, while the substitution of #{} variables is in the DBMS.

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

#{} treats incoming arguments as a string and adds a double quote to the arguments passed in

${} The incoming arguments are displayed directly in SQL and no quotes are added

#{} can be very large in Chengdu to prevent SQL injection, ${} cannot prevent SQL injection

${} has been replaced by a variable before precompiling, which risks SQL injection. The following SQL

SELECT * from ${tablename} where name = ${name}

If the passed parameter tablename to user; Delete user; -After SQL dynamic parsing, the SQL before precompiling becomes:

select * from user; Delete user; --WHERE name =?;

--After the statement will not work as a comment, suddenly I and my little friend stunned!!! See, the original query statement, unexpectedly secretly contains a delete table data sql, is deleted, DELETE, delete!!! The important thing to say three times, can imagine, this risk is how big.

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

Where you can use #{}, try not to use ${}

To get to the point, through the above analysis, I believe you may have some ideas on how to dynamically invoke 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 the dynamic invocation table name and field name, you cannot use precompiled, you need to add Statementtype= "STATEMENT".

Statementtype:statement (not precompiled), PREPARED (precompiled) or any one of the callable, which tells MyBatis to use STATEMENT respectively, PreparedStatement or CallableStatement. Default: PREPARED. It is obviously not possible to use precompilation, which is to be changed to not precompiled.

Second, the variables in SQL are ${xxx}, not #{xxx}.

Because ${} is to display the incoming parameters directly to the build SQL, such as ${xxx} The incoming parameters are string data, you need to enclose the arguments before they are passed in, such as:

String name = "Sprite";
Name = "'" + Name + "'";

The above is a small set to introduce the MyBatis dynamic call Table name and field name of the solution, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!