MyBatisSystemException异常产生原因及解决方案

淩亂°似流年 2024-02-17 11:46 11阅读 0赞

## MyBatisSystemException异常产生原因及解决方案 ##

### 01 异常发生场景 ###

*  当我使用mybatis对数据库操作时报的错误

<resultMap id="shoppingCartProduct" type="shoppingCartProductVo">
            <id property="shoppingCartId" column="shopping_cart_id"></id>
            <result property="productId" column="product_id"></result>
            <result property="num" column="num"></result>
            <result property="productName" column="product_name"></result>
            <result property="productTitle" column="product_title"></result>
            <result property="productIntro" column="product_intro"></result>
            <result property="productPicture" column="product_picture"></result>
            <result property="productPrice" column="product_price"></result>
            <result property="productSellingPrice" column="product_selling_price"></result>
        </resultMap>
        <select id="selectShoppingCartByIds" resultMap="shoppingCartProduct">
            select
                s.shopping_cart_id,
                s.num,
                s.product_id,
                p.product_name,
                p.product_title,
                p.product_intro,
                p.product_picture,
                p.product_price,
                p.product_selling_price
                FROM
                shopping_cart AS s
                left JOIN
                product AS p
                ON s.product_id = p.product_id
                WHERE
                s.shopping_cart_id 
                in (<foreach collection="list" index="id" separator=",">
                    #{id}
                </foreach>)
        </select>

### 02 尝试解决问题的过程 ###

#### 1.事务问题 ####

*  一开始，我以为是事务问题，于是在service层加上了@Transactional开启事务

@Override
    @Transactional
    public GetData postOrders(List<Long> shoppingCartIds, Long userId) {
        
        //1.判断用户是否存在
        if (msUserMapper.FindUser(userId) == null) {
        
            GetData getData=new GetData(500,"无此账号",null);
            return getData;
        }
        //2.生成订单
        Orders orders=new Orders();
        orders.setOrderNum(UUID.randomUUID().toString());
        orders.setUserId(userId);
        orders.setOrderTime(new Date());
        orderProductMapper.addOrders(orders);
        System.out.println(orders.getOrderId());
        System.out.println(orders.getOrderId().getClass().getTypeName());
        System.out.println(shoppingCartIds);
    
        //3.查询购物车商品数据
        List<ShoppingCartProductVo> shoppingCartProductVos = productMapper.selectShoppingCartByIds(shoppingCartIds);
        System.out.println(shoppingCartProductVos.size());
        System.out.println(shoppingCartProductVos);
        List<OrdersDtl> ordersDtls = new ArrayList<>();
        for (ShoppingCartProductVo vo : shoppingCartProductVos) {
        
            OrdersDtl ordersDtl = new OrdersDtl();
            ordersDtl.setOrderId(orders.getOrderId());
            ordersDtl.setProductId(vo.getProductId());
            ordersDtl.setProductIntro(vo.getProductIntro());
            ordersDtl.setProductName(vo.getProductName());
            ordersDtl.setProductPicture(vo.getProductPicture());
            ordersDtl.setProductPrice(vo.getProductPrice());
            ordersDtl.setProductSellingPrice(vo.getProductSellingPrice());
            ordersDtl.setProductTitle(vo.getProductTitle());
            ordersDtl.setNum(vo.getNum());
            ordersDtls.add(ordersDtl);
        }
        System.out.println(ordersDtls);
        orderProductMapper.addBatchOrderDtlsInt(ordersDtls);
        System.out.println(1);
        int rs =shoppingCartMapper.deleteShoppingCarts(shoppingCartIds);
    
        if (rs>0){
        
            GetData getData=new GetData(200,"操作成功",null);
            return getData;
        }else {
        
            GetData getData=new GetData(500,"操作成功",rs);
            return getData;
        }
    
    }

*  现在想想，真是没抓住重点，看错了代码的报错信息

#### 2.MyBatis一级缓存问题 ####

*  接着排查发现发现查询出的数据与同样的代码在数据库里不一样，人当场傻了
 *  面向百度编程后认为是出现了MyBatis一级缓存问题
 *  于是在yml文件中关闭关闭了MyBatis一级缓存

# 配置mybatis
    mybatis:
      # mapper配置文件
      mapper-locations: classpath:mapper/*.xml
      # resultType别名，没有这个配置resultType包名要写全，配置后只要写类名
      type-aliases-package: com.example.demo.com.mashang.dao
      configuration:
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
        map-underscore-to-camel-case: true
        local-cache-scope: statement # 设置一级缓存关闭，mybatis默认开启

*  当然还是不对，无可奈何之下，我只能回归最初的报错信息，意思大概是文件映射有问题

### 03 问题的产生及其原因 ###

*  没办法，如果真是映射出了错，那就只能一个一个排查过去了
 *  终于在结合了springboot的报错日志
 *  log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

select 
    s.num, s.product_id, p.product_name, p.product_title, p.product_intro, p.product_picture, p.product_price, p.product_selling_price 
    FROM shopping_cart AS s 
    INNER JOIN product AS p 
    ON s.product_id = p.product_id 
    WHERE s.shopping_cart_id 
    in ( ? , ? , ? )

*  终于，我发现mybatis执行的语句中，三个问号插入的值是固定的0,1,2
 *  也就是循环插入出了问题
 *  果然，我错误的使用了标签index

### 04 解决方式 ###

*  只要认真检查mybatis的映射文件，我的话是把标签换成即可，以下是修改后的mybatis映射文件

<resultMap id="shoppingCartProduct" type="shoppingCartProductVo">
        <id property="shoppingCartId" column="shopping_cart_id"></id>
        <result property="productId" column="product_id"></result>
        <result property="num" column="num"></result>
        <result property="productName" column="product_name"></result>
        <result property="productTitle" column="product_title"></result>
        <result property="productIntro" column="product_intro"></result>
        <result property="productPicture" column="product_picture"></result>
        <result property="productPrice" column="product_price"></result>
        <result property="productSellingPrice" column="product_selling_price"></result>
    </resultMap>
    <select id="selectShoppingCartByIds" resultMap="shoppingCartProduct">
        select
            s.shopping_cart_id,
            s.num,
            s.product_id,
            p.product_name,
            p.product_title,
            p.product_intro,
            p.product_picture,
            p.product_price,
            p.product_selling_price
            FROM
            shopping_cart AS s
            left JOIN
            product AS p
            ON s.product_id = p.product_id
            WHERE
            s.shopping_cart_id 
            in (<foreach collection="list" item="id" separator=",">
                #{id}
            </foreach>)
    </select>