mybatis中查询结果进行分组

在用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">

巴特西

mybatis中查询结果进行分组

最新文章

热门文章