手机网站模板安装方法,电商网站开发环境,群晖系统可以做网站吗,王野天 葛优文章目录 需求描述定义实体方式一、mybatisPlus实现方式二、自定义SQL实现简单查询过滤查询 异常处理1、SQL拼写异常 在使用Mybatis或MybatisPlus进行数据统计#xff0c;在【
SpringBoot的Mybatis-plus实战之基础知识】中对mybatisplus引入有介绍#xff0c;本次要使用其进… 文章目录 需求描述定义实体方式一、mybatisPlus实现方式二、自定义SQL实现简单查询过滤查询 异常处理1、SQL拼写异常 在使用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 ListOrderDetail orderDetailList;private String orderStartDate;private String orderEndDate;public Long getTaxtedAmount() {return taxtedAmount;}public void setTaxtedAmount(Long taxtedAmount) {this.taxtedAmount taxtedAmount;}public ListOrderDetail getOrderDetailList() {return orderDetailList;}public void setOrderDetailList(ListOrderDetail 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);// 创建QueryWrapperQueryWrapperOrder 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);// 执行查询ListMapString, Object results mapper.selectMaps(queryWrapper);// 处理查询结果for (MapString, 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 BaseMapperOrder{
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,jdbcTypeVARCHAR},AND order_date lt; #{orderEndDate,jdbcTypeVARCHAR},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 testshopId ! null ,AND shop_id #{shopId,jdbcTypeVARCHAR},/if,AND order_date gt; #{orderStartDate,jdbcTypeVARCHAR},AND order_date lt; #{orderEndDate,jdbcTypeVARCHAR},/where,GROUP BY,shop_id,/script
})异常处理
1、SQL拼写异常
错误信息 Error creating document instance. Cause: org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 286; 元素内容必须由格式正确的字符数据或标记组成 异常原因 mapper对大于、小于号进行转义 处理方案 即将符号进行转义处理如下所示。
将 大于号 改写为 gt;
小于号 改写为 lt;
