Java SpringBoot Mybatis 日期类型属性空值保存出现异常
日期字段空导致保存异常
数据库端表结构
可以看到字段 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>
回答:
你看看是不是所有数据库对应字段的值都是null,所以生成了这样的语句:
### SQL: insert into raw_main
才会报语法错误。
以上是 Java SpringBoot Mybatis 日期类型属性空值保存出现异常 的全部内容, 来源链接: utcz.com/p/171459.html