Problem correctly adding boolean value to mysql via spring jpa

Question

So I want to persist some objects to SQL. This is my table:

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()

);

Right now, I'm working on a Spring Boot application that uses JpaRepository. This is my class.

@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;
    }
}

My repository class looks like this:

public interface UserSegmentRepository extends JpaRepository<UserSegment, Integer> {

}

Now, I have the code that actually sticks to this.

I call the constructor first and then the standard .save() method.

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

The name, count and fileUrl are generated correctly. I know this because I debugged and evaluated it before the .save() statement. I have attached the screenshot of the same.

However, when actually performing the save, I get this SQL error. I even printed some logs. It seems like all values ​​are being set to 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

I solved my problem. I see that all parameters are treated as VARCHAR when binding, although this is not the case.

Elsewhere in my code I have a converter labeled @Converter(autoApply= true). This converter works on VARCHAR fields and is automatically applied to all fields in all my entities. This was unintentional on my part.

If you want an automatically applied converter, some fields can override this converter by additionally specifying more properties in the @Column annotation. Below are the modifications that worked for me.

@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;
    }
}