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

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

1、开启事务前（以下代码只是样例，实际可能无法运行）

　　OrderMapper.xml配置

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 3 <mapper namespace="cn.test.mapper.OrderMapper" >
 4 
 5 
 6   <!-- 创建商品订单 -->
 7   <insert id="createGoodsOrder" parameterType="cn.test.pojo.dto.GoodsOrderDTO" useGeneratedKeys="true" keyProperty="orderId">
 8     INSERT INTO goods_order (
 9         order_no, shop_id, account_id, total_original_price, total_discount_price, total_price, order_status, order_source, goods_statement_no, create_time
10     ) VALUES (
11         #{orderNo}, #{shopId}, #{accountId}, #{totalOriginalPrice}, #{totalDiscountPrice}, #{totalPrice}, #{orderStatus}, #{orderSource}, #{goodsStatementNo}, #{createTime}
12     )
13   </insert>
14   
15   <!-- 创建商品订单详情 -->
16   <insert id="createGoodsOrderDetail" parameterType="cn.test.pojo.dto.GoodsOrderDetailDTO" >
17     INSERT INTO goods_order_detail (
18         order_id, goods_sku_id, goods_num, original_price, discount_price, price, create_time
19     ) VALUES (
20         #{orderId}, #{skuId}, #{goodsNum}, #{originalPrice}, #{discountPrice}, #{price}, #{createTime}
21     )
22   </insert>
23 </mapper>

　　OrderMapper.java

 1 package cn.test.mapper;
 2 
 3 import cn.test.pojo.dto.GoodsOrderDTO;
 4 import cn.test.pojo.dto.GoodsOrderDetailDTO;
 5 import org.apache.ibatis.annotations.Mapper;
 6 
 7 @Mapper
 8 public interface OrderMapper {
 9     
10     /**
11      * 创建商品订单
12      *
13      * @param goodsOrderDTO
14      * @return
15      */
16     void createGoodsOrder(GoodsOrderDTO goodsOrderDTO);
17 
18     /**
19      * 创建商品订单详情
20      *
21      * @param goodsOrderDetailDTO
22      */
23     void createGoodsOrderDetail(GoodsOrderDetailDTO goodsOrderDetailDTO);
24     
25 }

　　OrderService.java

 1 package cn.test.service;
 2 
 3 import cn.test.pojo.vo.CreateGoodsOrderVO;
 4 import cn.tesst.pojo.bo.CreateOrderBO;
 5 
 6 public interface OrderService {
 7 
 8     /**
 9      * 创建商品订单
10      * @param createOrderBO
11      * @return
12      */
13     CreateGoodsOrderVO createGoodsOrder(CreateOrderBO createOrderBO);
14     
15 }

　　OrderServiceImpl.java

 1 package cn.test.service.impl;
 2 
 3 import cn.test.pojo.vo.CreateGoodsOrderVO;
 4 import cn.test.pojo.bo.CreateOrderBO;
 5 import cn.test.utils.OrdersUtil;
 6 import cn.test.constant.enums.OrderStatusEnums;
 7 
 8 @Service
 9 public class OrderServiceImpl implements OrderService {
10 
11     /**
12      * 创建商品订单
13      * @param createOrderBO
14      * @return
15      */
16     @Override
17     public CreateGoodsOrderVO createGoodsOrder(CreateOrderBO createOrderBO) {
18         // 1 生成订单
19         GoodsOrderDTO goodsOrderDTO = new GoodsOrderDTO();
20         // 1.1 生成订单号
21         String orderNo = orderPrefix + OrdersUtil.createOrderNo();
22         // 1.2 封装订单实体类
23         goodsOrderDTO.setOrderNo(orderNo);
24         goodsOrderDTO.setShopId(shopId);
25         goodsOrderDTO.setAccountId(createOrderBO.getAccountId());
26         // 数据库按分处理
27         goodsOrderDTO.setTotalPrice(totalPrice);
28         goodsOrderDTO.setTotalOriginalPrice(totalOriginalPrice);
29         goodsOrderDTO.setTotalDiscountPrice(totalOriginalPrice - totalPrice);
30         goodsOrderDTO.setOrderSource(createOrderByShopBO.getOrderSource());
31         goodsOrderDTO.setOrderStatus(OrderStatusEnums.CREATE_ORDER_SUCCESS.getCode());
32         goodsOrderDTO.setGoodsStatementNo(statementNo);
33         goodsOrderDTO.setCreateTime(dateString);
34         // 1.3 创建订单
35         orderMapper.createGoodsOrder(goodsOrderDTO);
36         
37         // 2 封装订单详情
38         GoodsOrderDetailDTO goodsOrderDetailDTO = new GoodsOrderDetailDTO();
39         goodsOrderDetailDTO.setOrderId(goodsOrderDTO.getOrderId());　　//不开启事务可以获取到订单自增主键
40         goodsOrderDetailDTO.setSkuId(skuId);
41         goodsOrderDetailDTO.setGoodsNum(buyCounts);
42         goodsOrderDetailDTO.setOriginalPrice(originalPrice);
43         goodsOrderDetailDTO.setDiscountPrice(originalPrice - price);
44         goodsOrderDetailDTO.setPrice(price);
45         goodsOrderDetailDTO.setCreateTime(dateString);
46         // TODO  查询库存、减库存
47         // 2.1创建订单详情
48         orderMapper.createGoodsOrderDetail(goodsOrderDetailDTO);
49         
50         // 3 返回订单实体类
51         CreateGoodsOrderVO createGoodsOrderVO = new CreateGoodsOrderVO();
52         createGoodsOrderVO.setOrderNo(orderNo);
53         createGoodsOrderVO.setTotalPrice(totalPrice);
54         createGoodsOrderVO.setCreateTime(dateString);
55         return createGoodsOrderVO;
56     }
57     
58 }

2、开启事务后（只修改了OrderMapper.xml和OrderServiceImpl.java）

　　OrderMapper.xml

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2  <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 3  <mapper namespace="cn.test.mapper.OrderMapper" >
 4  
 5  
 6    <!-- 创建商品订单 -->
 7    <insert id="createGoodsOrder" parameterType="cn.test.pojo.dto.GoodsOrderDTO" >
 8      <selectKey resultType="java.lang.Integer" keyProperty="orderId">
 9       SELECT LAST_INSERT_ID()
10      </selectKey>
11      INSERT INTO goods_order (
12          id,order_no, shop_id, account_id, total_original_price, total_discount_price, total_price, order_status, order_source, goods_statement_no, create_time
13      ) VALUES (
14          #{orderId},#{orderNo}, #{shopId}, #{accountId}, #{totalOriginalPrice}, #{totalDiscountPrice}, #{totalPrice}, #{orderStatus}, #{orderSource}, #{goodsStatementNo}, #{createTime}
15      )
16    </insert>
17    
18    <!-- 创建商品订单详情 -->
19    <insert id="createGoodsOrderDetail" parameterType="cn.test.pojo.dto.GoodsOrderDetailDTO" >
20      INSERT INTO goods_order_detail (
21          order_id, goods_sku_id, goods_num, original_price, discount_price, price, create_time
22      ) VALUES (
23          #{orderId}, #{skuId}, #{goodsNum}, #{originalPrice}, #{discountPrice}, #{price}, #{createTime}
24      )
25    </insert>
26  </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()。

如果有错误之处请指正！