sql增强中无法支持自增id

qubo · 发表于 2022-2-25 16:58:36

本帖最后由 qubo 于 2022-3-3 14:00 编辑

# 版本号：
Jeecg Boot Version: 2.4.2
Spring Boot Version: 2.3.5.RELEASE (v2.3.5.RELEASE)
# 问题描述：
在在线表单的sql增强中输入sql语句是个很强的功能，但是在insert时候出现了错误

正常在mysql执行如下语句
insert into table ces(create_by, create_time, update_by, update_time, sys_org_code, name) select "管理员", SYSDATE(), "管理员", SYSDATE(), "A01", "张三"

里面没指定id字段是没有问题的。因为mysql会提供ces的主键自增设置补上id。

改写成jeecg的sql增强之后为：
insert into table ces(create_by, create_time, update_by, update_time, sys_org_code, name) select #{sys_user_name}, #{sys_time}, #{sys_user_name}, #{sys_time}, #{sys_org_code}, "张三"

该语句也应该能正常执行，但是在jeecg中报错如下：
执行失败, ### Error updating database. Cause: java.sql.SQLException: Field 'id' doesn't have a default value ### The error may exist in URL [jar:file:/D:/Program%20Files/apache-maven-3.8.4/repository/org/jeecgframework/boot/hibernate-re/3.0.02/hibernate-re-3.0.02.jar!/org/jeecg/modules/online/cgform/mapper/xml/OnlCgformHeadMapper.xml] ### The error may involve org.jeecg.modules.online.cgform.mapper.OnlCgformHeadMapper.executeDDL-Inline ### The error occurred while setting parameters ### SQL: insert into bumenchuli(create_by,create_time,update_by, update_time, sys_org_code, shipinid, bumen, chulijieguo) ( select "管理员", SYSDATE(), "管理员", SYSDATE(), "A01", t2.id, "A01","wcl" from ( select * from zhengju where id='1497097392108826625' ) t1 left join shipinjilu t2 on t1.zhengjuwenjian = t2.spwj ) ### Cause: java.sql.SQLException: Field 'id' doesn't have a default value ; Field 'id' doesn't have a default value; nested exception is java.sql.SQLException: Field 'id' doesn't have a default value

原因应该是jeecg没有自动提供#{sys_table_auto_id}类似的系统变量，如果提供了，用户就可以写如下语句
insert into table ces(id, create_by, create_time, update_by, update_time, sys_org_code, name) select #{sys_table_auto_id}, #{sys_user_name}, #{sys_time}, #{sys_user_name}, #{sys_time}, #{sys_org_code}, "张三"

来完成id自增

# 截图&代码：

insert into table ces(create_by, create_time, update_by, update_time, sys_org_code, name) select "管理员", SYSDATE(), "管理员", SYSDATE(), "A01", "张三"