MyBatis 数据插入

使用 <insert> 和 <selectKey> 标签

在 MyBatis\src\main\resources\mappers\goods.xml 文件 下的 <mapper namespace="goods" >标签下添加下面标签：

    <insert id="insert" parameterType="indi.chester.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})
            <!-- 会在插入数据表以后, 将新的 goods_id 字段赋值回 Goods.goodsId-->
            <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
                select  last_insert_id();
            </selectKey>
    </insert>

这里插入数据时并没有指定主键, 这是因为这个数据表的主键被设置为自增。

在 MyBatis\src\test\java\MyBatisTest.java 添加下面这个方法：

    @Test
    public void testInsert() throws Exception{
        SqlSession sqlSession=null;
        try {
            sqlSession=MyBatisUtils.openSession();
            Goods goods= new Goods();
            goods.setTitle("MyBatis测试商品");
            goods.setSubTitle("MyBatis测试商品子标题");
            goods.setOriginalCost(200f);
            goods.setCurrentPrice(100f);
            goods.setDiscount(0.5f);
            goods.setIsFreeDelivery(1);
            goods.setCategoryId(43);

            //insert 方法返回代表本次成功插入的记录总数
            int num =sqlSession.insert("goods.insert",goods);
            System.out.println(num);
            //提交事务
            sqlSession.commit();
            System.out.println(goods.getGoodsId());
        }catch (Exception e){
            if (sqlSession!=null){
                sqlSession.rollback();
            }
            throw e;
        }finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

输出结果:

09:58:39:517 [main]  DEBUG goods.insert -==>  Preparing: INSERT INTO babytun.t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery,category_id) VALUES (?, ?, ?, ?, ?, ?, ?)  
09:58:39:549 [main]  DEBUG goods.insert -==> Parameters: MyBatis测试商品(String), MyBatis测试商品子标题(String), 200.0(Float), 100.0(Float), 0.5(Float), 1(Integer), 43(Integer) 
09:58:39:559 [main]  DEBUG goods.insert -<==    Updates: 1 
09:58:39:561 [main]  DEBUG goods.insert!selectKey -==>  Preparing: select last_insert_id();  
09:58:39:562 [main]  DEBUG goods.insert!selectKey -==> Parameters:  
09:58:39:579 [main]  DEBUG goods.insert!selectKey -<==      Total: 1 
1
09:58:39:585 [main]  DEBUG o.a.i.t.jdbc.JdbcTransaction -Committing JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@79e2c065 [wrapping: com.mysql.jdbc.JDBC4Connection@3a93b025]] 
901

使用 <insert> 和 useGeneratedKeys 参数替换上面的 <selectKey> 标签

在 MyBatis\src\main\resources\mappers\goods.xml 文件 下的 <mapper namespace="goods" >标签下添加下面标签：

    <insert id="insert2" parameterType="indi.chester.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>

在 MyBatis\src\test\java\MyBatisTest.java 添加下面这个方法：

    @Test
    public void testInsert2() throws Exception{
        SqlSession sqlSession=null;
        try {
            sqlSession=MyBatisUtils.openSession();
            Goods goods= new Goods();
            goods.setTitle("MyBatis测试商品");
            goods.setSubTitle("MyBatis测试商品子标题");
            goods.setOriginalCost(200f);
            goods.setCurrentPrice(100f);
            goods.setDiscount(0.5f);
            goods.setIsFreeDelivery(1);
            goods.setCategoryId(43);

            //insert 方法返回代表本次成功插入的记录总数
            int num =sqlSession.insert("goods.insert2",goods);
            System.out.println(num);
            //提交事务
            sqlSession.commit();
            System.out.println(goods.getGoodsId());
        }catch (Exception e){
            if (sqlSession!=null){
                sqlSession.rollback();
            }
            throw e;
        }finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

输出结果:

10:01:04:042 [main]  DEBUG goods.insert2 -==>  Preparing: INSERT INTO babytun.t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery,category_id) VALUES (?, ?, ?, ?, ?, ?, ?)  
10:01:04:087 [main]  DEBUG goods.insert2 -==> Parameters: MyBatis测试商品(String), MyBatis测试商品子标题(String), 200.0(Float), 100.0(Float), 0.5(Float), 1(Integer), 43(Integer) 
10:01:04:089 [main]  DEBUG goods.insert2 -<==    Updates: 1 
1
10:01:04:099 [main]  DEBUG o.a.i.t.jdbc.JdbcTransaction -Committing JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@79e2c065 [wrapping: com.mysql.jdbc.JDBC4Connection@3a93b025]] 
902

可以看出两种方法的使用并没有任何区别

<selectKey> 标签 和 useGeneratedKeys 参数区别

• selectKey 适用于所有的关系型数据库, 是通用方案，但写起来麻烦

• useGeneratedKeys 支支持"自增主键“的数据库, 使用简单