Spring或SpringBoot開啟事務以后無法返回自增主鍵
場景：保存訂單和訂單詳情，訂單詳情需要訂單id，數據庫中的訂單表是自增主鍵，開啟事務后，導致訂單主鍵無法返回
1、開啟事務前（以下代碼只是樣例，實際可能無法運行）
OrderMapper.xml配置
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
<?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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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
|
1
2
3
4
5
6
7
8
9
10
11
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
<?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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
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代碼：
|
1
2
3
4
5
6
7
|
<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的方式不對，下面是我錯誤的寫法：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
@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方法獲得就好了。
如下貼出我正確的代碼：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
@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);} |
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持服務器之家。
原文鏈接：https://www.cnblogs.com/guduershi/p/14808716.html
