mybatis中实现让返回值与bean中字段相匹配

1. 编写目的

这个介绍的与那些修改mybatis.xml文件的方法不一样,目的也不一样。

当我们需要查询的数据跟entity的bean完全不匹配的时候(比如说需要统计的时候),我们不可能写多个dao层的查询接口,然后一个一个map到xml中去。

我们可以专门写一个类,根据自己的需要把统计的属性都写到里面去,然后一次性查询,就可以获得所有需要统计的数据。

2. 重要方法

专门编写一个实体类。实体类中包含的都是自己需要统计的属性。

编写dao层的接口方法的返回值就是这个实体类。

在映射的map.xml中编写查询时,使用as …的方法与实体类中的属性一一对应。

3. 具体案例

需求:需要统计日记表中某个用户的以下属性:

总共编写日记数目

删除日记数目

心情开心篇数

心情一般篇数

心情差篇数

心情极差篇数

晴朗天气篇数

阴天篇数

雨天篇数

实体类的编写

package cn.ailanglang.diary.util;

public class StatisticBean {
  private Integer sum;
  private Integer mood0;
  private Integer mood1;
  private Integer mood2;
  private Integer mood3;
  private Integer weather0;
  private Integer weather1;
  private Integer weather2;
  private Integer weather3;
  private Integer weather4;
  private Integer weather5;
  private Integer weather6;
  private Integer weather7;
  
  private Integer unknow_weather;
  private Integer unknow_mood;

  public Integer getSum() {
    return sum;
  }

  public void setSum(Integer sum) {
    this.sum = sum;
  }

  public Integer getWeather0() {
    return weather0;
  }

  public void setWeather0(Integer weather0) {
    this.weather0 = weather0;
  }

  public Integer getWeather1() {
    return weather1;
  }

  public void setWeather1(Integer weather1) {
    this.weather1 = weather1;
  }

  public Integer getWeather2() {
    return weather2;
  }

  public void setWeather2(Integer weather2) {
    this.weather2 = weather2;
  }

  public Integer getWeather3() {
    return weather3;
  }

  public void setWeather3(Integer weather3) {
    this.weather3 = weather3;
  }

  public Integer getWeather4() {
    return weather4;
  }

  public void setWeather4(Integer weather4) {
    this.weather4 = weather4;
  }

  public Integer getWeather5() {
    return weather5;
  }

  public void setWeather5(Integer weather5) {
    this.weather5 = weather5;
  }

  public Integer getWeather6() {
    return weather6;
  }

  public void setWeather6(Integer weather6) {
    this.weather6 = weather6;
  }

  public Integer getWeather7() {
    return weather7;
  }

  public void setWeather7(Integer weather7) {
    this.weather7 = weather7;
  }

  public Integer getUnknow_weather() {
    return unknow_weather;
  }

  public void setUnknow_weather(Integer unknow_weather) {
    this.unknow_weather = unknow_weather;
  }

  public Integer getUnknow_mood() {
    return unknow_mood;
  }

  public void setUnknow_mood(Integer unknow_mood) {
    this.unknow_mood = unknow_mood;
  }

  public Integer getMood0() {
    return mood0;
  }

  public void setMood0(Integer mood0) {
    this.mood0 = mood0;
  }

  public Integer getMood1() {
    return mood1;
  }

  public void setMood1(Integer mood1) {
    this.mood1 = mood1;
  }

  public Integer getMood2() {
    return mood2;
  }

  public void setMood2(Integer mood2) {
    this.mood2 = mood2;
  }

  public Integer getMood3() {
    return mood3;
  }

  public void setMood3(Integer mood3) {
    this.mood3 = mood3;
  }
}

dao层接口方法

  /**
   * 统计
   * @param userid
   * @return
   */
  StatisticBean countMood(Long userid);

mapper.xml的编写

重点注意as …

<select id="countMood" parameterType="java.lang.Long" resultType="cn.smileyan.diary.util.StatisticBean">
    select
     count(diary.pk_diaryid) as sum,
     sum(case when diary.mood='0' then 1 else 0 end) as mood0,
     sum(case when diary.mood='1' then 1 else 0 end) as mood1,
     sum(case when diary.mood='2' then 1 else 0 end) as mood2,
     sum(case when diary.mood='3' then 1 else 0 end) as mood3,
     sum(case when diary.weather='0' then 1 else 0 end) as weather0,
     sum(case when diary.weather='1' then 1 else 0 end) as weather1,
     sum(case when diary.weather='2' then 1 else 0 end) as weather2,
     sum(case when diary.weather='3' then 1 else 0 end) as weather3,
     sum(case when diary.weather='4' then 1 else 0 end) as weather4,
     sum(case when diary.weather='5' then 1 else 0 end) as weather5,
     sum(case when diary.weather='6' then 1 else 0 end) as weather6,
     sum(case when diary.weather='7' then 1 else 0 end) as weather7
    from user_diary,diary
    where diary.pk_diaryid = user_diary.fk_diaryid
    and user_diary.fk_userid = #{userid};
  </select>

mybatis中实现让返回值与bean中字段相匹配

扫一扫手机访问