CODEWITHSUNDEEP
javaibatismybatis
tomasz-mer
tomasz-mer

Reputation: 3910

MyBatis - how to create w dynamic WHERE Clause

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?

Upvotes: 5

Views: 23341

Answers (3)

Anderson
Anderson

Reputation: 2752

Check the myBatis doc here about dynamic where clause. https://mybatis.org/mybatis-3/dynamic-sql.html

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

The where element knows to only insert “WHERE” if there is any content returned by the containing tags. Furthermore, if that content begins with “AND” or “OR”, it knows to strip it off.

Upvotes: 0

Mark McLaren
Mark McLaren

Reputation: 11540

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>

Upvotes: 8

Rolf
Rolf

Reputation: 7278

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.

Upvotes: 2

Related Questions