通过 spring jpa 正确将布尔值添加到 mysql 中的问题

Question

所以我想将一些对象持久化到SQL。这是我的桌子：

CREATE TABLE `user_segment`  (
`id` INT(11) PRIMARY KEY AUTO_INCREMENT NOT NULL ,
`name` VARCHAR(50) NOT NULL,
`active` BOOLEAN NOT NULL DEFAULT true,
`count` INT(11) NOT NULL,
`s3_link` VARCHAR(255) NOT NULL,
`created_date` datetime NOT NULL DEFAULT current_timestamp(),
`modified_date` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()

);

现在，我正在使用一个使用 JpaRepository 的 Spring Boot 应用程序。这是我的课。

@Data
@Entity
@Table(name = "user_segment")
public class UserSegment {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "name")
    private String name;
    
    @Column(name = "active")
    private Boolean active = true;

    @Column(name = "count")
    private Integer count;

    @Column(name = "s3_link")
    private String s3Link;

    @CreatedDate
    @Column(name = "created_date")
    private Date createdDate = new Date();

    public UserSegment(String name, String s3Link,int count) {
        this.name = name;
        this.s3Link = s3Link;
        this.count = count;
    }
}

我的存储库类如下所示：

public interface UserSegmentRepository extends JpaRepository<UserSegment, Integer> {

}

现在，我实际坚持这一点的代码。

我先调用构造函数，然后调用标准 .save() 方法。

UserSegment userSegment = new UserSegment(name, fileUrl, count);
        userSegmentRepository.save(userSegment);

名称、计数和 fileUrl 已正确生成。我知道这一点是因为我在 .save() 语句之前对其进行了调试和评估。我已附上相同的屏幕截图。

但是，当实际执行保存时，我收到此 SQL 错误。我什至打印了一些日志。似乎所有值都被设置为 null：

org.hibernate.SQL                        : insert into user_segment (`active`, `count`, created_date, `name`, s3_link) values (?, ?, ?, ?, ?)
2022-02-22 20:38:06.061 DEBUG 28799 --- [nio-8080-exec-2] tributeConverterSqlTypeDescriptorAdapter : Converted value on binding : null -> null
2022-02-22 20:38:06.063 TRACE 28799 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [null]
2022-02-22 20:38:06.063 DEBUG 28799 --- [nio-8080-exec-2] tributeConverterSqlTypeDescriptorAdapter : Converted value on binding : null -> null
2022-02-22 20:38:06.063 TRACE 28799 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [null]
2022-02-22 20:38:06.063 DEBUG 28799 --- [nio-8080-exec-2] tributeConverterSqlTypeDescriptorAdapter : Converted value on binding : null -> null
2022-02-22 20:38:06.063 TRACE 28799 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [VARCHAR] - [null]
2022-02-22 20:38:06.063 DEBUG 28799 --- [nio-8080-exec-2] tributeConverterSqlTypeDescriptorAdapter : Converted value on binding : null -> null
2022-02-22 20:38:06.063 TRACE 28799 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [4] as [VARCHAR] - [null]
2022-02-22 20:38:06.064 DEBUG 28799 --- [nio-8080-exec-2] tributeConverterSqlTypeDescriptorAdapter : Converted value on binding : null -> null
2022-02-22 20:38:06.064 TRACE 28799 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [5] as [VARCHAR] - [null]
2022-02-22 20:38:06.106  WARN 28799 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1366, SQLState: 22001
2022-02-22 20:38:06.107 ERROR 28799 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : Data truncation: Incorrect integer value: 'null' for column 'active' at row 1
2022-02-22 20:38:06.216 ERROR 28799 --- [nio-8080-exec-2] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [/notificationservice/api/v1] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not execute statement] with root cause

com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Incorrect integer value: 'null' for column 'active' at row 1
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104) ~[mysql-connector-java-8.0.16.jar:8.0.16]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:955) ~[mysql-connector-java-8.0.16.jar:8.0.16]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1094) ~[mysql-connector-java-8.0.16.jar:8.0.16]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1042) ~[mysql-connector-java-8.0.16.jar:8.0.16]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1345) ~[mysql-connector-java-8.0.16.jar:8.0.16]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1027) ~[mysql-connector-java-8.0.16.jar:8.0.16]

Answer 1

我解决了我的问题。我看到所有参数在绑定时都被视为 VARCHAR，尽管情况并非如此。

在我的代码的其他地方，我有一个转换器标记为 @Converter(autoApply= true).该转换器适用于 VARCHAR 字段，并且会自动应用于我所有实体中的所有字段。这是我无意的。

如果想要一个自动应用的转换器，某些字段可以通过在 @Column 注释中额外指定更多属性来覆盖此转换器。以下是对我有用的修改。

@Data
@Entity
@Table(name = "user_segment")
@NoArgsConstructor
public class UserSegment {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @Column(name = "`name`", nullable = false, length = 50)
    private String name;

    @Column(name = "`active`", nullable = false, columnDefinition = "default bit 1")
    private boolean active = true;

    @Column(name = "`count`", nullable = false, columnDefinition = "default integer 0")
    private int count;

    @Column(name = "s3_link", nullable = false, length = 255)
    private String s3Link;

    @CreatedDate
    @Temporal(value = TemporalType.TIMESTAMP)
    @Column(name = "created_date", nullable = false, columnDefinition = "default datetime current_timestamp()")
    private Date createdDate;

    public UserSegment(String name, String s3Link,int count) {
        this.name = name;
        this.s3Link = s3Link;
        this.count = count;
    }
}