Mybatis will report error: field * doesn’t have a default value after adding data

Article catalog

After mybatis adds data, it will report error: field * Don’t have a default value database table. If the same SQL is inserted into the database through mybatis, it will report an error. Do not insert the not null field, do not insert the nut null default field

How to design a general insert statement method 1: set the default value on the entity class method 2: use the label

Database 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='异常申请操作日志'

exception_ The date setting is not empty, and the default value ‘0000-00-00’
Apply_ Date setting is not empty, and the default value ‘2021-00-00’
other fields are not empty, and there is no default value.

Now start inserting data into the database

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

Execution result

if not null is set 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 empty string

If the same SQL is inserted into the database through mybatis, an error will be reported

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

Execute direct report apply_ person_ ID is not empty. The default value of mybatis does not take effect

Do not insert nut null default field

xml层
    <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层
    Integer insertTwo();
        @Test
    public void test8() {
        applyLog1Mapper.insertTwo();
    }

Insertion succeeded
non empty fields with their own default values can be successfully inserted, and the default value is used for relevant fields

How to design general insert statements

xml层
    <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层
	Integer insert(@Param("applyLog") ApplyLog1 applyLog);

if no default value is set for the attribute of the entity class, the default value of the encapsulation type is null, so an error will be reported during insertion

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

All inserted values are null

Method 1: set the default value on the entity class

When the new object is, make the entity class set the initial value

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

Perform test

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

Insert succeeded and the default value of entity class is used
[ Insert picture description here]（ https://img-blog.csdnimg.cn/94ccd730abf64893b8927361163fc4b2.png

Method 2: use labels

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 the tag to remove the redundant “,” 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 relevant methods

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

Successfully inserted