admin

如果你有一个行列形式的数据集，比如说，二维数组，你想用 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

admin

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("不支持!");
    }
}

admin

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;

admin

好了就好

admin

我帮你看一下，你只需要取到下图所示的 LikeExpression 那个实例就行了. 怎么样取到这个实例，你懂的.

admin

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

admin

使用eclipse的单步调试即可.

admin

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

admin

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

admin

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