java MyBatis 3 - 从映射器获取 SQL 字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33197085/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MyBatis 3 - get SQL string from mapper
提问by Lof
I'd like to use MyBatis3 only to produce SQL string (using the xml mapping) but the SQL i obtain is not valid.
我只想使用 MyBatis3 来生成 SQL 字符串（使用 xml 映射），但我获得的 SQL 无效。
Example, I obtain the sql string:
例如，我获取 sql 字符串：
SELECT * FROM USER WHERE NAME = john
In this sql isn't present the 'char sorrounding the string value john
在这个 sql 中不存在'围绕字符串值john的字符
in mybatis.xml:
在mybatis.xml 中：
...
<mappers>
<mapper resource="sql1.xml"/>
</mappers>
...
sql1.xml
sql1.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="sql1">
<select id="select1" parameterType="map" resultType="String" >
SELECT * FROM USERS
WHERE
name LIKE ${name} AND num = ${number}
</select>
</mapper>
in MyBatisSql.java:
在MyBatisSql.java 中：
SqlSessionFactory sessionFactory = ConnectionFactory.getSqlSessionFactory();
Configuration configuration = sessionFactory.getConfiguration();
Map pars = new HashMap<String, Object>();
pars.put("name", "john");
pars.put("number", 1345);
MappedStatement ms = configuration.getMappedStatement("sql1.select1);
BoundSql boundSql = ms.getBoundSql(params);
String sql = boundSql.getSql();
System.out.println(sql);
the result is
结果是
SELECT * FROM USERS
WHERE
name LIKE john AND num = 12345
in this SQL, the string john, isn't enclosed by the 'char so it's not a valid SQL (my purpose is only to produce valid SQL string using myBatis). I'd like to have:
在此 SQL 中，字符串john未包含在'char 中，因此它不是有效的 SQL（我的目的只是使用 myBatis 生成有效的 SQL 字符串）。我想要：
SELECT * FROM USERS
WHERE
name LIKE 'john' AND num = 12345
thanks
谢谢
回答by vahapt
You should use #{name}instead of ${name}.
您应该使用#{name}而不是${name}。
The sample below will generate a valid SQL
下面的示例将生成有效的 SQL
<mapper namespace="sql1">
<select id="select1" parameterType="map" resultType="String" >
SELECT * FROM USERS
WHERE
name LIKE #{name} AND num = #{number}
</select>
</mapper>
MyBatis directly copies and pastes the string parameter if you use $ character. On the other hand it uses parameter binding if you use # character.
如果使用 $ 字符，MyBatis 直接复制粘贴字符串参数。另一方面，如果您使用 # 字符，它会使用参数绑定。
You should then execute your sql using selectMap, selectList or selectOne,
然后您应该使用 selectMap、selectList 或 selectOne 执行您的 sql，
List<String> resultSet = sessionFactory.openSession().selectList("sql1.select1", pars);
This call will automatically bind the parameters to the statement and execute it.
此调用将自动将参数绑定到语句并执行它。
WARNING:
警告：
<select id="select1" parameterType="map" resultType="String" >
SELECT * FROM USERS
WHERE
name LIKE #{name} AND num = #{number}
</select>
might fail to execute since MyBatis cannot map multiple columns (SELECT *) to a single string (resultType="String") two possible corrections to the query is shown below:
可能无法执行，因为 MyBatis 无法将多列（SELECT *）映射到单个字符串（resultType="String"）查询的两个可能的更正如下所示：
<!--Solution One-->
<select id="select1" parameterType="map" resultType="String" >
SELECT name FROM USERS
WHERE
name LIKE #{name} AND num = #{number}
</select>
<!--Solution Two-->
<select id="select1" parameterType="map" resultType="java.util.LinkedHashMap" >
SELECT * FROM USERS
WHERE
name LIKE #{name} AND num = #{number}
</select>
For solution twoyou should execute mybatis query using the java code below:
对于解决方案二，您应该使用以下 java 代码执行 mybatis 查询：
List<Map<?, ?>> resultSet = sessionFactory.openSession().selectList("sql1.select1", pars);
Details of Why getBoundSql Returns a Query with ?:
为什么 getBoundSql 返回带有 ? 的查询的详细信息：
Parameter binding is done at driver level so you will not get an sql string like this
参数绑定在驱动程序级别完成，因此您不会得到这样的 sql 字符串
SELECT * FROM USERS
WHERE
name LIKE 'john' AND num = 12345
Instead you'll get sql query template which is ready for parameter binding,
相反，您将获得可用于参数绑定的 sql 查询模板，
SELECT * FROM USERS
WHERE
name LIKE ? AND num = ?
Adding parameters into sql string allows sql injection. Safe way is to use parameter binding method provided with SQL Driver and MyBatis always uses parameter binding.
将参数添加到 sql 字符串允许sql 注入。安全的方式是使用 SQL Driver 提供的参数绑定方式，MyBatis 总是使用参数绑定。
Suppose that you manually created your sql command as string, and suppose that I'm a malicius user trying to access your data. I can write
假设您手动将 sql 命令创建为字符串，并假设我是一个试图访问您的数据的恶意用户。我可以写
john' or ''='
So this will generate the sql command below:
所以这将生成下面的 sql 命令：
SELECT * FROM USERS
WHERE
name LIKE 'john' or ''='' AND num = 12345
Additional Benefits of Parameter Binding
参数绑定的额外好处
The second benefit of parameter binding is that it allows prepared statements. Suppose that you need to execute same sql 1000 times with different parameters.
参数绑定的第二个好处是它允许准备好的语句。假设您需要使用不同的参数执行相同的 sql 1000 次。
If you generate sql strings with parameters bound,
如果生成绑定参数的sql字符串，
SELECT * FROM USERS WHERE name LIKE 'john' AND num = 12345;
SELECT * FROM USERS WHERE name LIKE 'foo' AND num = 67890;
database server will need to parse each sql command one by one and then execute them.
数据库服务器需要一一解析每一条sql命令，然后执行。
With parameterized sql queries,
使用参数化的 sql 查询，
SELECT * FROM USERS WHERE name LIKE ? AND num = ?
SQL driver caches the query so parsing is done only once and then it binds different parameters to the same SQL command.
SQL 驱动程序缓存查询，因此解析只进行一次，然后将不同的参数绑定到同一个 SQL 命令。
Update: Using BoundSql Outside of MyBatis
更新：在 MyBatis 之外使用 BoundSql
You can still use the parameterized sql (boundSql) with an another library or Java's java.sql.Connection. Below is an example:
您仍然可以将参数化 sql (boundSql) 与另一个库或 Java 的 java.sql.Connection 一起使用。下面是一个例子：
Connection myConnection;
PreparedStatement preparedStatement = myConnection.prepareStatement(boundSql);
preparedStatement.setString(1, "john"); //First parameter starts with 1 not 0!
preparedStatement.setInt(2, 12345);
ResultSet results = preparedStatement.executeQuery();
回答by Trang NT
SELECT(" * ");
FROM(" student ");
WHERE(" ten LIKE '%' #{ten} '%' ");
You can use it and you must put a space(_) between '%' and #{}
你可以使用它，你必须在 '%' 和 #{} 之间放一个空格（_）
