MyBatis - how to create w dynamic WHERE clause

Question

The service receives an unknown object containing a list of three values ​​[column, operator, value] For example, EMAIL - like - "TEST"

According to the resulting list for constructing the WHERE clause, I have, but I could also construct such a condition as follows (for example)

WHERE (email like 'test' AND user_id <> 5) OR (trans_id <100 AND session_id> 500)

Can anyone help me how to do this?

Answer 1

I reopened MyBatis after a long absence (I was familiar with iBatis at a time). The Rolf example looks like it might be a .Net implementation, I could be wrong, but I don't think Java notation now looks like this. Rolf’s advice on literal strings is very helpful.

I created a small class to store columns, operators, and values ​​and pass them to MyBatis for processing. Columns and operators are string literals, but I left the values ​​as sql parameters (I suppose MyBatis will be able to perform any type conversion necessary).

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 am using Spring, but I expect something like this will probably work outside of the Spring environment.

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

Answer 2

There are two key parts to this question. One of them is the "dynamic" element, and the other is 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 documentation on this topic.