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()。
如果有错误之处请指正！
