使用Java根据约定格式生成Oracle存储过程

记录：285

场景：在实际开发中，例如，系统升级，数模变化差异大，就涉及数模全量字段转化。根据建表语句逐个摘取字段，可行，但相对比较慢。本例根据表名从数据库读取字段，统一组装插入(INSERT INTO)和查询(SELECT)等基础SQL语句，具体业务SQL在此基础上按需修改。本例基于Oracle存储过程方式实现数模转换，先定义好一个规范的存储过程模板，使用Java代码，根据表名批量生成Oracle存储过程。这样就只需集中开发具体业务SQL即可。本例使用Java根据约定格式生成Oracle存储过程。

版本：Spring Boot 2.6.3

版本：Oracle Database 11g

工具：PL/SQL Developer

一、案例场景

1.存储过程模板

文件名：存储过程模板.sql

-- 注释
PROCEDURE PRO_NAME(INPUT_PARA VARCHAR2,OUTPUT_MSG OUT VARCHAR2) IS
  BEGIN
    -- 声明变量
    DECLARE
      LOG_ID  NUMBER; -- 日志表主键
      EXECUTE_NUM NUMBER := 0; -- 操作表的记录条数
    
    BEGIN
      -- 1.插入日志
      PKG_LOG.PRO_LOG_INSERT('业务描述', '表名',INPUT_PARA,LOG_ID);
	  OUTPUT_MSG :='';
    
      -- 2.业务处理
    
      EXECUTE_NUM := EXECUTE_NUM + SQL%ROWCOUNT; -- 计算记录条数
      COMMIT;
      -- 3.更新成功日志
      PKG_LOG.PRO_LOG_UPDATE(LOG_ID, '1', '', EXECUTE_NUM);
    EXCEPTION
      WHEN OTHERS THEN
        -- 4.更新异常日志
        PKG_LOG.PRO_LOG_UPDATE(LOG_ID, '0', SQLERRM, EXECUTE_NUM);
		OUTPUT_MSG :='异常信息: ' || SQLERRM;
        ROLLBACK;
    END;
END PRO_NAME;

2.表清单

文件名：TableList.txt

表清单中，每行是一个表信息，使用##分割，Java代码以此分割字符串，再加工。

格式：表名(英文名称)##表注释

T_SENSOR_DATA##传感器数据
T_02##表T_02注释
T_03##表T_03注释
T_04##表T_04注释
T_05##表T_05注释
......

3.生成的存储过程文件

本例执行Java代码生成Oracle存储过程定义和Oracle存储过程实现两个文件如下。

存储过程定义文件：20220713_declare.sql

存储过程实现文件：20220713_procedure.sql

比如：T_SENSOR_V1、T_USER等多张表关联后，转换到T_SENSOR_DATA中。本例只取T_SENSOR_DATA字段组装SQL，关联表的各个字段即实际业务要开发的。

3.1 存储过程定义文件

文件名：20220713_declare.sql

-- 传感器数据
PROCEDURE PRO_01_T_SENSOR_DATA(INPUT_PARA VARCHAR2,OUTPUT_MSG OUT VARCHAR2);

3.2 存储过程实现文件

本例中的PKG_LOG.PRO_LOG_INSERT、PKG_LOG.PRO_LOG_UPDATE是PKG_LOG包下的已经封装好的2个统一操作日志的存储过程。

文件名：20220713_procedure.sql

--传感器数据
PROCEDURE PRO_01_T_SENSOR_DATA(INPUT_PARA VARCHAR2,OUTPUT_MSG OUT VARCHAR2) IS
  BEGIN
    -- 声明变量
    DECLARE
      LOG_ID  NUMBER; -- 日志表主键
      EXECUTE_NUM NUMBER := 0; -- 操作表的记录条数
    
    BEGIN
      -- 1.插入日志
      PKG_LOG.PRO_LOG_INSERT('业务描述', '表名',INPUT_PARA,LOG_ID);
	  OUTPUT_MSG :='';
    
      -- 2.业务处理
    INSERT INTO T_SENSOR_DATA
    (ID,
    REGION,
    VALUE1,
    VALUE2)
    SELECT ID,
        REGION,
        VALUE1,
        VALUE2
    FROM T_SENSOR_DATA;
    
      EXECUTE_NUM := EXECUTE_NUM + SQL%ROWCOUNT; -- 计算记录条数
      COMMIT;
      -- 3.更新成功日志
      PKG_LOG.PRO_LOG_UPDATE(LOG_ID, '1', '', EXECUTE_NUM);
    EXCEPTION
      WHEN OTHERS THEN
        -- 4.更新异常日志
        PKG_LOG.PRO_LOG_UPDATE(LOG_ID, '0', SQLERRM, EXECUTE_NUM);
		OUTPUT_MSG :='异常信息: ' || SQLERRM;
        ROLLBACK;
    END;
END PRO_01_T_SENSOR_DATA;

4.业务举例

Java代码在读取到存储过程模板中的“-- 2.业务处理”后，会添加组装的基础SQL.例如，操作的目标表T_SENSOR_DATA，就会到数据库读取T_SENSOR_DATA的所有字段，组成插入和查询的基础SQL。在做业务开发时，就在此基础上修改具体where条件逻辑和字段对应关系。

Java代码生成的基础SQL。

INSERT INTO T_SENSOR_DATA
    (ID,
    REGION,
    VALUE1,
    VALUE2)
    SELECT ID,
        REGION,
        VALUE1,
        VALUE2
    FROM T_SENSOR_DATA;

在基础SQL上开发的业务SQL。

INSERT INTO T_SENSOR_DATA
    (ID,
    REGION,
    VALUE1,
    VALUE2)
    SELECT AA.DATA_ID AS ID,
        BB.REGION AS REGION,
        AA.D1 AS VALUE1,
        AA.D2 AS VALUE2
FROM T_SENSOR AA 
JOIN T_USER BB
ON AA.ID = BB.ID;

5.加载字段SQL

从Oracle数据库中加载指定表名的所有字段SQL。

select aa.COLUMN_ID AS columnId, 
       aa.COLUMN_NAME AS columnName
  from user_tab_columns aa
 where aa.TABLE_NAME = ?
 order by aa.COLUMN_ID asc;

二、代码

1.读文件

从文件中读取数据,一次读取一行,存放在List<String>，函数的参数Boolean isTrim，当为true时，读取一行数据会使用String的trim()函数去掉首位空格，即不保持原有格式。当为false时，读取的字符串不做操作，保持原有格式。

/**
  * 从文件中读取数据,一次读取一行,存放在List<String>
 */
public static ArrayList<String> readFromFile(String fileName, Boolean isTrim) {
  ArrayList<String> listAll = new ArrayList<>();
  try {
    BufferedReader br = new BufferedReader(new FileReader(fileName));
    String oneLine = null;
    while ((oneLine = br.readLine()) != null) {
      if (isTrim) {
          listAll.add(oneLine.trim());
      } else {
          listAll.add(oneLine);
      }
    }
    br.close();
  } catch (Exception e) {
    System.out.println("读异常:");
    e.printStackTrace();
  }
  return listAll;
}

2.写文件

逐行写文件，以追加方式写入，不覆盖已经写入的内容。

/**
 * 字符串写入文件中,逐行追加方式写入
 */
public static void writeFile(String fileName, String content) {
  try {
    BufferedWriter bw = new BufferedWriter(new FileWriter(fileName, true));
    bw.write(content);
    bw.close();
  } catch (IOException e) {
    System.out.println("写异常:");
    e.printStackTrace();
  }
}

3.操作数据源

使用org.springframework.jdbc.core.JdbcTemplate操作数据源。

/**
 * 获取JdbcTemplate数据源
 */
public static JdbcTemplate getJdbcTemplate() {
    DruidDataSource dataSource = new DruidDataSource();
    /**数据库连接信息*/
    String username = "***数据库用户名**";
    String password = "***数据库口令**";
    String jdbcUrl = "***数据库连接URL**";
    String driverName = "oracle.jdbc.OracleDriver";
    /** 设置数据源属性参数 */
    dataSource.setPassword(password);
    dataSource.setUrl(jdbcUrl);
    dataSource.setUsername(username);
    dataSource.setDriverClassName(driverName);
    /** 获取spring的JdbcTemplate*/
    JdbcTemplate jdbcTemplate = new JdbcTemplate();
    /** 设置数据源 */
    jdbcTemplate.setDataSource(dataSource);
    return jdbcTemplate;
}

4.处理表清单信息

处理从文件名：TableList.txt读取的表清单信息。最终加工成List<TableBO> 。

/**
 * 把从文件中读取的字符串转换为List<TableBO>格式
 */
public static List<TableBO> getTableInfo(List<String> fromTxtStr) {
  ArrayList<TableBO> result = new ArrayList<>();
  for (String oneLine : fromTxtStr) {
      ArrayList<String> one = getOneLine(oneLine, "##");
      TableBO tableBO = new TableBO();
      tableBO.setTableName(one.get(0));
      tableBO.setComment(one.get(1));
      result.add(tableBO);
  }
  return result;
}
/**
 * 把 T_TABLE##表注释
 * 根据分割 ## 拆分为两个字符串
 * 存放到 List<String>
 */
public static ArrayList<String> getOneLine(String content, String split) {
  String[] strArr = content.split(split);
  ArrayList<String> oneLine = new ArrayList<String>(strArr.length);
  Collections.addAll(oneLine, strArr);
  return oneLine;
}

5.生成存储过程定义文件

按照规则生成存储过程定义文件。

/**
 * 生成存储过程定义文件
 */
public static void generateDeclare(String file, String procedure,
                                 String comment) {
  String declare = "-- " + comment + "\n" + "PROCEDURE "
     + procedure + "(INPUT_PARA VARCHAR2,OUTPUT_MSG OUT VARCHAR2)" + ";"
     + "\n";
  writeFile(file, declare + "\n");
}

6.生成存储过程实现文件

按照规则生成生成存储过程实现文件。

6.1 存储过程实现文件主流程

主要包括：从数据库获取字段信息、读取存储过程模板文件、生成存储过程。

/**
 * 生成存储过程实现文件
 */
public static void generateProcedure(String file, String procedure,
                      String table, String comment,
                      JdbcTemplate jt,
                      String templateFile) {
  String sql = "select aa.COLUMN_ID AS columnId, aa.COLUMN_NAME AS columnName\n" +
          " from user_tab_columns aa\n" +
          " where aa.TABLE_NAME = ?  " +
          " order by aa.COLUMN_ID asc";
  // 根据表名从数据库读取表字段信息
  List<TableColumnPO> columnList = jt.query(sql,
          new BeanPropertyRowMapper<>(TableColumnPO.class), table);
  // 从模板文件中读取模板
  List<String> template = readFromFile(templateFile, false);
  // 生成SQL
  String oneSQL = comeTrue(procedure, table, 
                   columnList, comment, template);
  writeFile(file, oneSQL + "\n");
}
//组装存储过程实现
public static String comeTrue(String procedure, String table, 
                    List<TableColumnPO> columnList,
                    String comment, List<String> template) {
  StringBuffer sb = new StringBuffer();
  //遍历模板生成数据
  for (String oneLine : template) {
      oneLine = oneLine+"\n";
      if (oneLine.contains("业务处理")) {
          sb.append(oneLine);
          sb.append(getBizSql(table, columnList));
      } else if (oneLine.contains("PRO_NAME")) {
          oneLine = oneLine.replace("PRO_NAME", procedure);
          sb.append(oneLine);
      } else if (oneLine.contains("注释")) {
          String partComment = "--" + comment + "\n";
          sb.append(partComment);
      } else {
          sb.append(oneLine);
      }
  }
  return sb.toString();
}

6.2 存储过程实现文件生成业务SQL

// 组装基础业务SQL
public static String getBizSql(String table, List<TableColumnPO> columnList) {
  String part01 = "  INSERT INTO " + table + "\n";
  String sbColumns1 = getColumns(columnList, "    ");
  String sbColumns2 = getColumns(columnList, "        ");
  String part02 = "    (" + sbColumns1 + ")" + "\n";
  String part03 = "    SELECT " + sbColumns2.toString().toString() + "\n";
  String part04 = "    FROM " + table + ";" + "\n";
  String bizSQL = part01 + part02 + part03 + part04;
  return bizSQL;
}
// 获取一张表的所有字段以逗号分割,根据space来简单排版
public static String getColumns(List<TableColumnPO> columnList, String space) {
  StringBuffer sbColumns = new StringBuffer();
  int size = columnList.size();
  int index = 1;
  for (TableColumnPO po : columnList) {
    String column = "";
    if (index == size) {
        column = po.getColumnName();
    } else {
        column = po.getColumnName() + "," + "\n";
    }
    if (index == 1) {
        sbColumns.append("" + column);
    } else {
        sbColumns.append(space + column);
    }
    index++;
  }
  return sbColumns.toString();
}

7.生成存储过程主入口

生成存储过程主入口，核心逻辑。

public static void generate(String baseDir, String tableFile,
                          String templateFile) {
  List<String> tableList = readFromFile(tableFile, true);
  List<TableBO> tableInfoList = getTableInfo(tableList);
  int index = 1;
  String indexN = "";
  JdbcTemplate jt = getJdbcTemplate();
  // 遍历tableInfoList,即遍历取出每张表
  for (TableBO tableBO : tableInfoList) {
   // 表名
   String table = tableBO.getTableName();
   // 表注释
   String comment = tableBO.getComment();
   if (index < 10) {
       indexN = "0" + index;
   } else {
       indexN = "" + index;
   }
   // 存储过程定义文件路径
   String declareFile = baseDir + "20220713_declare" + ".sql";
   // 存储过程实现文件路径
   String procedureFile = baseDir + "20220713_procedure" + ".sql";
   // 存储过程名称
   String procedure = "PRO_" + indexN + "_" + table;
   // 生成存储过程定义文件
   generateDeclare(declareFile, procedure, comment);
   // 生成存储过程实现文件
   generateProcedure(procedureFile, procedure, table, comment, jt,templateFile);
   index++;
  }
}

8.main函数入口

main函数入口。

public static void main(String[] args) {
  System.out.println("开始...");
  String baseDir = "D:\\example\\";
  String tableFile = baseDir + "TableList" + ".txt";
  String templateFile = baseDir + "存储过程模板" + ".sql";
  generate(baseDir, tableFile, templateFile);
  System.out.println("结束...");
}

9.辅助实体类

TableBO和TableColumnPO两个辅助JavaBean。

//表信息
public class TableBO {
  private String tableName;
  private String comment;
}
//表字段信息
@Data
@NoArgsConstructor
public class TableColumnPO {
  private String columnId;
  private String columnName;
}

以上，感谢。

2022年7月13日