通过java来格式化sql语句(r4笔记第61天)
经常在抓取一些sql语句的时候，得到的sql文本有格式的问题，如果尝试得到执行计划，每次都会费一番周折。比如下面的sql语句，基本包含了常见的格式问题。第3行，第4行出现了断行，执行的时候就会报错。select /*+ leading(s) index(s TABLE_BPM_STEP_INST_5IX) use_nl(s pp step) */ s.ALLOW_CREATE, s.ASSIGNEE, s.ASYNC_RETURNED_PARAMS, s.ATTACHER2STEP_INST, s.COMMITTER, s.CONTROL_COUNT, s.CURR_FAULT2FAULT_INFO, s.DO_AVAIL_ON_RESUM, s.DO_FIN_ON_RESUM, s.HAS_DEPENDENTS, s.HAS_MARCH_REND, s.HAS_REND, s.INFLOW_BITS, s.ITER_COUNT, s.NUM_OR_PREREQS, s.NUM_PENDING, s.NUM_PENDING_PREREQS, s.OBJID, s.OUTFLOW_BITS, s.PARAMS, s.PARENT2PROC_INST, s.ROOT2PROC_INST, s.START_TIME, s.STATUS, s.STATUS_CHANGE_TIME, s.STEP2STEP, s.TARGETED_BY_ALARMS, s.TRIGGERS_ALARMS, s.WAIT_TIME, s.WORKER FROM TABLE_BPM_PROC_INST p, TABLE_BPM_STEP_INST s, TABLE_BPM_STEP step WHERE s.root2proc_inst = p.objid AND s.step2step = step.objid AND ( NOT (step.step_type = 4)) AND p.root_status in (0, 14) AND s.committer = :1 AND s.assignee in ('BpmInServer', 'BpmInServerSmThr', 'BpmJms') AND s.status in (50, 55) AND s.curr_fault2fault_info is null and ( p.EXEC_DOMAIN like :2 ) 今天下定决心使用java来格式化了一把sql文本。格式化后的文本如下，得到的效果还是不错的。select /*+ leading(s) index(s TABLE_BPM_STEP_INST_5IX) use_nl(spp step) */ s.ALLOW_CREATE, s.ASSIGNEE, s.ASYNC_RETURNED_PARAMS,s.ATTACHER2STEP_INST, s.COMMITTER, s.CONTROL_COUNT,s.CURR_FAULT2FAULT_INFO, s.DO_AVAIL_ON_RESUM, s.DO_FIN_ON_RESUM,s.HAS_DEPENDENTS, s.HAS_MARCH_REND, s.HAS_REND, s.INFLOW_BITS, s.ITER_COUNT,s.NUM_OR_PREREQS, s.NUM_PENDING, s.NUM_PENDING_PREREQS, s.OBJID,s.OUTFLOW_BITS, s.PARAMS, s.PARENT2PROC_INST, s.ROOT2PROC_INST,s.START_TIME, s.STATUS, s.STATUS_CHANGE_TIME, s.STEP2STEP,s.TARGETED_BY_ALARMS, s.TRIGGERS_ALARMS, s.WAIT_TIME, s.WORKER FROMTABLE_BPM_PROC_INST p, TABLE_BPM_STEP_INST s, TABLE_BPM_STEP stepWHERE s.root2proc_inst = p.objid AND s.step2step = step.objid AND (NOT (step.step_type = 4)) AND p.root_status in (0, 14) ANDs.committer = :1 AND s.assignee in ('BpmInServer', 'BpmInServerSmThr','BpmJms') AND s.status in (50, 55) AND s.curr_fault2fault_info isnull and ( p.EXEC_DOMAIN like :2 )
对应的java代码结构如下，适当做了重构，可以在稍后把java代码封装一下。
点击(此处)折叠或打开
public class FormatSql {
public static void main(String[] args) throws IOException {
ArrayList<String> strArr = readFromFile(); //从指定的文件中读取文件内容
formatSQL(strArr); //格式化sql文件
OutputFormatSql(strArr); //输出格式化后的sql语句
}
完整的代码如下：