Home > Enterprise >  Spring : java.sql.SQLException: Field '**' doesn't have a default value
Spring : java.sql.SQLException: Field '**' doesn't have a default value

Time:03-09

My Entity is a data class has default values and the json is being received in the server in the post api call but one field, which is not present in the data class is always null for some reason.

@Entity
@Table(name = "checking_point")
data class CheckingPointEntity(

    @Column(nullable = false, unique = false, length = 500)
    open var name: String = "",

    @Column(nullable = false, unique = false, length = 500)
    open var description: String = "",

    @Column(nullable = false)
    open var cpTypeId: Int = 0,

    @Column(nullable = false)
    open var isCumulative: Boolean = false,

    @Column(nullable = false)
    open var cpCategoryGroupId: Long = 0L,

    @Column(nullable = false)
    open var isTemplate: Boolean = false,

    @Column(nullable = false)
    open var isTopTemplate: Boolean = false

) : BkpBaseEntity1()

BastEntity1 :

@MappedSuperclass
open class  BkpBaseEntity1 {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    open var id:Long = 0L

    // **************************************************************************
    // *****    Below 4 columns should be there in each and every table   *******
    // **************************************************************************

    @Column(nullable = false, updatable = false,  columnDefinition = "varchar(100) default 'adhoc-sql'")
    open var dbCreateSource: String = BkpConstants.DB_CREATE_SOURCE_NAME;


    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss" )
    @Temporal(TemporalType.DATE)
    @Column(nullable = false,insertable = false, updatable=false, columnDefinition = "datetime DEFAULT now()")
    open var dbCreateDts: Date = Date() //Do not set this field. It will be populated by DB.


    @Column(nullable = false, columnDefinition = "varchar(100) default 'adhoc-sql'")
    open var dbUpdateSource: String = BkpConstants.DB_CREATE_SOURCE_NAME;

    @Column(nullable = false, insertable = false, updatable=false, columnDefinition = "datetime  NOT NULL DEFAULT now() on update now()")
    @Temporal(TemporalType.DATE)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    open var dbUpdateDts: Date = Date() //Do not set this field. It will be populated by DB.

}

Controller :

@RequestMapping(path = [ControllerEndPoints.AddCheckingPoint], method = [RequestMethod.POST])
    fun addCheckingPoint(@RequestBody(required = true) reqData: ChartServerVo): ResponseEntity<ChartAck> {
        mLog("in add cp ")
        var cpId = 0L
        var pcId = 0L
         val cpDto : CheckingPointDto = reqData.checkingPointDto
        val gson = GsonBuilder().setDateFormat("yyyy-MM-dd HH:mm:ss").create()
        val payload = gson.toJson(reqData)
        mLog("json: \n json $payload ")
            mLog("json: saved cpDto name ${cpDto.name}, description ${cpDto.description} cpTypeId ${cpDto.cpTypeId} isCumulative ${cpDto.isCumulative} categoryGroupId ${cpDto.cpCategoryGroupId} isTemplate")
            cpr.save(cpDto.toEntity())
...................
}

Json received by the server has all the fields

    {"checkingPointDto":{"name":"demo","description":"","cpTypeId":1,"isCumulative":false,"cpCategoryGroupId":16,"isTemplate":false,"isTopTemplate":false,"id":0,"dbCreateSource":"","dbCreateDts":"2022-03-08 21:07:32","dbUpdateSource":"","dbUpdateDts":"2022-03-08 21:07:32"},"purusharthChartCpMappingDto":{"id":0,"purusharthChartId":16466443,"cpId":0},"purusharthChartDto":{"id":16466443,"adminID":8,"name":"demo","description":"","userId":8,"startDate":"2022-03-08 21:07:32","endDate":"2022-12-30 21:07:32","isSelfChart":false},"isNewchart":false} 

The error says that a field cp_type_id that isn't anywhere in the entity is null.

java.sql.SQLException: Field 'cp_type_id' doesn't have a default value
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.25.jar:8.0.25]

The only other place that I can find this field is in a .sql file. I am new to spring I do not know what it is supposed to do. As far as my sql knowledge goes it looks ok to me

create table checking_point(
    id int primary key NOT NULL auto_increment,
    name_str_id int,
    foreign key(name_str_id) references localization(id),
    description_str_id varchar(500),
    cp_type_id int,
    foreign key(cp_type_id) references dim_cp_type(id),
    cp_category_group_id int,
    foreign key(cp_category_group_id) references cp_category_group(id),
    repeat_type_id int,
    foreign key(repeat_type_id) references dim_repeat_type(id),
    cp_repeat_reminder_id int,
    is_template boolean,
    is_top_template boolean,
    db_create_dts date,
    db_create_source nvarchar(320),
    db_updated_dts date,
    db_update_source nvarchar(320)
);

What am I missing? why is that filed which isn't even present in the entity in question causing this issue ?

CodePudding user response:

Have you tried to assign a default value to it through SQL query? If you already have a database GUI ( like MySQL workbench )you can just execute this additional query that will set the field to have a default value.

ALTER TABLE `checking_point` ALTER `cp_type_id` SET DEFAULT NULL

Or you can alter your sql query that you already have

create table checking_point(
    id int primary key NOT NULL auto_increment,
    name_str_id int,
    foreign key(name_str_id) references localization(id),
    description_str_id varchar(500),
    cp_type_id int DEFAULT NULL, <-HERE
    foreign key(cp_type_id) references dim_cp_type(id),
    cp_category_group_id int,
    foreign key(cp_category_group_id) references cp_category_group(id),
    repeat_type_id int,
    foreign key(repeat_type_id) references dim_repeat_type(id),
    cp_repeat_reminder_id int,
    is_template boolean,
    is_top_template boolean,
    db_create_dts date,
    db_create_source nvarchar(320),
    db_updated_dts date,
    db_update_source nvarchar(320)
);

With assigning the te default value, you will stop getting these types of exceptions for cp_type_id, same approach goes for any other field that it is complaining about.

CodePudding user response:

another approach if you don't need this null-check you can remove it @Column(nullable = false) as no other properties inside @Column you can remove it from above your wanted field

  • Related