Java框架MyBatis学习历程（4）——数据插入、更新和删除操作以及预防SQL注入攻击

1、MyBatis数据插入操作

goods.xml:

    <insert id="insert" parameterType="com.imooc.mybatis.entity.Goods">
        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 resultType="Integer" keyProperty="goodsId" order="AFTER">
            select last_insert_id()
        </selectKey>
    </insert>

</mapper>

Java:

    @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();//回滚事务
            }
            e.printStackTrace();
        } finally {
        }

    }

2、selectKey与useGeneratedKeys的区别

3、更新与删除操作

goods.xml:

    <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 = #{isFreeDelivert},
          category_id = #{categoryId}
        where
          goods_id = #{goodsId}
    </update>

    <delete id="delete" parameterType="Integer">
        delete from t_goods where goods_id = #{value}
    </delete>

测试代码：

    @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();//回滚事务
            }
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }
    @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();//回滚事务
            }
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

4、预防SQL注入攻击

goods.xml:

    <select id="selectByTitle" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods where title = #{title}
        ${order}
    </select>

测试代码：

    @Test
    public void testSelectByTitle() throws Exception {
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            Map param = new HashMap();
            // ${} 原文传值
            // select * from t_goods
            //         where title = '' or 1=1 or title = 'hahaha'
            // #{} 预编译
            // select * from t_goods
            //        where title = "'' or 1=1 or title = 'hahaha'"

            param.put("title","''or 1=1 or title = '【德国】Aptamil爱他美奶粉 1段 0-6月 800g'");
            param.put("order","order by title desc");
            List<Goods> list = session.selectList("goods.selectByTitle", param);
            for (Goods g : list) {
                System.out.println(g.getTitle()+":"+g.getCurrentPrice());
            }
        } catch (Exception e) {
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

5、MyBatis工作流程