基于Maven工程下的MyBatis基本使用之数据插入【回填】、修改与删除

MyBatis基本使用

声明：基于《基于Maven工程下的MyBatis框架+MySQL+连接池的数据查询操作》与《基于Maven工程下的MyBatis基本使用之SQL传单/多参、多表关联查询》进一步拓展，相关配置文件、数据文件可阅以上两篇。

数据插入,使用进行回填自动生成主键值

<!--需要明确编写获取最新主键的SQL语句-->
<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods" flushCache="true">
    INSERT INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id)
    VALUES (#{title} , #{subTitle} , #{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})
        <!--selectKey用来回填自动生成的主键属性，last_insert_id()函数用于获取当前连接最后产生的主键ID-->
        <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
            select last_insert_id()
        </selectKey>
    </insert>

@Test
    public void testInsert() throws Exception {
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            Goods goods = new Goods();
            goods.setTitle("测试插入商品");
            goods.setSubTitle("测试子标题");
            goods.setOriginalCost(200f);
            goods.setCurrentPrice(100f);
            goods.setDiscount(0.5f);
            goods.setIsFreeDelivery(1);
            goods.setCategoryId(43);
            //insert()方法返回值代表本次成功插入的记录总数
            int num = session.insert("goods.insert", goods);
            session.commit();//提交事务数据
            System.out.println(goods.getGoodsId());
        }catch (Exception e){
            if(session != null){
                session.rollback();//回滚事务
            }
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }

数据插入,使用useGeneratedKeys属性进行回填自动生成主键值

这里只需要修改元素：

<!--根据驱动生成对应SQL语句-->
 <insert id="insertII" parameterType="com.imooc.mybatis.entity.Goods"
            useGeneratedKeys="true" keyProperty="goodsId" keyColumn="goods_id">
        INSERT INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id)
        VALUES (#{title} , #{subTitle} , #{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})
    </insert>

数据更新

<update id="update" parameterType="com.imooc.mybatis.entity.Goods">
        UPDATE t_goods SET title = #{title} ,sub_title = #{subTitle} ,
               original_cost = #{originalCost} ,current_price = #{currentPrice} ,
               discount = #{discount} ,is_free_delivery = #{isFreeDelivery} ,
               category_id = #{categoryId}
        WHERE  goods_id = #{goodsId}
    </update>

@Test
    public void testUpdate() throws Exception {
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            Goods goods = session.selectOne("goods.selectById", 739);
            goods.setTitle("更新测试商品");
            int num = session.update("goods.update" , goods);
            session.commit();//提交事务数据
        }catch (Exception e){
            if(session != null){
                session.rollback();//回滚事务
            }
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }

数据删除

<!--根据主键删除-->
<delete id="delete" parameterType="Integer">
        delete from t_goods where goods_id = #{value} 
</delete>

@Test
    public void testDelete() throws Exception {
        SqlSession session = null;
        try{
            session = MyBatisUtils.openSession();
            int num = session.delete("goods.delete" , 739);
            session.commit();//提交事务数据
        }catch (Exception e){
            if(session != null){
                session.rollback();//回滚事务
            }
            throw e;
        }finally {
            MyBatisUtils.closeSession(session);
        }
    }