最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

【已解决】spring boot报错:SqlExceptionHelper.java Data truncation Data too long for column at row 1

Java crifan 445浏览 0评论
折腾:
【未解决】VSCode中用Java的Spring Boot搭建智能电力系统后端框架
期间,调试时
用postman测试post,结果报错:
{
"timestamp": "2020-02-02T06:00:52.390+0000",

"status": 500,

"error": "Internal Server Error",

"message": "could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not execute statement",

"trace": "org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not execute statement\n\tat org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:302)\n\tat org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)\n\tat org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:538)\n\tat 
...
org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)\n\t... 85 more\n",

"path": "/iec104/add/"
}
然后回头看log是:
src/server/xxx/xxx/xxx.log
Caused by: com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Data too long for column 'parse_result' at row 1

at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104) ~[mysql-connector-java-8.0.18.jar:8.0.18]
然后看之前返回的内容:
很明显是 string字符串太长了。
而回去看定义
CREATE TABLE `iec104` (
  `id` int(11) NOT NULL,
  `data` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `parse_result` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
即:
此处字符串最长设置了255,此处长度是:
795个字符,超过255限制了。
所以现在问题转换为:
如何让Spring Boot的JPA在创建类的定义:
src/server/xxx/xxx/src/main/java/com/crifan/xxx/IEC104.java
@Entity // This tells Hibernate to make a table out of this class
public class IEC104 {
  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private Integer id;

  private String data;

  private String parseResult;
时,就指定字符串长度
或者直接手动修改mysql的定义,把
varchar(255)
改为更长的,比如
varchar(2048)
之类的
但是最好还是JPA自动创建时就指定长度
JPA string length
java – JPA – How to set string column to varchar(max) in DDL – Stack Overflow
好像用
@Column(length = 1337)
final String someString;
即可?
最好还是找到定义和官网文档
java – Difference between @Size, @Length and @Column(length=value) when using JPA and Hibernate – Stack Overflow
Column (Java(TM) EE 7 Specification APIs)
Length.List (Hibernate Validator 6.1.2.Final)
javax.validation.constraints (Java(TM) EE 7 Specification APIs)
java – What does the length attribute do when set on the @Column JPA annontation? – Stack Overflow
Column (Java EE 5 SDK)
“length
public abstract int length
(Optional) The column length. (Applies only if a string-valued column is used.)
Default:255″
JPA Tutorial – JPA Column Length Example
去试试
import javax.persistence.Column;
...
@Entity // This tells Hibernate to make a table out of this class
public class IEC104 {
...
  @Column(length=2048)
  private String parseResult;
结果:
可以了:
hibernate @size
Difference Between @Size, @Length, and @Column | Baeldung
Hibernate Tips: Difference Between @Column(length=50) and @Size(max=50)
Size (Hibernate Validator API Documentation)
再去试试:只写size
import javax.validation.constraints.Size;

@Size(min = 1, max = 2048)
private String parseResult;
结果:
也是可以的
效果是一样的。
-》所以此处可以只用@Size的max,而不需要@Column的length了
【总结】
之前代码:
@Entity // This tells Hibernate to make a table out of this class
public class IEC104 {
...
  private String parseResult;
创建的mysql的table的定义是:
  `parse_result` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
即:没有指定长度,默认是255
导致:此处保存长度超过255的字符串,执行mysql时就会报错
解决办法:
增加此处varchar的长度
方式1:通过@Size的max
import javax.validation.constraints.Size;

@Entity // This tells Hibernate to make a table out of this class
public class IEC104 {
...
  @Size(min = 1, max = 2048)
  private String parseResult;
详见:
Size (Hibernate Validator API Documentation)
方式2:通过@Column的length
import javax.persistence.Column;

@Entity // This tells Hibernate to make a table out of this class
public class IEC104 {
...
  @Column(length=2048)
  private String parseResult;
详见:
length() – Column (Java EE 5 SDK)
https://docs.oracle.com/javaee/5/api/javax/persistence/Column.html#length()
Column (Java(TM) EE 7 Specification APIs)
额外解释:
上述两种写法,都会生成对应长度的varchar
  `parse_result` varchar(2048) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
效果基本一样,不过略有区别:
  • @Size:属于Bean Validation的annotation
    • 不仅用于创建表结构
    • 还用于校验:当保存的内容不满足size条件时,会报错
  • @Column:属于JPA的annotation
    • 只用于DDL,即database schema,即表结构中的
      • varchar(xxx)
另外:
  • @Length:属于Hibernate的annotation
    • 此处没去试,有兴趣的自己尝试

转载请注明:在路上 » 【已解决】spring boot报错:SqlExceptionHelper.java Data truncation Data too long for column at row 1

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
86 queries in 0.145 seconds, using 20.74MB memory