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

日期字段空导致保存异常

数据库端表结构

数据库端日期字段.png

可以看到字段 bill_datedatetime 日期类型,没有设置为 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()); 去掉注释再次执行测试,成功写入数据到数据库了。

问题

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

补充

打印请求参数.png
打印出来的发送请求中带的参数对象是:
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

回到顶部