MyBatis: dynamic where condition

Question

I'm new to myBatis and I need to put where statement inside my query.

My mapper is defined in this way:

<select id="findMyTableByWhereCondition" parameterType="map" resultMap="mytable">
   SELECT *
   FROM mytable m 
   <where>#{whereCondition}</where>
</select>

My Dao:

public List<MyTalbe> findMyTableByWhereCondition(String whereCondition) {
        Map<String, Object> param = new HashMap<String, Object>();
        param.put("whereCondition", "m.name = 'Test' and m.surname= 'Test'");
        return sqlSession.selectList("findMyTableByWhereCondition", param );
    }

When I try to execute this query, i get "Invalid relational operator". What is the best practice to handle this kind of query? I need to replace it over the "where" because this might change too often and it may even be very complex.

Thanks in advance

The `param` variable in your example is unused. What is the `whereCondition` string you pass to `sqlSession.selectList()`? — Mick Mnemonic, May 15 '17 at 09:35
I think you're not using the API correctly. Have a look at the examples in this question: [Passing multiple arguments into a SELECT without using a complex object](http://stackoverflow.com/questions/9308210/passing-multiple-arguments-into-a-select-without-using-a-complex-object), which uses one field/value per map element in the WHERE condition. — Mick Mnemonic, May 15 '17 at 09:38
I think the syntax for that is different in the XML. Did you have a look at the docs for [Dynamic SQL](http://www.mybatis.org/mybatis-3/dynamic-sql.html)? — Mick Mnemonic, May 15 '17 at 09:59

Blank · Accepted Answer · 2017-05-15T12:30:37.033

Try to use ${...} to refer parameter whereCondition:

<select id="findMyTableByWhereCondition" parameterType="map" resultMap="mytable">
   SELECT *
   FROM mytable m 
   <where>${whereCondition}</where>
</select>

Document wrote:

By default, using the #{} syntax will cause MyBatis to generate PreparedStatement properties and set the values safely against the PreparedStatement parameters (e.g. ?). While this is safer, faster and almost always preferred, sometimes you just want to directly inject an unmodified string into the SQL Statement. For example, for ORDER BY, you might use something like this:
ORDER BY ${columnName}
Here MyBatis won't modify or escape the string.

Note: Use $ will cause SQL Injection attack, you'd better read Dynamic SQL from official doc and try to construct sql like that.

score 0 · Answer 2 · edited Jul 31 '23 at 17:09

Please go through this link https://mybatis.org/mybatis-dynamic-sql/docs/conditions.html

List<Animal> search(String searchName){
        SelectStatementProvider selectStatement=select(id,animalName,bodyWeight,brainWeight)
        .from(animalData)
        .where(animalName,isLike(searchName).map(this::appendWildCards))
        .orderBy(id)
        .build()
        .render(RenderingStrategies.MYBATIS3);
}
        
String appendWildCards(String in) {
    return "%" + in + "%";
}

score 0 · Answer 3 · answered Jul 27 '23 at 11:37

0

for SQL server "... LIKE '%' + #{param} + '%' ..."

for ORACLE "... LIKE '%' || #{param} || '%' ..."

ref : https://mybatis.org/mybatis-3/java-api.html

answered Jul 27 '23 at 11:37

user22293714

1

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 27 '23 at 19:41

MyBatis: dynamic where condition

3 Answers3