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);
}
以上为个人经验，希望能给大家一个参考，也希望大家多多支持www.887551.com。
黄山市民网：https://www.huangshanshimin.com/
