mysql - SQL Error : 1054, SQLState : 42S22 , Spring Boot jpa 应用程序中 'NaN' 中的未知列 'field list'

Question

首先，我是 Spring boot JPA 项目的初学者，在这里我遇到了一些奇怪的问题。当我尝试更新记录时出现以下异常。这是我的第一个 Spring boot JPA 项目。请让我知道什么时候出错了

模型类

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

@Entity
@Table(name = "RIDESUMMARY")
@EntityListeners(AuditingEntityListener.class)
@JsonIgnoreProperties(value= {"createdDate","updatedDate"})
public class RideSummary implements Serializable 
{
    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "RIDESUMMARYID")
    private long rideSummaryId;

    @Column(name="BOOKINGID")
    private long bookingId;

    @Column(name="ORGID")
    private long orgId;

    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss")
    @Column(name = "STARTTIME")
    private Date startTime;

    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss")
    @Column(name = "ENDTIME")
    private Date endTime;

    @Column(name = "STARTLAT")
    private Double startLat;

    @Column(name = "STARTLNG")
    private Double startLng;

    @Column(name = "STARTLOC")
    private String startLoc;

    @Column(name = "ENDLAT")
    private Double endLat;

    @Column(name = "ENDLNG")
    private Double endLng;

    @Column(name = "ENDLOC")
    private String endLoc;

    @Column(name = "DISTANCE")
    private float distance;

    @Column(name = "FUELCONSUMED")
    private float fuelConsumed;

    @Column(name = "STATUS")
    private String status;

    @Column(name = "RIDETIME")
    private float rideTime;

    @Column(name = "MILEAGE")
    private float mileage;

    @Column(name = "AVGSPEED")
    private float avgSpeed;

    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss")
    @Column(name = "CREATEDDATE", updatable = false)
    @CreatedDate
    private Date createdDate;

    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss")
    @Column(name="UPDATEDDATE")
    @LastModifiedDate
    private Date updatedDate;

    public Date getStartTime() {
        return startTime;
    }

    public void setStartTime(Date startTime) {
        this.startTime = startTime;
    }

    public Date getEndTime() {
        return endTime;
    }

    public void setEndTime(Date endTime) {
        this.endTime = endTime;
    }

    public Double getStartLat() {
        return startLat;
    }

    public void setStartLat(Double startLat) {
        this.startLat = startLat;
    }

    public Double getStartLng() {
        return startLng;
    }

    public void setStartLng(Double startLng) {
        this.startLng = startLng;
    }

    public Double getEndLat() {
        return endLat;
    }

    public void setEndLat(Double endLat) {
        this.endLat = endLat;
    }

    public Double getEndLng() {
        return endLng;
    }

    public void setEndLng(Double endLng) {
        this.endLng = endLng;
    }

    public float getDistance() {
        return distance;
    }

    public void setDistance(float distance) {
        this.distance = distance;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public Date getUpdatedDate() {
        return updatedDate;
    }

    public void setUpdatedDate(Date updatedDate) {
        this.updatedDate = updatedDate;
    }

    public long getRideSummaryId() {
        return rideSummaryId;
    }

    public void setRideSummaryId(long rideSummaryId) {
        this.rideSummaryId = rideSummaryId;
    }

    public Date getCreatedDate() {
        return createdDate;
    }

    public void setCreatedDate(Date createdDate) {
        this.createdDate = createdDate;
    }

    public long getBookingId() {
        return bookingId;
    }

    public void setBookingId(long bookingId) {
        this.bookingId = bookingId;
    }

    public long getOrgId() {
        return orgId;
    }

    public void setOrgId(long orgId) {
        this.orgId = orgId;
    }

    public String getStartLoc() {
        return startLoc;
    }

    public void setStartLoc(String startLoc) {
        this.startLoc = startLoc;
    }

    public String getEndLoc() {
        return endLoc;
    }

    public void setEndLoc(String endLoc) {
        this.endLoc = endLoc;
    }

    public float getRideTime() {
        return rideTime;
    }

    public void setRideTime(float rideTime) {
        this.rideTime = rideTime;
    }

    public float getMileage() {
        return mileage;
    }

    public void setMileage(float mileage) {
        this.mileage = mileage;
    }

    public float getAvgSpeed() {
        return avgSpeed;
    }

    public void setAvgSpeed(float avgSpeed) {
        this.avgSpeed = avgSpeed;
    }

    public float getFuelConsumed() {
        return fuelConsumed;
    }

    public void setFuelConsumed(float fuelConsumed) {
        this.fuelConsumed = fuelConsumed;
    }
}

服务等级

RideSummary ride = rideSummaryRepository.findByBookingId(bookingId);
  if(ride!=null)
  {
      ride.setDistance(totalDistance);
      ride.setFuelConsumed(fuelConsumed);
      ride.setEndLat(endLat);
      ride.setEndLng(endLng);
      Date endTim = commons.convertStringToDate(endTime);
      ride.setEndTime(endTim);
      ride.setStatus("COMPLETED");
      ride.setOrgId(book.getOrgId());
      ride.setEndLoc(address);

      long rideTimeInMillis = endTim.getTime() - ride.getStartTime().getTime();
      float rideTimeInMinutes = (rideTimeInMillis/(1000*60));
      ride.setRideTime(rideTimeInMinutes);

      float mileage = totalDistance/fuelConsumed;
      float totalTime = rideTimeInMinutes / 60;
      float avgSpeed = totalDistance/totalTime;

      ride.setAvgSpeed(avgSpeed);
      ride.setMileage(mileage);
      rideSummaryRepository.save(ride);
  }

ridesummary 表中的现有记录:

ridesummaryid, bookingid, createddate, distance, endlat, endlng, endtime, startlat, startlng, starttime, status, updateddate, orgid, avgspeed, endloc, fuelconsumed, mileage, ridetime, startloc
    '6', '13', '2018-07-27 10:57:58', '0', NULL, NULL, NULL, '345.544', '343.232', '2018-07-16 01:38:11', 'RUNNING', '2018-07-27 10:57:58', '4', '0', NULL, '0', '0', '0', 'New York'

mysql workbench View 中具有数据类型的表属性

ridesummaryid bigint(20) AI PK 
bookingid int(11) 
createddate datetime 
distance float 
endlat double 
endlng double 
endtime datetime 
startlat double 
startlng double 
starttime datetime 
status varchar(255) 
updateddate datetime 
orgid bigint(20) 
avgspeed float 
endloc varchar(255) 
fuelconsumed float 
mileage float 
ridetime float 
startloc varchar(255)

控制台

2018-07-27 11:40:12.262  WARN 4440 --- [nio-8081-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1054, SQLState: 42S22
2018-07-27 11:40:12.262 ERROR 4440 --- [nio-8081-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : Unknown column 'NaN' in 'field list'
2018-07-27 11:40:12.262  INFO 4440 --- [nio-8081-exec-2] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements
2018-07-27 11:40:12.263 ERROR 4440 --- [nio-8081-exec-2] o.h.i.ExceptionMapperStandardImpl        : HHH000346: Error during managed flush [org.hibernate.exception.SQLGrammarException: could not execute statement]
org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:242)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:225)
    at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:540)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:746)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:714)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:532)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:304)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy197.save(Unknown Source)
    at com.telematics.fleet.service.RideSummaryServiceImpl.endRide(RideSummaryServiceImpl.java:122)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'NaN' in 'field list'
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)

Answer 1

double 类型有问题。一种解决方案，您可以将其转换为 Bigdecimal。

如果你得到无效的double类型数据，那么你需要手动处理NaN。