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");// 创建QueryWrapperQueryWrapper<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对大于、小于号进行转义
处理方案
即将符号进行转义处理，如下所示。

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