发新话题
打印

用jsqlparser过滤数据集,使用sql 查询的语法

用jsqlparser过滤数据集,使用sql 查询的语法

如果你有一个行列形式的数据集,比如说,二维数组,你想用 sql 兼容的语法过滤数据,该怎么办? 我的答案是用 jsqlparser 结合 beanshell(bsh)来做到。

以下是简表中, 过滤一个csv数据集的示例,供大家参考。思路是采用jsqlparser的visitor编程模型,分析一行是否满足 where表达式。
复制内容到剪贴板
代码:
  
public static void main(String[] args) {       try {
            // 准备数据集
            Object[][] data = {
                    { "崔永远", 18 },
                    { "王小鸭", 16 }
                };
            jatools.dataset.Column[] columns = new jatools.dataset.Column[2];
            columns[0] = new jatools.dataset.Column("姓名", String.class);
            columns[1] = new jatools.dataset.Column("年龄", Integer.class);

            RowMeta rowMeta = new RowMeta(columns);

            Statement stat = new CCJSqlParserManager().parse(new StringReader(
                        "select * from a where 姓名='崔永远'"));
            Select select = (Select) stat;
            Expression where = ((PlainSelect) select.getSelectBody()).getWhere();

            WhereExpressionVisitor visitor = new WhereExpressionVisitor(rowMeta, where);

            for (int i = 0; i < data.length; i++) {
                Object result = visitor.eval(data[i]);

                if (result instanceof Boolean && ((Boolean) result).booleanValue()) {
                    System.out.print("通过=====>");
                } else {
                    System.out.print("不通过=====>");
                }

                System.out.println(StringUtils.join(data[i], ","));
            }
        } catch (JSQLParserException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
上面代码的输出
复制内容到剪贴板
代码:
通过=====>崔永远,18
不通过=====>王小鸭,16

TOP

JCP兼容打印机推荐:
WhereExpressionVisitor.java 源码
复制内容到剪贴板
代码:
public class WhereExpressionVisitor implements ExpressionVisitor, ParserConstants {
    private Object result;
    private RowMeta rowInfo;
    private Expression expression;
    private Object[] values;

    /**
     * Creates a new MyExpressionVisitor object.
     *
     * @param rowInfo DOCUMENT ME!
     */
    public WhereExpressionVisitor(RowMeta rowInfo, Expression expression) {
        this.rowInfo = rowInfo;
        this.expression = expression;
    }

    /**
    * DOCUMENT ME!
    *
    * @param exp DOCUMENT ME!
    */
    public Object eval(Object[] values) {
        this.values = values;
        expression.accept(this);

        return result;
    }

    /**
     * DOCUMENT ME!
     *
     * @param args DOCUMENT ME!
     */
   
    /**
     * DOCUMENT ME!
     *
     * @param nullValue DOCUMENT ME!
     */
    public void visit(NullValue nullValue) {
        this.result = null;
    }

    /**
     * DOCUMENT ME!
     *
     * @param function DOCUMENT ME!
     */
    public void visit(Function function) {
        throw new UnsupportedOperationException("不支持!");
    }

    /**
     * DOCUMENT ME!
     *
     * @param inverseExpression DOCUMENT ME!
     */
    public void visit(InverseExpression inverseExpression) {
        throw new UnsupportedOperationException("不支持!");
    }

    /**
     * DOCUMENT ME!
     *
     * @param jdbcParameter DOCUMENT ME!
     */
    public void visit(JdbcParameter jdbcParameter) {
        throw new UnsupportedOperationException("不支持!");
    }

    /**
     * DOCUMENT ME!
     *
     * @param doubleValue DOCUMENT ME!
     */
    public void visit(DoubleValue doubleValue) {
        result = doubleValue.getValue();
    }

    /**
     * DOCUMENT ME!
     *
     * @param longValue DOCUMENT ME!
     */
    public void visit(LongValue longValue) {
        this.result = longValue.getValue();
    }

    /**
     * DOCUMENT ME!
     *
     * @param dateValue DOCUMENT ME!
     */
    public void visit(DateValue dateValue) {
        result = dateValue.getValue();
    }

    /**
     * DOCUMENT ME!
     *
     * @param timeValue DOCUMENT ME!
     */
    public void visit(TimeValue timeValue) {
        result = timeValue.getValue();
    }

    /**
     * DOCUMENT ME!
     *
     * @param timestampValue DOCUMENT ME!
     */
    public void visit(TimestampValue timestampValue) {
        result = timestampValue.getValue();
    }

    /**
     * DOCUMENT ME!
     *
     * @param parenthesis DOCUMENT ME!
     */
    public void visit(Parenthesis parenthesis) {
        parenthesis.getExpression().accept(this);
    }

    /**
     * DOCUMENT ME!
     *
     * @param stringValue DOCUMENT ME!
     */
    public void visit(StringValue stringValue) {
        result = stringValue.getValue();
    }

    /**
     * DOCUMENT ME!
     *
     * @param addition DOCUMENT ME!
     */
    public void visit(Addition equalsTo) {
        Expression left = equalsTo.getLeftExpression();
        left.accept(this);

        Object leftValue = result;

        Expression right = equalsTo.getRightExpression();
        right.accept(this);

        Object rightValue = result;

        result = BinaryOperation.eval(leftValue, rightValue, PLUS);
    }

    /**
     * DOCUMENT ME!
     *
     * @param division DOCUMENT ME!
     */
    public void visit(Division division) {
        Expression left = division.getLeftExpression();
        left.accept(this);

        Object leftValue = result;

        Expression right = division.getRightExpression();
        right.accept(this);

        Object rightValue = result;

        result = BinaryOperation.eval(leftValue, rightValue, SLASH);
    }

    /**
     * DOCUMENT ME!
     *
     * @param multiplication DOCUMENT ME!
     */
    public void visit(Multiplication multiplication) {
        Expression left = multiplication.getLeftExpression();
        left.accept(this);

        Object leftValue = result;

        Expression right = multiplication.getRightExpression();
        right.accept(this);

        Object rightValue = result;
        result = BinaryOperation.eval(leftValue, rightValue, STAR);
    }

    /**
     * DOCUMENT ME!
     *
     * @param subtraction DOCUMENT ME!
     */
    public void visit(Subtraction subtraction) {
        Expression left = subtraction.getLeftExpression();
        left.accept(this);

        Object leftValue = result;

        Expression right = subtraction.getRightExpression();
        right.accept(this);

        Object rightValue = result;
        result = BinaryOperation.eval(leftValue, rightValue, MINUS);
    }

    /**
     * DOCUMENT ME!
     *
     * @param andExpression DOCUMENT ME!
     */
    public void visit(AndExpression andExpression) {
        Expression left = andExpression.getLeftExpression();
        left.accept(this);

        Object leftValue = result;

        Expression right = andExpression.getRightExpression();
        right.accept(this);

        Object rightValue = result;

        result = BinaryOperation.eval(leftValue, rightValue, BOOL_AND);
    }

    /**
     * DOCUMENT ME!
     *
     * @param orExpression DOCUMENT ME!
     */
    public void visit(OrExpression orExpression) {
        Expression left = orExpression.getLeftExpression();
        left.accept(this);

        Object leftValue = result;

        Expression right = orExpression.getRightExpression();
        right.accept(this);

        Object rightValue = result;

        result = BinaryOperation.eval(leftValue, rightValue, BOOL_OR);
    }

    /**
     * DOCUMENT ME!
     *
     * @param between DOCUMENT ME!
     */
    public void visit(Between between) {
        Expression left = between.getLeftExpression();
        left.accept(this);

        Object leftValue = result;

        Expression start = between.getBetweenExpressionStart();
        start.accept(this);

        Object startValue = result;

        Expression end = between.getBetweenExpressionStart();
        end.accept(this);

        Object endValue = result;

        if (!between.isNot()) {
            Object ge = BinaryOperation.eval(leftValue, startValue, GE);
            Object le = BinaryOperation.eval(leftValue, endValue, LE);

            result = BinaryOperation.eval(ge, le, BOOL_AND);
        } else {
            Object ge = BinaryOperation.eval(leftValue, startValue, LT);
            Object le = BinaryOperation.eval(leftValue, endValue, GT);

            result = BinaryOperation.eval(ge, le, BOOL_OR);
        }
    }

    /**
     * DOCUMENT ME!
     *
     * @param equalsTo DOCUMENT ME!
     */
    public void visit(EqualsTo equalsTo) {
        Expression left = equalsTo.getLeftExpression();
        left.accept(this);

        Object leftValue = result;

        Expression right = equalsTo.getRightExpression();
        right.accept(this);

        Object rightValue = result;

        result = BinaryOperation.eval(leftValue, rightValue, EQ);
    }

    /**
     * DOCUMENT ME!
     *
     * @param greaterThan DOCUMENT ME!
     */
    public void visit(GreaterThan greaterThan) {
        Expression left = greaterThan.getLeftExpression();
        left.accept(this);

        Object leftValue = result;

        Expression right = greaterThan.getRightExpression();
        right.accept(this);

        Object rightValue = result;

        result = BinaryOperation.eval(leftValue, rightValue, GT);
    }

    /**
     * DOCUMENT ME!
     *
     * @param greaterThanEquals DOCUMENT ME!
     */
    public void visit(GreaterThanEquals greaterThanEquals) {
        Expression left = greaterThanEquals.getLeftExpression();
        left.accept(this);

        Object leftValue = result;

        Expression right = greaterThanEquals.getRightExpression();
        right.accept(this);

        Object rightValue = result;

        result = BinaryOperation.eval(leftValue, rightValue, GE);
    }

    /**
     * DOCUMENT ME!
     *
     * @param inExpression DOCUMENT ME!
     */
    public void visit(InExpression inExpression) {
        throw new UnsupportedOperationException("不支持!");
    }

    /**
     * DOCUMENT ME!
     *
     * @param isNullExpression DOCUMENT ME!
     */
    public void visit(IsNullExpression isNullExpression) {
        Expression left = isNullExpression.getLeftExpression();
        left.accept(this);

        boolean isnull = result == null;

        if (isNullExpression.isNot()) {
            result = !isnull;
        } else {
            result = isnull;
        }
    }

    /**
     * DOCUMENT ME!
     *
     * @param likeExpression DOCUMENT ME!
     */
    public void visit(LikeExpression likeExpression) {
        throw new UnsupportedOperationException("不支持!");
    }

    /**
     * DOCUMENT ME!
     *
     * @param minorThan DOCUMENT ME!
     */
    public void visit(MinorThan minorThan) {
        Expression left = minorThan.getLeftExpression();
        left.accept(this);

        Object leftValue = result;

        Expression right = minorThan.getRightExpression();
        right.accept(this);

        Object rightValue = result;

        result = BinaryOperation.eval(leftValue, rightValue, LT);
    }

    /**
     * DOCUMENT ME!
     *
     * @param minorThanEquals DOCUMENT ME!
     */
    public void visit(MinorThanEquals minorThanEquals) {
        Expression left = minorThanEquals.getLeftExpression();
        left.accept(this);

        Object leftValue = result;

        Expression right = minorThanEquals.getRightExpression();
        right.accept(this);

        Object rightValue = result;

        result = BinaryOperation.eval(leftValue, rightValue, LE);
    }

    /**
     * DOCUMENT ME!
     *
     * @param notEqualsTo DOCUMENT ME!
     */
    public void visit(NotEqualsTo notEqualsTo) {
        Expression left = notEqualsTo.getLeftExpression();
        left.accept(this);

        Object leftValue = result;

        Expression right = notEqualsTo.getRightExpression();
        right.accept(this);

        Object rightValue = result;

        result = BinaryOperation.eval(leftValue, rightValue, NE);
    }

    /**
     * DOCUMENT ME!
     *
     * @param tableColumn DOCUMENT ME!
     */
    public void visit(Column tableColumn) {
        int col = this.rowInfo.getIndexByColumnName(tableColumn.getColumnName());

        if ((col > -1) && (col < this.values.length)) {
            result = this.values[col];
        } else {
            result = null;
        }
    }

    /**
     * DOCUMENT ME!
     *
     * @param subSelect DOCUMENT ME!
     */
    public void visit(SubSelect subSelect) {
        throw new UnsupportedOperationException("不支持!");
    }

    /**
     * DOCUMENT ME!
     *
     * @param caseExpression DOCUMENT ME!
     */
    public void visit(CaseExpression caseExpression) {
        throw new UnsupportedOperationException("不支持!");
    }

    /**
     * DOCUMENT ME!
     *
     * @param whenClause DOCUMENT ME!
     */
    public void visit(WhenClause whenClause) {
        throw new UnsupportedOperationException("不支持!");
    }

    /**
     * DOCUMENT ME!
     *
     * @param existsExpression DOCUMENT ME!
     */
    public void visit(ExistsExpression existsExpression) {
        throw new UnsupportedOperationException("不支持!");
    }

    /**
     * DOCUMENT ME!
     *
     * @param allComparisonExpression DOCUMENT ME!
     */
    public void visit(AllComparisonExpression allComparisonExpression) {
        throw new UnsupportedOperationException("不支持!");
    }

    /**
     * DOCUMENT ME!
     *
     * @param anyComparisonExpression DOCUMENT ME!
     */
    public void visit(AnyComparisonExpression anyComparisonExpression) {
        throw new UnsupportedOperationException("不支持!");
    }
}

TOP

Statement stat = new CCJSqlParserManager().parse(new StringReader(
sql));

Update update = (Update) stat;
有了这个,就好办了,在jsqlparser的源代码里可以看到,update对象里,有很多东西,是你需要的

public class Update implements Statement {
private Table table;
private Expression where;
private List columns;
private List expressions;

TOP

好了就好

TOP

我帮你看一下,你只需要取到下图所示的 LikeExpression 那个实例就行了. 怎么样取到这个实例,你懂的.
附件: 您所在的用户组无法下载或查看附件

TOP

你问的是不是9# 楼的那个贴图,这个贴图是eclipse的调试窗口噢!

TOP

使用eclipse的单步调试即可.

TOP

中文不能解析,请参照这里:
http://bbs.jatools.com/viewthread.php?tid=561&extra=page%3D1

TOP

(orgcode,grpcustno) in ...不能这么用吧? 只能用一个字段吧。

TOP

可以在myeclipse上运行的,放心.

TOP

发新话题