Mybatis注解方式动态拼接SQL

在spring boot中，集成Mybatis可以使用完全注解的方式，完全不用新增任何配置文件。多条件判断，可以使用<script>和<set>搭配实现。

@Update

@Update("<script>update t_user " +
        "<set> " +
        "<if test='userName != null'> user_name = #{userName},</if>" +
        "<if test='userPwd != null'> user_pwd = #{userPwd},</if>" +
        "<if test='userRemark != null'> user_remark = #{userRemark},</if>" +
        "<if test='userPhone != null'> user_phone = #{userPhone},</if>" +
        "<if test='userEmail != null'> user_email = #{userEmail},</if>" +
        "</set> " +
        "where user_id = #{userId}</script>")

@Select

@Select("select id,name,description,enabled,deleted,date_created as dateCreated,last_modified as lastModified from admin_role (#{roleParam})")

//不使用 @Param 时，那么，此时 collection 需要定义为 list，否则会报错
@Select({
        "<script>",
        "SELECT * ",
        "FROM users WHERE id IN",
        "<foreach item='id' index='index' collection='ids' open='(' separator=',' close=')'>",
        "#{id}",
        "</foreach>",
        "</script>"
})
List<UserEntity> getUserById(@Param("ids") List<String> ids);

@Insert

@Insert("insert into t_alert_log (alert_name,severity,message,start_at,end_at) " +
            "values(#{alertName},#{severity},#{message},#{startAt},#{endAt})")
@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")

@Update

@Update("update t_alert_log set status=true,remark=#{remark} where token=#{token}")

@ResultType

@ResultType(String.class)

注解方式传入表名和字段名

// 一般通过#{xxx}方式传入内容,但是表名和字段名不能通过这个方式.
// 动态传入表名和字段名用到${xxx}
@Delete("delete from ${tableName} where DATE(${columnName}) <= DATE(DATE_SUB(NOW(),INTERVAL #{interval} day));")
void delete(String tableName, String columnName, int interval);