通過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程式碼封裝一下。
完整的程式碼如下:
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;
}
}
比如下面的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程式碼封裝一下。
點選(此處)摺疊或開啟
- public class FormatSql {
-
-
public static void main(String[] args) throws IOException {
-
-
ArrayList<String> strArr = readFromFile(); //從指定的檔案中讀取檔案內容
-
-
formatSQL(strArr); //格式化sql檔案
-
-
OutputFormatSql(strArr); //輸出格式化後的sql語句
-
- }
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
formatSQL(strArr);
OutputFormatSql(strArr);
}
private static void formatSQL(ArrayList
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
for (int i = 0; i < strArr.size(); i++) {
System.out.println(strArr.get(i));
}
}
private static void formatSqlLine(String tmpCurrLine, String tmpNextLine,
ArrayList
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
throws FileNotFoundException, IOException {
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;
}
}
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23718752/viewspace-1444910/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 透過java來格式化sql語句JavaSQL
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句?JavaSQL
- 通過pl/sql來格式化sqlSQL
- 通過sql語句分析足彩SQL
- 通過java程式抽取日誌中的sql語句JavaSQL
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- oracle 通過sql profile為sql語句加hintOracleSQL
- 通過SQL PROFILE自動優化SQL語句SQL優化
- ORACLE 通過SPM為SQL語句加HINTOracleSQL
- 通過使用hint unnest調優sql語句SQL
- 通過ORA錯誤反思sql語句規範SQL
- 通過sql語句分析足彩(第三篇)SQL
- 通過SQL語句提取儲存過程中的內容SQL儲存過程
- MySQL_通過binlog檢視原始SQL語句MySql
- 用 Phoenix 通過 SQL 語句更新操作 HBase 資料SQL
- 通過分析SQL語句的執行計劃優化SQL 二SQL優化
- 在 PHP 中格式化並高亮 SQL 語句PHPSQL
- MySQL在ROW模式下通過binlog提取SQL語句MySql模式
- 通過圖表簡化sql語句的表關聯SQL
- 通過分析SQL語句的執行計劃優化SQL(總結)SQL優化
- 透過java程式抽取日誌中的sql語句JavaSQL
- 通過Linux命令過濾出binlog中完整的SQL語句LinuxSQL
- 透過sql語句分析足彩SQL
- oracle自定義過程來獲得完整的sql語句OracleSQL
- 通過日誌檢視mysql正在執行的SQL語句MySql
- 【轉】通過sql語句獲取資料庫的基本資訊SQL資料庫
- 使用hint來調優sql語句SQL
- 通過_optimizer_rownum_pred_based_fkr優化一條sql語句優化SQL
- mysql執行sql語句過程MySql
- SQL語句的處理過程SQL
- [20150724]無法通過sql_id找到sql語句.txtSQL
- Java學習筆記--sql語句Java筆記SQL
- 如何通過注入SQL語句盜取網站管理許可權AHSQL網站
- 聯絡生活來簡化sql語句SQL
- SQL語句執行過程詳解SQL
- CoreData執行過程的sql語句SQL
- sql語句執行過程小結SQL
- SQL語句的處理過程修正SQL