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

网友投稿 749 2022-12-24

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

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

Spring或SpringBoot开启事务以后无法返回自增主键

场景:保存订单和订单详情,订单详情需要订单id,数据库中的订单表是自增主键,开启事务后,导致订单主键无法返回

1、开启事务前(以下代码只是样例,实际可能无法运行)

OrderMapper.xml配置

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

)

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 {

/**

* 创建商品订单

*

* @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;

@Service

public 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 查询库存、nDWjLKgFVy减库存

// 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

SELECT LAST_INSERT_ID()

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

)

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()。

SpringBoot整合MyBatis获得插入数据后的主键,返回的一直是1(已解决)

MyBatis如何获得自增主键id,此处我不再叙述,网上有很多,这里就记录一下我遇到的问题。

先看一下我获得主键的部分SQL代码:

keyProperty="cardId" useGeneratedKeys="true" >

insert into test

......

我采用keyProperty的方式获取主键,我的实体类上也有对应的属性cardId,而我数据库的主键card_id也是自增的,但是获得的值一直是1?!!!

em…难道是我启动项目的姿势不对?要不我换个妖娆的姿势启动看看?莫非代码也看脸?

几经周转才发现原来是因为我service层获得主键id的方式不对,下面是我错误的写法:

@Override

@Transactional

public 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

@Transactional

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

}

keyProperty="cardId" useGeneratedKeys="true" >

insert into test

......

我采用keyProperty的方式获取主键,我的实体类上也有对应的属性cardId,而我数据库的主键card_id也是自增的,但是获得的值一直是1?!!!

em…难道是我启动项目的姿势不对?要不我换个妖娆的姿势启动看看?莫非代码也看脸?

几经周转才发现原来是因为我service层获得主键id的方式不对,下面是我错误的写法:

@Override

@Transactional

public 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

@Transactional

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

}

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:智能车载终端研究背景(智能小车的研究背景)
下一篇:智能车载终端研究报告(智能汽车调研报告)
相关文章

 发表评论

暂时没有评论,来抢沙发吧~