sql增强中无法支持自增id
本帖最后由 qubo 于 2022-3-3 14:00 编辑# 版本号：
JeecgBoot 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 ### 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", "张三"
页:
[1]