com.webapp.mybatis.helper.SqlHelper Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of utils-mybatis Show documentation
The newest version!
package com.webapp.mybatis.helper;

import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.StringJoiner;
import java.util.function.Function;
import java.util.stream.Collectors;

import org.apache.commons.lang3.StringUtils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.webapp.mybatis.helper.Cnds.Op;
import com.webapp.mybatis.helper.Cnds.Order;
import com.webapp.utils.string.Utils;

/**
 * @author xincl
 * @date 2017年4月1日
 * @desc handle sql helper
 */
public class SqlHelper {

	protected static final String PKEY = "key";
	protected static final String PVAL = "val";
	protected static final String TABLE = "##table##";
	private static final String INDEX = "##index##";
	protected static final String SEL_COLS = "##selCols##";
	protected static Style style = Style.snake;
	protected enum Style {
		snake, camel;
	}
	protected enum AndOr{
		AND, OR;
	}
	protected enum Keys{
		ORDER("ORDER BY"), LIMIT("LIMIT");

		private String keys;
		Keys(String keys){
			this.keys = keys;
		}
		public String toString() {
			return this.keys.toString();
		}
	}

	static class ColAndVal{
		String table;
		String selCols;
		String insCols;
		String insVals;
		public ColAndVal(String table, String selCols, String insCols, String insVals) {
			super();
			this.table = table;
			this.selCols = selCols;
			this.insCols = insCols;
			this.insVals = insVals;
		}
		public String getTable() {
			return table;
		}
		public void setTable(String table) {
			this.table = table;
		}
		public String getSelCols() {
			return selCols;
		}
		public void setSelCols(String selCols) {
			this.selCols = selCols;
		}
		public String getInsCols() {
			return insCols;
		}
		public void setInsCols(String insCols) {
			this.insCols = insCols;
		}
		public String getInsVals() {
			return insVals;
		}
		public void setInsVals(String insVals) {
			this.insVals = insVals;
		}
	}


	//  Provide for #Cnds //
	/**
	 * build sql limit
	 */
	protected static String limit(int index, int count){
		return String.format("%1$s %2$s,%3$s", Keys.LIMIT, index, count);
	}

	/**
	 * build sql order by
	 */
	protected static String orderBy(Order order, String ...name){
		return String.format("%1$s %2$s %3$s", Keys.ORDER, spacer(name), order.name());
	}

	/**
	 * build sql between
	 */
	protected static String between(String name){
		return String.format("%1$s %2$s %3$s AND %4$s", spacer(name), Op.BETWEEN, placeholder(name), placeholder(name));
	}

	/**
	 * build sql in
	 */
	protected static String in(String name, Op inOrNot, int count){
		String sj = handleJoiner(count, new StringJoiner(",", "(", ")"), (i)->placeholder(name));
		return String.format("%1$s %2$s %3$s", spacer(name), inOrNot, sj);
	}

	protected static String like(String name, Op likeOrNot){
		return where(name, likeOrNot);
	}

	/**
	 * build sql where
	 */
	protected static String where(String name, Op op){
		return where(name, name, op, null);
	}
	private static String where(String name, String placeCol, Op op, String prefix){
		if(StringUtils.isNotEmpty(prefix)){
			placeCol = String.format("%1$s.%2$s", prefix, placeCol);
		}
		return String.format("%1$s %2$s %3$s", spacer(name), op, placeholder(placeCol));
	}
	protected static String whereAlone(String name, Op op){
		return String.format("%1$s %2$s", spacer(name), op);
	}

	/**
	 * build sql all
	 */
	protected static String all(String name, Op op, int count){
		String where = "";
		if(op.equals(Op.BETWEEN)){
			if(count != 2) {
				throw new RuntimeException("Parameter number is not correct");
			}
			where = between(name);
		}else if(op.equals(Op.ISNULL) || op.equals(Op.NISNULL)){
			where = whereAlone(name, op);
		}else if(op.equals(Op.IN) || op.equals(Op.NIN)){
			where = in(name, op, count);
		}else {
			if(count != 1) {
				throw new RuntimeException("Parameter number is not correct");
			}
			where = where(name, op);
		}
		return where;
	}


	//  Provide for #Sqls //
	/**
	 * handle insert column and values
	 */
	protected static  ColAndVal handleInsColsAndVals(T model) {
//		JSONObject json = JSON.parseObject(JSON.toJSONString(model));
		Field[] fields = model.getClass().getDeclaredFields();

		StringJoiner insCols = new StringJoiner(",", "(", ")");
		StringJoiner insVals = new StringJoiner(",", "(", ")");
//		json.forEach((key,val)->{
//			insCols.add(spacer(key));
//			insVals.add(placeholder(key));
//		});
		for(Field field : fields){
			insCols.add(spacer(field.getName()));
			insVals.add(placeholder(field.getName()));
		}

		return new ColAndVal(spacer(model.getClass().getSimpleName()), "", insCols.toString(), insVals.toString());
	}

	/**
	 * handle insert column and values for list
	 */
	protected static  ColAndVal handleInsColsAndVals(List models) {
		int count = models.size();

		Class modelClz = models.get(0).getClass();

		ColAndVal cav = handleColsAndVals(modelClz);
		String imc = cav.getInsVals();
		String sj = handleJoiner(count, new StringJoiner(","), (i)->imc.replace(INDEX, i));

		cav.setInsVals(sj);
		return cav;
	}

	protected static  ColAndVal handleColsAndVals(Class modelClz){
		String table = modelClz.getSimpleName();

		StringJoiner selCols = new StringJoiner(",");
		StringJoiner insCols = new StringJoiner(",");
		StringJoiner insMulCols = new StringJoiner(",", "(", ")");

		Field[] fields = modelClz.getDeclaredFields();
		Arrays.stream(fields).forEach(f->{
	        String prop = f.getName();

	        String col = spacer(prop);
	        selCols.add(String.format("%1$s %2$s", col, col.contains(prop) ? "" : prop).trim());
			insCols.add(col);
			insMulCols.add(placeholder(String.format("%1$s[%2$s].%3$s", PKEY, INDEX, prop)));
		});
		return new ColAndVal(spacer(table), selCols.toString(), insCols.toString(), insMulCols.toString());
	}

	/**
	 * handle select column
	 */
	protected static String handleSelCols(String... cols){
		return Arrays.asList(cols).stream().map((c)->spacer(c)).collect(Collectors.joining(","));
	}

	/**
	 * handle update set
	 */
	protected static  String handleUpdSets(T set) {
		return handleJoiner(set, new StringJoiner(","), key->where(key, Op.EQ));
	}
	/**
	 * handle update set where prefix
	 */
	protected static  String handleUpdSets(T set, String prefix) {
		return handleJoiner(set, new StringJoiner(","), key->where(key, key, Op.EQ, prefix));
	}

	//  Provide for #BaseDao //
	protected static String id(){
		return where("id", Op.EQ);
	}
	protected static String pk(Map param){
		return where(param.get(PKEY).toString(), param.get(PKEY).toString(), Op.EQ, PVAL);
	}
	protected static String kv(Map param){
		return where(param.get(PKEY).toString(), PVAL, Op.EQ, null);
	}
	protected static  String model(Map model){
		StringJoiner sj = new StringJoiner(" and ");
		model.forEach((key, val)->{
			sj.add(where(key, Op.EQ));
		});
		return sj.toString();
	}

	private static  String handleJoiner(int count, StringJoiner sj, Function apply){
		for(int i=0; i String handleJoiner(T model, StringJoiner sj, Function apply){
		JSONObject json = JSON.parseObject(JSON.toJSONString(model));
		json.forEach((key, val)->{ sj.add(apply.apply(key)); });
		return sj.toString();
	}

	/**
	 * storage field and value
	 */
	protected static String placeholder(String field){
		return String.format("#{%1$s}", field);
	}
	protected static String replace(String field){
		return String.format("#\\{%1$s\\}", field);
	}

	/**
	 * Add spacer to database field
	 */
	protected static String spacer(String... field){
		return Arrays.asList(field).stream().map(n->{
			if(style.equals(Style.snake)){
				n = Utils.toSnake(n);
			}else if (style.equals(Style.camel)) {
				n = Utils.toCamel(n);
			}
			return n.startsWith("`") ? n : String.format("`%s`", n);
		}).collect(Collectors.joining(","));
	}
	protected static String daoReplace(String field){
		return String.format("`%s`", field);
	}

}