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
reconnect the Navicat will solve the problem.
