package nl.astraeus.jdbc; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.util.HashMap; import java.util.List; import java.util.Map; /** * User: rnentjes * Date: 4/18/12 * Time: 10:10 PM */ public class SqlFormatter { private final static Logger logger = LoggerFactory.getLogger(SqlFormatter.class); private static SqlTokenizer tokenizer = new SqlTokenizer(); private static enum Newline { PRE, POST, PREPOST, NONE } private static class LayoutMapping { int preIndent; int postIndent; Newline nl; private LayoutMapping() { preIndent = 0; postIndent = 0; nl = Newline.NONE; } private LayoutMapping(int preIndent, int postIndent) { this.preIndent = preIndent; this.postIndent = postIndent; nl = Newline.NONE; } private LayoutMapping(Newline nl) { preIndent = 0; postIndent = 0; this.nl = nl; } private LayoutMapping(int preIndent, int postIndent, Newline nl) { this.preIndent = preIndent; this.postIndent = postIndent; this.nl = nl; } public boolean preNewline() { return nl == Newline.PRE || nl == Newline.PREPOST; } public boolean postNewline() { return nl == Newline.POST || nl == Newline.PREPOST; } public int getPreIndent() { return preIndent; } public int getPostIndent() { return postIndent; } } private static Map<SqlTokenType, LayoutMapping> mapping = new HashMap<SqlTokenType, LayoutMapping>(); private static LayoutMapping defaultMapping = new LayoutMapping(); static { //mapping.put(SqlTokenType.EXPRESSION_OPEN, new LayoutMapping(0, 0, Newline.PRE)); //mapping.put(SqlTokenType.EXPRESSION_CLOSE, new LayoutMapping(0, 0, Newline.POST)); mapping.put(SqlTokenType.SELECT, new LayoutMapping(-1, 1, Newline.POST)); mapping.put(SqlTokenType.FROM, new LayoutMapping(-1, 1, Newline.PREPOST)); mapping.put(SqlTokenType.WHERE, new LayoutMapping(-1, 1, Newline.PREPOST)); mapping.put(SqlTokenType.ORDER_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); mapping.put(SqlTokenType.GROUP_BY, new LayoutMapping(-1, 1, Newline.PREPOST)); mapping.put(SqlTokenType.HAVING, new LayoutMapping(-1, 1, Newline.PREPOST)); mapping.put(SqlTokenType.UPDATE, new LayoutMapping(-1, 1, Newline.POST)); mapping.put(SqlTokenType.SET, new LayoutMapping(-1, 1, Newline.PREPOST)); mapping.put(SqlTokenType.INSERT_INTO, new LayoutMapping(-1, 1, Newline.POST)); mapping.put(SqlTokenType.VALUES, new LayoutMapping(-1, 1, Newline.PREPOST)); mapping.put(SqlTokenType.COMMA, new LayoutMapping(0, 0, Newline.POST)); mapping.put(SqlTokenType.AS, new LayoutMapping(0, 0, Newline.NONE)); mapping.put(SqlTokenType.AND, new LayoutMapping(0, 0, Newline.PRE)); mapping.put(SqlTokenType.OR, new LayoutMapping(0, 0, Newline.PRE)); mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); mapping.put(SqlTokenType.JOIN, new LayoutMapping(0, 0, Newline.PREPOST)); mapping.put(SqlTokenType.CREATE_TABLE, new LayoutMapping(0, 0, Newline.POST)); mapping.put(SqlTokenType.ALTER_TABLE, new LayoutMapping(0, 0, Newline.PRE)); mapping.put(SqlTokenType.ADD, new LayoutMapping(0, 0, Newline.PRE)); mapping.put(SqlTokenType.DELETE_FROM, new LayoutMapping(0, 0, Newline.PRE)); mapping.put(SqlTokenType.CASE, new LayoutMapping(0, 1, Newline.PREPOST)); mapping.put(SqlTokenType.WHEN, new LayoutMapping(0, 0, Newline.PRE)); mapping.put(SqlTokenType.THEN, new LayoutMapping(0, 0, Newline.NONE)); mapping.put(SqlTokenType.ELSE, new LayoutMapping(0, 0, Newline.NONE)); mapping.put(SqlTokenType.END, new LayoutMapping(-1, 0, Newline.PREPOST)); mapping.put(SqlTokenType.EXISTS, new LayoutMapping(0, 0, Newline.NONE)); mapping.put(SqlTokenType.NULL, new LayoutMapping(0, 0, Newline.PRE)); } private Map<Integer, String> cache = new HashMap<Integer, String>(); public static String getHTMLFormattedSQL2(String sql) { String formatted = null; //cache.get(sql.hashCode()); if (formatted == null) { StringBuilder result = new StringBuilder(); List<SqlToken> tokens = tokenizer.parse(sql); int indent = 0; for (SqlToken token : tokens) { LayoutMapping lm = mapping.get(token.getType()); if (lm == null) { lm = defaultMapping; } indent += lm.getPreIndent(); if (indent < 0) { indent = 0; } if (lm.preNewline()) { result.append("\n"); indentation(indent, result); } if (lm != defaultMapping) { result.append("<b>"); } result.append(token.getText()); if (lm != defaultMapping) { result.append("</b>"); } result.append(" "); indent += lm.getPostIndent(); if (lm.postNewline()) { result.append("\n"); indentation(indent, result); } } formatted = result.toString(); //tcache.put(sql.hashCode(), formatted); } return formatted; } public static void indentation(int nr, StringBuilder result) { while (nr-- > 0) { result.append(" "); } } public static String getHTMLFormattedSQL(String sql) { StringBuilder tmp = new StringBuilder(" "+sql.toLowerCase()); String tab = " "; String ntab = "\n"+tab; replaceAll(tmp, " left outer join ", ntab+blue("LEFT OUTER JOIN ")); replaceAll(tmp, " create table ", blue("CREATE TABLE")+ntab); replaceAll(tmp, " insert into ", blue("INSERT INTO")+ntab); replaceAll(tmp, " delete from ", blue("DELETE FROM")+ntab); replaceAll(tmp, " values\\(", "\n"+blue("VALUES")+"("); replaceAll(tmp, " select ", blue("SELECT")+ntab); replaceAll(tmp, " from ", "\n"+blue("FROM")+ntab); replaceAll(tmp, " where ", "\n"+blue("WHERE")+ntab); replaceAll(tmp, " order by ", "\n"+blue("ORDER BY")+ntab); replaceAll(tmp, " group by ", "\n"+blue("GROUP BY")+ntab); replaceAll(tmp, " having ", "\n"+blue("HAVING")+tab); replaceAll(tmp, " update ", blue("UPDATE")+ntab); replaceAll(tmp, " set ", "\n"+blue("SET")+ntab); replaceAll(tmp, " commit", "\n"+blue("COMMIT")); replaceAll(tmp, " rollback", "\n"+blue("ROLLBACK")); replaceAll(tmp, " close", "\n"+blue("CLOSE")); replaceAll(tmp, " case ", ntab+blue("CASE ")); replaceAll(tmp, " when ", ntab+blue("WHEN ")); replaceAll(tmp, " is ", blue(" IS ")); replaceAll(tmp, " then ", blue(" THEN ")); replaceAll(tmp, " not ", blue(" NOT ")); replaceAll(tmp, " end ", blue(" END ")); replaceAll(tmp, " null ", blue(" NULL ")); replaceAll(tmp, " else ", blue(" ELSE ")); replaceAll(tmp, " on ", blue(" ON ")); replaceAll(tmp, " and ", ntab+blue("AND ")); replaceAll(tmp, " or ", ntab+blue("OR ")); replaceAll(tmp, " as ", blue(" AS ")); replaceAll(tmp, ", ", ","+ntab); return tmp.toString(); } private static void replaceAll(StringBuilder str, String txt, String repl) { int index = -1; while((index = str.indexOf(txt)) > -1) { str.replace(index, index + txt.length(), repl); } } public static String blue(String text) { return "<span style=\"color: blue;\">"+text+"</span>"; } public static void main(String [] args) { SqlFormatter f = new SqlFormatter(); System.out.println(f.getHTMLFormattedSQL2("CREATE TABLE TEST291 (ID INT PRIMARY KEY, NAME VARCHAR(255))")); } }