mybatis使用注解进行增删改查

1.创建bean

2.书写dao

@Repository
public interface PurchaseMaterialDao extends TableMapper<PurchaseMaterialBean> {
    @Insert("<script> insert into purchase_material\n" +
            "        <trim prefix=\"(\" suffix=\")\" suffixOverrides=\",\">\n" +
            "            <if test=\"p.purchaseNumber != null\">\n" +
            "                purchase_number,\n" +
            "            </if>\n" +
            "            <if test=\"p.purchaseDesc != null\">\n" +
            "                purchase_desc,\n" +
            "            </if>\n" +
            "            <if test=\"p.purchaseDescLong != null\">\n" +
            "                purchase_desc_long,\n" +
            "            </if>\n" +
            "            <if test=\"p.unitId != null\">\n" +
            "                unit_id,\n" +
            "            </if>\n" +
            "            <if test=\"p.purchaseClassifyNumber != null\">\n" +
            "                purchase_classify_number,\n" +
            "            </if>\n" +
            "            <if test=\"p.frozeStatus != null\">\n" +
            "                froze_status,\n" +
            "            </if>\n" +
            "            <if test=\"p.feature != null\">\n" +
            "                feature,\n" +
            "            </if>\n" +
            "            <if test=\"p.fileList != null\">\n" +
            "                fileList,\n" +
            "            </if>\n" +
            "            <if test=\"p.createdAt != null\">\n" +
            "                created_at,\n" +
            "            </if>\n" +
            "            <if test=\"p.updatedAt != null\">\n" +
            "                updated_at,\n" +
            "            </if>\n" +
            "            <if test=\"p.deletedAt != null\">\n" +
            "                deleted_at,\n" +
            "            </if>\n" +
            "        </trim>\n" +
            "        <trim prefix=\"values (\" suffix=\")\" suffixOverrides=\",\">\n" +
            "            <if test=\"p.purchaseNumber != null\">\n" +
            "                #{p.purchaseNumber,jdbcType=VARCHAR},\n" +
            "            </if>\n" +
            "            <if test=\"p.purchaseDesc != null\">\n" +
            "                #{p.purchaseDesc,jdbcType=VARCHAR},\n" +
            "            </if>\n" +
            "            <if test=\"p.purchaseDescLong != null\">\n" +
            "                #{p.purchaseDescLong,jdbcType=VARCHAR},\n" +
            "            </if>\n" +
            "            <if test=\"p.unitId != null\">\n" +
            "                #{p.unitId,jdbcType=INTEGER},\n" +
            "            </if>\n" +
            "            <if test=\"p.purchaseClassifyNumber != null\">\n" +
            "                #{p.purchaseClassifyNumber,jdbcType=VARCHAR},\n" +
            "            </if>\n" +
            "            <if test=\"p.frozeStatus != null\">\n" +
            "                #{p.frozeStatus,jdbcType=INTEGER},\n" +
            "            </if>\n" +
            "            <if test=\"p.feature != null\">\n" +
            "                #{p.feature,jdbcType=VARCHAR},\n" +
            "            </if>\n" +
            "            <if test=\"p.fileList != null\">\n" +
            "                #{p.fileList,jdbcType=VARCHAR},\n" +
            "            </if>\n" +
            "            <if test=\"p.createdAt != null\">\n" +
            "                #{p.createdAt,jdbcType=BIGINT},\n" +
            "            </if>\n" +
            "            <if test=\"p.updatedAt != null\">\n" +
            "                #{p.updatedAt,jdbcType=BIGINT},\n" +
            "            </if>\n" +
            "            <if test=\"p.deletedAt != null\">\n" +
            "                #{p.deletedAt,jdbcType=INTEGER},\n" +
            "            </if>\n" +
            "        </trim>\n</script>")
    ***@Options(useGeneratedKeys = true, keyProperty = "id")***
    void insertPurchaseMaterial(@Param("p") PurchaseMaterialBean purchaseMaterialBean);

    @Update("<script>update purchase_material\n" +
            "        <set>\n" +
            "            <if test=\"p.purchaseNumber != null\">\n" +
            "                purchase_number = #{p.purchaseNumber,jdbcType=VARCHAR},\n" +
            "            </if>\n" +
            "            <if test=\"p.purchaseDesc != null\">\n" +
            "                purchase_desc = #{p.purchaseDesc,jdbcType=VARCHAR},\n" +
            "            </if>\n" +
            "            <if test=\"p.purchaseDescLong != null\">\n" +
            "                purchase_desc_long = #{p.purchaseDescLong,jdbcType=VARCHAR},\n" +
            "            </if>\n" +
            "            <if test=\"p.unitId != null\">\n" +
            "                unit_id = #{p.unitId,jdbcType=INTEGER},\n" +
            "            </if>\n" +
            "            <if test=\"p.purchaseClassifyNumber != null\">\n" +
            "                purchase_classify_number = #{p.purchaseClassifyNumber,jdbcType=VARCHAR},\n" +
            "            </if>\n" +
            "            <if test=\"p.frozeStatus != null\">\n" +
            "                froze_status = #{p.frozeStatus,jdbcType=INTEGER},\n" +
            "            </if>\n" +
            "            <if test=\"p.feature != null\">\n" +
            "                feature = #{p.feature,jdbcType=VARCHAR},\n" +
            "            </if>\n" +
            "        </set>\n" +
            "        where id = #{p.id,jdbcType=INTEGER}</script>")
    void updatePurchaseMaterials(@Param("p") PurchaseMaterialBean purchaseMaterialBean);

    @Update("update purchase_material set deleted_at=0 where id in (${id})")
    void deletById(@Param("id") String id);

    @Select(" <script>select t.*,p.name from (select * from purchase_material where deleted_at = 1\n" +
            "        <if test=\"m.purchaseNumber != null and m.purchaseNumber!=''\">and purchase_number like #{m.purchaseNumber}</if>\n" +
            "        <if test=\"m.purchaseClassifyNumber != null and m.purchaseClassifyNumber!=''\"> and purchase_classify_number like #{m.purchaseClassifyNumber}</if>\n" +
            "        <if test=\"m.frozeStatus != null\">and froze_status = #{m.frozeStatus}</if>\n" +
            "        <if test=\"m.purchaseDesc != null and m.purchaseDesc!=''\">and purchase_desc like #{m.purchaseDesc}</if>\n" +
            "        order by created_at limit #{begin},#{pageSize}\n" +
            "        ) t left join purchase_classify p on t.purchase_number=p.number</script>")
    List<PurchaseMaterialBean> getList(@Param("m") PurchaseMaterialBean m, @Param("begin") long begin, @Param("pageSize") long pageSize);

    @Select("<script>select count(1) from purchase_material where deleted_at = 1\n" +
            "        <if test=\"m.purchaseNumber != null and m.purchaseNumber!=''\">and purchase_number like #{m.purchaseNumber}</if>\n" +
            "        <if test=\"m.purchaseClassifyNumber != null and m.purchaseClassifyNumber!=''\"> and purchase_classify_number like #{m.purchaseClassifyNumber}</if>\n" +
            "        <if test=\"m.frozeStatus != null\">and froze_status = #{m.frozeStatus}</if>\n" +
            "        <if test=\"m.purchaseDesc != null and m.purchaseDesc!=''\">and purchase_desc like #{m.purchaseDesc}</if></script>")
    long pageCount(@Param("m") PurchaseMaterialBean m);

    @Select("select * from purchase_material where id = #{id}")
    PurchaseMaterialBean getDetail(@Param("id") Integer id);

    @Select("select count(1) from purchase_material where purchase_number = #{purchaseNumber} and deleted_at=1\n")
    long weightCorrection(@Param("purchaseNumber") String purchaseNumber);

    @Select("select count(1) from purchase_material where purchase_number = #{purchaseNumber} and id != #{id} and deleted_at=1\n")
    long weightCorrectionExcludeId(@Param("purchaseNumber") String purchaseNumber, @Param("id") Integer id);
}