解決Spring或SpringBoot開啟事務(wù)以后無法返回自增主鍵的問題
Spring或SpringBoot開啟事務(wù)以后無法返回自增主鍵
場(chǎng)景:保存訂單和訂單詳情,訂單詳情需要訂單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 生成訂單號(hào) 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 生成訂單號(hào) 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í)體類上也有對(duì)應(yīng)的屬性cardId,而我數(shù)據(jù)庫的主鍵card_id也是自增的,但是獲得的值一直是1??。?!
em…難道是我啟動(dòng)項(xiàng)目的姿勢(shì)不對(duì)?要不我換個(gè)妖嬈的姿勢(shì)啟動(dòng)看看?莫非代碼也看臉?
幾經(jīng)周轉(zhuǎn)才發(fā)現(xiàn)原來是因?yàn)槲襰ervice層獲得主鍵id的方式不對(duì),下面是我錯(cuò)誤的寫法:
@Override @Transactional public int save(CardDo cardDo,Long personId) { PersonDo personDo=new PersonDo(); //看見了嗎,就是這句代碼,這是錯(cuò)誤的獲取id的方式!錯(cuò)誤的!錯(cuò)誤的! 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ù)的主鍵后是存儲(chǔ)在我們的實(shí)體類中的,至于存儲(chǔ)在實(shí)體類的哪一個(gè)屬性中,那就要看keyProperty設(shè)置的值是什么了。
所以獲得主鍵直接用這個(gè)實(shí)體類對(duì)應(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); }
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
相關(guān)文章
SpringBoot實(shí)現(xiàn)mysql與clickhouse多數(shù)據(jù)源的項(xiàng)目實(shí)踐
本文主要介紹了SpringBoot實(shí)現(xiàn)mysql與clickhouse多數(shù)據(jù)源的項(xiàng)目實(shí)踐,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-11-11Jetty啟動(dòng)項(xiàng)目中引用json-lib相關(guān)類庫報(bào)錯(cuò)ClassNotFound的解決方案
今天小編就為大家分享一篇關(guān)于Jetty啟動(dòng)項(xiàng)目中引用json-lib相關(guān)類庫報(bào)錯(cuò)ClassNotFound的解決方案,小編覺得內(nèi)容挺不錯(cuò)的,現(xiàn)在分享給大家,具有很好的參考價(jià)值,需要的朋友一起跟隨小編來看看吧2018-12-12詳解SpringBoot 添加對(duì)JSP的支持(附常見坑點(diǎn))
這篇文章主要介紹了詳解SpringBoot 添加對(duì)JSP的支持(附常見坑點(diǎn)),非常具有實(shí)用價(jià)值,需要的朋友可以參考下2017-10-10淺談java反射和自定義注解的綜合應(yīng)用實(shí)例
本篇文章主要介紹了java反射和自定義注解的綜合應(yīng)用,小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-09-09使用Java代碼進(jìn)行因數(shù)分解和求最小公倍數(shù)的示例
這篇文章主要介紹了使用Java代碼進(jìn)行因數(shù)分解和求最小公倍數(shù)的示例,都是基于最基礎(chǔ)的算法原理實(shí)現(xiàn),需要的朋友可以參考下2015-11-11面試必問項(xiàng)之Set實(shí)現(xiàn)類:TreeSet
這篇文章主要介紹了Java TreeSet類的簡(jiǎn)單理解和使用,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2021-07-07