Java Code Examples for net.sf.jsqlparser.expression.Function

The following examples show how to use net.sf.jsqlparser.expression.Function. These examples are extracted from open source projects. 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 Project: FastSQL   Source File: CountSqlParser.java    License: 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 2
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 3
Source Project: sql-to-mongo-db-query-converter   Source File: SqlUtils.java    License: 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 4
Source Project: sql-to-mongo-db-query-converter   Source File: SqlUtils.java    License: 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 5
Source Project: sql-to-mongo-db-query-converter   Source File: SqlUtils.java    License: 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 6
Source Project: openprodoc   Source File: ObjPD.java    License: 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 7
Source Project: quetzal   Source File: CTEToNestedQueryConverter.java    License: 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 8
Source Project: Mybatis-PageHelper   Source File: FunctionCountTest.java    License: 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 9
Source Project: herddb   Source File: BuiltinFunctions.java    License: 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 10
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 11
Source Project: sql-to-mongo-db-query-converter   Source File: SqlUtils.java    License: 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 12
Source Project: sql-to-mongo-db-query-converter   Source File: SqlUtils.java    License: 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 13
Source Project: sql-to-mongo-db-query-converter   Source File: SqlUtils.java    License: 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 14
Source Project: sql-to-mongo-db-query-converter   Source File: SqlUtils.java    License: 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 15
Source Project: sql-to-mongo-db-query-converter   Source File: SqlUtils.java    License: 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 16
Source Project: Mybatis-PageHelper   Source File: FunctionCountTest.java    License: 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 17
@Override
public void visit(Function function) {
}
 
Example 18
Source Project: spanner-jdbc   Source File: DMLWhereClauseVisitorAdapter.java    License: MIT License 4 votes vote down vote up
@Override
public void visit(Function function) {
  invalid = true;
  super.visit(function);
}
 
Example 19
/**
 * 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 20
Source Project: sql-to-mongo-db-query-converter   Source File: SqlUtils.java    License: 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 21
Source Project: openprodoc   Source File: ObjPD.java    License: 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 22
Source Project: Mybatis-PageHelper   Source File: CountSqlParser.java    License: 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;
}