ifnull(exp1,0)使用mybatis传入，为对象赋值还是为null解决办法

在为公司做排行榜的时候，需要对数据进行一些计算，为了排除null的影响，使用mysql数据库的ifnull字段，把null转0，在mybatis查询完成，对象的赋值还是为null；下面是我碰到的问题及解决办法

数据库查询在没有使用ifnull的时候

select  
current_home_score,
current_guest_score,
current_home_victory,
current_guest_victory,
current_integral,
current_home_dogfall,
current_guest_dogfall,
current_victory,
current_score,
from table
where season = 2019 

数据库查询使用了ifnull的时候

select 
	ifnull(current_home_score,0),
	ifnull(current_guest_score,0),
	ifnull(current_home_victory,0),
	ifnull(current_guest_victory,0),
	ifnull(current_integral,0),
	ifnull(current_home_dogfall,0),
	ifnull(current_guest_dogfall,0),
	ifnull(current_victory,0),
	ifnull(current_score,0),
from table
where season = 2019  

到这里是没有问题的
继续上代码

mapper映射

<resultMap id="resultMap" type="com.tiao.table">
        <id column="id" property="id" />
        <result column="current_home_score" property="currentHomeScore"/>
        <result column="current_guest_score" property="currentGuestScore"/>
        <result column="current_home_victory" property="currentHomeVictory"/>
        <result column="current_guest_victory" property="currentGuestVictory"/>
        <result column="current_integral" property="currentIntegral"/>
        <result column="current_home_dogfall" property="currentHomeDogfall"/>
        <result column="current_guest_dogfall" property="currentGuestDogfall"/>
        <result column="current_victory" property="currentVictory"/>
        <result column="current_score" property="currentScore"/>
    </resultMap>
    <sql id="baseSql">
        current_home_score,
		current_guest_score,
		current_home_victory,
		current_guest_victory,
		current_integral,
		current_home_dogfall,
		current_guest_dogfall,
		current_victory,
		current_score
    </sql>

查询

<select id="selectByTaRoundIntegral" resultMap="resultMap">
        select
        <include refid="baseSql"></include>
        from ta_round_integral
        where 1=1
        <if test="season !=null">
            and season=#{season}
        </if>
  </select>

结果为null 继续修改，我们需要的是0不是null,把映射sql标签里面的重新修改

 <sql id="baseSql">
  	ifnull(current_home_score,0) ,
      ifnull(current_guest_score,0) ,
      ifnull(current_home_victory,0) ,
      ifnull(current_guest_victory,0) ,
      ifnull(current_integral,0),
      ifnull(current_home_dogfall,0),
      ifnull(current_guest_dogfall,0),
      ifnull(current_victory,0),
      ifnull(current_score,0)
   </sql>

查询出来的结果依然为null，放到数据库运行，结果是0,查了很多原因，是因为在映射中查出来的是ifnull(exp1,0)的值，例如

在mybatis中查询的结果是ifull（current_guest_score,0）=0
而并不是current_guest_score=0;
和映射column="current_guest_score"property="currentGuestScore"无法对应，无法给currentGuestScore的属性赋值，所有依然为null

所以我们得起个别名，对映射的sql标签元素重新修改

   <sql id="baseSql">
		ifnull(current_home_score,0) current_home_score,
      ifnull(current_guest_score,0) current_guest_score,
      ifnull(current_home_victory,0) current_home_victory,
      ifnull(current_guest_victory,0) current_guest_victory,
      ifnull(current_integral,0) current_integral,
      ifnull(current_home_dogfall,0) current_home_dogfall,
      ifnull(current_guest_dogfall,0) current_guest_dogfall,
      ifnull(current_victory,0) current_victory,
      ifnull(current_score,0) current_score
    </sql>

在程序中执行查询


执行成功，null值替换成0