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

本文主要是介绍java动态生成指定oracle中表的触发器和新建保存数据表，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

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

@RestController
@RequestMapping("/create")
public class CreateTriggerController {
    @Autowired
    BocService bocService;
   
 /*   @Autowired
    JsonDataService jsonDataService;*/
    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<String, String> tableNameMap = new HashMap<>();
            tableNameMap.put("tableName", tableName);
            List<Map<String, Object>> 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<String> 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<String> cls, String oldTableName, String newTableName, boolean checkIdTiggerFlag, String langName) {
        String mode =
                "create or replace trigger triggerName\n" +
                        "     before insert or update or delete on oldTableName\n" +
                        "  for each row\n" +
                        "  begin\n" +
                        " if inserting then\n" +
                        "     insert into newTableName\n" +
                        "    (\n" +
                        "        ID,\n" +
                        "C-L-O-M-N-S" + "," +
                        "        FLAG,\n" +
                        "        CREATE_DT\n" +
                        "    )\n" +
                        "    values\n" +
                        "    (\n" +
                        "        sys_guid(),\n" +
                        "NEW-CLOMNS" + "," +
                        "       'I',\n" +
                        "       sysdate\n" +
                        "    );\n" +
                        " elsif updating then\n" +
                        "    insert into newTableName\n" +
                        "    (\n" +
                        "       ID,\n" +
                        "C-L-O-M-N-S" + "," +
                        "       FLAG,\n" +
                        "       CREATE_DT\n" +
                        "    )\n" +
                        "    values\n" +
                        "    (\n" +
                        "        sys_guid(),\n" +
                        "NEW-CLOMNS" + "," +
                        "       'U',\n" +
                        "       sysdate\n" +
                        "    );\n" +
                        " else\n" +
                        "    insert into newTableName\n" +
                        "    (\n" +
                        "       ID,\n" +
                        "C-L-O-M-N-S" + "," +
                        "       FLAG,\n" +
                        "       CREATE_DT\n" +
                        "    )\n" +
                        "    values\n" +
                        "    (\n" +
                        "        sys_guid(),\n" +
                        "OLD-CLOMNS" + "," +
                        "       'D',\n" +
                        "       sysdate\n" +
                        "    );\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<Map<String, Object>> lis, String tableName, String longName) {
        String sql = "";
        for (Map<String, Object> 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<String, Object> qryCreateSqlByName(Map<String, String> map);
    void createTiggerTable(String createTableSql);
    List<String> 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<USER_TAB_COLUMNS> qryColumnsInfo(@Param("tableName")String tableName);
    List<Map<String,Object>> qryColumnsInfomap(@Param("tableNameMap")Map<String,String> tableNameMap);

}```

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

    @Override
    public Map<String, Object> qryCreateSqlByName(Map<String, String> map) {
        return queryCreateSqlMapper.qryCreateSqlByName(map);
    }

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

    @Override
    public List<String> 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<USER_TAB_COLUMNS> qryColumnsInfo(String tableName) {
        return queryCreateSqlMapper.qryColumnsInfo(tableName);
    }

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

@Repository
public interface QueryCreateSqlMapper {

    Map<String,Object> qryCreateSqlByName(Map<String,String> map);

    void createTiggerTable(@Param("createTableSql")String createTableSql);
    List<String> 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<USER_TAB_COLUMNS> qryColumnsInfo(@Param("tableName")String tableName);
    List<Map<String,Object>> qryColumnsInfomap(@Param("tableNameMap")Map<String,String> tableNameMap);
}

```xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- 如果获取的表是属于当前连接用户，可以省略owner参数，因此SQL工具类只有两个参数 -->
<mapper namespace="com.boc.mapper.QueryCreateSqlMapper">
    <resultMap id="cols" type="com.boc.data.USER_TAB_COLUMNS">
        <result column="DATA_TYPE" property="DATA_TYPE"></result>
        <result column="COLUMN_NAME" property="COLUMN_NAME"></result>
        <result column="DATA_LENGTH" property="DATA_LENGTH"></result>
    </resultMap>

    <select id="qryCreateSqlByName" resultType="java.util.Map">

        SELECT DBMS_METADATA.GET_DDL('TABLE',#{tableName}) CREATE_STR FROM DUAL
    </select>
    <update id="createTiggerTable">
          ${createTableSql}
    </update>
    <select id="qryColumns" parameterType="java.lang.String" resultType="java.lang.String">
      select A.COLUMN_NAME from user_tab_columns A where TABLE_NAME= #{tableName}
    </select>
    <update id="createTigger" statementType="STATEMENT">
          ${createTriggerSql}
    </update>
    <select id="isTableExist" parameterType="string" resultType="int">
          select count(*) from user_tables where table_name =upper(#{tableName})
    </select>
    <update id="deleteTable">
    DROP TABLE ${tableName}
    </update>
    <update id="deleteTrigger">
    DROP TRIGGER  ${trigger}
    </update>
    <select id="qryColumnsInfo" parameterType="java.lang.String" resultMap="cols">
      select A.COLUMN_NAME from user_tab_columns A where TABLE_NAME= #{tableName}
    </select>
    <select id="qryColumnsInfomap" parameterType="java.util.Map" resultType="java.util.HashMap">
      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}
    </select>
</mapper>

这篇关于java动态生成指定oracle中表的触发器和新建保存数据表的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！