英文:
How to solve "org.springframework.dao.DataIntegrityViolationException: could not execute query; SQL"
问题
以下是你要翻译的内容:
我试图通过给定的城市ID从“Temperature”表中获取所有项。出现了一些问题,我收到了以下错误消息:
org.springframework.dao.DataIntegrityViolationException: 无法执行查询;SQL [select temperatur0_.id as id1_3_,
temperatur0_.Id as Id1_3_, temperatur0_.dateTime as dateTime2_3_,
temperatur0_.TemperatureValue as Temperat3_3_ from temperature
temperatur0_ left outer join city_attributes city1_ on
temperatur0_.Id=city1_.Id where city1_.Id=?]; 嵌套异常是
org.hibernate.exception.DataException: 无法执行查询
我对Java还不熟悉,不确定我是否正确使用了表关系的映射。
@Entity
@Table(name = "city_attributes")
public class City {
@javax.persistence.Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "Id")
private Long id;
@Column(name = "City")
private String name;
@Column(name = "Country")
private String country;
@Column(name = "Latitude")
private String latitude;
@Column(name = "Longitude")
private String longitude;
@OneToMany(mappedBy = "city")
private Collection<Temperature> temperatures = new LinkedList<Temperature>();
// ...其他方法...
}
@Entity
@Table(name = "temperature")
public class Temperature {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Temporal(TemporalType.DATE)
private Date dateTime;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "Id", nullable = false)
private City city;
@Column(name = "TemperatureValue")
private double temperatureValue;
// ...其他方法...
}
try {
city = cityService.findCityByName(name);
Long cityId = city.getId();
temperature = temperatureService.findTempByCityId(cityId);
map.addObject("temperature", temperature);
} catch (Exception e) {
System.out.println(e.toString());
}
=== 更新 ===
当我调试控制器中的方法时,我发现了这个错误:
> java.sql.SQLDataException: 无法从字符串“1/1/2015 0:00”确定值类型
@Robert Niestroj,这是我TemperatureService和TemperatureRepository的实现:
``` java
@Override
public List<Temperature> findTempByCityId(Long id) {
return temperatureRepository.findByCityId(id);
}
public List<Temperature> findByCityId(Long id);
英文:
I try to get all items from table Temperature by a given city id. Something went wrong and I receive the following error:
> org.springframework.dao.DataIntegrityViolationException: could not
> execute query; SQL [select temperatur0_.id as id1_3_,
> temperatur0_.Id as Id1_3_, temperatur0_.dateTime as dateTime2_3_,
> temperatur0_.TemperatureValue as Temperat3_3_ from temperature
> temperatur0_ left outer join city_attributes city1_ on
> temperatur0_.Id=city1_.Id where city1_.Id=?]; nested exception is
> org.hibernate.exception.DataException: could not execute query
I am new to java and I don't know if I used in a correct way the mapping for table relationships.
@Entity
@Table(name = "city_attributes")
public class City {
@javax.persistence.Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "Id")
private Long id;
@Column(name = "City")
private String name;
@Column(name = "Country")
private String country;
@Column(name = "Latitude")
private String latitude;
@Column(name = "Longitude")
private String longitude;
@OneToMany(mappedBy = "city")
private Collection<Temperature> temperatures = new LinkedList<Temperature>();
public City() {}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
public String getLatitude() {
return latitude;
}
public void setLatitude(String latitude) {
this.latitude = latitude;
}
public String getLongitude() {
return longitude;
}
public void setLongitude(String longitude) {
this.longitude = longitude;
}
@Override
public String toString() {
return "City [Id=" + id + ", name=" + name + ", country=" + country + ", latitude=" + latitude + ", longitude="
+ longitude + "]";
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Collection<Temperature> getTemperatures() {
return temperatures;
}
public void setTemperatures(Collection<Temperature> temperatures) {
this.temperatures = temperatures;
}
}
And
@Entity
@Table(name = "temperature")
public class Temperature {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Temporal(TemporalType.DATE)
private Date dateTime;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "Id", nullable = false)
private City city;
@Column(name = "TemperatureValue")
private double temperatureValue;
public Temperature() {}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Date getDateTime() {
return dateTime;
}
public void setDateTime(Date dateTime) {
this.dateTime = dateTime;
}
public City getCity() {
return city;
}
public void setCity(City city) {
this.city = city;
}
public double getTemperatureValue() {
return temperatureValue;
}
public void setTemperatureValue(double temperatureValue) {
this.temperatureValue = temperatureValue;
}
}
try {
city = cityService.findCityByName(name);
Long cityId = city.getId();
temperature = temperatureService.findTempByCityId(cityId);
map.addObject("temperature", temperature);
} catch (Exception e) {
System.out.println(e.toString());
}
===
Updated
I found this error when I debug the method from controller
> java.sql.SQLDataException: Cannot determine value type from string
> '1/1/2015 0:00'
@Robert Niestroj here is my implementation for TemperatureService and TemperatureRepository
@Override
public List<Temperature> findTempByCityId(Long id) {
return temperatureRepository.findByCityId(id);
}
public List<Temperature> findByCityId(Long id);
答案1
得分: 0
在Temperature
中,@JoinColumn(name = "Id", nullable = false)
看起来很可疑,因为它被映射到了id列。我怀疑它应该是id_city
或类似的内容。这可能会导致错误。
英文:
In the Temperature
the @JoinColumn(name = "Id", nullable = false)
looks suspicios as it's mapped to the id column. I suspect it schould be id_city
or sth like that. That might cause the error.
专注分享java语言的经验与见解,让所有开发者获益!
评论