How to solve "org.springframework.dao.DataIntegrityViolationException: could not execute query; SQL"

huangapple 未分类评论57阅读模式
英文:

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 = &quot;city_attributes&quot;)
public class City {
	
	@javax.persistence.Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = &quot;Id&quot;)
	private Long id;
	
	@Column(name = &quot;City&quot;)
	private String name;
	
	@Column(name = &quot;Country&quot;)
	private String country;
	
	@Column(name = &quot;Latitude&quot;)
	private String latitude;
	
	@Column(name = &quot;Longitude&quot;)
	private String longitude;
	
	@OneToMany(mappedBy = &quot;city&quot;)
	private Collection&lt;Temperature&gt; temperatures = new LinkedList&lt;Temperature&gt;();
	
	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 &quot;City [Id=&quot; + id + &quot;, name=&quot; + name + &quot;, country=&quot; + country + &quot;, latitude=&quot; + latitude + &quot;, longitude=&quot;
				+ longitude + &quot;]&quot;;
	}

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public Collection&lt;Temperature&gt; getTemperatures() {
		return temperatures;
	}

	public void setTemperatures(Collection&lt;Temperature&gt; temperatures) {
		this.temperatures = temperatures;
	}
	
	
}

And

@Entity
@Table(name = &quot;temperature&quot;)
public class Temperature {
	
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;
	
	@Temporal(TemporalType.DATE)
	private Date dateTime;
	
	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = &quot;Id&quot;, nullable = false)
	private City city;
	
	@Column(name = &quot;TemperatureValue&quot;)
	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(&quot;temperature&quot;, 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&lt;Temperature&gt; findTempByCityId(Long id) {
	return temperatureRepository.findByCityId(id);
}
public List&lt;Temperature&gt; findByCityId(Long id);

答案1

得分: 0

Temperature中,@JoinColumn(name = "Id", nullable = false)看起来很可疑,因为它被映射到了id列。我怀疑它应该是id_city或类似的内容。这可能会导致错误。

英文:

In the Temperature the @JoinColumn(name = &quot;Id&quot;, 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.

huangapple
  • 本文由 发表于 2020年7月27日 19:35:23
  • 转载请务必保留本文链接:https://java.coder-hub.com/63114475.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定