QueryBuilderHelper.java

package com.ibiz.util.helper;

import com.ibiz.util.SearchContext;
import com.ibiz.util.SearchFieldFilter;
import com.ibiz.util.SearchFilter;
import com.ibiz.util.SearchGroupFilter;
import com.ibiz.util.enums.SearchFieldType;
import com.ibiz.util.enums.SearchGroupType;

import org.apache.commons.lang3.EnumUtils;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import java.util.List;

/**
 * 关系型数据库SQL转换工具类
 */
@Component
public class QueryBuilderHelper {
    /**
     * 解析SearchContext中的field与group，返回sql
     * @param context
     * @return
     */
    public String buildSQL(SearchContext context){
        String resultCond="";
        StringBuffer sbfSQL=new StringBuffer();
        List<SearchFilter> conditions=context.getCondition();

        String customCond=context.getCustomCond();//自定义条件查询

        String defaultGroupType="AND";////组内条件的组合关系(AND/OR)

        for(SearchFilter cond: conditions){//条件查询
            String tempCond = "";
            if(cond instanceof SearchFieldFilter){ //单条件查询
                tempCond=parseFieldCond((SearchFieldFilter) cond);
            }
            else if (cond instanceof SearchGroupFilter){//组条件查询
                tempCond=parseGroupCond((SearchGroupFilter) cond);
            }

            if(!StringUtils.isEmpty(tempCond))
                sbfSQL.append(String.format("%s %s ",defaultGroupType,tempCond));
        }
        
        for (String key : context.getParams().keySet()) {
			String[] keys = key.split("_") ;
			if(keys.length ==3 && keys[0].equals("n") && EnumUtils.isValidEnumIgnoreCase(SearchFieldType.class, keys[2]) ){
				SearchFieldFilter field = new SearchFieldFilter() ;
				field.setCondition(EnumUtils.getEnumIgnoreCase(SearchFieldType.class, keys[2]));
				field.setParam(keys[1]);
				field.setValue(context.getParams().get(key));
				sbfSQL.append(String.format("%s %s ",StringUtils.isEmpty(sbfSQL.toString())?"":defaultGroupType,parseFieldCond(field)));
			}
		}

       if(!StringUtils.isEmpty(customCond))//自定义条件查询
           sbfSQL.append(String.format("%s %s ",defaultGroupType,customCond));

        resultCond=parseResult(sbfSQL,defaultGroupType);

        return resultCond;
    }

    /**
     * 字段条件解析
     * @param field
     * @return
     */
    private String parseFieldCond(SearchFieldFilter field){
        String strSQL="";
        String param=field.getParam();
        SearchFieldType cond=field.getCondition();
        Object value=field.getValue();
        if(StringUtils.isEmpty(param)|| StringUtils.isEmpty(cond))
            return strSQL;

        switch (cond){
            case GT:
                strSQL=String.format(" %s > '%s'",param ,value); break;
            case GTANDEQ:
                strSQL=String.format(" %s >= '%s'",param ,value); break;
            case EQ:
                strSQL=String.format(" %s = '%s'",param ,value); break;
            case NOTEQ:
                strSQL=String.format(" %s <> '%s'",param ,value); break;
            case LT:
                strSQL=String.format(" %s < '%s'",param ,value); break;
            case LTANDEQ:
                strSQL=String.format(" %s <= '%s'",param ,value); break;
            case LIKE:
                strSQL=String.format(" %s like '%%%s%%'",param ,value); break;
            case LEFTLIKE:
                strSQL=String.format(" %s like '%s%%'",param ,value); break;
            case RIGHTLIKE:
                strSQL=String.format(" %s like '%%%s'",param ,value); break;
            case ISNULL:
                strSQL=String.format(" %s is null",param); break;
            case ISNOTNULL:
                strSQL=String.format(" %s is not null ",param); break;
            case IN:
                if(value instanceof List){
                    String tempValue=formatStringArr((List<String>) value);
                    if(!StringUtils.isEmpty(tempValue))
                        strSQL=String.format(" %s in (%s)",param,tempValue); break;
                }
                break;
            case NOTIN:
                if(value instanceof List){
                    String tempValue=formatStringArr((List<String>) value);
                    if(!StringUtils.isEmpty(tempValue))
                        strSQL=String.format(" %s not in (%s)",param,tempValue); break;
                }
                 break;
        }
        return strSQL;
    }

    /**
     * 组条件解析
     * @param group
     * @return
     */
    private String parseGroupCond(SearchGroupFilter group){
        String sql="";
        List<SearchFilter> groupCond=group.getCondition();//组内条件
        SearchGroupType groupType=group.getSearchGroupType();//组内条件的组合关系(AND/OR)
        if(groupCond.size()==0 || StringUtils.isEmpty(groupType))
            return sql;

        String resultCond="";
        String strGroupType=getGroupType(groupType);
        StringBuffer sbfSQL=new StringBuffer();

        for(SearchFilter cond : groupCond){
            String tempCond = "";
            if(cond instanceof SearchFieldFilter){ //单条件查询
                tempCond=parseFieldCond((SearchFieldFilter) cond);
            }
            else if (cond instanceof SearchGroupFilter){//组条件查询
                tempCond=parseGroupCond((SearchGroupFilter) cond);
            }

            if(!StringUtils.isEmpty(tempCond))
                sbfSQL.append(String.format("%s %s ",strGroupType,tempCond));
        }
        resultCond=parseResult(sbfSQL,strGroupType);
        resultCond=String.format("(%s)",resultCond);

        return resultCond;
    }

    /**
     * 组内条件的组合关系(AND/OR)
     * @param groupType
     * @return
     */
    private String getGroupType(SearchGroupType groupType){
        String strGroupType="";
        switch(groupType){
            case AND: strGroupType="AND";break;
            case OR: strGroupType="OR"; break;
        }
        return strGroupType;
    }

    /**
     * 格式转换
     * @param cond
     * @param operator
     * @return
     */
    private String parseResult(StringBuffer cond,String  operator){
        String resultCond = cond.toString();
        if (resultCond.startsWith(operator))
            resultCond = resultCond.replaceFirst(operator, "");
        if (resultCond.endsWith(operator))
            resultCond = resultCond.substring(0, resultCond.lastIndexOf(operator));
        return resultCond;
    }

    /**
     * 转换[a,b]格式字符串到 'a','b'格式
     *
     * @return
     */
    private String formatStringArr(List<String> array) {
        String[] arr = array.toArray(new String[array.size()]);
        return "'" + String.join("','",arr) + "'";
    }

}