Issue with correctly adding a boolean value into mysql through spring jpa-CodePudding

So I want to persist some objects to SQL. Here 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()

);

Now, I'm using 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 :

public interface UserSegmentRepository extends JpaRepository<UserSegment, Integer> {

}

Now, the code where I actually persist this.

I'm calling the constructor 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 it and evaluated it just before the .save() statement. I have attached a screenshot of the same.

However, I am getting this SQL error when the save is actually executed. I even printed some logs. It seems that 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]

CodePudding user response：

I solved my issue. I saw that all the parameters were being treated as VARCHAR while binding even though that is not the case.

Elsewhere in my code I had a converter marked with @Converter(autoApply= true). This converter was for a VARCHAR field and this was being auto-applied to all fields in all my entities. This was unintentional from my side.

In case one wants to have a converter that autoapplies, certain fields can override this converter by additionally specifying more of the attributes in the @Column annotation. Following 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;
    }
}