foreach

<if test="statusList != null and statusList.size()>0">
    and stage_status in
    <foreach collection="statusList" item="status" open="(" separator="," close=")">
        #{status}
    </foreach>
</if>

choose

<choose>
    <when test="title != null">
        AND title like #{title}
    </when>
    <otherwise>
        AND featured = 1
    </otherwise>
</choose>

bind

<select id="selectBlogsLike" resultType="Blog">
    <bind name="pattern" value="'%' + _parameter.getTitle() + '%'"/>
    SELECT * FROM BLOG WHERE title LIKE #{pattern}
</select>

toString和比较

<if test="investmentType=='2'.toString()">
    and total_investment &gt;= 100000 and total_investment &lt;= 500000
</if>

<if test="startDate != null and startDate != ''">
and create_time <![CDATA[ >= ]]> #{startDate}
</if>

注解

@Select({"<script>",
    "select * from table where del_flag=0 and username in ",
    "<foreach collection='usernames' item='username' separator=',' close=')' open='('>#{username}</foreach> ",
    "<if test=\"nickname != null and nickname != ''\"> and nickname like concat('%', #{nickname}, '%') </if>",
    "</script>"})

provider

• 方式一

@SelectProvider(type = UserSqlBuilder.class, method = "buildGetUsersByName")
List<User> getUsersByName(String name);

public class UserSqlBuilder {
  public static String buildGetUsersByName(final String name) {
    return new SQL(){{
      SELECT("*");
      FROM("users");
      if (name != null) {
        WHERE("name like #{value} || '%'");
      }
      ORDER_BY("id");
    }}.toString();
  }
}

• 方式二

//同名可以不加method
@SelectProvider(type = UserSqlBuilder.class")
List<User> selectPersonSql(String name);

public String selectPersonSql() {
  return new SQL()
    .SELECT("P.ID", "A.USERNAME", "A.PASSWORD", "P.FULL_NAME", "D.DEPARTMENT_NAME", "C.COMPANY_NAME")
    .FROM("PERSON P", "ACCOUNT A")
    .INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID", "COMPANY C on D.COMPANY_ID = C.ID")
    .WHERE("P.ID = A.ID", "P.FULL_NAME like #{name}")
    .ORDER_BY("P.ID", "P.FULL_NAME")
    .toString();
}

sql

• ALTER TABLE

# 删除users表phone字段
ALTER TABLE users DROP COLUMN phone;

• case

SELECT CASE sex WHEN 1 THEN '男' WHEN 2 THEN '女' ELSE '未知' END AS gender FROM table;

SELECT name, CASE WHEN score >= 90 THEN '优秀' WHEN score >= 60 THEN '及格' ELSE '不及格' END AS level FROM table;

• find_in_set

select * from sys_dept where find_in_set('101', ancestors)