格式化sql

无格式的sql

SELECT * FROM (SELECT ROWNUM AS NUMROW, c.* from   (           select contract.*   , p.SETT_TYPE   , p.DELI_TYPE   , p.DELIVERY_STATUS   , p.DELIVERY_DATE   , p.IS_NEED_INVOICE   , p.IS_NEED_RECEIPT   , p.PICK_STATUS   , p.seller_del_commission   , p.buyer_del_commission   , p.SENDTORZT_STATUS    from (SELECT a.ID   , a.MARKET_ID, a.CONTRACT_NO, a.TRADE_TYPE_STR, a.TRADE_ID, a.BUYER_USER_ID, a.SELLER_USER_ID, a.BUYER_USER_NAME, a.SELLER_USER_NAME,         a.SELLER_TRADE_ACCOUNT, a.BUYER_TRADE_ACCOUNT, a.BUYER_DELIVERY_ACCOUNT, a.SELLER_DELIVERY_ACCOUNT, a.BUYER_DEPOSIT,         a.SELLER_DEPOSIT, a.BUYER_COMMISSION, a.SELLER_COMMISSION, a.TRANS_DATETIME, a.TRADE_STATUS, a.LESS_COMMISSION, a.TOTAL_PRICE_NUM,         a.REAL_TOTAL_PRICE, a.CAN_SHORT, a.CONTRACT_WEIGHT, a.PIECE_QUANTITY, a.INVOICE_DESC, a.COMMENTS, a.CUSTOM_CONTRACT_NO,         a.CONTRACT_TEMPLATE, a.LOAN_CONTRACT, a.SELLERS_ID, a.CONTRACT_TEMPLATE_TITLE, a.IS_INVENTORY_LIST, a.BUYER_COMMISSION_REFUND,         a.SELLER_COMMISSION_REFUND, a.SIGNATURE_STATUS, a.CENTER_INVOICE_DESC, a.GMT_CREATE, a.GMT_MODIFY,a.MARKET_NAME,a.OPERATE_FLAG,a.FILE_ID,a.SELLER_OPERATE_NAME,a.BUYER_OPERATE_NAME,a.JSON_STRING,a.VARIETY_NAME,a.STAT_UNIT,a.SELLER_RETURN_DEPOSIT,a.BUYER_PAY_PAYMENT         ,a.BUYER_RETURN_PAYMENT,a.SELLER_RECEIVE_PAYMENT,a.FROZEN_INVOICE_DEPOSIT,a.RELEASE_INVOICE_DEPOSIT,a.INVOICE_COMPANY_NAME,a.INVOICE_NUMBER,a.INVOICE_ADDRESS,a.INVOICE_MOBILE,a.INVOICE_BANK_NAME,a.INVOICE_BANK_ACCOUNT,a.RISK_MARKER,a.DEAL_TYPE,a.BILL_FILE_ID,a.INVOICE_NO,a.INVOICE_DATE,f.FINANCE_STATUS,f.FINANCE_COMMENTS from TRADE_CONTRACT a left join TRADE_CONTRACT_FINANCE f on a.id=f.CONTRACT_NUM_ID) contract ,TRADE_CONTRACT_DELIVERY p where contract.ID = p.CONTRACT_NUM_ID and contract.TRADE_STATUS not in ('5','6')                 and     contract.TRADE_STATUS in (select * from table(str2varlist(?)))                                  and    contract.TRADE_STATUS = ?                                                       ORDER BY contract.GMT_CREATE desc ,CONTRACT.CONTRACT_NO DESC            ) c where rownum <=     ?) WHERE NUMROW >= ?       

使用阿里的Druid.jar工具包进行格式化，下载地址：http://www.java2s.com/Code/Jar/d/Downloaddruid029jar.htm

Druid.jar 中的方法：

public static String formatMySql(String sql)
public static String formatOracle(String sql)
public static String formatOdps(String sql)
public static String toOracleString(SQLObject sqlObject)
public static String toPGString(SQLObject sqlObject)
public static String toDB2String(SQLObject sqlObject)
public static String toSQLServerString(SQLObject sqlObject)
public static String formatPGSql(String sql) 

 

测试：

@Test
    public void test() {

　　//无格式的sql
        String sql = "SELECT * FROM (SELECT ROWNUM AS NUMROW, c.* from   (           select contract.*   , p.SETT_TYPE   , p.DELI_TYPE   , p.DELIVERY_STATUS   , p.DELIVERY_DATE   , p.IS_NEED_INVOICE   , p.IS_NEED_RECEIPT   , p.PICK_STATUS   , p.seller_del_commission   , p.buyer_del_commission   , p.SENDTORZT_STATUS    from (SELECT a.ID   , a.MARKET_ID, a.CONTRACT_NO, a.TRADE_TYPE_STR, a.TRADE_ID, a.BUYER_USER_ID, a.SELLER_USER_ID, a.BUYER_USER_NAME, a.SELLER_USER_NAME,         a.SELLER_TRADE_ACCOUNT, a.BUYER_TRADE_ACCOUNT, a.BUYER_DELIVERY_ACCOUNT, a.SELLER_DELIVERY_ACCOUNT, a.BUYER_DEPOSIT,         a.SELLER_DEPOSIT, a.BUYER_COMMISSION, a.SELLER_COMMISSION, a.TRANS_DATETIME, a.TRADE_STATUS, a.LESS_COMMISSION, a.TOTAL_PRICE_NUM,         a.REAL_TOTAL_PRICE, a.CAN_SHORT, a.CONTRACT_WEIGHT, a.PIECE_QUANTITY, a.INVOICE_DESC, a.COMMENTS, a.CUSTOM_CONTRACT_NO,         a.CONTRACT_TEMPLATE, a.LOAN_CONTRACT, a.SELLERS_ID, a.CONTRACT_TEMPLATE_TITLE, a.IS_INVENTORY_LIST, a.BUYER_COMMISSION_REFUND,         a.SELLER_COMMISSION_REFUND, a.SIGNATURE_STATUS, a.CENTER_INVOICE_DESC, a.GMT_CREATE, a.GMT_MODIFY,a.MARKET_NAME,a.OPERATE_FLAG,a.FILE_ID,a.SELLER_OPERATE_NAME,a.BUYER_OPERATE_NAME,a.JSON_STRING,a.VARIETY_NAME,a.STAT_UNIT,a.SELLER_RETURN_DEPOSIT,a.BUYER_PAY_PAYMENT         ,a.BUYER_RETURN_PAYMENT,a.SELLER_RECEIVE_PAYMENT,a.FROZEN_INVOICE_DEPOSIT,a.RELEASE_INVOICE_DEPOSIT,a.INVOICE_COMPANY_NAME,a.INVOICE_NUMBER,a.INVOICE_ADDRESS,a.INVOICE_MOBILE,a.INVOICE_BANK_NAME,a.INVOICE_BANK_ACCOUNT,a.RISK_MARKER,a.DEAL_TYPE,a.BILL_FILE_ID,a.INVOICE_NO,a.INVOICE_DATE,f.FINANCE_STATUS,f.FINANCE_COMMENTS from TRADE_CONTRACT a left join TRADE_CONTRACT_FINANCE f on a.id=f.CONTRACT_NUM_ID) contract ,TRADE_CONTRACT_DELIVERY p where contract.ID = p.CONTRACT_NUM_ID and contract.TRADE_STATUS not in ('5','6')                 and     contract.TRADE_STATUS in (select * from table(str2varlist(?)))                                  and    contract.TRADE_STATUS = ?                                                       ORDER BY contract.GMT_CREATE desc ,CONTRACT.CONTRACT_NO DESC            ) c where rownum <=     ?) WHERE NUMROW >= ?         ";

        String formatSql = SQLUtils.formatOracle(sql);
        System.out.println(formatSql);
    }

 

输出结果（输出内容太多，截图没截全）：

效果很Nice

转载于:https://www.cnblogs.com/loveUworld/p/9269058.html