最近接了一个紧急的需求要求给出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>