Java mysql unknown column in field list

SQL Error: 1054, SQLState: 42S22 , Unknown column ‘NaN’ in ‘field list’ in spring boot jpa application

First of all I’m a beginner in Spring boot JPA project and here I facing some strange problem. When I trying to update the record I’m getting the below exception. This is my first Spring boot JPA project. Please let me know what might when wrong Model Class

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= ) 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); >

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' 

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) 

Источник

Hibernate : Unknown column in field list

I’am trying to do an unidirectional @OneTaMany relationship like in the Hibernate User Guide (2.7.2) but when I try to save the following object in a MariaDB Database:

Filter filter = new Filter("TLS_A320"); filter.addConstraint(new Constraint(ConstraintType.DEPARTURE, "TLS")); filter.addConstraint(new Constraint(ConstraintType.AIRCRAFT, "A320")); session.save(filter); 

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'Filter_idFilter' in 'field list' 

public class Filter < @Id @Column(name = "idFilter") @GeneratedValue(strategy = GenerationType.IDENTITY) private int id; @Column(name = "name") private String name; @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true) private ListconstraintList; //more code >; 

CREATE TABLE `Constraint` ( idConstraint INT NOT NULL AUTO_INCREMENT, type INT NOT NULL, value VARCHAR(10) NOT NULL, PRIMARY KEY (idConstraint) ); CREATE TABLE Filter ( idFilter INT NOT NULL AUTO_INCREMENT, name VARCHAR(50) UNIQUE NOT NULL, PRIMARY KEY (idFilter) ); CREATE TABLE Filter_Constraint ( idFilter INT UNIQUE NOT NULL, idConstraint INT NOT NULL, CONSTRAINT fk_Filter_Constraint_Filter FOREIGN KEY (idFilter) REFERENCES Filter(idFilter), CONSTRAINT fk_Filter_Constraint_Constraint FOREIGN KEY (idConstraint) REFERENCES `Constraint`(idConstraint) ); 

It seems to me that that Filter and Constraint insertions are fine and the exception happens when inserting in the Filter_Constraint table :

DEBUG org.hibernate.SQL - insert into Filter (name) values (?) DEBUG org.hibernate.id.IdentifierGeneratorHelper - Natively generated identity: 4 DEBUG org.hibernate.SQL - insert into `Constraint` (type, value) values (?, ?) DEBUG org.hibernate.id.IdentifierGeneratorHelper - Natively generated identity: 5 DEBUG org.hibernate.SQL - insert into `Constraint` (type, value) values (?, ?) DEBUG org.hibernate.id.IdentifierGeneratorHelper - Natively generated identity: 6 DEBUG org.hibernate.SQL - insert into `Filter_Constraint` (Filter_idFilter, `constraintList_idConstraint`) values (?, ?) DEBUG org.hibernate.engine.jdbc.spi.SqlExceptionHelper - could not execute statement [n/a] DEBUG com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'Filter_idFilter' in 'field list' 

Источник

java.sql.SQLSyntaxErrorException: Unknown column in ‘field list’

I am trying to work on a OneToMany relation using Hibernate. I am using @Temporal annotation to tell hibernate about the Data field. I am not sure why am I getting this error here. Looks like there is a problem with the Date format. Please let me know how to solve it. Customer

package regular; import java.util.List; import javax.persistence.CascadeType; import javax.persistence.Entity; import javax.persistence.FetchType; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.JoinColumn; import javax.persistence.OneToMany; @Entity public class Customers < @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int customer_id; private String customerName; private String contactName; private String address; private String city; private String postalCode; private String country; @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, orphanRemoval = true) @JoinColumn(name = "customer_id") private Listorder; public Customers() < >public List getOrder() < return order; >public void setOrder(List order) < this.order = order; >public Customers(String customerName, String contactName, String address, String city, String postalCode, String country, List order) < this.customerName = customerName; this.contactName = contactName; this.address = address; this.city = city; this.postalCode = postalCode; this.country = country; this.order = order; >public String getCustomerName() < return customerName; >public void setCustomerName(String customerName) < this.customerName = customerName; >public String getContactName() < return contactName; >public void setContactName(String contactName) < this.contactName = contactName; >public String getAddress() < return address; >public void setAddress(String address) < this.address = address; >public String getCity() < return city; >public void setCity(String city) < this.city = city; >public String getPostalCode() < return postalCode; >public void setPostalCode(String postalCode) < this.postalCode = postalCode; >public String getCountry() < return country; >public void setCountry(String country) < this.country = country; >public int getCustomer_id() < return customer_id; >@Override public String toString() < return "Customers [customer_id=" + customer_id + ", customerName=" + customerName + ", contactName=" + contactName + ", address=" + address + ", city=" + city + ", postalCode=" + postalCode + ", country=" + country + ", order=" + order + "]"; >> 

package regular; import java.util.Date; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Temporal; import javax.persistence.TemporalType; @Entity public class Orders < @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private int orderId; @Temporal(value = TemporalType.TIMESTAMP) private Date orderDate; private String productName; private int quantity; public Orders(String productName, int quantity) < this.orderDate = new Date(); this.productName = productName; this.quantity = quantity; >public Orders() < >public Date getOrderDate() < return orderDate; >public void setOrderDate(Date orderDate) < this.orderDate = orderDate; >public String getProductName() < return productName; >public void setProductName(String productName) < this.productName = productName; >public int getQuantity() < return quantity; >public void setQuantity(int quantity) < this.quantity = quantity; >public int getOrderId() < return orderId; >@Override public String toString() < return "Orders [orderId=" + orderId + ", orderDate=" + orderDate + ", productName=" + productName + ", quantity=" + quantity + "]"; >> 

package regular; import java.util.ArrayList; import java.util.List; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.hibernate.cfg.Configuration; public class Runner < public static void main(String[] args) < SessionFactory sessionFactory = new Configuration().configure("/regular/hibernate.cfg.xml") .addAnnotatedClass(Customers.class).addAnnotatedClass(Orders.class).buildSessionFactory(); Session session = sessionFactory.openSession(); session.beginTransaction(); Customers customer = new Customers(); customer.setCustomerName("Robert Bosch"); customer.setAddress("404 California Ave"); customer.setCity("California"); customer.setPostalCode("60466"); customer.setCountry("USA"); Listorders = new ArrayList<>(); orders.add(new Orders("Car", 4)); orders.add(new Orders("Headphones", 6)); customer.setOrder(orders); session.save(customer); session.close(); > > 

Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'orderDate' in 'field list' at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:686) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:663) at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:653) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115) at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2041) at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1827) at com.mysql.cj.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2041) at com.mysql.cj.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:1977) at com.mysql.cj.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:4963) at com.mysql.cj.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1962) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:204) . 41 more 

Источник