通过java来格式化sql语句

经常在抓取一些sql语句的时候，得到的sql文本有格式的问题，如果尝试得到执行计划，每次都会费一番周折。

比如下面的sql语句，基本包含了常见的格式问题。第3行，第4行出现了断行，执行的时候就会报错。

select /*+ leading(s) index(s TABLE_BPM_STEP_INST_5IX) use_nl(s p

p step) */ s.ALLOW_CREATE, s.ASSIGNEE, s.ASYNC_RETURNED_PARAMS, s

.ATTACHER2STEP_INST, s.COMMITTER, s.CONTROL_COUNT, s.CURR_FAULT2F

AULT_INFO, s.DO_AVAIL_ON_RESUM, s.DO_FIN_ON_RESUM, s.HAS_DEPENDEN

TS, 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.S

TART_TIME, s.STATUS, s.STATUS_CHANGE_TIME, s.STEP2STEP, s.TARGETE

D_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 ( NO

T (step.step_type = 4)) AND p.root_status in (0, 14) AND s.commit

ter = :1 AND s.assignee in ('BpmInServer', 'BpmInServerSmThr', 'B

pmJms') AND s.status in (50, 55) AND s.curr_fault2fault_info is n

ull and ( p.EXEC_DOMAIN like :2 )

今天下定决心使用java来格式化了一把sql文本。

 格式化后的文本如下，得到的效果还是不错的。

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代码结构如下，适当做了重构，可以在稍后把java代码封装一下。

1public class FormatSql { 2.

2

3public static void main(String[] args) throws IOException {

4

5 ArrayList strArr = readFromFile(); //从指定的文件中读取文件内容

6

7formatSQL(strArr); //格式化sql文件

8

9OutputFormatSql(strArr); //输出格式化后的sql语句10.

10

11 }

完整的代码如下：

import java.io.BufferedReader;

import java.io.FileNotFoundException;

import java.io.FileReader;

import java.io.IOException;

import java.util.ArrayList;

public class FormatSql {

public static void main(String[] args) throws IOException {

ArrayList strArr = readFromFile();

formatSQL(strArr);

OutputFormatSql(strArr);

}

private static void formatSQL(ArrayList strArr) {

String tmpCurrLine;

String tmpNextLine;

for (int i = 0; i < strArr.size(); i++) {

tmpCurrLine = strArr.get(i);

// consider last line

if (strArr.size() == (i + 1)) {

tmpNextLine = "";

} else {

tmpNextLine = strArr.get(i + 1);

formatSqlLine(tmpCurrLine, tmpNextLine, strArr, i);

private static void OutputFormatSql(ArrayList strArr) {

for (int i = 0; i < strArr.size(); i++) {

System.out.println(strArr.get(i));

private static void formatSqlLine(String tmpCurrLine, String tmpNextLine,

ArrayList strArr, int i) {

String tmpCurrFormatLine;

String tmpCurrLeftLine;

for (int j = tmpCurrLine.length() - 1; j > 0;) {

if (tmpCurrLine.charAt(j) == ' ') {

tmpCurrFormatLine = tmpCurrLine.substring(0, j);

tmpCurrLeftLine = tmpCurrLine.substring(j + 1, tmpCurrLine

.length()); // keep last space

strArr.set(i, tmpCurrFormatLine);

strArr.set(i + 1, tmpCurrLeftLine + tmpNextLine);

tmpNextLine = tmpCurrLeftLine + tmpNextLine;

// System.out.println(tmpCurrFormatLine);

// System.out.println(tmpCurrLeftLine);

// System.out.println(tmpNextLine);

break;

} else {

j--;

}

}

 }

private static ArrayList readFromFile()

throws FileNotFoundException, IOException {

ArrayList strArr = new ArrayList();

FileReader reader = new FileReader("c://a.sql");

BufferedReader br = new BufferedReader(reader);

String str = null;

while ((str = br.readLine()) != null) {

strArr.add(str);

}

br.close();

reader.close();

return strArr;

}

 }