java动态生成指定oracle中表的触发器和新建保存数据表

最近接了一个紧急的需求要求给出Oracle表名称生成指定触发器和触发器对应的表，几乎是万能的，就是不支持表中有long类型的数表，因为时间紧而且是工具性质就没有太多注意代码规范请见谅

@RestController
@RequestMapping("/create")
public class CreateTriggerController {
    @Autowired
    BocService bocService;
   
 
    private static Logger logger = LoggerFactory.getLogger(CreateTriggerController.class);

    @GetMapping("/Trigger")
    @ResponseBody
    public String CreateTrigger(String tableName) throws IOException, SQLException {
       String trigger = bocService.createTrigger(tableName);
        return JSON.toJSONString(trigger);
    }

}插入代码片

@Service
public class BocService {
    private static Logger logger = LoggerFactory.getLogger(BocService.class);

    @Autowired
    QueryCreateSqlService queryCreateSqlService;
    @Autowired
    SqListService sqListService;
    public String flag = ""FLAG"" + " VARCHAr2(20)," + ""CREATE_DT"" + " DATE,";

    public String createTrigger(String tableName) {
        try {
            //判断输入是否为空
            if (StringUtils.isEmpty(tableName)) {
                return ResultEnum.RESUT_TABLENAME_ISNULL.toString();
            }
            //判断表是否存在
            tableName = tableName.toUpperCase();
            int num = queryCreateSqlService.isTableExist(tableName);
            if (num == 0) {
                return ResultEnum.RESUT_TABLE_NOTIS.toString();
            }
            String tiggerName = "TRI_" + tableName;
            //将表名全部转换成大写
            //目标表是否已经建立
            int tableExist = queryCreateSqlService.isTableExist(tiggerName);
            if (tableExist > 0) {
                queryCreateSqlService.deleteTable(tiggerName);
                queryCreateSqlService.deleteTrigger(tableName + "_A_TIGGER");
                logger.info("目标表已创建删除已有目标表和已有的触发器" + "表名：" + tableName + " 触发器：" + tableName + "_A_TIGGER");
            }
            Map tableNameMap = new HashMap<>();
            tableNameMap.put("tableName", tableName);
            List> maps = queryCreateSqlService.qryColumnsInfomap(tableNameMap);
            //根据字段类型创建语句
            String langName = "";
            String str = getClos(maps, tableName, langName);
            logger.info("查询到见表语句" + str);
            int i = str.indexOf("(");
            String substring = str.substring(0, i + 1);
            String replace = substring.replace(tableName, tiggerName);
            String tihuantmp = "";
            boolean checkIdTiggerFlag = false;
            if (str.contains(""ID"")) {
                logger.info("原有表中已经包含ID");
                tihuantmp = replace + "  " + ""TRIGGER_ID"" + " VARCHAr2(32)," + "  " + ""FLAG"" + " VARCHAr2(1)," + "  " + ""CREATE_DT"" + " DATE,  ";
                checkIdTiggerFlag = true;
            } else {
                logger.info("原有表中不包含ID");
                tihuantmp = replace + "  " + ""ID"" + " VARCHAr2(32)," + "  " + ""FLAG"" + " VARCHAr2(1)," + "  " + ""CREATE_DT"" + " DATE,  ";
            }
            ///create/Trigger?tableName=CHK_CREDIT
            // String str1 = "";
            //加上修改类型和修改时间
            String createTableSql = str.replace(substring, tihuantmp);
            logger.info(createTableSql);
            //生成触发器表格
            queryCreateSqlService.createTiggerTable(createTableSql);
            logger.info(createTableSql);
            List strings = queryCreateSqlService.qryColumns(tableName);
            String trigger = getTrigger(strings, tableName, tiggerName, checkIdTiggerFlag, langName);
            logger.info(trigger);
            // trigger = trigger.replaceAll("\","")
            queryCreateSqlService.createTigger(trigger);
            return ResultEnum.RESUT_SUCESS.toString();
        } catch (Exception e) {
            logger.info(JSON.toJSONString(e));
            return ResultEnum.RESUT_ERROR.toString();
        }

    }

    public String getTrigger(List cls, String oldTableName, String newTableName, boolean checkIdTiggerFlag, String langName) {
        String mode =
                "create or replace trigger triggerNamen" +
                        "     before insert or update or delete on oldTableNamen" +
                        "  for each rown" +
                        "  beginn" +
                        " if inserting thenn" +
                        "     insert into newTableNamen" +
                        "    (n" +
                        "        ID,n" +
                        "C-L-O-M-N-S" + "," +
                        "        FLAG,n" +
                        "        CREATE_DTn" +
                        "    )n" +
                        "    valuesn" +
                        "    (n" +
                        "        sys_guid(),n" +
                        "NEW-CLOMNS" + "," +
                        "       'I',n" +
                        "       sysdaten" +
                        "    );n" +
                        " elsif updating thenn" +
                        "    insert into newTableNamen" +
                        "    (n" +
                        "       ID,n" +
                        "C-L-O-M-N-S" + "," +
                        "       FLAG,n" +
                        "       CREATE_DTn" +
                        "    )n" +
                        "    valuesn" +
                        "    (n" +
                        "        sys_guid(),n" +
                        "NEW-CLOMNS" + "," +
                        "       'U',n" +
                        "       sysdaten" +
                        "    );n" +
                        " elsen" +
                        "    insert into newTableNamen" +
                        "    (n" +
                        "       ID,n" +
                        "C-L-O-M-N-S" + "," +
                        "       FLAG,n" +
                        "       CREATE_DTn" +
                        "    )n" +
                        "    valuesn" +
                        "    (n" +
                        "        sys_guid(),n" +
                        "OLD-CLOMNS" + "," +
                        "       'D',n" +
                        "       sysdaten" +
                        "    );n" +
                        "  end if;n" +
                        "end;";
        String newstr = "";
        String oldstr = "";
        for (String oldCl : cls) {
            //  String rep = oldCl.replace(""", "");
            String oldCle = (":OLD." + """ + oldCl + """).toString();
            //logger.info(oldCl);
            oldstr = oldstr + oldCle + ",";
            String newCle = (":NEW." + """ + oldCl + """).toString();
            newstr = newstr + newCle + ",";
            //  logger.info(newCle);
          
        }
        newstr = newstr.substring(0, newstr.length() - 1);
        logger.info(newstr);
        oldstr = oldstr.substring(0, oldstr.length() - 1);
        logger.info(oldstr);
        if (checkIdTiggerFlag) {
            mode = mode.replace("ID", "TRIGGER_ID");
        }
        String s = JSON.toJSONString(cls);
        String replace1 = s.replace(""", "").replace("[", "").replace("]", "");
        String modSql = mode.replace("oldTableName", oldTableName).replace("newTableName", newTableName).replace("C-L-O-M-N-S", replace1).replace("OLD-CLOMNS", oldstr).replace("NEW-CLOMNS", newstr).replace("triggerName", oldTableName + "_A_TIGGER");
        logger.info(modSql);
        return modSql;
    }

    private String getClos(List> lis, String tableName, String longName) {
        String sql = "";
        for (Map li : lis) {
            if (String.valueOf(li.get("DATA_TYPE")).equals("VARCHAR2")
                    || String.valueOf(li.get("DATA_TYPE")).equals("NUMBER")
                    || String.valueOf(li.get("DATA_TYPE")).equals("RAW")
                    || String.valueOf(li.get("DATA_TYPE")).equals("NUMBER")
                    || String.valueOf(li.get("DATA_TYPE")).equals("NVARCHAR2")
                    || String.valueOf(li.get("DATA_TYPE")).equals("CHAR")) {
                if (String.valueOf(li.get("DATA_TYPE")).equals("NUMBER") && null != li.get("DATA_SCALE") && new BigDecimal(li.get("DATA_SCALE").toString()).intValue() > 0) {
                    sql = sql + li.get("COLUMN_NAME") + "  " + li.get("DATA_TYPE") + "(" + new BigDecimal(String.valueOf(li.get("DATA_PRECISION"))).toString() + "," + new BigDecimal(String.valueOf(li.get("DATA_SCALE"))).intValue() + ")" + ",";

                } else {
                    sql = sql + li.get("COLUMN_NAME") + "  " + li.get("DATA_TYPE") + "(" + new BigDecimal(String.valueOf(li.get("DATA_LENGTH"))).toString() + ")" + ",";
                }
            } else {
                sql = sql + li.get("COLUMN_NAME") + "  " + li.get("DATA_TYPE") + ",";
            }

        }
        String substring = sql.substring(0, sql.length() - 1);
        String sqlInfo = "create table   " + tableName + "(" + substring + ")";

        return sqlInfo;
    }


}

public interface QueryCreateSqlService {
    Map qryCreateSqlByName(Map map);
    void createTiggerTable(String createTableSql);
    List qryColumns(@Param("tableName")String tableName);
    void createTigger(@Param("createTriggerSql")String createTriggerSql);
    void deleteTable(@Param("tableName") String tableName);
    int isTableExist(@Param("tableName") String tableName);
    void deleteTrigger(@Param("trigger") String trigger);
    List qryColumnsInfo(@Param("tableName")String tableName);
    List> qryColumnsInfomap(@Param("tableNameMap")Map tableNameMap);

}```

```java
@Service
public class QueryCreateSqlServiceImpl implements QueryCreateSqlService {
    @Autowired
    QueryCreateSqlMapper queryCreateSqlMapper;

    @Override
    public Map qryCreateSqlByName(Map map) {
        return queryCreateSqlMapper.qryCreateSqlByName(map);
    }

    @Override
    public void createTiggerTable(String createTableSql) {
        queryCreateSqlMapper.createTiggerTable(createTableSql);
    }

    @Override
    public List qryColumns(String tableName) {
        return queryCreateSqlMapper.qryColumns(tableName);
    }

    @Override
    public void createTigger(String createTriggerSql) {
        queryCreateSqlMapper.createTigger(createTriggerSql);
    }

    @Override
    public void deleteTable(String tableName) {
        queryCreateSqlMapper.deleteTable(tableName);
    }

    @Override
    public int isTableExist(String tableName) {
       return queryCreateSqlMapper.isTableExist(tableName);
    }

    @Override
    public void deleteTrigger(String trigger) {
        queryCreateSqlMapper.deleteTrigger(trigger);
    }

    @Override
    public List qryColumnsInfo(String tableName) {
        return queryCreateSqlMapper.qryColumnsInfo(tableName);
    }

    @Override
    public List> qryColumnsInfomap(Map tableNameMap) {
        return queryCreateSqlMapper.qryColumnsInfomap(tableNameMap);
    }

@Repository
public interface QueryCreateSqlMapper {

    Map qryCreateSqlByName(Map map);

    void createTiggerTable(@Param("createTableSql")String createTableSql);
    List qryColumns(@Param("tableName")String tableName);
    void createTigger(@Param("createTriggerSql")String createTriggerSql);
    void deleteTable(@Param("tableName") String tableName);
    int isTableExist(@Param("tableName") String tableName);
    void deleteTrigger(@Param("trigger") String trigger);
    List qryColumnsInfo(@Param("tableName")String tableName);
    List> qryColumnsInfomap(@Param("tableNameMap")Map tableNameMap);
}

```xml




    
        
        
        
    

    

        SELECT DBMS_metaDATA.GET_DDL('TABLE',#{tableName}) CREATE_STR FROM DUAL
    
    
          ${createTableSql}
    
    
      select A.COLUMN_NAME from user_tab_columns A where TABLE_NAME= #{tableName}
    
    
          ${createTriggerSql}
    
    
          select count(*) from user_tables where table_name =upper(#{tableName})
    
    
    DROP TABLE ${tableName}
    
    
    DROP TRIGGER  ${trigger}
    
    
      select A.COLUMN_NAME from user_tab_columns A where TABLE_NAME= #{tableName}
    
    
      select  A.DATA_TYPE,A.COLUMN_NAME,A.DATA_LENGTH,A.DATA_PRECISION,A.DATA_SCALE   from user_tab_columns A where TABLE_NAME= #{tableNameMap.tableName}