net.sf.jsqlparser.expression.Function Java Examples

The following examples show how to use net.sf.jsqlparser.expression.Function. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: FunctionCountTest.java    From Mybatis-PageHelper with MIT License 6 votes vote down vote up 
@Test
public void test() {
    Select select = select("select max(name),code,min(aa),nvl(ab,0),heh from user where a > 100");
    List<SelectItem> selectItems = ((PlainSelect) select.getSelectBody()).getSelectItems();
    for (SelectItem item : selectItems) {
        if (item instanceof SelectExpressionItem) {
            Expression exp = ((SelectExpressionItem) item).getExpression();
            if (exp instanceof Function) {
                System.out.println("Function:" + item.toString());
            } else {
                System.out.println("Not a function:" + exp.toString());
            }
        } else {
            System.out.println("Not a function:" + item.toString());
        }
    }
}
Example #2
Source File: CountSqlParser.java    From FastSQL with Apache License 2.0 6 votes vote down vote up 
/**
 * 是否可以用简单的count查询方式
 *
 */
public boolean isSimpleCount(PlainSelect select) {
    //包含group by的时候不可以
    if (select.getGroupByColumnReferences() != null) {
        return false;
    }
    //包含distinct的时候不可以
    if (select.getDistinct() != null) {
        return false;
    }
    for (SelectItem item : select.getSelectItems()) {
        //select列中包含参数的时候不可以，否则会引起参数个数错误
        if (item.toString().contains("?")) {
            return false;
        }
        //如果查询列中包含函数，也不可以，函数可能会聚合列
        if (item instanceof SelectExpressionItem) {
            if (((SelectExpressionItem) item).getExpression() instanceof Function) {
                return false;
            }
        }
    }
    return true;
}
Example #3
Source File: CTEToNestedQueryConverter.java    From quetzal with Eclipse Public License 2.0 6 votes vote down vote up 
@Override
public void visit(Function function) {
	clear();
	boolean tmpLeftTableFound = false;
	boolean tmpRightTableFound = false;
	boolean tmpWhereOnlyExpFound = false;
	boolean prevIsTopVal = isTopLevel;
	isTopLevel = false;
	if (function.getParameters()!=null) {
		for (Expression e: function.getParameters().getExpressions()) {
			e.accept(this);
			tmpLeftTableFound |= leftTableFound;
			tmpRightTableFound |= rightTableFound;
			tmpWhereOnlyExpFound |= whereOnlyExpFound;
		}
	}
	leftTableFound = tmpLeftTableFound;
	rightTableFound = tmpRightTableFound;
	whereOnlyExpFound = tmpWhereOnlyExpFound;
	isTopLevel = prevIsTopVal;
	defaultTopLevelProcessing(function);
}
Example #4
Source File: QueryConverter.java    From sql-to-mongo-db-query-converter with Apache License 2.0 6 votes vote down vote up 
private void parseFunctionForAggregation(Function function, Document document, List<String> groupBys, Alias alias) throws ParseException {
    String op = function.getName().toLowerCase();
    String aggField = SqlUtils.generateAggField(function, alias);
    switch(op) {
    	case "count":
    		document.put(aggField,new Document("$sum",1));
    		break;
    	case "sum":
    	case "min":
    	case "max":
    	case "avg":
    		createFunction(op,aggField, document,"$"+ SqlUtils.getFieldFromFunction(function));
    		break;
    	default:
    		throw new ParseException("could not understand function:" + function.getName());
    }
}
Example #5
Source File: ObjPD.java    From openprodoc with GNU Affero General Public License v3.0 6 votes vote down vote up 
private int EvalExprType(Expression where)
{
if (where instanceof AndExpression)
    return (EXPR_AND);
else if (where instanceof OrExpression)
    return (EXPR_OR);
else if (where instanceof BinaryExpression)
    return(EXPR_BASIC);
else if (where instanceof Function)
    return(EXPR_FUNCT);
else if (where instanceof Parenthesis)
    return (EXPR_PAR);
else if (where instanceof InExpression) 
    return (EXPR_IN);
else if (where instanceof NotExpression) 
    return (EXPR_NOT);
return(-1);
}
Example #6
Source File: SqlUtils.java    From sql-to-mongo-db-query-converter with Apache License 2.0 6 votes vote down vote up 
public static Object parseFunctionArguments(final ExpressionList parameters,
                                            final FieldType defaultFieldType,
                                            final Map<String, FieldType> fieldNameToFieldTypeMapping) {
    if (parameters == null) {
        return null;
    } else if (parameters.getExpressions().size()==1) {
        return getStringValue(parameters.getExpressions().get(0));
    } else {
        return Lists.newArrayList(Lists.transform(parameters.getExpressions(),
                new com.google.common.base.Function<Expression, Object>() {
                    @Override
                    public Object apply(Expression expression) {
                        try {
                            return getValue(expression, null, defaultFieldType,
                                    fieldNameToFieldTypeMapping);
                        } catch (ParseException e) {
                            return getStringValue(expression);
                        }
                    }
                }));
    }
}
Example #7
Source File: SqlUtils.java    From sql-to-mongo-db-query-converter with Apache License 2.0 6 votes vote down vote up 
private static RegexFunction getRegexFunction(Function function) throws ParseException {
    final String column = getStringValue(function.getParameters().getExpressions().get(0));
    final String regex = fixDoubleSingleQuotes(
        ((StringValue) (function.getParameters().getExpressions().get(1))).getValue());
    try {
        Pattern.compile(regex);
    } catch (PatternSyntaxException e) {
        throw new ParseException(e.getMessage());
    }
    RegexFunction regexFunction = new RegexFunction(column, regex);

    if (function.getParameters().getExpressions().size() == 3 && StringValue.class
        .isInstance(function.getParameters().getExpressions().get(2))) {
        regexFunction.setOptions(
            ((StringValue) (function.getParameters().getExpressions().get(2))).getValue());
    }
    return regexFunction;
}
Example #8
Source File: SqlUtils.java    From sql-to-mongo-db-query-converter with Apache License 2.0 6 votes vote down vote up 
public static DateFunction isDateFunction(Expression incomingExpression) throws ParseException {
    if (ComparisonOperator.class.isInstance(incomingExpression)) {
        ComparisonOperator comparisonOperator = (ComparisonOperator)incomingExpression;
        String rightExpression = getStringValue(comparisonOperator.getRightExpression());
        if (Function.class.isInstance(comparisonOperator.getLeftExpression())) {
            Function function = ((Function)comparisonOperator.getLeftExpression());
            if ("date".equals(function.getName().toLowerCase())
                    && (function.getParameters().getExpressions().size()==2)
                    && StringValue.class.isInstance(function.getParameters().getExpressions().get(1))) {
                String column = getStringValue(function.getParameters().getExpressions().get(0));
                DateFunction dateFunction = null;
                try {
                    dateFunction = new DateFunction(((StringValue)(function.getParameters().getExpressions().get(1))).getValue(),rightExpression,column);
                    dateFunction.setComparisonFunction(comparisonOperator);
                } catch (IllegalArgumentException e) {
                    throw new ParseException(e.getMessage());
                }
                return dateFunction;
            }

        }
    }
    return null;
}
Example #9
Source File: FunctionCountTest.java    From Mybatis-PageHelper with MIT License 5 votes vote down vote up 
@Test
public void test2() {
    Select select = select("select distinct(name) from user where a > 100");
    List<SelectItem> selectItems = ((PlainSelect) select.getSelectBody()).getSelectItems();
    for (SelectItem item : selectItems) {
        if (item instanceof Function) {
            System.out.println("Function:" + item.toString());
        } else {
            System.out.println("Not a function:" + item.toString());
        }
    }
}
Example #10
Source File: SqlUtils.java    From sql-to-mongo-db-query-converter with Apache License 2.0 5 votes vote down vote up 
public static String getFieldFromFunction(Function function) throws ParseException{
	 List<String> parameters = function.getParameters()== null ? Collections.<String>emptyList() : Lists.transform(function.getParameters().getExpressions(), new com.google.common.base.Function<Expression, String>() {
           @Override
           public String apply(@Nonnull Expression expression) {
               return SqlUtils.getStringValue(expression);
           }
       });
       if (parameters.size() > 1) {
           throw new ParseException(function.getName()+" function can only have one parameter");
       }
       return parameters.size() > 0 ? Iterables.get(parameters, 0) : null;
}
Example #11
Source File: SqlUtils.java    From sql-to-mongo-db-query-converter with Apache License 2.0 5 votes vote down vote up 
public static String generateAggField(Function f, String alias) throws ParseException {
String field = getFieldFromFunction(f);
String function = f.getName().toLowerCase();
if("*".equals(field) || function.equals("count") ){
	return (alias == null?function:alias);
}
else {
	return (alias == null?function + "_" + field.replaceAll("\\.","_"):alias);
}
  	
  }
Example #12
Source File: SqlUtils.java    From sql-to-mongo-db-query-converter with Apache License 2.0 5 votes vote down vote up 
public static List<String> getGroupByColumnReferences(PlainSelect plainSelect) {
    if (plainSelect.getGroupBy()==null) {
        return Collections.emptyList();
    }

    return Lists.transform(plainSelect.getGroupBy().getGroupByExpressions(),
            new com.google.common.base.Function<Expression, String>() {
        @Override
        public String apply(@Nonnull  Expression expression) {
            return SqlUtils.getStringValue(expression);
        }
    });
}
Example #13
Source File: SqlUtils.java    From sql-to-mongo-db-query-converter with Apache License 2.0 5 votes vote down vote up 
public static boolean isCountAll(List<SelectItem> selectItems) {
    if (selectItems !=null && selectItems.size()==1) {
        SelectItem firstItem = selectItems.get(0);
        if ((SelectExpressionItem.class.isInstance(firstItem))
                && Function.class.isInstance(((SelectExpressionItem)firstItem).getExpression())) {
            Function function = (Function) ((SelectExpressionItem) firstItem).getExpression();

            if ("count(*)".equals(function.toString())) {
                return true;
            }

        }
    }
    return false;
}
Example #14
Source File: SqlUtils.java    From sql-to-mongo-db-query-converter with Apache License 2.0 5 votes vote down vote up 
public static boolean isSpecialtyFunction(Expression incomingExpression) {
    if (incomingExpression == null) {
        return false;
    }

    if (Function.class.isInstance(incomingExpression) && containsIgnoreCase(SPECIALTY_FUNCTIONS, ((Function)incomingExpression).getName())) {
        return true;
    }

    return false;
}
Example #15
Source File: QueryConverter.java    From sql-to-mongo-db-query-converter with Apache License 2.0 5 votes vote down vote up 
private static String toJson(List<Document> documents) throws IOException {
    StringWriter stringWriter = new StringWriter();
    IOUtils.write("[", stringWriter);
    IOUtils.write(Joiner.on(",").join(Lists.transform(documents, new com.google.common.base.Function<Document, String>() {
        @Override
        public String apply(@Nonnull Document document) {
            return document.toJson(relaxed);
        }
    })),stringWriter);
    IOUtils.write("]", stringWriter);
    return stringWriter.toString();
}
Example #16
Source File: BuiltinFunctions.java    From herddb with Apache License 2.0 5 votes vote down vote up 
public static Column toAggregatedOutputColumn(String fieldName, Function f) {
    if (f.getName().equalsIgnoreCase(BuiltinFunctions.COUNT)) {
        return Column.column(fieldName, ColumnTypes.LONG);
    }
    if (f.getName().equalsIgnoreCase(BuiltinFunctions.SUM) && f.getParameters() != null && f.getParameters().getExpressions() != null && f.getParameters().getExpressions().size() == 1) {
        return Column.column(fieldName, ColumnTypes.LONG);
    }
    if (f.getName().equalsIgnoreCase(BuiltinFunctions.MIN) && f.getParameters() != null && f.getParameters().getExpressions() != null && f.getParameters().getExpressions().size() == 1) {
        return Column.column(fieldName, ColumnTypes.LONG);
    }
    if (f.getName().equalsIgnoreCase(BuiltinFunctions.MAX) && f.getParameters() != null && f.getParameters().getExpressions() != null && f.getParameters().getExpressions().size() == 1) {
        return Column.column(fieldName, ColumnTypes.LONG);
    }
    return null;
}
Example #17
Source File: SqlUtils.java    From sql-to-mongo-db-query-converter with Apache License 2.0 4 votes vote down vote up 
public static String generateAggField(Function f, Alias alias)  throws ParseException{
	String aliasStr = (alias == null? null : alias.getName());
	return generateAggField(f, aliasStr);
}
Example #18
Source File: QueryConverter.java    From sql-to-mongo-db-query-converter with Apache License 2.0 4 votes vote down vote up 
/**
 * Build a mongo shell statement with the code to run the specified query.
 * @param outputStream the {@link java.io.OutputStream} to write the data to
 * @throws IOException when there is an issue writing to the {@link java.io.OutputStream}
 */
public void write(OutputStream outputStream) throws IOException {
    MongoDBQueryHolder mongoDBQueryHolder = getMongoQuery();
    boolean isFindQuery = false;
    if (mongoDBQueryHolder.isDistinct()) {
        IOUtils.write("db." + mongoDBQueryHolder.getCollection() + ".distinct(", outputStream);
        IOUtils.write("\""+getDistinctFieldName(mongoDBQueryHolder) + "\"", outputStream);
        IOUtils.write(" , ", outputStream);
        IOUtils.write(prettyPrintJson(mongoDBQueryHolder.getQuery().toJson(relaxed)), outputStream);
    } else if (sqlCommandInfoHolder.isCountAll() && !isAggregate(mongoDBQueryHolder)) {
        IOUtils.write("db." + mongoDBQueryHolder.getCollection() + ".count(", outputStream);
        IOUtils.write(prettyPrintJson(mongoDBQueryHolder.getQuery().toJson(relaxed)), outputStream);
    } else if (isAggregate(mongoDBQueryHolder)) {
    	IOUtils.write("db." + mongoDBQueryHolder.getCollection() + ".aggregate(", outputStream);
        IOUtils.write("[", outputStream);
        
        IOUtils.write(Joiner.on(",").join(Lists.transform(generateAggSteps(mongoDBQueryHolder,sqlCommandInfoHolder), new com.google.common.base.Function<Document, String>() {
            @Override
            public String apply(Document document) {
                return prettyPrintJson(document.toJson(relaxed));
            }
        })),outputStream);
        IOUtils.write("]", outputStream);

        Document options = new Document();
        if (aggregationAllowDiskUse != null) {
            options.put("allowDiskUse", aggregationAllowDiskUse);
        }

        if (aggregationBatchSize != null) {
            options.put("cursor",new Document("batchSize", aggregationBatchSize));
        }

        if (options.size() > 0) {
            IOUtils.write(",",outputStream);
            IOUtils.write(prettyPrintJson(options.toJson(relaxed)),outputStream);
        }



    } else {
    	if(sqlCommandInfoHolder.getSqlCommandType() == SQLCommandType.SELECT) {
     	isFindQuery = true;
         IOUtils.write("db." + mongoDBQueryHolder.getCollection() + ".find(", outputStream);
    	}
    	else if(sqlCommandInfoHolder.getSqlCommandType() == SQLCommandType.DELETE){
    		IOUtils.write("db." + mongoDBQueryHolder.getCollection() + ".remove(", outputStream);
    	}
        IOUtils.write(prettyPrintJson(mongoDBQueryHolder.getQuery().toJson(relaxed)), outputStream);
        if (mongoDBQueryHolder.getProjection() != null && mongoDBQueryHolder.getProjection().size() > 0 && sqlCommandInfoHolder.getSqlCommandType() == SQLCommandType.SELECT) {
            IOUtils.write(" , ", outputStream);
            IOUtils.write(prettyPrintJson(mongoDBQueryHolder.getProjection().toJson(relaxed)), outputStream);
        }
    }
    IOUtils.write(")", outputStream);

    if(isFindQuery) {
    	if (mongoDBQueryHolder.getSort()!=null && mongoDBQueryHolder.getSort().size() > 0) {
            IOUtils.write(".sort(", outputStream);
            IOUtils.write(prettyPrintJson(mongoDBQueryHolder.getSort().toJson(relaxed)), outputStream);
            IOUtils.write(")", outputStream);
        }
        
        if (mongoDBQueryHolder.getOffset()!=-1) {
            IOUtils.write(".skip(", outputStream);
            IOUtils.write(mongoDBQueryHolder.getOffset()+"", outputStream);
            IOUtils.write(")", outputStream);
        }

        if (mongoDBQueryHolder.getLimit()!=-1) {
            IOUtils.write(".limit(", outputStream);
            IOUtils.write(mongoDBQueryHolder.getLimit()+"", outputStream);
            IOUtils.write(")", outputStream);
        }
    }
}
Example #19
Source File: ObjPD.java    From openprodoc with GNU Affero General Public License v3.0 4 votes vote down vote up 
private Conditions EvalExpr(Expression ParentExpr ) throws PDException 
{
Conditions New = new Conditions();    
int ExprType= EvalExprType(ParentExpr);
//System.out.println("ParentExpr=["+ParentExpr+"]  Type="+ExprType);    
switch (ExprType)
    {
    case EXPR_BASIC:
        ComparisonOperator CO = (ComparisonOperator) ParentExpr;
        String Left=CO.getLeftExpression().toString();
        String Comp=CO.getStringExpression();
        String Right=CO.getRightExpression().toString();
        if (isField(Left) && isField(Right))
            New.addCondition(new Condition(Left,  Right));
        else  
            {
            String FieldName;
            Object Value;
            int TypeVal;
            if (isField(Left))
                {
                FieldName=Left;
                Value=CalcVal(Right);
                TypeVal=CalcTypeVal(Right);
                }
            else
                {
                FieldName=Right;
                Value=CalcVal(Left);
                TypeVal=CalcTypeVal(Left);
                }
//            System.out.println("Value="+Value+"  class="+Value.getClass().getName());
            New.addCondition(new Condition(FieldName,  getCompConv().get(Comp), Value, TypeVal));
            }
        break;    
    case EXPR_NOT:
        Conditions Cs=EvalExpr(((NotExpression) ParentExpr).getExpression());
        Cs.setInvert(true);
        New.addCondition(Cs);
        break;
    case EXPR_AND:
        New.addCondition(EvalExpr(((AndExpression) ParentExpr).getLeftExpression() ));
        New.addCondition(EvalExpr(((AndExpression) ParentExpr).getRightExpression() ));
        break;
    case EXPR_OR:
        New.addCondition(EvalExpr(((OrExpression) ParentExpr).getLeftExpression() ));
        New.addCondition(EvalExpr(((OrExpression) ParentExpr).getRightExpression() ));
        New.setOperatorAnd(false);
        break;
    case EXPR_PAR:
        New.addCondition(EvalExpr(((Parenthesis) ParentExpr).getExpression() ));
        break;
    case EXPR_IN:
        String FieldNameIn=((InExpression)ParentExpr).getLeftExpression().toString();
        HashSet<String> ListTerms = new HashSet<String>();
        List<Expression> LT =((ExpressionList)((InExpression)ParentExpr).getLeftItemsList()).getExpressions();
        for (Iterator<Expression> iterator = LT.iterator(); iterator.hasNext();)
            {
            StringValue NextTerm = (StringValue)iterator.next();
            ListTerms.add(NextTerm.getValue());
            }
        New.addCondition(new Condition(FieldNameIn,ListTerms));
        break;
    case EXPR_FUNCT:
        String Arg=((Function)ParentExpr).getParameters().getExpressions().get(0).toString();
        switch (((Function)ParentExpr).getName())
            {
            case Condition.CONTAINS:
                New.addCondition(Condition.genContainsCond(PDDocs.getTableName(),Arg, getDrv())); 
                break;
            case Condition.INTREE:
                New.addCondition(Condition.genInTreeCond( Arg, getDrv())); 
                break;
            case Condition.INFOLDER:
                New.addCondition(Condition.genInFolder(Arg, getDrv()));
                break;
                
            }
        break;    
        
    }
return(New);
}
Example #20
Source File: CountSqlParser.java    From Mybatis-PageHelper with MIT License 4 votes vote down vote up 
/**
 * 是否可以用简单的count查询方式
 *
 * @param select
 * @return
 */
public boolean isSimpleCount(PlainSelect select) {
    //包含group by的时候不可以
    if (select.getGroupBy() != null) {
        return false;
    }
    //包含distinct的时候不可以
    if (select.getDistinct() != null) {
        return false;
    }
    for (SelectItem item : select.getSelectItems()) {
        //select列中包含参数的时候不可以，否则会引起参数个数错误
        if (item.toString().contains("?")) {
            return false;
        }
        //如果查询列中包含函数，也不可以，函数可能会聚合列
        if (item instanceof SelectExpressionItem) {
            Expression expression = ((SelectExpressionItem) item).getExpression();
            if (expression instanceof Function) {
                String name = ((Function) expression).getName();
                if (name != null) {
                    String NAME = name.toUpperCase();
                    if(skipFunctions.contains(NAME)){
                        //go on
                    } else if(falseFunctions.contains(NAME)){
                        return false;
                    } else {
                        for (String aggregateFunction : AGGREGATE_FUNCTIONS) {
                            if(NAME.startsWith(aggregateFunction)){
                                falseFunctions.add(NAME);
                                return false;
                            }
                        }
                        skipFunctions.add(NAME);
                    }
                }
            }
        }
    }
    return true;
}
Example #21
Source File: DMLWhereClauseVisitorAdapter.java    From spanner-jdbc with MIT License 4 votes vote down vote up 
@Override
public void visit(Function function) {
  invalid = true;
  super.visit(function);
}
Example #22
Source File: ExpressionVisitorImpl.java    From DataPermissionHelper with Apache License 2.0 4 votes vote down vote up 
@Override
public void visit(Function function) {
}