MyBatis 3 - 从mapper获取SQL字符串 [英] MyBatis 3 - get SQL string from mapper

问题描述

我想仅使用MyBatis3生成SQL字符串
（使用xml映射），但我获得的SQL无效。

I'd like to use MyBatis3 only to produce SQL string (using the xml mapping) but the SQL i obtain is not valid.

示例，我获取sql字符串：

Example, I obtain the sql string:

SELECT * FROM USER WHERE NAME = john

在这个sql中不存在'字符串值 john

In this sql isn't present the ' char sorrounding the string value john

mybatis.xml ：

...
    <mappers>
        <mapper resource="sql1.xml"/>
    </mappers>
...

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>

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);

结果

SELECT * FROM USERS
WHERE 
name LIKE john AND num = 12345

在这个SQL中，字符串 john 不包含在' char
因此它不是有效的SQL（我的目的只是使用myBatis生成有效的SQL字符串）。
我想要：

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:

SELECT * FROM USERS
WHERE 
name LIKE 'john' AND num = 12345

谢谢

推荐答案

你应该使用＃{name} 而不是 $ {name} 。

以下示例将生成有效的SQL

The sample below will generate a valid SQL

<mapper namespace="sql1">
    <select id="select1" parameterType="map" resultType="String" >
        SELECT * FROM USERS
        WHERE 
        name LIKE #{name} AND num = #{number}
    </select>
</mapper>

如果使用$ character，MyBatis会直接复制并粘贴字符串参数。另一方面，如果你使用#character，它会使用参数绑定。

MyBatis directly copies and pastes the string parameter if you use $ character. On the other hand it uses parameter binding if you use # character.

然后你应该使用selectMap，selectList或selectOne执行你的sql，

You should then execute your sql using selectMap, selectList or selectOne,

List<String> resultSet = sessionFactory.openSession().selectList("sql1.select1", pars);

此调用将自动将参数绑定到语句并执行它。

This call will automatically bind the parameters to the statement and execute it.

警告：

<select id="select1" parameterType="map" resultType="String" >
    SELECT * FROM USERS
        WHERE 
        name LIKE #{name} AND num = #{number}
</select>

可能无法执行，因为MyBatis无法映射多个列（ SELECT * ）单个字符串（ resultType =String）查询的两个可能的更正如下所示：

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:

<!--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>

对于解决方案二，您应该使用以下java代码执行mybatis查询：

For solution two you should execute mybatis query using the java code below:

List<Map<?, ?>> resultSet = sessionFactory.openSession().selectList("sql1.select1", pars);

为什么getBoundSql返回查询的详细信息？：

参数绑定在驱动程序级别完成，因此你不会得到像这样的sql字符串

Details of Why getBoundSql Returns a Query with ?:

Parameter binding is done at driver level so you will not get an sql string like this

SELECT * FROM USERS
WHERE 
name LIKE 'john' AND num = 12345

相反，你会获取准备参数绑定的sql查询模板，

Instead you'll get sql query template which is ready for parameter binding,

SELECT * FROM USERS
    WHERE 
    name LIKE ? AND num = ?

在sql字符串中添加参数允许 sql注入。安全的方法是使用SQL Driver提供的参数绑定方法，MyBatis总是使用参数绑定。

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命令为字符串，并假设我'是一个试图访问您的数据的applesius用户。我可以写

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

john' or ''='

所以这将生成下面的sql命令：

So this will generate the sql command below:

SELECT * FROM USERS
WHERE 
name LIKE 'john' or ''='' AND num = 12345

参数绑定的其他好处

参数绑定的第二个好处是它允许预处理语句。假设您需要使用不同的参数执行相同的sql 1000次。

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字符串，

If you generate sql strings with parameters bound,

SELECT * FROM USERS WHERE name LIKE 'john' AND num = 12345;
SELECT * FROM USERS WHERE name LIKE 'foo' AND num = 67890;

数据库服务器需要逐个解析每个sql命令然后执行它们。

database server will need to parse each sql command one by one and then execute them.

使用参数化的sql查询，

With parameterized sql queries,

SELECT * FROM USERS WHERE name LIKE ? AND num = ?

SQL驱动程序缓存查询，因此只进行一次解析，然后将不同的参数绑定到相同的SQL命令。

SQL driver caches the query so parsing is done only once and then it binds different parameters to the same SQL command.

您仍然可以使用参数化的sql（boundSql）与另一个库或Java的java.sql.Connection。下面是一个例子：

You can still use the parameterized sql (boundSql) with an another library or Java's java.sql.Connection. Below is an example:

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();

这篇关于MyBatis 3 - 从mapper获取SQL字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！