MyBatis中的#{}和${}的区别(参数替换和字符串替换)

短命女 2022-01-22 01:41 602阅读 0赞

## 中文翻译 ##

**Parameters部分：**

`Parameters参数`是MyBatis中非常强大的元素，正常来说90％以上的情况你要用的，都应该是`#{}`。

1.parameter mapping，`命名参数映射`。如果你传入的参数是一个`对象`（指复杂对象，非Integer或者String这些简单的），用`paramterType="object"`的情况，可以直接用`#{attribute}`取值。

<insert id="insertUser" parameterType="User">
      insert into users (id, username, password)
      values (#{id}, #{username}, #{password})
    </insert>

2.对于需要特殊指定`javaType`或者`jdbcType`的，可以在`#{param}`中进行指定，你还可以在这里指定`numericScale`小数位。

#{height,javaType=double,jdbcType=NUMERIC,numericScale=2}

3.甚至你还可以自定义`typeHandler`来处理一些特殊的情况和特殊的类型，通常有两种方式，一种是实现TypeHandler接口，还有一种就是继承自BaseTypeHandler类(这种应该比较少，用了这么久我还没处理过)。

#{age,javaType=int,jdbcType=NUMERIC,typeHandler=MyTypeHandler}

4.另外，`mode`属性允许您指定`IN，OUT或INOUT`参数。 如果参数为OUT或INOUT，则参数对象属性的实际值将会更改，就像调用输出参数时那样。 如果mode = OUT/INOUT且jdbcType = CURSOR（即Oracle REFCURSOR），则必须指定resultMap以将ResultSet映射到参数的类型。 请注意，javaType属性在这里是可选的，如果留空并使用CURSOR作为jdbcType，它将自动设置为ResultSet。

#{department, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=departmentResultMap}

\*。默认情况下，使用`＃{}`语法将导致MyBatis生成`PreparedStatement`属性并根据PreparedStatement参数`安全`地设置值（例如？）。

**String Substitution部分：**

1.虽然`#{}`更安全，更快速且几乎总是首选，但有时您只想将未修改的字符串直接注入SQL语句。 例如，对于ORDER BY，就要用`String Substitution，字符串替换`了。

ORDER BY ${columnName}

2.另外一种`高端自由查询`的用法， 你可以用`${column}`指定`查询`字段，用`#{value}`指定查询的`值`，是不是很6的用法。

@Select("select * from user where ${column} = #{value}")
    User findByColumn(@Param("column") String column, @Param("value") String value);

3.字符串替换是`很弱的安全性能`，容易被`SQL注入`，所以用的时候请保持小心。原则是：`能用参数替换就用参数，不能用或者动态字段查询才用字符串替换`。

## 英文原文 ##

[http://www.mybatis.org/mybatis-3/sqlmap-xml.html][http_www.mybatis.org_mybatis-3_sqlmap-xml.html]

### Parameters ###

In all of the past statements, you've seen examples of simple parameters. Parameters are very powerful elements in MyBatis. For simple situations, probably 90% of the cases, there's not much to them, for example:

<select id="selectUsers" resultType="User">
      select id, username, password
      from users
      where id = #{id}
    </select>

The example above demonstrates a very simple named parameter mapping. The parameterType is set to int, so therefore the parameter could be named anything. Primitive or simple data types such as Integer and String have no relevant properties, and thus will replace the full value of the parameter entirely. However, if you pass in a complex object, then the behavior is a little different. For example:

<insert id="insertUser" parameterType="User">
      insert into users (id, username, password)
      values (#{id}, #{username}, #{password})
    </insert>

If a parameter object of type User was passed into that statement, the id, username and password property would be looked up and their values passed to a PreparedStatement parameter.

That's nice and simple for passing parameters into statements. But there are a lot of other features of parameter maps.

First, like other parts of MyBatis, parameters can specify a more specific data type.

#{property,javaType=int,jdbcType=NUMERIC}

Like the rest of MyBatis, the javaType can almost always be determined from the parameter object, unless that object is a HashMap. Then the javaType should be specified to ensure the correct TypeHandler is used.

NOTE The JDBC Type is required by JDBC for all nullable columns, if null is passed as a value. You can investigate this yourself by reading the JavaDocs for the PreparedStatement.setNull() method.

To further customize type handling, you can also specify a specific TypeHandler class (or alias), for example:

#{age,javaType=int,jdbcType=NUMERIC,typeHandler=MyTypeHandler}

So already it seems to be getting verbose, but the truth is that you'll rarely set any of these.

For numeric types there's also a numericScale for determining how many decimal places are relevant.

#{height,javaType=double,jdbcType=NUMERIC,numericScale=2}

Finally, the mode attribute allows you to specify IN, OUT or INOUT parameters. If a parameter is OUT or INOUT, the actual value of the parameter object property will be changed, just as you would expect if you were calling for an output parameter. If the mode=OUT (or INOUT) and the jdbcType=CURSOR (i.e. Oracle REFCURSOR), you must specify a resultMap to map the ResultSet to the type of the parameter. Note that the javaType attribute is optional here, it will be automatically set to ResultSet if left blank with a CURSOR as the jdbcType.

#{department, mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=departmentResultMap}

MyBatis also supports more advanced data types such as structs, but you must tell the statement the type name when registering the out parameter. For example (again, don't break lines like this in practice):

#{middleInitial, mode=OUT, jdbcType=STRUCT, jdbcTypeName=MY_TYPE, resultMap=departmentResultMap}

Despite all of these powerful options, most of the time you'll simply specify the property name, and MyBatis will figure out the rest. At most, you'll specify the jdbcType for nullable columns.

#{firstName}
    #{middleInitial,jdbcType=VARCHAR}
    #{lastName}

#### String Substitution ####

By default, using the \#\{\} syntax will cause MyBatis to generate PreparedStatement properties and set the values safely against the PreparedStatement parameters (e.g. ?). While this is safer, faster and almost always preferred, sometimes you just want to directly inject an unmodified string into the SQL Statement. For example, for ORDER BY, you might use something like this:

ORDER BY ${
        columnName}

Here MyBatis won't modify or escape the string.

String Substitution can be very useful when the metadata(i.e. table name or column name) in the sql statement is dynamic, for example, if you want to select from a table by any one of its columns, instead of writing code like:

@Select("select * from user where id = #{id}")
    User findById(@Param("id") long id);

@Select(“select \* from user where name = \#\{name\}”)  
User findByName(@Param(“name”)String name);

@Select(“select \* from user where email = \#\{email\}”)  
User findByEmail(@Param(“email”)String email);

// and more “findByXxx” method  
you can just write:

@Select("select * from user where ${column} = #{value}")
    User findByColumn(@Param("column") String column, @Param("value") String value);

in which the $\{column\} will be substituted directly and the \#\{value\} will be "prepared". Thus you can just do the same work by:

User userOfId1 = userMapper.findByColumn("id", 1L);
    User userOfNameKid = userMapper.findByColumn("name", "kid");
    User userOfEmail = userMapper.findByColumn("email", "noone@nowhere.com");

This idea can be applied to substitute the table name as well.

NOTE It's not safe to accept input from a user and supply it to a statement unmodified in this way. This leads to potential SQL Injection attacks and therefore you should either disallow user input in these fields, or always perform your own escapes and checks.

[http_www.mybatis.org_mybatis-3_sqlmap-xml.html]: http://www.mybatis.org/mybatis-3/sqlmap-xml.html