透過 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;
    }
}