Mybatis在註解上如何實現動態SQL

Posted on by

在註解上實現動態SQL

使用Mybatis註解實現sql語句，但是有些時候有些字段是空的，這時候這個空的字段就要從條件查詢語句中刪除，這個時候就需要用到動態Sql。

註解的動態語句支持以下

  • trim
  • where
  • set
  • foreach
  • if
  • choose
  • when
  • otherwise
  • bind
@Select({"<script> " +
        "select * from t_user " +
        "where  1=1 " +
        "<if test='userId!=null'> and id = #{userId}</if> " +
        "</script>"})

要加上標簽就可以實現條件判斷

但是在無法使用大於號 、小於號，那如何解決這問題呢，其實隻要把大於號、小於號轉義即可

註解方式動態sql寫法和註意事項

@Select({"<script>" +
            "select * from tb_examine" +
            "<where> 1 = 1" +
            "<if test = \" employeeId != null and  employeeId != '' \"> AND employee_id = #{employeeId} </if>" +
            "<if test = \" gradeId != null and gradeId != '' \"> AND grade_id = #{gradeId} </if>" +
            "<if test = \" year != null and year != '' \"> AND year like #{year} </if>" +
            "<if test = \" (statrMonth != null and statrMonth != '') and (endMonth == null or endMonth == '') \"> AND month &gt;= #{statrMonth} </if>" +
            "<if test = \" (statrMonth == null or  statrMonth == '') and (endMonth != null and endMonth != '')   \"> AND month &lt;= #{endMonth} </if>" +
            "<if test = \" (statrMonth != null and statrMonth != '') and (endMonth != null and endMonth != '')   \">AND month &gt;= #{statrMonth} AND month &lt;= #{endMonth}  </if>" +
            "</where>" +
            "</script>"})
public List<Examine> getName(Examine examine);

判斷字符串為空串 用單引號

大於等於用

小於等於用

以上為個人經驗，希望能給大傢一個參考，也希望大傢多多支持WalkonNet。

推薦閱讀：