解決Spring或SpringBoot開啟事務(wù)以后無法返回自增主鍵的問題

更新時間：2021年07月28日 17:08:54 作者：孤獨(dú)二世

這篇文章主要介紹了解決Spring或SpringBoot開啟事務(wù)以后無法返回自增主鍵的問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

Spring或SpringBoot開啟事務(wù)以后無法返回自增主鍵

場景：保存訂單和訂單詳情，訂單詳情需要訂單id，數(shù)據(jù)庫中的訂單表是自增主鍵，開啟事務(wù)后，導(dǎo)致訂單主鍵無法返回

1、開啟事務(wù)前（以下代碼只是樣例，實(shí)際可能無法運(yùn)行）

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" >
  <!-- 創(chuàng)建商品訂單 -->
  <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>
  <!-- 創(chuàng)建商品訂單詳情 -->
  <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 {
    /**
     * 創(chuàng)建商品訂單
     *
     * @param goodsOrderDTO
     * @return
     */
    void createGoodsOrder(GoodsOrderDTO goodsOrderDTO);
    /**
     * 創(chuàng)建商品訂單詳情
     *
     * @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 {
    /**
     * 創(chuàng)建商品訂單
    * @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 {
   /**
    * 創(chuàng)建商品訂單
    * @param createOrderBO
    * @return
    */
   @Override
   public CreateGoodsOrderVO createGoodsOrder(CreateOrderBO createOrderBO) {
       // 1 生成訂單
       GoodsOrderDTO goodsOrderDTO = new GoodsOrderDTO();
       // 1.1 生成訂單號
       String orderNo = orderPrefix + OrdersUtil.createOrderNo();
       // 1.2 封裝訂單實(shí)體類
       goodsOrderDTO.setOrderNo(orderNo);
       goodsOrderDTO.setShopId(shopId);
       goodsOrderDTO.setAccountId(createOrderBO.getAccountId());
       // 數(shù)據(jù)庫按分處理
       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 創(chuàng)建訂單
       orderMapper.createGoodsOrder(goodsOrderDTO);
       
       // 2 封裝訂單詳情
       GoodsOrderDetailDTO goodsOrderDetailDTO = new GoodsOrderDetailDTO();
       goodsOrderDetailDTO.setOrderId(goodsOrderDTO.getOrderId());　　//不開啟事務(wù)可以獲取到訂單自增主鍵
       goodsOrderDetailDTO.setSkuId(skuId);
       goodsOrderDetailDTO.setGoodsNum(buyCounts);
       goodsOrderDetailDTO.setOriginalPrice(originalPrice);
       goodsOrderDetailDTO.setDiscountPrice(originalPrice - price);
       goodsOrderDetailDTO.setPrice(price);
       goodsOrderDetailDTO.setCreateTime(dateString);
       // TODO  查詢庫存、減庫存
       // 2.1創(chuàng)建訂單詳情
       orderMapper.createGoodsOrderDetail(goodsOrderDetailDTO);
       
       // 3 返回訂單實(shí)體類
       CreateGoodsOrderVO createGoodsOrderVO = new CreateGoodsOrderVO();
       createGoodsOrderVO.setOrderNo(orderNo);
       createGoodsOrderVO.setTotalPrice(totalPrice);
       createGoodsOrderVO.setCreateTime(dateString);
       return createGoodsOrderVO;
   }
}

2、開啟事務(wù)后（只修改了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" > 
 
   <!-- 創(chuàng)建商品訂單 -->
   <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>
  
  <!-- 創(chuàng)建商品訂單詳情 -->
  <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 {
    /**
     * 創(chuàng)建商品訂單
     * @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 封裝訂單實(shí)體類
        goodsOrderDTO.setOrderNo(orderNo);
        goodsOrderDTO.setShopId(shopId);
        goodsOrderDTO.setAccountId(createOrderBO.getAccountId());
        // 數(shù)據(jù)庫按分處理
        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 創(chuàng)建訂單
        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創(chuàng)建訂單詳情
        orderMapper.createGoodsOrderDetail(goodsOrderDetailDTO);
        
        // 3 返回訂單實(shí)體類
        CreateGoodsOrderVO createGoodsOrderVO = new CreateGoodsOrderVO();
        createGoodsOrderVO.setOrderNo(orderNo);
        createGoodsOrderVO.setTotalPrice(totalPrice);
        createGoodsOrderVO.setCreateTime(dateString);
        return createGoodsOrderVO;
    }    
}

OrderServiceImpl.java中添加事務(wù)后，需要在OrderMapper.xml使用 SELECT LAST_INSERT_ID()。

SpringBoot整合MyBatis獲得插入數(shù)據(jù)后的主鍵，返回的一直是1(已解決)

MyBatis如何獲得自增主鍵id，此處我不再敘述，網(wǎng)上有很多，這里就記錄一下我遇到的問題。

先看一下我獲得主鍵的部分SQL代碼：

<insert id="save" parameterType="com.test.domain.CardDo" 
  
  keyProperty="cardId" useGeneratedKeys="true" >
         
         insert into test
         <trim prefix="(" suffix=")" suffixOverrides=",">
         ......

我采用keyProperty的方式獲取主鍵，我的實(shí)體類上也有對應(yīng)的屬性cardId，而我數(shù)據(jù)庫的主鍵card_id也是自增的，但是獲得的值一直是1？?。?！

em…難道是我啟動項(xiàng)目的姿勢不對？要不我換個妖嬈的姿勢啟動看看？莫非代碼也看臉？

幾經(jīng)周轉(zhuǎn)才發(fā)現(xiàn)原來是因?yàn)槲襰ervice層獲得主鍵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);
}

關(guān)鍵就在上面那句有注釋的代碼，save方法返回的是影響行數(shù)而不是主鍵id，實(shí)際上，在我們向數(shù)據(jù)庫插入數(shù)據(jù)，獲得此條數(shù)據(jù)的主鍵后是存儲在我們的實(shí)體類中的，至于存儲在實(shí)體類的哪一個屬性中，那就要看keyProperty設(shè)置的值是什么了。

所以獲得主鍵直接用這個實(shí)體類對應(yīng)的屬性的get方法獲得就好了。

如下貼出我正確的代碼：

@Override
@Transactional
public int save(CardDo cardDo,Long personId) {
        PersonDo personDo=new PersonDo();
        //插入數(shù)據(jù)
        cardDao.save(cardDo);
        
        //獲取主鍵
        Long cardId=cardDo.getCardId()
        
        PersonDo .setCardId(cardId);
        PersonDo .setPersonId(personId);
        return personDao.update(PersonDo);
}

以上為個人經(jīng)驗(yàn)，希望能給大家一個參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: