mybatis之数据统计与自定义异常处理

在使用Mybatis或MybatisPlus进行数据统计，在【 SpringBoot的Mybatis-plus实战之基础知识】中对mybatisplus引入有介绍，本次要使用其进行数据统计。

需求描述

计算各个店铺每日销量的总金额。

定义实体

首先定义order实体，有金额amount，店铺shop_id等字段，如下图所示。

import java.io.Serializable;
import java.time.LocalDateTime;
import java.util.List;

public class Order implements Serializable{

	/**
	 * 序列化
	 */
	private static final long serialVersionUID = 6652550451095312169L;

	/**
	 * 订单号
	 */
	private String orderNo;

	/**
	 * 订单日期
	 */
	private LocalDateTime orderDate;
	
	/**
	 * ֧支付金额
	 */
	private Long payAmount;
	
	/**
	 * 税额
	 */
	private Long rateAmount;

	/**
	 * 订单数量
	 */
	private Long skuNum;
	
	/**
	 * 不含税金额
	 */
	private Long taxtedAmount;
	
	/**
	 * 订单明细
	 */
	private List<OrderDetail> orderDetailList;

	private String orderStartDate;

	private String orderEndDate;

	public Long getTaxtedAmount() {
		return taxtedAmount;
	}

	public void setTaxtedAmount(Long taxtedAmount) {
		this.taxtedAmount = taxtedAmount;
	}

	public List<OrderDetail> getOrderDetailList() {
		return orderDetailList;
	}

	public void setOrderDetailList(List<OrderDetail> orderDetailList) {
		this.orderDetailList = orderDetailList;
	}

	public String getOrderNo() {
		return orderNo;
	}

	public void setOrderNo(String orderNo) {
		this.orderNo = orderNo;
	}

	public Long getPayAmount() {
		return payAmount;
	}

	public void setPayAmount(Long payAmount) {
		this.payAmount = payAmount;
	}

	public Long getRateAmount() {
		return rateAmount;
	}

	public void setRateAmount(Long rateAmount) {
		this.rateAmount = rateAmount;
	}

	public Long getSkuNum() {
		return skuNum;
	}

	public void setSkuNum(Long skuNum) {
		this.skuNum = skuNum;
	}

	public LocalDateTime getOrderDate() {
		return orderDate;
	}

	public void setOrderDate(LocalDateTime orderDate) {
		this.orderDate = orderDate;
	}

	public String getOrderStartDate() {
		return orderStartDate;
	}

	public void setOrderStartDate(String orderStartDate) {
		this.orderStartDate = orderStartDate;
	}

	public String getOrderEndDate() {
		return orderEndDate;
	}

	public void setOrderEndDate(String orderEndDate) {
		this.orderEndDate = orderEndDate;
	}
}

方式一、mybatisPlus实现

使用 QueryWrapper 构建查询条件，并使用 groupBy 方法指定分组字段

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.henu.mapper.OrderMapper;
import com.henu.dao.Order;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
import java.util.Map;
public static void main(String[] args) {

    SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSession();
    try {
        // 获取Mapper接口
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        Order orderQuery = new Order();
        orderQuery.setOrderStartDate("2024-12-06 23:59:59");
        orderQuery.setOrderEndDate("2024-12-07 23:59:59");
        // 创建QueryWrapper
        QueryWrapper<Order> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("ifnull(sum(total_amount),0) AS totalAmount,count(*) AS orderCount,shop_id from tb_order")
                .ge("order_date ",orderQuery.getOrderStartDate())
                .lt("order_date ",orderQuery.getOrderEndDate())
                .groupBy("shop_id");

        // 执行查询
        List<Map<String, Object>> results = mapper.selectMaps(queryWrapper);

        // 处理查询结果
        for (Map<String, Object> result : results) {
            System.out.println(result);
        }
    } finally {
        sqlSession.close();
    }
}

方式二、自定义SQL实现

对于复杂场景，可采用自定义SQL的方式，在 Java的mapper类中，自定义SQL，进行数据统计。

简单查询

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
@Mapper
public interface OrderMapper extends BaseMapper<Order>{
@Select({
      "SELECT ",
          "ifnull(sum(total_amount),0) AS totalAmount, ",
          "count(*) AS orderCount, ",
          "shop_id",
      "FROM ",
          "tb_order",
      "WHERE ",
          "1 = 1",
      "AND order_date &gt;= #{orderStartDate,jdbcType=VARCHAR}",
      "AND order_date &lt;= #{orderEndDate,jdbcType=VARCHAR}",
      "GROUP BY",
          "shop_id"
	})
}

过滤查询

若存在条件判断，则使用 if 标签，Java的mapper文件中 使用script 标签，如下所示。

@Select({
     "<script>",
     "SELECT ",
         "ifnull(sum(total_amount),0) AS totalAmount, ",
         "count(*) AS orderCount, ",
         "shop_id",
     "FROM ",
         "tb_order",
     "<where> ",
         "1 = 1",
     "<if test='shopId != null '>",
         "AND shop_id = #{shopId,jdbcType=VARCHAR}",
     "</if>",
     "AND order_date &gt;= #{orderStartDate,jdbcType=VARCHAR}",
     "AND order_date &lt;= #{orderEndDate,jdbcType=VARCHAR}",
     "</where>",
     "GROUP BY",
         "shop_id",
     "</script>"
})

异常处理

1、SQL拼写异常

错误信息：
Error creating document instance. Cause: org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 286; 元素内容必须由格式正确的字符数据或标记组成
异常原因
mapper对大于、小于号进行转义
处理方案
即将符号进行转义处理，如下所示。

将 大于号 改写为 >
小于号 改写为 <