使用MyBatis动态选择SQL语句 [英] Dynamic Select SQL statements with MyBatis

问题描述

我想在Oracle 12g数据库中使用mybatis生成动态sql语句，在这里我可以利用哈希映射添加条件，如下所示:

I would like to have dynamic sql statements using mybatis in an Oracle 12g database where I can utilize a hash map to add conditionals something like the following:

<select id="getUsers" resultType="hashmap" parameterType="hashmap"> 
  select * 
  from users 
  <where> 
  <iterate var="i=0" increment> 
    ${columni} like #{valuei} 
  </iterate> 
  </where> 
</select> 

我有办法完成类似的事情吗?

Is there a way for me to accomplish something like that?

推荐答案

来自文档:

字符串替换

默认情况下，使用#{}语法将使MyBatis生成PreparedStatement属性，并根据PreparedStatement参数(例如?)安全地设置值.尽管这是更安全，更快且几乎总是首选的，但有时您只想直接将未经修改的字符串插入SQL语句.例如，对于ORDER BY，您可以使用以下代码:

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 a string unmodified into the SQL Statement. For example, for ORDER BY, you might use something like this:

ORDER BY ${columnName}

MyBatis不会修改或转义字符串.

Here MyBatis won't modify or escape the string.

这使您可以将列名作为参数传递给查询等.

This allows you to e.g. pass column names as parameters to query etc.

记住要始终清除直接粘贴到SQL的数据.

Remember to always sanitize data that you are directly pasting to SQL.

如果需要为WHERE子句生成多个条件，请在内部使用<where>标记和<foreach>.请注意，<foreach>具有允许指定分隔符，打开/结束字符串等的高级属性.与${}表示法结合使用之前，我已经提到过，这允许构造动态的WHERE子句.例如，请参见此答案.

If you need to generate multiple conditions for WHERE clause, use <where> tag with <foreach> inside. Note that <foreach> has advanced attributes that allow to specify separator, opening/ending string etc. Combined with ${} notation I've mentioned before this allows construction of dynamic WHERE clause. For an example, see this answer.

这篇关于使用MyBatis动态选择SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！