解决Spring或SpringBoot开启事务以后无法返回自增主键的问题
程序员文章站
2022-07-30 12:22:49
spring或springboot开启事务以后无法返回自增主键场景:保存订单和订单详情,订单详情需要订单id,数据库中的订单表是自增主键,开启事务后,导致订单主键无法返回1、开启事务前(以下代码只是样...
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; @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 查询库存、减库存 // 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; @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代码:
<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 @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); }
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。