Java SpringBoot Mybatis 日期类型属性空值保存出现异常

Question

日期字段空导致保存异常

数据库端表结构

可以看到字段 bill_date 是 datetime 日期类型，没有设置为 not null，那么如果前段传递来的对象中该属性为空，应该可以保存。

HTTP请求测试代码

测试代码如下：

    @Test
    public void test01() throws Exception {
        RawMain record = new RawMain();
        record.setBill_type("1001");
//        record.setBill_date(new Date());

        ObjectMapper mapper = new ObjectMapper();
        ObjectWriter ow = mapper.writer().withDefaultPrettyPrinter();
        java.lang.String requestJson = ow.writeValueAsString(record);

        System.out.print("即将写入的数据是：" + requestJson);
        String result = mockMvc.perform(MockMvcRequestBuilders.post("/rawMain/insert")//发送 get 请求
                .contentType(MediaType.APPLICATION_JSON_UTF8).content(requestJson))// 发送的请求中带名称为 username 的参数
                .andExpect(MockMvcResultMatchers.status().isOk())// 要求返回200
                .andReturn().getResponse().getContentAsString();
        System.out.println("插入后返回：" + result);
    }

使用 SpringBootTest发送 http 请求测试保存，手动创建即将要保存的对象，只设置了一个属性 bill_type，其他属性都没有设置，并且数据库端对应表中的字段都没有设置必填，照理应该能保存成功，但是执行后报错：

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
### The error may exist in file [D:\Java\ideaProject\femis\target\classes\mapper\business\RawMainMapper.xml]
### The error may involve com.ccsoft.femis.dao.RawMainMapper.insertSelective-Inline
### The error occurred while setting parameters
### SQL: insert into raw_main
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

注意上面的 The error occurred while setting parameters 可以看出是传入的参数有问题

反证

就如上面的 java 代码，把设置属性 bill_date 为当前日期的代码行 record.setBill_date(new Date()); 去掉注释再次执行测试，成功写入数据到数据库了。

问题

那么问题来了，明明数据库端没有设置必填，请求参数（表对应的对象）中该属性为空应该能写入，但实际情况是只有设置了日期才能成功写入，这是哪里有问题？

补充

打印出来的发送请求中带的参数对象是：
RawMain(iid=null, seq_month=null, seq_day=null, seq_month_all=null, seq_day_all=null, bill_type=1001, bill_date=null, transport=null, assemble=null, driver=null, phone=null, car_number=null, freight_charge=null, assemble_charge=null, freight_settlement=null, remark=null, create_time=null, is_test=null, code_month=null, code_day=null, code_month_all=null, code_day_all=null, bill_code=null)

可以看到属性 bill_type 有值，其他属性都是NULL
mybatis的相关 xml 文件的代码如下：

<insert id="insertSelective" parameterType="com.ccsoft.femis.model.RawMain">  
 <selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="iid">  
  SELECT LAST_INSERT_ID()  
  </selectKey>  
  insert into raw_main  
  <trim prefix="(" suffix=")" suffixOverrides=",">  
 <if test="iid != null">  
  iid,  
    </if>  
 <if test="seq_month != null">  
  seq_month,  
    </if>  
 <if test="seq_day != null">  
  seq_day,  
    </if>  
 <if test="seq_month_all != null">  
  seq_month_all,  
    </if>  
 <if test="seq_day_all != null">  
  seq_day_all,  
    </if>  
 <if test="bill_date != null">  
  bill_date,  
    </if>  
 <if test="transport != null">  
  transport,  
    </if>  
 <if test="assemble != null">  
  assemble,  
    </if>  
 <if test="driver != null">  
  driver,  
    </if>  
 <if test="phone != null">  
  phone,  
    </if>  
 <if test="car_number != null">  
  car_number,  
    </if>  
 <if test="freight_charge != null">  
  freight_charge,  
    </if>  
 <if test="assemble_charge != null">  
  assemble_charge,  
    </if>  
 <if test="freight_settlement != null">  
  freight_settlement,  
    </if>  
 <if test="remark != null">  
  remark,  
    </if>  
 <if test="create_time != null">  
  create_time,  
    </if>  
 </trim> <trim prefix="values (" suffix=")" suffixOverrides=",">  
 <if test="iid != null">  
  #{iid,jdbcType=INTEGER},  
    </if>  
 <if test="seq_month != null">  
  #{seq_month,jdbcType=INTEGER},  
    </if>  
 <if test="seq_day != null">  
  #{seq_day,jdbcType=INTEGER},  
    </if>  
 <if test="seq_month_all != null">  
  #{seq_month_all,jdbcType=INTEGER},  
    </if>  
 <if test="seq_day_all != null">  
  #{seq_day_all,jdbcType=INTEGER},  
    </if>  
 <if test="bill_date != null">  
  #{bill_date,jdbcType=TIMESTAMP},  
    </if>  
 <if test="transport != null">  
  #{transport,jdbcType=VARCHAR},  
    </if>  
 <if test="assemble != null">  
  #{assemble,jdbcType=VARCHAR},  
    </if>  
 <if test="driver != null">  
  #{driver,jdbcType=VARCHAR},  
    </if>  
 <if test="phone != null">  
  #{phone,jdbcType=VARCHAR},  
    </if>  
 <if test="car_number != null">  
  #{car_number,jdbcType=VARCHAR},  
    </if>  
 <if test="freight_charge != null">  
  #{freight_charge,jdbcType=DECIMAL},  
    </if>  
 <if test="assemble_charge != null">  
  #{assemble_charge,jdbcType=DECIMAL},  
    </if>  
 <if test="freight_settlement != null">  
  #{freight_settlement,jdbcType=VARCHAR},  
    </if>  
 <if test="remark != null">  
  #{remark,jdbcType=VARCHAR},  
    </if>  
 <if test="create_time != null">  
  #{create_time,jdbcType=TIMESTAMP},  
    </if>  
 </trim></insert>

Answer 1

你看看是不是所有数据库对应字段的值都是null，所以生成了这样的语句：

### SQL: insert into raw_main

才会报语法错误。