Mybatis注解开发 @Select @Insert @Update @Delete

Mybatis注解开发 @Select @Insert @Update @Delete

@Select用法

如果未使用mybatis的动态标签，则不需要加<script></script>标签
注意若数据库为在oracle，使用容易造成字段类型不匹配的问题

@Mapper
public interface RtcCheckParamBussDao {
    @Select(value = "<script>"+
            "select count(*) from r_verify_parm where code_type = 'TLT' " +
                    "<if test = 'branchCode != \"ALL\"' > and branch = #{branchCode,jdbcType=VARCHAR} </if>" +
                    "<if test = 'subBranchCode != \"ALL\"' > and sub_branch = #{subBranchCode,jdbcType=VARCHAR}</if>" +
                    "<if test = 'userId != null and userId != \"\"' > and verify_code = #{userId,jdbcType=VARCHAR} </if>"
    +"</script>")
    int checkUserBranch(@Param(value = "branchCode" ) String branchCode, @Param(value = "subBranchCode" )String subBranchCode,@Param(value = "userId" ) String userId);

    @Select(value = "<script>"+
            "select count(*) from r_verify_parm where code_type = 'LN' " +
            "<if test = 'account_no != \"\" and account_no != null' > and level_1 = #{account_no,jdbcType=VARCHAR} </if>" +
            "<if test = 'contract_no != \"\" and contract_no != null' > and level_2 = #{contract_no,jdbcType=VARCHAR}</if>"
            +"</script>")
    int checkAccountContract(@Param(value = "account_no") String account_no,@Param(value = "contract_no") String contract_no);

    @Select(value = "<script>"+
            "select count(*) from r_verify_parm where code_type = 'FIX' " +
            "<if test = 'branchCode != \"ALL\"' > and branch = #{branchCode,jdbcType=VARCHAR} </if>" +
            "<if test = 'subBranchCode != \"ALL\"' > and sub_branch = #{subBranchCode,jdbcType=VARCHAR}</if>" +
            "<if test = 'product != \"ALL\"'  > and level_1 = #{product,jdbcType=VARCHAR} </if>" +
            "<if test = 'subProduct != \"ALL\"'  > and level_2 = #{subProduct,jdbcType=VARCHAR} </if>"
            +"</script>")
    int checkProductSubProduct(@Param(value = "branchCode")String branchCode, @Param(value = "subBranchCode")String subBranchCode, @Param(value = "product")String product, @Param(value = "subProduct")String subProduct);

    @Select(value = "<script>"+
            "select count(*) from r_verify_parm where code_type = 'LIB' " +
            "<if test = 'branchCode != \"ALL\"' > and branch = #{branchCode,jdbcType=VARCHAR} </if>" +
            "<if test = 'subBranchCode != \"ALL\"' > and sub_branch = #{subBranchCode,jdbcType=VARCHAR}</if>" +
            "<if test = 'department != null and department != \"\" and department != \"ALL\"'  > and level_1 = #{department,jdbcType=VARCHAR} </if>" +
            "<if test = 'tillCode != null and tillCode != \"\" and tillCode != \"ALL\"'  > and level_2 = #{tillCode,jdbcType=VARCHAR} </if>" +
            "<if test = 'currency != null and currency != \"\" and currency != \"ALL\"'  > and level_3 = #{currency,jdbcType=VARCHAR} </if>"
            +"</script>")
    int checkTillDepartmentCurrency(@Param(value = "branchCode")String branchCode, @Param(value = "subBranchCode")String subBranchCode, @Param(value = "department")String department, @Param(value = "tillCode")String tillCode,@Param(value = "currency") String currency);

    @Select(value = "<script>"+
            "select F_VERIFY_PARM_LN01(#{parm01,jdbcType=VARCHAR},#{parm02,jdbcType=VARCHAR})  as result "
            +"</script>")
    int checkAccountNoContractNo(@Param(value = "parm01") String parm01, @Param(value = "parm02") String parm02);

    @Select(value = "<script>"+
            "select F_PAGE_VERIFY(#{callType,jdbcType=VARCHAR},#{reportCode,jdbcType=VARCHAR},#{branchCode,jdbcType=VARCHAR},#{subBranchCode,jdbcType=VARCHAR}," +
            "#{userId,jdbcType=VARCHAR},#{parm01,jdbcType=VARCHAR},#{parm02,jdbcType=VARCHAR}," +
            "#{parm03,jdbcType=VARCHAR},#{parm04,jdbcType=VARCHAR},#{parm05,jdbcType=VARCHAR}," +
            "#{parm06,jdbcType=VARCHAR},#{parm07,jdbcType=VARCHAR},#{parm08,jdbcType=VARCHAR}," +
            "#{parm09,jdbcType=VARCHAR},#{parm10,jdbcType=VARCHAR})  as result "
            +"</script>")
    String checkPageVerify(Map<String, String> map);
}

@Update使用

  @Update({ "update sys_role set role_name = #{roleName},enabled = #{enabled},create_by = #{createBy},create_time = #{createTime, jdbcType=TIMESTAMP} where id = #{id}" })
    int updateSysRoleById(SysRole sysRole);

@Insert使用

// 批量插入数据
@Insert("<script>" +
"insert into index_kline (currency_id, currency, zone, period, high, open, close, low, vol, date_time) " +
"values " +
"<foreach collection='Lines' item='Line' index='index' separator=','>" +
"(#{Line.currencyId}, #{Line.currency}, #{Line.zone}, #{Line.period})" +
"</foreach>" +
"</script>")
void addBatch(@Param("Lines") List<IndexKLine> Lines);

@Delete使用

@Delete("delete from sys_role where id = #{id}")
    int deleteSysRoleById(Long id);