Sql Format

最新推荐文章于 2023-02-18 12:49:35 发布

原创最新推荐文章于 2023-02-18 12:49:35 发布 · 334 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

Oracle 专栏收录该内容

0 篇文章

订阅专栏

SQLLinForm.jar


SQLForm form = new SQLForm();
		form.setCase(false, false);
		form.setLowerCase(false);
		form.setGraphLevel(false);
		form.setSuppressSpace(true);
		form.setQuoteCharacter("'");
		form.setSuppressEmptyLine(true);
		form.setFormatLanguage("SQL");
		form.setBracketSpaces("noSpacesAroundBracket");
		form.setCommaSpaces("oneSpaceAfterComma");
		form.setEqualSpaces("oneSpaceAroundEqual");
		form.setSmallSQLWidth(80);
		form.setPageWidth(80);
		form.setAndOrIndention(true);
		form.setInitialIndentation(8);
		String formatSql = form.formatSQLAsString(SQL);
		System.out.println(formatSql);

My impl:


package com.statestreet.sqlformat;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SqlFormat {

	private final static String SQL = 
		"select aa,bb,cc,dd from userrole ur,fdsaf aa where ur.pp='haha' and ur.aa='cc' and c = (select c,fds,s,s from b where a=a) order by ur.cc";

	private final static String INDENT = "\t";
	private final static String ENTER = "\n";
	private final static String COMMA = ",";
	private final static Boolean EXPAND_COMMA_LIST = false;
	private final static String[] KEYWORDS = new String[]{"select","from","where","and","order by"};
	private final static Map<String,Integer> KEYCOUNT= new HashMap<String,Integer>();
	/**
	 * @param args
	 */
	public static void main(String[] args) { 
		String tempSql = SQL;
		for(String keyword:KEYWORDS){
			String formatSql = format(keyword,tempSql);
			tempSql = formatSql;
		}
		int length = KEYWORDS.length-1;
		tempSql = tempSql.substring(length);
		System.out.println(tempSql);
	}

	private static String format(String keyword,String normalSql) {
		List<String> sectionList = splitKeyWords(keyword,normalSql);
		//System.out.println(sectionList);
		//System.out.println(sectionList.size());
		String formatsql = packageSql(sectionList);
		//System.out.println(formatsql);
		return formatsql;
	}

	private static String packageSql(List<String> sectionList) {
		StringBuffer sb = new StringBuffer();
		String previouKeyWord = null;
		for(String section:sectionList){ 
			if(section != null && !section.equals("")){
				if(isKeyWords(section)){ 
					if(KEYCOUNT.containsKey(section)){
						if(!section.equalsIgnoreCase("and")){
							KEYCOUNT.put(section, KEYCOUNT.get(section)+1);
						} 
					}else{
						KEYCOUNT.put(section, 1);
					} 
					Integer count = KEYCOUNT.get(section);
					for(int i=1;i<count;i++){
						sb.append(INDENT);
					}
					sb.append(section.toUpperCase()).append(ENTER);
					previouKeyWord = section;
				}else{
					Integer count = KEYCOUNT.get(previouKeyWord);
					if(count!=null){
						for(int i=1;i<count;i++){
							sb.append(INDENT);
						}
					}
					if(EXPAND_COMMA_LIST){
						String subsections[] = section.split(COMMA);
						for(String subsection:subsections){
							sb.append(INDENT).append(subsection).append(COMMA).append(ENTER);
						}
					}else{
						sb.append(INDENT).append(section).append(ENTER);
					}
				}

			}
		}
		return sb.toString();
	}

	private static boolean isKeyWords(String section) {
		for(String keyword:KEYWORDS){
			if(keyword.equalsIgnoreCase(section)){
				return true;
			}
		}
		return false;
	}

	private static List<String> splitKeyWords(String keyword,String normalSql) {
		String tempsql = normalSql;
		List<String> sectionList = new ArrayList<String>();  
		String sections[] = tempsql.split(keyword);
		for(String section:sections){ 
			 sectionList.add(section);
			 sectionList.add(keyword); 
		} 
		sectionList=sectionList.subList(0, sectionList.size()-1);
		return sectionList;
	}

}