MyBatis - how to create w dynamic WHERE Clause

Question

The service gets an unknown object containing a list of three values [column, operator, value] For example, 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 (email like 'test' AND user_id <> 5) OR (trans_id <100 AND session_id> 500)

Does anyone can help me how to do it?

score 8 · Answer 1 · answered Sep 26 '11 at 19:40

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.

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

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.

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

That would require a 4 argument clause (column, operator, value1, value2) - or at least some way of turning your 2 dates into a suitable value. Maybe create a BetweenClause class. This is not an especially elegant solution I'm afraid as the date formatting (for the value field) could easily become database vendor specific unless you go to special lengths to stick within ANSI standards. — Mark McLaren, Dec 04 '13 at 09:44
It would be simpler to implement BETWEEN using two SQL clauses... http://stackoverflow.com/questions/4809083/between-clause-versus-and — Mark McLaren, Dec 04 '13 at 10:10
Hi Mark ... I implemented the same way as the 4 argument clause ... Thanks for your input on this !!! — Krithika Vittal, Dec 09 '13 at 19:40

score 2 · Answer 2 · answered Jul 01 '11 at 19:15

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 documentation on this topic.

I believed this is iBatis approach, it is not applicable for myBatis — QWERTY, Jun 15 '20 at 08:39

MyBatis - how to create w dynamic WHERE Clause

2 Answers2

Linked