Mybatis：動態(tài)SQL分組查詢

????<resultMap?id="groupResultMap"?type="java.util.Map">
????????<result?column="count(1)"?property="num"?jdbcType="VARCHAR"/>
????????<result?column="user_unit"?jdbcType="VARCHAR"?property="userUnit"/>
????????<result?column="train_cycle_zh"?jdbcType="VARCHAR"?property="trainCycleZh"/>
????????<result?column="train_cycle_en"?jdbcType="VARCHAR"?property="trainCycleEn"/>
????????<result?column="train_major_zh"?jdbcType="VARCHAR"?property="trainMajorZh"/>
????????<result?column="train_major_en"?jdbcType="VARCHAR"?property="trainMajorEn"/>
????????<result?column="certif_type"?jdbcType="VARCHAR"?property="certifType"/>
????????<result?column="certif_status"?jdbcType="INTEGER"?property="certifStatus"/>
????resultMap>
?
????<select?id="groupCetifInfoBySelective"?parameterType="com.ruoyi.certif.domain.CertifInfoDO"
????????????resultMap="groupResultMap">
????????SELECT COUNT(1),
????????<trim?suffixOverrides=",">
????????????<if?test="userUnit != null">
????????????????user_unit,
????????????if>
????????????<if?test="trainCycleZh != null">
????????????????train_cycle_zh,
????????????if>
????????????<if?test="trainCycleEn != null">
????????????????train_cycle_en,
????????????if>
????????????<if?test="trainMajorZh != null">
????????????????train_major_zh,
????????????if>
????????????<if?test="trainMajorEn != null">
????????????????train_major_en,
????????????if>
????????????<if?test="certifType != null">
????????????????certif_type,
????????????if>
????????????<if?test="certifStatus != null">
????????????????certif_status,
????????????if>
????????trim>
????????FROM certif_info GROUP BY
????????<trim?suffixOverrides=",">
????????????<if?test="userUnit != null">
????????????????user_unit,
????????????if>
????????????<if?test="trainCycleZh != null">
????????????????train_cycle_zh,
????????????if>
????????????<if?test="trainCycleEn != null">
????????????????train_cycle_en,
????????????if>
????????????<if?test="trainMajorZh != null">
????????????????train_major_zh,
????????????if>
????????????<if?test="trainMajorEn != null">
????????????????train_major_en,
????????????if>
????????????<if?test="certifType != null">
????????????????certif_type,
????????????if>
????????????<if?test="certifStatus != null">
????????????????certif_status,
????????????if>
????????trim>
????select>

/**
?????* 分組查詢證書信息列表
?????* @param?certifInfo
?????* @return
?????*/
????public?List

public?AjaxResult groupCetifInfo(CertifInfoDO certifInfo)?{
?
????????log.info("【分組查詢證書信息】,參數(shù):{}", certifInfo);
????????if?(Objects.isNull(certifInfo)) {
????????????log.error("【分組查詢證書信息列表】參數(shù)缺失");
????????????AjaxResult.error("分組查詢證書信息,參數(shù)缺失");
????????}
?
????????List

import?java.util.HashMap;
import?com.ruoyi.common.core.constant.HttpStatus;
import?com.ruoyi.common.core.utils.StringUtils;
?
/**
?* 操作消息提醒
?* 
?*/
public?class?AjaxResult?extends?HashMap<String, Object>
{
????private?static?final?long?serialVersionUID = 1L;
?
????/** 狀態(tài)碼 */
????public?static?final?String CODE_TAG = "code";
?
????/** 返回內(nèi)容 */
????public?static?final?String MSG_TAG = "msg";
?
????/** 數(shù)據(jù)對象 */
????public?static?final?String DATA_TAG = "data";
?
????/**
?????* 初始化一個新創(chuàng)建的 AjaxResult 對象，使其表示一個空消息。
?????*/
????public?AjaxResult()
????{
????}
?
????/**
?????* 初始化一個新創(chuàng)建的 AjaxResult 對象
?????* 
?????* @param?code 狀態(tài)碼
?????* @param?msg 返回內(nèi)容
?????*/
????public?AjaxResult(int?code, String msg)
????{
????????super.put(CODE_TAG, code);
????????super.put(MSG_TAG, msg);
????}
?
????/**
?????* 初始化一個新創(chuàng)建的 AjaxResult 對象
?????* 
?????* @param?code 狀態(tài)碼
?????* @param?msg 返回內(nèi)容
?????* @param?data 數(shù)據(jù)對象
?????*/
????public?AjaxResult(int?code, String msg, Object data)
????{
????????super.put(CODE_TAG, code);
????????super.put(MSG_TAG, msg);
????????if?(StringUtils.isNotNull(data))
????????{
????????????super.put(DATA_TAG, data);
????????}
????}
?
????/**
?????* 返回成功消息
?????* 
?????* @return?成功消息
?????*/
????public?static?AjaxResult success()
????{
????????return?AjaxResult.success("操作成功");
????}
?
????/**
?????* 返回成功數(shù)據(jù)
?????* 
?????* @return?成功消息
?????*/
????public?static?AjaxResult success(Object data)
????{
????????return?AjaxResult.success("操作成功", data);
????}
?
????/**
?????* 返回成功消息
?????* 
?????* @param?msg 返回內(nèi)容
?????* @return?成功消息
?????*/
????public?static?AjaxResult success(String msg)
????{
????????return?AjaxResult.success(msg, null);
????}
?
????/**
?????* 返回成功消息
?????* 
?????* @param?msg 返回內(nèi)容
?????* @param?data 數(shù)據(jù)對象
?????* @return?成功消息
?????*/
????public?static?AjaxResult success(String msg, Object data)
????{
????????return?new?AjaxResult(HttpStatus.SUCCESS, msg, data);
????}
?
????/**
?????* 返回錯誤消息
?????* 
?????* @return
?????*/
????public?static?AjaxResult error()
????{
????????return?AjaxResult.error("操作失敗");
????}
?
????/**
?????* 返回錯誤消息
?????* 
?????* @param?msg 返回內(nèi)容
?????* @return?警告消息
?????*/
????public?static?AjaxResult error(String msg)
????{
????????return?AjaxResult.error(msg, null);
????}
?
????/**
?????* 返回錯誤消息
?????* 
?????* @param?msg 返回內(nèi)容
?????* @param?data 數(shù)據(jù)對象
?????* @return?警告消息
?????*/
????public?static?AjaxResult error(String msg, Object data)
????{
????????return?new?AjaxResult(HttpStatus.ERROR, msg, data);
????}
?
????/**
?????* 返回錯誤消息
?????* 
?????* @param?code 狀態(tài)碼
?????* @param?msg 返回內(nèi)容
?????* @return?警告消息
?????*/
????public?static?AjaxResult error(int?code, String msg)
????{
????????return?new?AjaxResult(code, msg, null);
????}
}

/**
?????* 分組查詢證書信息列表
?????*/
????@GetMapping("/statis")
????public?AjaxResult listGroup(CertifInfoDO certifInfo)?{
????????AjaxResult result = certifInfoService.groupCetifInfo(certifInfo);
????????return?result;
????}

http://localhost:8081/certif/info/statis?trainMajorZh=GROUP 【中文專業(yè)分組】
http://localhost:8081/certif/info/statis?userUnit=GROUP 【用戶單位分組】
http://localhost:8081/certif/info/statis?certifType=GROUP 【證書類型分組】
http://localhost:8081/certif/info/statis?certifStatus=GROUP 【證書狀態(tài)分組】

原文鏈接csdn.net/fly910905/article/details/108099743