MyBatis dynamic SQL expressions

For an existing project, ORM framework uses MyBatis. When conducting list query, select a state (value 0) and splicing WHERE conditions through dynamic SQL but fail to return normal query results, and then conduct investigation.

POJO

private Integer status;// State, which can be 0, 1, 2, 3.
/ /... Omit the otherCopy the code

Mapper XML

<sql>
  <trim prefix="where" prefixOverrides="and | or ">/ /... Omit the other<if test="status ! = null and status ! = "">and status = #{status}</if> 
  <trim prefix="where" prefixOverrides="and | or ">
</sql>Copy the code

SQLand status = 0 is not properly concatenated, that is, the expression in test is false, which results in an error. However, obviously the value (Integer :0)! = null also! = “, should be true.

Public Boolean apply(DynamicContext context) public Boolean apply(DynamicContext context) public Boolean apply(DynamicContext context) If (evaluator. EvaluateBoolean (test, the context getBindings (), the code is parsed < if test = “status! =null and status ! =””>test; if true, concatenate SQL; otherwise ignored.

public class IfSqlNode implements SqlNode {
  private ExpressionEvaluator evaluator;
  private String test;
  private SqlNode contents;

  public IfSqlNode(SqlNode contents, String test) {
    this.test = test;
    this.contents = contents;
    this.evaluator = new ExpressionEvaluator(a); }public boolean apply(DynamicContext context) {
    if (evaluator.evaluateBoolean(test, context.getBindings())) {
      contents.apply(context);
      return true;
    }
    return false; }}Copy the code

Open the ExpressionEvaluator class and you’ll find that the expression is parsed using OGNL, which is familiar if you’ve ever worked with the ancient Struts framework. Through OgnlCache. GetValue (expression, parameterObject); MyBatis also caches expressions to improve performance.

public class ExpressionEvaluator {  
  public boolean evaluateBoolean(String expression, Object parameterObject) {  
    Object value = OgnlCache.getValue(expression, parameterObject);  
    if (value instanceof Boolean) return (Boolean) value;  
    if (value instanceof Number) return !new BigDecimal(String.valueOf(value)).equals(BigDecimal.ZERO);  
    returnvalue ! =null;  
  }Copy the code

Private static Object parseExpression(String expression) parseExpression(String expression) parseExpression(String expression) Then call ognl.getValue (parseExpression(expression), root) to get the value of the expression.

public class OgnlCache {

  private static final Map<String, ognl.Node> expressionCache = new ConcurrentHashMap<String, ognl.Node>();

  public static Object getValue(String expression, Object root) throws OgnlException {
    return Ognl.getValue(parseExpression(expression), root);
  }

  private static Object parseExpression(String expression) throws OgnlException {
    try {
      Node node = expressionCache.get(expression);
      if (node == null) {
        node = new OgnlParser(new StringReader(expression)).topLevelExpression();
        expressionCache.put(expression, node);
      }
      return node;
    } catch (ParseException e) {
      throw new ExpressionSyntaxException(expression, e);
    } catch (TokenMgrError e) {
      throw new ExpressionSyntaxException(expression, e); }}Copy the code

As for how ognl. getValue(parseExpression(expression), root) works, if you are interested, you can find out for yourself. So far, we know that MyBatis expressions are handled with OGNL, which is enough. Let’s go to the OGNL website to see if there is a problem with our expression syntax that is causing this problem.

Interpreting Objects as Booleans

Any object can be used where a boolean is required. OGNL interprets objects as booleans like this:

1. If the object is a Boolean, its value is extracted and returned;
2. If the object is a Number, its double-precision floating-point value is compared with zero; non-zero is treated as true, zero as false;
3. If the object is a Character, its boolean value is true if and only if its char value is non-zero;
4. Otherwise, its boolean value is true if and only if it is non-null.

Sure enough, if the object is a Number type, the value 0 will be resolved to false, otherwise true, as will the floating point 0.00. OGNL defines Boolean somewhat like JavaScript, that is, ” == 0 == false.

and status= #{status}
= “is not true, causing the expression to have a value of false.

Change the expression to

and status = #{status}
The root cause of this problem is still from the specification of the code, only the String type need to determine whether! = “, there is no need for other types. The problem may be caused by the developer directly copying the previous line to save trouble or the MyBatis generation tool used is not precise.

It’s necessary to mention another pit here, if you have something like String STR =”A”;

you should be careful. Because OGNL recognizes a single character inside a single quote as a Java char, String and char return false.

Two articles are recommended:

• In-depth understanding of MyBatis parameters
• OGNL tutorial in MyBatis