error when adding data into Mybatis: ERROR: Field * doesn’t have a default value

created at 08-19-2021 views: 9

Table

CREATE TABLE `apply_log` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `exception_date` date NOT NULL DEFAULT '0000-00-00' COMMENT '异常日期',
  `apply_date` date NOT NULL DEFAULT '2021-00-00' COMMENT '申请日期',
  `apply_person` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '申请人',
  `apply_person_id` int(10) NOT NULL COMMENT '申请人id',
  `operate_person` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '审批人',
  `operate_person_id` int(10) NOT NULL COMMENT '审批人id',
  `apply_result` tinyint(1) NOT NULL COMMENT '申请结果,1.申请中.2.审批拒绝,3.审批同意',
  `comment` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '备注',
  `apply_id` int(10) NOT NULL COMMENT '绑定申请信息',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='异常申请操作日志'

The exception_date is set to non-empty and the default value is set to 0000-00-00
apply_date is not empty and set the default value 2021-00-00
Other fields are set to non-empty, and there is no default value.

Now start to perform the operation of inserting data in the database:

INSERT INTO apply_log(apply_person) VALUES('yyds');

result

Execution result

If set to not null in mysql, the default value of DATA type is 0000-00-00, the default value of int is 0, and the default value of varchar type is an empty string.

The same sql inserted into the database through mybatis will report an error
Do not insert not null fields
Entity class

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class ApplyLog1 {
   private Integer id;
   private String exceptionDate; 
   private String applyDate;
   private String applyPerson;
   private Integer applyPersonId;
   private String operatePerson;
   private Integer operatePersonId;
   private Integer applyResult;
   private String comment ;
   private Integer applyId ;
}

mybatis

    <insert id="insertGao" >
        INSERT INTO apply_log(apply_person) VALUE ('17yyds')
    </insert>

mapper

Integer insertGao();

The execution directly reports that apply_person_id is not empty, and the default default value of mybatis has not taken effect.

error information

Don't insert the nut null default field

xml layer
    <insert id="insertTwo">
        insert into apply_log(apply_person,apply_person_id,operate_person,operate_person_id,apply_result
                             ,comment,apply_id) values ('1111',1111,'1111',1111,2,'1111',11)
    </insert>
mapper layer
    Integer insertTwo();
        @Test
    public void test8() {
        applyLog1Mapper.insertTwo();
    }

Inserted successfully:

Inserted successfully

Non-empty fields with default values set by yourself can be inserted successfully, and related fields use default values

How to design a universal insert statement

xml layer
    <insert id="insert" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
        insert into apply_log(exception_date,apply_date,apply_person,apply_person_id,operate_person,operate_person_id,apply_result
        ,comment,apply_id) values (#{applyLog.exceptionDate},#{applyLog.applyDate},#{applyLog.applyPerson},#{applyLog.applyPersonId}
        ,#{applyLog.operatePerson},#{applyLog.operatePersonId},#{applyLog.applyResult},#{applyLog.comment},#{applyLog.applyId})
    </insert>
mapper layer
    Integer insert(@Param("applyLog") ApplyLog1 applyLog);

If the default value is not set for the attribute of the entity class, the default value of the package type is null, so an error will be reported when inserting


    @Test
    public void test9() {
        ApplyLog1 applyLog1 = new ApplyLog1();
        applyLog1.setApplyPerson("18yyds");
        applyLog1Mapper.insert(applyLog1);
    }

The inserted values are all null

Method 1: Set the default value on the entity class

In the new object, set the initial value of the entity class

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class ApplyLog1 {
    private Integer id;
    private String exceptionDate="0000-00-00";
    private String applyDate="2021-00-00";
    private String applyPerson="19yyds";
    private Integer applyPersonId=19;
    private String operatePerson="19yyds";
    private Integer operatePersonId=19;
    private Integer applyResult=2;
    private String comment="19yyds";
    private Integer applyId=89;
}

test

    @Test
    public void test9() {
        ApplyLog1 applyLog1 = new ApplyLog1();
        applyLog1.setApplyPerson("独孤求败");
        applyLog1Mapper.insert(applyLog1);
    }

The insertion is successful and the default value of the entity class is used

successful insertion

Method 2: Use tags

Modify the table structure to establish default values for all fields

Create Table

CREATE TABLE `apply_log` (
 `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键',
 `exception_date` date NOT NULL DEFAULT '0000-00-00' COMMENT '异常日期',
 `apply_date` date NOT NULL DEFAULT '2021-00-00' COMMENT '申请日期',
 `apply_person` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '申请人',
 `apply_person_id` int(10) NOT NULL COMMENT '申请人id',
 `operate_person` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '审批人',
 `operate_person_id` int(10) NOT NULL COMMENT '审批人id',
 `apply_result` tinyint(1) NOT NULL COMMENT '申请结果,1.申请中.2.审批拒绝,3.审批同意',
 `comment` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '备注',
 `apply_id` int(10) NOT NULL COMMENT '绑定申请信息',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='异常申请操作日志'

Entity class

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class ApplyLog1 {
   private Integer id;
   private String exceptionDate;
   private String applyDate;
   private String applyPerson;
   private Integer applyPersonId;
   private String operatePerson;
   private Integer operatePersonId;
   private Integer applyResult;
   private String comment;
   private Integer applyId;
}

mapper, use tags to remove the extra "," characters that may appear at the beginning and end of the fragment

    <insert id="insertThree" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
       insert into apply_log(<include refid="baseColumn"></include>) values (<include refid="baseProperty"></include>)
   </insert>
   <sql id="baseColumn">
       <trim suffixOverrides=",">
           <if test="applyLog.exceptionDate != null">exception_date,</if>
           <if test="applyLog.applyDate != null">apply_date,</if>
           <if test="applyLog.applyPerson != null">apply_person,</if>
           <if test="applyLog.applyPersonId != null">apply_person_id,</if>
           <if test="applyLog.operatePerson != null">operate_person,</if>
           <if test="applyLog.operatePersonId != null">operate_person_id,</if>
           <if test="applyLog.applyResult != null">apply_result,</if>
           <if test="applyLog.comment != null">comment,</if>
           <if test="applyLog.applyId != null">apply_id,</if>
       </trim>
   </sql>

   <sql id="baseProperty">
       <trim suffixOverrides=",">
           <if test="applyLog.exceptionDate != null">#{applyLog.exceptionDate},</if>
           <if test="applyLog.applyDate != null">#{applyLog.applyDate},</if>
           <if test="applyLog.applyPerson != null">#{applyLog.applyPerson},</if>
           <if test="applyLog.applyPersonId != null">#{applyLog.applyPersonId},</if>
           <if test="applyLog.operatePerson != null">#{applyLog.operatePerson},</if>
           <if test="applyLog.operatePersonId != null">#{applyLog.operatePersonId},</if>
           <if test="applyLog.applyResult != null">#{applyLog.applyResult},</if>
           <if test="applyLog.comment != null">#{applyLog.comment},</if>
           <if test="applyLog.applyId != null">#{applyLog.applyId},</if>
       </trim>
   </sql>

Use related methods

    @Test
    public void test10() {
        ApplyLog1 applyLog1 = new ApplyLog1();
        applyLog1.setApplyPerson("天下无敌");
        applyLog1Mapper.insertThree(applyLog1);
    }

inserted successfully:

inserted successfully

created at:08-19-2021
edited at: 08-19-2021: