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

这篇文章主要介绍了解决Spring或SpringBoot开启事务以后无法返回自增主键的问题,具有很好的参考价值,希望对大家有所帮助。如有错误或未考虑完全的地方,望不吝赐教

解决Spring或SpringBoot开启事务以后无法返回自增主键的问题,久久派带你了解更多相关信息。

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);}

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

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,请发送邮件至 203304862@qq.com 举报,一经查实,本站将立刻删除。转转请注明出处:https://www.szhjjp.com/n/7866.html

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注