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;@Mapperpublic 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;@Servicepublic 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;@Servicepublic 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@Transactionalpublic 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@Transactionalpublic 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);}
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。
漫过心际的孤独，早已蔚然成冰，而你是这个季节里最美的音符。
