SQL.java

/*Copyright (C) 2020 THL A29 Limited, a Tencent company.  All rights reserved.

This source code is licensed under the Apache License Version 2.0.*/


package apijson;

/**SQL语句，函数名尽量和JDK中相同或类似功能的函数的名称一致
 * @author Lemon
 */
public class SQL {

	public static final String OR = " OR ";
	public static final String AND = " AND ";
	public static final String NOT = " NOT ";
	public static final String AS = " AS ";
	public static final String IS = " is ";
	public static final String NULL = " null ";
	
	//括号必须紧跟函数名！ count (...) 报错！
	public static final String COUNT = "count";
	public static final String SUM = "sum";
	public static final String MAX = "max";
	public static final String MIN = "min";
	public static final String AVG = "avg";

	/**
	 * isNull = true 
	 * @return {@link #isNull(boolean)}
	 */
	public static String isNull() {
		return isNull(true);
	}
	/**
	 * @param isNull
	 * @return {@link #IS} + (isNull ? "" : {@link #NOT}) + {@link #NULL};
	 */
	public static String isNull(boolean isNull) {
		return IS + (isNull ? "" : NOT) + NULL;
	}
	/**
	 * isNull = true
	 * @param s
	 * @return {@link #isNull(String, boolean)}
	 */
	public static String isNull(String s) {
		return isNull(s, true);
	}
	/**
	 * @param s
	 * @param isNull
	 * @return s + {@link #isNull(boolean)}
	 */
	public static String isNull(String s, boolean isNull) {
		return s + isNull(isNull);
	}

	/**
	 * isEmpty = true
	 * @param s
	 * @return {@link #isEmpty(String, boolean)}
	 */
	public static String isEmpty(String s) {
		return isEmpty(s, true);
	}
	/**
	 * trim = false
	 * @param s
	 * @param isEmpty
	 * @return {@link #isEmpty(String, boolean, boolean)}
	 */
	public static String isEmpty(String s, boolean isEmpty) {
		return isEmpty(s, isEmpty, false);
	}
	/**
	 * nullable = true
	 * @param s
	 * @param isEmpty <=0
	 * @param trim s = trim(s);
	 * @return {@link #isEmpty(String, boolean, boolean, boolean)}
	 */
	public static String isEmpty(String s, boolean isEmpty, boolean trim) {
		return isEmpty(s, isEmpty, trim, true);
	}
	/**
	 * @param s
	 * @param isEmpty <=0
	 * @param trim s = trim(s);
	 * @param nullable isNull(s, true) + {@link #OR} +
	 * @return {@link #lengthCompare(String, String)}
	 */
	public static String isEmpty(String s, boolean isEmpty, boolean trim, boolean nullable) {
		if (trim) {
			s = trim(s);
		}
		return (nullable ? isNull(s, true) + OR : "") + lengthCompare(s, (isEmpty ? "<=" : ">") + "0");
	}
	/**
	 * @param s 因为POWER(x,y)等函数含有不只一个key，所以需要客户端添加进去，服务端检测到条件中有'('和')'时就不转换，直接当SQL语句查询
	 * @return {@link #length(String)} + compare
	 */
	public static String lengthCompare(String s, String compare) {
		return length(s) + compare;
	}


	/**
	 * @param s 因为POWER(x,y)等函数含有不只一个key，所以需要客户端添加进去，服务端检测到条件中有'('和')'时就不转换，直接当SQL语句查询
	 * @return "length(" + s + ")"
	 */
	public static String length(String s) {
		return "length(" + s + ")";
	}
	/**
	 * @param s 因为POWER(x,y)等函数含有不只一个key，所以需要客户端添加进去，服务端检测到条件中有'('和')'时就不转换，直接当SQL语句查询
	 * @return "char_length(" + s + ")"
	 */
	public static String charLength(String s) {
		return "char_length(" + s + ")";
	}

	/**
	 * @param s
	 * @return "trim(" + s + ")"
	 */
	public static String trim(String s) {
		return "trim(" + s + ")";
	}
	/**
	 * @param s
	 * @return "ltrim(" + s + ")"
	 */
	public static String trimLeft(String s) {
		return "ltrim(" + s + ")";
	}
	/**
	 * @param s
	 * @return "rtrim(" + s + ")"
	 */
	public static String trimRight(String s) {
		return "rtrim(" + s + ")";
	}

	/**
	 * @param s
	 * @param n
	 * @return "left(" + s + "," + n + ")"
	 */
	public static String left(String s, int n) {
		return "left(" + s + "," + n + ")";
	}
	/**
	 * @param s
	 * @param n
	 * @return "right(" + s + "," + n + ")"
	 */
	public static String right(String s, int n) {
		return "right(" + s + "," + n + ")";
	}

	/**
	 * @param s
	 * @param start
	 * @param end
	 * @return "substring(" + s + "," + start + "," + (end-start) + ")"
	 */
	public static String subString(String s, int start, int end) {
		return "substring(" + s + "," + start + "," + (end-start) + ")";
	}

	/**
	 * @param s
	 * @param c
	 * @return "instr(" + s + ", " + c + ")"
	 */
	public static String indexOf(String s, String c) {
		return "instr(" + s + ", " + c + ")";
	}

	/**
	 * @param s
	 * @param c1
	 * @param c2
	 * @return "replace(" + s + ", " + c1 + ", " + c2 + ")"
	 */
	public static String replace(String s, String c1, String c2) {
		return "replace(" + s + ", " + c1 + ", " + c2 + ")";
	}
	
	/**
	 * @param s1
	 * @param s2
	 * @return "concat(" + s1 + ", " + s2 + ")"
	 */
	public static String concat(String s1, String s2) {
		return "concat(" + s1 + ", " + s2 + ")";
	}

	/**
	 * @param s1
	 * @param s2
	 * @return "strcmp(" + s1 + ", " + s2 + ")"
	 */
	public static String equals(String s1, String s2) {
		return "strcmp(" + s1 + ", " + s2 + ")";
	}

	/**
	 * @param s
	 * @return "upper(" + s + ")"
	 */
	public static String toUpperCase(String s) {
		return "upper(" + s + ")";
	}
	/**
	 * @param s
	 * @return "lower(" + s + ")"
	 */
	public static String toLowerCase(String s) {
		return "lower(" + s + ")";
	}

	
	

	//column and function<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
	
	/**字段
	 * @param column
	 * @return column.isEmpty() ? "*" : column;
	 */
	public static String column(String column) {
		column = StringUtil.getTrimedString(column);
		return column.isEmpty() ? "*" : column;
	}
	/**有别名的字段
	 * @param column
	 * @return {@link #count(String)} + {@link #AS};
	 */
	public static String columnAs(String column) {
		return count(column) + AS;
	}
	
	/**函数
	 * @param column if (StringUtil.isEmpty(column, true) || column.contains(",")) -> column = null;
	 * @return " " + fun + "(" + {@link #column(String)} + ") ";
	 */
	public static String function(String fun, String column) {
		if (StringUtil.isEmpty(column, true) || column.contains(",")) {
			column = null; //解决 count(id,name) 这种多个字段导致的SQL异常
		}
		return " " + fun + "(" + column(column) + ") ";
	}
	/**有别名的函数
	 * @param column
	 * @return {@link #function(String, String)} + {@link #AS} + fun;
	 */
	public static String functionAs(String fun, String column) {
		return function(fun, column) + AS + fun + " ";
	}
	
	/**计数
	 * column = null
	 * @return {@link #count(String)}
	 */
	public static String count() {
		return count(null);
	}
	/**计数
	 * fun = {@link #COUNT}
	 * @param column
	 * @return {@link #functionAs(String, String)}
	 */
	public static String count(String column) {
		return functionAs(COUNT, column);
	}
	/**求和
	 * fun = {@link #SUM}
	 * @param column
	 * @return {@link #functionAs(String, String)}
	 */
	public static String sum(String column) {
		return functionAs(SUM, column);
	}
	/**最大值
	 * fun = {@link #MAX}
	 * @param column
	 * @return {@link #functionAs(String, String)}
	 */
	public static String max(String column) {
		return functionAs(MAX, column);
	}
	/**最小值
	 * fun = {@link #MIN}
	 * @param column
	 * @return {@link #functionAs(String, String)}
	 */
	public static String min(String column) {
		return functionAs(MIN, column);
	}
	/**平均值
	 * fun = {@link #AVG}
	 * @param column
	 * @return {@link #functionAs(String, String)}
	 */
	public static String avg(String column) {
		return functionAs(AVG, column);
	}

	//column and function>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	
	
	
	//search<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

	public static final int SEARCH_TYPE_CONTAIN_FULL = 0;
	public static final int SEARCH_TYPE_CONTAIN_ORDER = 1;
	public static final int SEARCH_TYPE_CONTAIN_SINGLE = 2;
	public static final int SEARCH_TYPE_CONTAIN_ANY = 3;
	public static final int SEARCH_TYPE_START = 4;
	public static final int SEARCH_TYPE_END = 5;
	public static final int SEARCH_TYPE_START_SINGLE = 6;
	public static final int SEARCH_TYPE_END_SINGLE = 7;
	public static final int SEARCH_TYPE_PART_MATCH = 8;
	/**获取搜索值
	 * @param s
	 * @return
	 */
	public static String search(String s) {
		return search(s, SEARCH_TYPE_CONTAIN_FULL);
	}
	/**获取搜索值
	 * @param s
	 * @param type
	 * @return
	 */
	public static String search(String s, int type) {
		return search(s, type, true);
	}
	/**获取搜索值
	 * @param s
	 * @param type
	 * @param ignoreCase
	 * @return default SEARCH_TYPE_CONTAIN_FULL
	 */
	public static String search(String s, int type, boolean ignoreCase) {
		if (s == null) {
			return null;
		}
		switch (type) {
		case SEARCH_TYPE_CONTAIN_SINGLE:
			return "_" + s + "_";
		case SEARCH_TYPE_CONTAIN_ORDER:
			char[] cs = s.toCharArray();
			if (cs == null) {
				return null;
			}
			String value = "%";
			for (int i = 0; i < cs.length; i++) {
				value += cs[i] + "%";
			}
			return value;
		case SEARCH_TYPE_START:
			return s + "%";
		case SEARCH_TYPE_END:
			return "%" + s;
		case SEARCH_TYPE_START_SINGLE:
			return s + "_";
		case SEARCH_TYPE_END_SINGLE:
			return "_" + s;
		case SEARCH_TYPE_CONTAIN_ANY:
		case SEARCH_TYPE_PART_MATCH:
			cs = s.toCharArray();
			if (cs == null) {
				return null;
			}
			value = "";
			for (int i = 0; i < cs.length; i++) {
				value += search("" + cs[i], SEARCH_TYPE_CONTAIN_FULL, ignoreCase);
			}
			return value;
		default://SEARCH_TYPE_CONTAIN_FULL
			return "%" + s + "%";
		}
	}
	
	//search>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

}