mybatis dynamically calls table names and field names

I have been using the ORM framework Mybatis, and I use some common functions in mybatis. Today, in project development, there is a business that needs to restrict each user's query of fields in certain tables and whether certain fields are displayed. For example, certain fields of a certain table are not allowed to be queried by users. In this case, you need to build sql to dynamically pass in the table name and field name. Now I will summarize the solutions. I hope it will be helpful to friends who encounter the same problem.

　 Dynamic SQL is one of the powerful features of mybatis. Before mybatis pre-compiles the sql statement, it will dynamically parse the sql and parse it into a BoundSql object. This is where the dynamic sql is processed. Let us first familiarize ourselves with the usage of #{} and ${} in mybatis:

In the dynamic sql parsing process, the effects of #{} and ${} are different:

#{ } 解析为一个 JDBC 预编译语句（prepared statement）的参数标记符。

For example, the following sql statement

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

　 Will be parsed as:

select * from user where name = ?;

　 You can see that #{} is parsed as a parameter placeholder? .

${ } 仅仅为一个纯碎的 string 替换，在动态 SQL 解析阶段将会进行变量替换

　For example, the following sql statement:

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

When we pass the parameter "sprite", the sql will be parsed as:

select * from user where name = "sprite";

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

                                                The replacement stage of the variables of ${ } is in the dynamic SQL parsing stage, while the replacement stage of the variables of # { } is in the DBMS.

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

#{} treats the incoming parameter as a string and adds a double quote to the incoming parameter

${} will pass The imported parameters are directly displayed and generated in sql without adding quotation marks.

#{} can greatly prevent sql injection. ${} cannot prevent sql injection.

　 ${} has been replaced by variables before precompilation. There is a risk of sql injection. The following sql

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

　If the incoming parameter tableName is user; delete user; --, then after the sql is dynamically parsed, the sql before precompilation will become:

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

　--The subsequent statements will not work as comments, My friends and I were shocked! ! ! Did you see that the original query statement secretly included a SQL statement to delete table data? Delete, delete, delete! ! ! If you say important things three times, you can imagine how high the risk is.

${} is generally used to transfer the table names, field names, etc. of the database.

Try not to use ${} where #{} can be used.

Let’s get to the point. Through the above analysis, I believe you may already know how to dynamically call There are some ideas for table names and field names. The example is 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 achieve dynamic calling of table names and field names, precompilation cannot be used, and statementType="STATEMENT"" needs to be added.

statementType：STATEMENT（非预编译），PREPARED（预编译）或CALLABLE中的任意一个，这就告诉 MyBatis 分别使用Statement，PreparedStatement或者CallableStatement。默认：PREPARED。这里显然不能使用预编译，要改成非预编译。

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

　Because ${} directly displays the incoming parameters to generate sql. For example, if the parameters passed in ${xxx} are string data, you need to add quotation marks before the parameters are passed in, such as:

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