一键生成insert，update，delete，的mybatis sql 语句

由于最近写增删改查，对与开发来说，编写一个insert和update 语句是十分耗时耗力的，因为有大量的字段要复制粘贴。粘贴完还要去比对一下有没有漏粘的，很耗费精力，于是就想着写一个sql，通过手动指定哪张表，运行这个sql后，程序便自动将表中的字段补充上去。自己在做一点微调即可，省下来大部分的开发时间，我们只需要将结果粘贴到mybatis.xml 映射文件中即可

实现效果

DWS数据库 执行

SELECT 'insert into '||TABLE_SCHEMA||'.'|| TABLE_NAME || '(
'||STRING_AGG(column_name, ',
')||'
)values (
'||STRING_AGG('#{'||column_name||', jdbcType=VARCHAR}', ',
') || '
)' as insertSql,
'insert into '||TABLE_SCHEMA || '.' || TABLE_NAME || '
'||'
select
'||STRING_AGG('#{'||column_name || ', jdbcType=VARCHAR} as ' || column_name, '
')|| '
from dual ' || '
' as batchInsertSql, 'update '||TABLE_SCHEMA || '.'|| TABLE_NAME || '
set
' || STRING_AGG(column_name || ' = #{' ||column_name||', jdbcType=VARCHAR}', ',
') || '
where 字段 = #{old_字段}' as updateSql,

'delete'||TABLE_SCHEMA || '.' || TABLE_NAME ||' where主键1 = #{old_主键1} 'as deleteSql,
'select * from ' || TABLE_SCHEMA || '.' || TABLE_NAME as "select",

'merge into' || TABLE_SCHEMA || '.' || TABLE_NAME || 't using (
select
'||STRING_AGG('#{' || column_name || ', jdbcType=VARCHAR} as '||column_name, ',
') || 'from dual ) tt
on(t.主键1 = tt.主键1
and t.主键2 = tt.主键2
) when matched then
update set
'|| STRING_AGG ('t.'||column_name || ' = tt.' || column_name, ',
') || 'when not matched then
insert (
'|| STRING_AGG ('t.' || column_name, ',
') || 'values (
' || STRING_AGG( 'tt.' || column_name, ',
')|| ')' as mergelntoSql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='dm_position'--替换为您的数据库名
AND TABLE_NAME='cfg_tnc_field'--替换你的表名
GROUP BY TABLE_SCHEMA, TABLE_NAME;

DWS 和Oracel 数据库可以执行

SELECT 'insert into '||OWNER||'.'|| TABLE_NAME || '(
'||LISTAGG(column_name, ',
') WITHIN GROUP (ORDER BY COLUMN_ID)||'
)values (
'||LISTAGG('#{'||column_name||', jdbcType=VARCHAR}', ',
') WITHIN GROUP (ORDER BY COLUMN_ID) || '
)' as insertSql,
'insert into '||OWNER|| '.' || TABLE_NAME || '
'||'
select
'||LISTAGG('#{'||column_name || ', jdbcType=VARCHAR} as ' || column_name, '
') WITHIN GROUP (ORDER BY COLUMN_ID)|| '
from dual ' || '
' as batchInsertSql,

 'update '||OWNER|| '.'|| TABLE_NAME || '
set
' || LISTAGG(column_name || ' = #{' ||column_name||', jdbcType=VARCHAR}', ',
') WITHIN GROUP (ORDER BY COLUMN_ID) || '
where 字段 = #{old_字段}' as updateSql,

'delete'||OWNER|| '.' || TABLE_NAME ||' where主键1 = #{old_主键1} 'as deleteSql,
'select * from ' || OWNER|| '.' || TABLE_NAME as "select",

'merge into' || OWNER|| '.' || TABLE_NAME || 't using (
select
'||LISTAGG('#{' || column_name || ', jdbcType=VARCHAR} as '||column_name, ',
') WITHIN GROUP (ORDER BY COLUMN_ID) || 'from dual ) tt
on(t.主键1 = tt.主键1
and t.主键2 = tt.主键2
) when matched then
update set
'|| LISTAGG('t.'||column_name || ' = tt.' || column_name, ',
')  WITHIN GROUP (ORDER BY COLUMN_ID)|| 'when not matched then
insert (
'|| LISTAGG('t.' || column_name, ',
') WITHIN GROUP (ORDER BY COLUMN_ID) || 'values (
' || LISTAGG( 'tt.' || column_name, ',
') WITHIN GROUP (ORDER BY COLUMN_ID)|| ')' as mergelntoSql
FROM ALL_TAB_COLUMNS
WHERE OWNER='dm_position'--替换为您的数据库名,oracel 要大写，dws 要小写
AND TABLE_NAME='cfg_tnc_field'--替换你的表名,oracel 要大写，dws 要小写
GROUP BY OWNER, TABLE_NAME;