java MyBatis - 如何创建动态的 WHERE 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6056770/
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 - how to create w dynamic WHERE Clause
提问by user6778654
The service gets an unknown object containing a list of three values ??[column, operator, value] For example, EMAIL - like - "TEST"
该服务获取一个未知对象，其中包含三个值的列表??[column, operator, value] 例如，EMAIL - like - "TEST"
Based on the resulting list to build the WHERE clause I have but I would also be able to build such a condition as follows (for example)
根据生成的列表来构建我拥有的 WHERE 子句，但我也可以构建如下条件（例如）
WHERE (email like 'test' AND user_id <> 5) OR (trans_id <100 AND session_id> 500)
WHERE (email like 'test' AND user_id <> 5) OR (trans_id <100 AND session_id> 500)
Does anyone can help me how to do it?
有没有人可以帮助我怎么做？
回答by Mark McLaren
I have been rediscovering MyBatis after a long absence myself (I was familiar with iBatis at one time). Rolf's example looks like it might be the .Net implementation, I may be wrong but I don't think the Java notation looks like that now. Rolf's tip about the literal strings is very useful.
久违的自己重新认识了 MyBatis（曾经对 iBatis 很熟悉）。Rolf 的示例看起来可能是 .Net 实现，我可能错了，但我不认为 Java 表示法现在看起来像那样。Rolf 关于文字字符串的提示非常有用。
I've created a little class to hold your columns, operators and value and pass these into MyBatis to do the processing. The columns and operators are string literals but I have left the values as sql parameters (I imagine MyBatis would be able to do any necessary type conversion).
我创建了一个小类来保存您的列、运算符和值，并将它们传递给 MyBatis 进行处理。列和运算符是字符串文字，但我将值保留为 sql 参数（我想 MyBatis 能够进行任何必要的类型转换）。
public class TestAnswer {
public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
SqlSessionFactory sqlFactory = (SqlSessionFactory) ctx.getBean("sqlSessionFactory");
MappedStatement statement = sqlFactory.getConfiguration().getMappedStatement("testAnswer");
ArrayList<Clause> params1 = new ArrayList<Clause>();
params1.add(new Clause("email","like","test"));
params1.add(new Clause("user","<>",5));
ArrayList<Clause> params2 = new ArrayList<Clause>();
params2.add(new Clause("trans_id","<",100));
params2.add(new Clause("session_id",">",500));
HashMap params = new HashMap();
params.put("params1", params1);
params.put("params2", params2);
BoundSql boundSql = statement.getBoundSql(params);
System.out.println(boundSql.getSql());
}
static class Clause{
private String column;
private String operator;
private Object value;
public Clause(String column, String operator, Object value){
this.column = column;
this.operator = operator;
this.value = value;
}
public void setColumn(String column) {this.column = column;}
public void setOperator(String operator) {this.operator = operator;}
public void setValue(Object value) {this.value = value;}
public String getColumn() {return column;}
public String getOperator() {return operator;}
public Object getValue() {return value;}
}
}
As you can see, I use Spring but I expect something similar would probably work outside of the Spring environment.
如您所见，我使用 Spring，但我希望类似的东西可能会在 Spring 环境之外工作。
<?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="com.stackoverflow.TestMapper">
<select id="testAnswer" parameterType="map" resultType="hashmap">
select *
FROM somewhere
<where>
<foreach item="clause" collection="params1" separator=" AND " open="(" close=")">
${clause.column} ${clause.operator} #{clause.value}
</foreach>
OR
<foreach item="clause" collection="params2" separator=" AND " open="(" close=")">
${clause.column} ${clause.operator} #{clause.value}
</foreach>
</where>
</select>
</mapper>
回答by Rolf
There are two key parts to this answer. One is the "dynamic" element, and the other are the $$ literal elements around the "operator" in your question.
这个答案有两个关键部分。一个是“动态”元素，另一个是您问题中“运算符”周围的 $$ 文字元素。
<select id="yourSelect" parameterClass="Map" resultMap="somethingsomething" >
select * from YOURTABLE
<dynamic prepend="WHERE">
<isNotNull prepend="AND" property="email">
email $operator$ #testvalue#
</isNotNull>
</dynamic>
</select>
See also the DataMapper Dynamic SQL documentationon this topic.
另请参阅有关此主题的DataMapper 动态 SQL 文档。
