【踩坑日志】TDengine在SpringBoot项目中使用order by ts desc limit 1出现错误

博主： Fivk
发布时间：2023 年 10 月 25 日
854 次浏览
暂无评论
3610字数
分类：踩坑日志

# 问题描述

我这里是SpringBoot+Mybatis+TDengine项目，我想获取一个表中的最新数据于是有了这样一条SQL：

```sql
select * from my_table_name order by ts desc limit 1
```

但是它执行报错了

```error
### SQL: select * from tw_nms_report.stable_olt_statistics order by ts desc limit 1
### Cause: java.sql.SQLException: Error
; uncategorized SQLException for SQL []; SQL state [null]; error code [0]; Error; nested exception is java.sql.SQLException: Error
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy127.selectOne(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:166)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:83)
```

我发现当被查询的表中只有一条数据时，它能正确执行，不报错。

![](https://blog.fivk.cn/usr/uploads/2023/10/1256066708.png)

但是表中的数据如果大于两条，那么他就会报上面的错误。

![](https://blog.fivk.cn/usr/uploads/2023/10/738456886.png)

# 项目结构

- Java实体类

```java
package com.tw.nms.report.td.entity;

import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.ToString;

import javax.persistence.Column;
import javax.persistence.Table;
import java.io.Serializable;

/**
 * @author wufan
 * @create 2023/10/24
 * @Description Olt设备信息统计
 */
@Data
@EqualsAndHashCode(callSuper = false)
@Table(name = "stable_olt_statistics", schema = "tw_nms_report")
@ToString
public class OltStatisticsTdEntity implements Serializable {
    // 序列化: 用于网络传输
    private static final long serialVersionUID = 1L;

// 时间戳
    @Column(name = "ts")
    private String ts;

// 设备总数：所有的设备数量
    @Column(name = "allDevices")
    private Integer allDevices;

// 在网设备数量：记录在网设备的数量
    @Column(name = "onlineDevices")
    private Integer onlineDevices;

// 在网设备占比：表示在网设备数量占总设备数量的比例
    @Column(name = "onlineDeviceRatio")
    private Double onlineDeviceRatio;

// 设备类型分布：记录各种设备类型的数量
    @Column(name = "deviceTypeDistribution")
    private String deviceTypeDistribution;

// 区域分布：记录各区域的设备数量
    @Column(name = "areaDistribution")
    private String areaDistribution;
}
```

- TDengine表结构

```sql
CREATE TABLE IF NOT EXISTS sTable_olt_statistics (  
    ts TIMESTAMP,  
    allDevices INT,  
    onlineDevices INT,  
    onlineDeviceRatio DOUBLE,  
    deviceTypeDistribution BINARY(255),  
    areaDistribution BINARY(255)  
);  
```

- Mapper

```java
public interface OltStatisticsTDMapper extends Mapper<OltStatisticsTdEntity> {
    OltStatisticsTdEntity getNewestOltStatistics();
}
```

- XML

```xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.tw.nms.report.td.mapper.OltStatisticsTDMapper">
    <select id="getNewestOltStatistics" resultType="com.tw.nms.report.td.entity.OltStatisticsTdEntity">
        select * from tw_nms_report.stable_olt_statistics order by ts desc limit 1
    </select>
</mapper>
```

# 解决方法

使用`last_row`代替。

```sql
select last_row(olts.ts) as ts,
       last_row(olts.allDevices) as allDevices,
       last_row(olts.onlineDevices) as onlineDevices,
       last_row(olts.onlineDeviceRatio) as onlineDeviceRatio,
       last_row(olts.deviceTypeDistribution) as deviceTypeDistribution,
       last_row(olts.areaDistribution) as areaDistribution
from tw_nms_report.stable_olt_statistics as olts
```

最后修改：2023 年 10 月 25 日