SpringBoot+mybatis查询灵异事件！

客官°小女子只卖身不卖艺 2021-09-18 16:42 241阅读 0赞

今天写公司项目中，遇到一个很奇怪的**灵异事情**

如图！![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FkbWluUHdk_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FkbWluUHdk_size_16_color_FFFFFF_t_70 1][]

问题解决思路，先从controller ==> service ==> dao ==>mapper.xml 排查

问题发现在mapper.xml中有问题

我的原始\*.xml是这样的写resultMap的

<resultMap  id="HaAreaMap"  type="HaArea">
            <id column="areaid" property="areaid" />
            <result column="areaNo" property="areano"/>
            <result column="registeredNo" property="registeredno"/>
            <result column="name" property="name"/>
            <result column="addr" property="addr"/>
            <result column="DS" property="ds"/>
            <result column="reserved" property="reserved"/>
            <result column="lastsumday" property="lastsumday"/>
            <result column="description" property="description"/>
    
            <association property="haMeter" javaType="HaMeter">
                <result column="meterid" property="meterid"/>
                <result column="TH_NUMBER" property="thNumber"/>
                <result column="S_NUMBER" property="sNumber"/>
                <result column="TH_R_TIME" property="thRTime"/>
                <result column="starttime" property="starttime"/>
                <result column="mareaid" property="areaid"/>
            </association>
    
            <association property="haRgn" javaType="HaRgn">
                <result column="id" property="id"/>
            </association>
    
            <association property="havMeterinfo" javaType="HavMeterinfo">
                <result column="meterid" property="meterid"/>
                <result column="状态" property="状态"/>
            </association>
    
    
        </resultMap>
    
    
    
        
        <select id="selectHaAreaList"  resultMap="HaAreaMap">
          SELECT
    	a.areaid AS areaid, Right(a.areaNo, 4) AS aareaNo, a.registeredNo AS aregisteredNo,
    	a.name AS aname,concat_ws('_',n.name,a.addr) AS aaddr,
    	case a.DS when 'S' then '单月' else '双月' end AS ads,
    	 COUNT(m.meterid) AS mmeterid,
    	 SUM(m.TH_NUMBER) AS mTH_NUMBER,
    	 SUM(m.S_NUMBER) AS mS_NUMBER,
    	 t.n AS atnNumber,
    	 a.reserved AS areserved,
    	 to_char(GREATEST(m.TH_R_TIME),'YYYY-MM-DD hh:mm:ss') AS mTH_R_TIME,
    	 to_char(a.lastsumday,'YYYY-MM-DD hh:mm:ss') AS alastsumday,
    	 to_char(LEAST(m.starttime),'YYYY-MM-DD') AS mstarttime, a.description AS adescription
    	 FROM ((ha_area a LEFT JOIN ha_meter m ON m.areaid=a.areaid)
    	 LEFT JOIN ha_rgn n ON n.id=Left(a.areaNo, 1))
    	 LEFT JOIN (SELECT Count(i.meterid) AS n,i.areaid AS areaid
    	 FROM hav_meterinfo i WHERE i.状态!='正常' AND i.状态!='正确' AND i.状态!='建档'
    	 AND i.状态!='集中器无返回' AND i.状态!='关阀' AND i.状态!='修改' AND i.状态!='已更正'
    	 AND i.状态!='已更换' AND i.状态!='采集器无返回' AND i.状态 NOT LIKE '%用量异常%'
    	  AND i.表号 >0
    	 GROUP BY i.areaid) t ON t.areaid=a.areaid
    	group by a.areaid,a.areaNo,a.registeredNo,a.name,n.name,a.addr,a.DS,t.n,
    	a.reserved,m.TH_R_TIME,
    	a.lastsumday,m.starttime,a.description
        </select>

估计在看的各位老鸟立马就能观察出来，没错就是你想的，

原因：

1.有中文 可能会出现乱码

2.查询语句和xml中的字段没有映射对

解决步骤，

1.去掉中文，把不需要的删掉；

2.把一些用聚合函数的取一个英文别名，和实体类的属性相等，没有的就给加上

最终解决完~

<resultMap  id="HaAreaMap"  type="HaArea">
            <id column="aareaid" property="areaid" />
            <result column="aareaNo" property="areano"/>
            <result column="aregisteredNo" property="registeredno"/>
            <result column="aname" property="name"/>
            <result column="aaddr" property="addr"/>
            <result column="ads" property="ds"/>
            <result column="areserved" property="reserved"/>
            <result column="alastsumday" property="lastsumday"/>
            <result column="adescription" property="description"/>
            <result column="atnNumber" property="atnNumber"/>
    
            <association property="haMeter" javaType="HaMeter">
                <result column="mmeterid" property="meterid"/>
                <result column="mTH_NUMBER" property="thNumber"/>
                <result column="mS_NUMBER" property="sNumber"/>
                <result column="mTH_R_TIME" property="thRTime"/>
                <result column="mstarttime" property="starttime"/>
                <result column="areaid" property="areaid"/>
            </association>
    
    
    
    
        </resultMap>
    
    
    
    
        
        <select id="selectHaAreaList"  resultMap="HaAreaMap">
          SELECT
    	a.areaid AS areaid, Right(a.areaNo, 4) AS aareaNo, a.registeredNo AS aregisteredNo,
    	a.name AS aname,concat_ws('_',n.name,a.addr) AS aaddr,
    	case a.DS when 'S' then '单月' else '双月' end AS ads,
    	 COUNT(m.meterid) AS mmeterid,
    	 SUM(m.TH_NUMBER) AS mTH_NUMBER,
    	 SUM(m.S_NUMBER) AS mS_NUMBER,
    	 t.n AS atnNumber,
    	 a.reserved AS areserved,
    	 to_char(GREATEST(m.TH_R_TIME),'YYYY-MM-DD hh:mm:ss') AS mTH_R_TIME,
    	 to_char(a.lastsumday,'YYYY-MM-DD hh:mm:ss') AS alastsumday,
    	 to_char(LEAST(m.starttime),'YYYY-MM-DD') AS mstarttime, a.description AS adescription
    	 FROM ((ha_area a LEFT JOIN ha_meter m ON m.areaid=a.areaid)
    	 LEFT JOIN ha_rgn n ON n.id=Left(a.areaNo, 1))
    	 LEFT JOIN (SELECT Count(i.meterid) AS n,i.areaid AS areaid
    	 FROM hav_meterinfo i WHERE i.状态!='正常' AND i.状态!='正确' AND i.状态!='建档'
    	 AND i.状态!='集中器无返回' AND i.状态!='关阀' AND i.状态!='修改' AND i.状态!='已更正'
    	 AND i.状态!='已更换' AND i.状态!='采集器无返回' AND i.状态 NOT LIKE '%用量异常%'
    	  AND i.表号 >0
    	 GROUP BY i.areaid) t ON t.areaid=a.areaid
    	group by a.areaid,a.areaNo,a.registeredNo,a.name,n.name,a.addr,a.DS,t.n,
    	a.reserved,m.TH_R_TIME,
    	a.lastsumday,m.starttime,a.description
        </select>

另外补充一下，我在查询中，除了表还有视图一起用，可以把视图当场一个表用这里不多说，具体可以参看

[Mybatis中视图的使用][Mybatis]

如果博文中有什么不对的地方，请各位老鸟多多指教

如果上面用到的方法，没有帮到你的话，那就是你的类映射错了，仔细检查是在你当前的类映射还是，应该在别的类映射

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FkbWluUHdk_size_16_color_FFFFFF_t_70]: /images/20210522/4ee8bf0b63d24a7299477fa6b8dba807.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0FkbWluUHdk_size_16_color_FFFFFF_t_70 1]: /images/20210522/3da433e6bc984fd5b1e7f766a3a37287.png
[Mybatis]: https://blog.csdn.net/cleble/article/details/54022119