mybatis中查询结果进行分组

最新推荐文章于 2024-09-12 20:32:02 发布

weixin_30908649

最新推荐文章于 2024-09-12 20:32:02 发布

阅读量2.1k

点赞数

CC 4.0 BY-SA版权

文章标签： java 数据库 json

原文链接：http://www.cnblogs.com/ahang/p/6677062.html

本文介绍如何使用MyBatis进行数据库查询结果的自动分组，并通过具体项目实例展示了mapper.xml配置方法及对应的Java实体类设计。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在用mybatis进行数据库查询时，对查询结果进行自动分组，在mapper.xml中的配置有些注意的地方，下面是实际项目中一个例子。在数据库中查询中如下：

在结果集中需要对alarmDate进行分组，比如2017-04-05这两条数据，分组后最终实现如下效果：

{
      "alarmDate": "2017-04-05",
      "entityCounts": [
        {
          "count": 2,
          "alarmLevel": "警告"
        },
        {
          "count": 567,
          "alarmLevel": "严重"
        }
      ]
}

那么这种情况在mapper.xml该怎么配呢？需要配一个resultMap, 在里面配置分组的关键字段。

  <resultMap id="alarmCountDate" type="monitor.entity.EntityCountDateList">
        <id property="alarmDate" column="alarmDate" />
        <collection property="entityCounts" ofType="monitor.entity.EntityCountDate">
            <result property="alarmLevel" column="alarmLevel" />
            <result property="count" column="count" />
        </collection>
    </resultMap>

    <!--resultMap的值为上面resultMap的id。 -->
    <select id="getAlarmCountByDate" parameterType="map" resultMap="alarmCountDate">
    <![CDATA[
    select count(t.alarmLevel) as count,t.alarmLevel,date(t.alarmDate) as alarmDate from (select alarmDate,alarmLevel 
    from alarm_info where alarmDate>=#{alarmStartDate} and alarmDate<=#{alarmEndDate}  
    group by alarmLevel,alarmDate ) t group by t.alarmLevel,date(t.alarmDate) order by alarmDate;
     
     ]]>
    </select>

涉及到两个对象类，如下

类monitor.entity.EntityCountDateList

package monitor.entity;

import java.util.List;

public class EntityCountDateList {

    private String alarmDate;
    private List<EntityCountDate> entityCounts;
    public String getAlarmDate() {
        return alarmDate;
    }
    public void setAlarmDate(String alarmDate) {
        this.alarmDate = alarmDate;
    }
    public List<EntityCountDate> getEntityCounts() {
        return entityCounts;
    }
    public void setEntityCounts(List<EntityCountDate> entityCounts) {
        this.entityCounts = entityCounts;
    }
    
    
}

类：monitor.entity.EntityCountDate

package monitor.entity;

public class EntityCountDate {

    private Integer count;
    private String alarmLevel;
    public EntityCountDate(){}
    public EntityCountDate(String alarmLevel,Integer count){
        this.count = count;
        this.alarmLevel = alarmLevel;
    }
    public Integer getCount() {
        return count;
    }
    public void setCount(Integer count) {
        this.count = count;
    }
    public String getAlarmLevel() {
        return alarmLevel;
    }
    public void setAlarmLevel(String alarmLevel) {
        this.alarmLevel = alarmLevel;
    }
    
}

在resultMap中配置的entityCounts为类EntityCountDateList中List<EntityCountDate>的名字，这一步很关键。

<collection property="entityCounts" ofType="monitor.entity.EntityCountDate">

转载于:https://www.cnblogs.com/ahang/p/6677062.html