java 如何用mybatis运行任意sql？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13374054/
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
How to run arbitrary sql with mybatis?
提问by eric2323223
I've an application that use mybatis for object persistence. But there are chances I need to run arbitrary sql(from user). Can I do it with mybatis?
我有一个使用 mybatis 进行对象持久化的应用程序。但是我有可能需要运行任意 sql（来自用户）。我可以用mybatis做吗？
Update:
更新：
I choose to use dbutils (JDBC) to run user-defined sql, but I need a instance of DataSource to create QueryRunner. Is there any way I can get datasource from mybatis?
我选择使用dbutils（JDBC）来运行用户自定义的sql，但是我需要一个DataSource的实例来创建QueryRunner。有什么办法可以从mybatis中获取数据源吗？
回答by Italo Borssatto
I use this utilitary class:
我使用这个实用类：
import java.util.List;
import org.apache.ibatis.annotations.SelectProvider;
public interface SqlMapper {
static class PureSqlProvider {
public String sql(String sql) {
return sql;
}
public String count(String from) {
return "SELECT count(*) FROM " + from;
}
}
@SelectProvider(type = PureSqlProvider.class, method = "sql")
public List<?> select(String sql);
@SelectProvider(type = PureSqlProvider.class, method = "count")
public Integer count(String from);
@SelectProvider(type = PureSqlProvider.class, method = "sql")
public Integer execute(String query);
}
回答by Ben
your question is similar to the question of How to exequte query directly from java code using mybatis?
您的问题类似于如何使用 mybatis 直接从 java 代码执行查询的问题？
I have already given the answer to that question. But I hope this solution will help you.
我已经给出了这个问题的答案。但我希望这个解决方案能帮助你。
Mybatis has already this function, but you must use the adapter as follows.
Mybatis 已经有这个功能了，但是必须使用如下的适配器。
create an adapter class;
public class SQLAdapter { String sql; public SQLAdapter(String sql) { this.sql = sql; } public String getSql() { return sql; } public void setSql(String sql) { this.sql = sql; } }create typeAlias of class SQLAdapter
创建一个适配器类；
public class SQLAdapter { String sql; public SQLAdapter(String sql) { this.sql = sql; } public String getSql() { return sql; } public void setSql(String sql) { this.sql = sql; } }创建类 SQLAdapter 的 typeAlias
<typeAlias alias="sqladapter" type="com.zj.xxx.xxx.SQLAdapter" />
<typeAlias alias="sqladapter" type="com.zj.xxx.xxx.SQLAdapter" />
put select tag in each object xml where you need to execute the sql directly.
<select id="findRecords" parameterType="SQLAdapter" resultMap="xxxxxResultMap"> ${sql} </select>call this select method like
在需要直接执行sql的每个对象xml中放置select标签。
<select id="findRecords" parameterType="SQLAdapter" resultMap="xxxxxResultMap"> ${sql} </select>像这样调用这个选择方法
String _sql = "select * from table where... order by... limit..."; xxxxx.findRecords(new SQLAdapter(_sql));
String _sql = "select * from table where... order by... limit..."; xxxxx.findRecords(new SQLAdapter(_sql));
- Things have been all done. you can no longer writer complex sql language in the xml file. Good Luck.
- 事情都做完了。您不能再在 xml 文件中编写复杂的 sql 语言。祝你好运。
回答by cinqS
Based on the answers provided, they both are good. But both of them required an Adapterclass to be used.
根据提供的答案，它们都很好。但是它们都需要使用一个Adapter类。
Using Mybatis version 3, I succeeded using a HashMap<String, String>to keep and pass the SQL.
使用Mybatis 版本 3，我成功地使用 aHashMap<String, String>来保持和传递SQL。
See the codes below.
请参阅下面的代码。
in Mapperclass
在Mapper课堂上
final String sql = "${sql}";
@Select(sql)
void execute(HashMap<String, String> m);
when invoke the method:
调用方法时：
String sql = "SELECT * FROM record limit 1";
HashMap<String, String> map = new HashMap<String, String>();
map.put("sql", sql);
mapper.execute(map);
HashMapprovides a way that you don't have to define the Class properties, or fields in code, you can use a Map to define it redomly.
HashMap提供了一种您不必在代码中定义 Class 属性或字段的方法，您可以使用 Map 来重新定义它。
Thanks.
谢谢。
回答by Harmit
Reusable fragment of SQL can be used to create select part of query dynamically. In you mapper pass query as normal parameter:
可重用的 SQL 片段可用于动态创建查询的选择部分。在您的映射器中，将查询作为普通参数传递：
@Param("sql")String sql
@Param("sql")String sql
In your query just access the parameter using ${sql}instead of #{sql}. Value in parameter sql can be a fully valid sql query or a fragment of sql query.
在您的查询中，只需使用${sql}而不是#{sql}访问参数。参数 sql 中的值可以是完全有效的 sql 查询或 sql 查询的片段。
