欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

解决Spring或SpringBoot开启事务以后无法返回自增主键的问题

程序员文章站 2022-07-30 12:22:49
spring或springboot开启事务以后无法返回自增主键场景:保存订单和订单详情,订单详情需要订单id,数据库中的订单表是自增主键,开启事务后,导致订单主键无法返回1、开启事务前(以下代码只是样...

spring或springboot开启事务以后无法返回自增主键

场景:保存订单和订单详情,订单详情需要订单id,数据库中的订单表是自增主键,开启事务后,导致订单主键无法返回

1、开启事务前(以下代码只是样例,实际可能无法运行)

ordermapper.xml配置

<?xml version="1.0" encoding="utf-8" ?>
<!doctype mapper public "-//mybatis.org//dtd mapper 3.0//en" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.test.mapper.ordermapper" >
  <!-- 创建商品订单 -->
  <insert id="creategoodsorder" parametertype="cn.test.pojo.dto.goodsorderdto" usegeneratedkeys="true" keyproperty="orderid">
    insert into goods_order (
        order_no, shop_id, account_id, total_original_price, total_discount_price, total_price, order_status, order_source, goods_statement_no, create_time
    ) values (
        #{orderno}, #{shopid}, #{accountid}, #{totaloriginalprice}, #{totaldiscountprice}, #{totalprice}, #{orderstatus}, #{ordersource}, #{goodsstatementno}, #{createtime}
    )
  </insert>
  <!-- 创建商品订单详情 -->
  <insert id="creategoodsorderdetail" parametertype="cn.test.pojo.dto.goodsorderdetaildto" >
    insert into goods_order_detail (
        order_id, goods_sku_id, goods_num, original_price, discount_price, price, create_time
    ) values (
        #{orderid}, #{skuid}, #{goodsnum}, #{originalprice}, #{discountprice}, #{price}, #{createtime}
    )
  </insert>
</mapper>

ordermapper.java

package cn.test.mapper;
import cn.test.pojo.dto.goodsorderdto;
import cn.test.pojo.dto.goodsorderdetaildto;
import org.apache.ibatis.annotations.mapper;
@mapper
public interface ordermapper {
    /**
     * 创建商品订单
     *
     * @param goodsorderdto
     * @return
     */
    void creategoodsorder(goodsorderdto goodsorderdto);
    /**
     * 创建商品订单详情
     *
     * @param goodsorderdetaildto
     */
    void creategoodsorderdetail(goodsorderdetaildto goodsorderdetaildto);
}

orderservice.java

package cn.test.service;
import cn.test.pojo.vo.creategoodsordervo;
import cn.tesst.pojo.bo.createorderbo;
public interface orderservice {
    /**
     * 创建商品订单
    * @param createorderbo
    * @return
    */
   creategoodsordervo creategoodsorder(createorderbo createorderbo);  
}

orderserviceimpl.java

package cn.test.service.impl;
import cn.test.pojo.vo.creategoodsordervo;
import cn.test.pojo.bo.createorderbo;
import cn.test.utils.ordersutil;
import cn.test.constant.enums.orderstatusenums;
@service
public class orderserviceimpl implements orderservice {
   /**
    * 创建商品订单
    * @param createorderbo
    * @return
    */
   @override
   public creategoodsordervo creategoodsorder(createorderbo createorderbo) {
       // 1 生成订单
       goodsorderdto goodsorderdto = new goodsorderdto();
       // 1.1 生成订单号
       string orderno = orderprefix + ordersutil.createorderno();
       // 1.2 封装订单实体类
       goodsorderdto.setorderno(orderno);
       goodsorderdto.setshopid(shopid);
       goodsorderdto.setaccountid(createorderbo.getaccountid());
       // 数据库按分处理
       goodsorderdto.settotalprice(totalprice);
       goodsorderdto.settotaloriginalprice(totaloriginalprice);
       goodsorderdto.settotaldiscountprice(totaloriginalprice - totalprice);
       goodsorderdto.setordersource(createorderbyshopbo.getordersource());
       goodsorderdto.setorderstatus(orderstatusenums.create_order_success.getcode());
       goodsorderdto.setgoodsstatementno(statementno);
       goodsorderdto.setcreatetime(datestring);
       // 1.3 创建订单
       ordermapper.creategoodsorder(goodsorderdto);
       
       // 2 封装订单详情
       goodsorderdetaildto goodsorderdetaildto = new goodsorderdetaildto();
       goodsorderdetaildto.setorderid(goodsorderdto.getorderid());  //不开启事务可以获取到订单自增主键
       goodsorderdetaildto.setskuid(skuid);
       goodsorderdetaildto.setgoodsnum(buycounts);
       goodsorderdetaildto.setoriginalprice(originalprice);
       goodsorderdetaildto.setdiscountprice(originalprice - price);
       goodsorderdetaildto.setprice(price);
       goodsorderdetaildto.setcreatetime(datestring);
       // todo  查询库存、减库存
       // 2.1创建订单详情
       ordermapper.creategoodsorderdetail(goodsorderdetaildto);
       
       // 3 返回订单实体类
       creategoodsordervo creategoodsordervo = new creategoodsordervo();
       creategoodsordervo.setorderno(orderno);
       creategoodsordervo.settotalprice(totalprice);
       creategoodsordervo.setcreatetime(datestring);
       return creategoodsordervo;
   }
}

2、开启事务后(只修改了ordermapper.xml和orderserviceimpl.java)

ordermapper.xml

<?xml version="1.0" encoding="utf-8" ?>
 <!doctype mapper public "-//mybatis.org//dtd mapper 3.0//en" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 <mapper namespace="cn.test.mapper.ordermapper" > 
 
   <!-- 创建商品订单 -->
   <insert id="creategoodsorder" parametertype="cn.test.pojo.dto.goodsorderdto" >
     <selectkey resulttype="java.lang.integer" keyproperty="orderid">
      select last_insert_id()
    </selectkey>
    insert into goods_order (
        id,order_no, shop_id, account_id, total_original_price, total_discount_price, total_price, order_status, order_source, goods_statement_no, create_time
    ) values (
        #{orderid},#{orderno}, #{shopid}, #{accountid}, #{totaloriginalprice}, #{totaldiscountprice}, #{totalprice}, #{orderstatus}, #{ordersource}, #{goodsstatementno}, #{createtime}
    )
  </insert>
  
  <!-- 创建商品订单详情 -->
  <insert id="creategoodsorderdetail" parametertype="cn.test.pojo.dto.goodsorderdetaildto" >
    insert into goods_order_detail (
        order_id, goods_sku_id, goods_num, original_price, discount_price, price, create_time
    ) values (
        #{orderid}, #{skuid}, #{goodsnum}, #{originalprice}, #{discountprice}, #{price}, #{createtime}
    )
  </insert>
</mapper>

orderserviceimpl.java

package cn.test.service.impl;
import cn.test.pojo.vo.creategoodsordervo;
import cn.test.pojo.bo.createorderbo;
import cn.test.utils.ordersutil;
import cn.test.constant.enums.orderstatusenums;
@service
public class orderserviceimpl implements orderservice {
    /**
     * 创建商品订单
     * @param createorderbo
     * @return
     */
    @transactional(propagation = propagation.required, rollbackfor = exception.class)
    @override
    public creategoodsordervo creategoodsorder(createorderbo createorderbo) {
        // 1 生成订单
        goodsorderdto goodsorderdto = new goodsorderdto();
        // 1.1 生成订单号
        string orderno = orderprefix + ordersutil.createorderno();
        // 1.2 封装订单实体类
        goodsorderdto.setorderno(orderno);
        goodsorderdto.setshopid(shopid);
        goodsorderdto.setaccountid(createorderbo.getaccountid());
        // 数据库按分处理
        goodsorderdto.settotalprice(totalprice);
        goodsorderdto.settotaloriginalprice(totaloriginalprice);
        goodsorderdto.settotaldiscountprice(totaloriginalprice - totalprice);
        goodsorderdto.setordersource(createorderbyshopbo.getordersource());
        goodsorderdto.setorderstatus(orderstatusenums.create_order_success.getcode());
        goodsorderdto.setgoodsstatementno(statementno);
        goodsorderdto.setcreatetime(datestring);
        // 1.3 创建订单
        ordermapper.creategoodsorder(goodsorderdto);
        
        // 2 封装订单详情
        goodsorderdetaildto goodsorderdetaildto = new goodsorderdetaildto();
        goodsorderdetaildto.setorderid(goodsorderdto.getorderid());
        goodsorderdetaildto.setskuid(skuid);
        goodsorderdetaildto.setgoodsnum(buycounts);
        goodsorderdetaildto.setoriginalprice(originalprice);
        goodsorderdetaildto.setdiscountprice(originalprice - price);
        goodsorderdetaildto.setprice(price);
        goodsorderdetaildto.setcreatetime(datestring);
        // todo  查询库存、减库存
        // 2.1创建订单详情
        ordermapper.creategoodsorderdetail(goodsorderdetaildto);
        
        // 3 返回订单实体类
        creategoodsordervo creategoodsordervo = new creategoodsordervo();
        creategoodsordervo.setorderno(orderno);
        creategoodsordervo.settotalprice(totalprice);
        creategoodsordervo.setcreatetime(datestring);
        return creategoodsordervo;
    }    
}

orderserviceimpl.java中添加事务后,需要在ordermapper.xml使用 select last_insert_id()。

springboot整合mybatis获得插入数据后的主键,返回的一直是1(已解决)

mybatis如何获得自增主键id,此处我不再叙述,网上有很多,这里就记录一下我遇到的问题。

先看一下我获得主键的部分sql代码:

<insert id="save" parametertype="com.test.domain.carddo" 
  
  keyproperty="cardid" usegeneratedkeys="true" >
         
         insert into test
         <trim prefix="(" suffix=")" suffixoverrides=",">
         ......

我采用keyproperty的方式获取主键,我的实体类上也有对应的属性cardid,而我数据库的主键card_id也是自增的,但是获得的值一直是1?!!!

em…难道是我启动项目的姿势不对?要不我换个妖娆的姿势启动看看?莫非代码也看脸?

几经周转才发现原来是因为我service层获得主键id的方式不对,下面是我错误的写法:

@override
@transactional
public int save(carddo carddo,long personid) {
        persondo persondo=new persondo();
        
        //看见了吗,就是这句代码,这是错误的获取id的方式!错误的!错误的!
        long cardid=carddao.save(carddo);
        
        persondo.setcardid(cardid);
        persondo.setpersonid(personid);
        return persondao.update(persondo);
}

关键就在上面那句有注释的代码,save方法返回的是影响行数而不是主键id,实际上,在我们向数据库插入数据,获得此条数据的主键后是存储在我们的实体类中的,至于存储在实体类的哪一个属性中,那就要看keyproperty设置的值是什么了。

所以获得主键直接用这个实体类对应的属性的get方法获得就好了。

如下贴出我正确的代码:

@override
@transactional
public int save(carddo carddo,long personid) {
        persondo persondo=new persondo();
        //插入数据
        carddao.save(carddo);
        
        //获取主键
        long cardid=carddo.getcardid()
        
        persondo .setcardid(cardid);
        persondo .setpersonid(personid);
        return persondao.update(persondo);
}

以上为个人经验,希望能给大家一个参考,也希望大家多多支持。