Search in sources :

Example 1 with Statement

use of net.sf.jsqlparser.statement.Statement in project Mybatis-PageHelper by pagehelper.

the class SqlServerParser method convertToPageSql.

/**
     * 转换为分页语句
     *
     * @param sql
     * @param offset
     * @param limit
     * @return
     */
public String convertToPageSql(String sql, Integer offset, Integer limit) {
    //解析SQL
    Statement stmt;
    try {
        stmt = CCJSqlParserUtil.parse(sql);
    } catch (Throwable e) {
        throw new PageException("不支持该SQL转换为分页查询!");
    }
    if (!(stmt instanceof Select)) {
        throw new PageException("分页语句必须是Select查询!");
    }
    //获取分页查询的select
    Select pageSelect = getPageSelect((Select) stmt);
    String pageSql = pageSelect.toString();
    //缓存移到外面了,所以不替换参数
    if (offset != null) {
        pageSql = pageSql.replace(START_ROW, String.valueOf(offset));
    }
    if (limit != null) {
        pageSql = pageSql.replace(PAGE_SIZE, String.valueOf(limit));
    }
    return pageSql;
}
Also used : PageException(com.github.pagehelper.PageException) Statement(net.sf.jsqlparser.statement.Statement)

Example 2 with Statement

use of net.sf.jsqlparser.statement.Statement in project dbeaver by serge-rider.

the class SQLQueryTransformerCount method transformQuery.

@Override
public SQLQuery transformQuery(SQLDataSource dataSource, SQLQuery query) throws DBException {
    try {
        Statement statement = CCJSqlParserUtil.parse(query.getQuery());
        if (statement instanceof Select && ((Select) statement).getSelectBody() instanceof PlainSelect) {
            PlainSelect select = (PlainSelect) ((Select) statement).getSelectBody();
            List<SelectItem> selectItems = new ArrayList<>();
            Function countFunc = new Function();
            countFunc.setName("count");
            countFunc.setParameters(new ExpressionList(Collections.<Expression>singletonList(new Column("*"))));
            SelectItem countItem = new SelectExpressionItem(countFunc);
            selectItems.add(countItem);
            select.setSelectItems(selectItems);
            return new SQLQuery(dataSource, select.toString(), query, false);
        } else {
            throw new DBException("Query [" + query.getQuery() + "] can't be modified");
        }
    } catch (JSQLParserException e) {
        throw new DBException("Can't transform query to SELECT count(*)", e);
    }
}
Also used : DBException(org.jkiss.dbeaver.DBException) Statement(net.sf.jsqlparser.statement.Statement) SelectExpressionItem(net.sf.jsqlparser.statement.select.SelectExpressionItem) JSQLParserException(net.sf.jsqlparser.JSQLParserException) PlainSelect(net.sf.jsqlparser.statement.select.PlainSelect) ArrayList(java.util.ArrayList) SQLQuery(org.jkiss.dbeaver.model.sql.SQLQuery) Function(net.sf.jsqlparser.expression.Function) Expression(net.sf.jsqlparser.expression.Expression) Column(net.sf.jsqlparser.schema.Column) SelectItem(net.sf.jsqlparser.statement.select.SelectItem) PlainSelect(net.sf.jsqlparser.statement.select.PlainSelect) Select(net.sf.jsqlparser.statement.select.Select) ExpressionList(net.sf.jsqlparser.expression.operators.relational.ExpressionList)

Example 3 with Statement

use of net.sf.jsqlparser.statement.Statement in project Mybatis-PageHelper by pagehelper.

the class CountSqlParser method getSmartCountSql.

/**
     * 获取智能的countSql
     *
     * @param sql
     * @return
     */
public String getSmartCountSql(String sql) {
    //校验是否支持该sql
    isSupportedSql(sql);
    //解析SQL
    Statement stmt = null;
    //特殊sql不需要去掉order by时,使用注释前缀
    if (sql.indexOf(KEEP_ORDERBY) >= 0) {
        return getSimpleCountSql(sql);
    }
    try {
        stmt = CCJSqlParserUtil.parse(sql);
    } catch (Throwable e) {
        //无法解析的用一般方法返回count语句
        return getSimpleCountSql(sql);
    }
    Select select = (Select) stmt;
    SelectBody selectBody = select.getSelectBody();
    try {
        //处理body-去order by
        processSelectBody(selectBody);
    } catch (Exception e) {
        //当 sql 包含 group by 时,不去除 order by
        return getSimpleCountSql(sql);
    }
    //处理with-去order by
    processWithItemsList(select.getWithItemsList());
    //处理为count查询
    sqlToCount(select);
    String result = select.toString();
    return result;
}
Also used : Statement(net.sf.jsqlparser.statement.Statement) PageException(com.github.pagehelper.PageException)

Example 4 with Statement

use of net.sf.jsqlparser.statement.Statement in project dbeaver by serge-rider.

the class QueryTransformerTop method transformQueryString.

@Override
public String transformQueryString(SQLQuery query) throws DBCException {
    limitSet = false;
    if (query.isPlainSelect()) {
        try {
            Statement statement = query.getStatement();
            if (statement instanceof Select) {
                Select select = (Select) statement;
                if (select.getSelectBody() instanceof PlainSelect) {
                    PlainSelect selectBody = (PlainSelect) select.getSelectBody();
                    if (selectBody.getTop() == null && CommonUtils.isEmpty(selectBody.getIntoTables())) {
                        Top top = new Top();
                        top.setPercentage(false);
                        top.setExpression(new LongValue(offset.longValue() + length.longValue()));
                        selectBody.setTop(top);
                        limitSet = true;
                        return statement.toString();
                    }
                }
            }
        } catch (Throwable e) {
            // ignore
            log.debug(e);
        }
    }
    return query.getQuery();
}
Also used : Top(net.sf.jsqlparser.statement.select.Top) DBCStatement(org.jkiss.dbeaver.model.exec.DBCStatement) Statement(net.sf.jsqlparser.statement.Statement) Select(net.sf.jsqlparser.statement.select.Select) PlainSelect(net.sf.jsqlparser.statement.select.PlainSelect) PlainSelect(net.sf.jsqlparser.statement.select.PlainSelect) LongValue(net.sf.jsqlparser.expression.LongValue)

Aggregations

Statement (net.sf.jsqlparser.statement.Statement)4 PageException (com.github.pagehelper.PageException)2 PlainSelect (net.sf.jsqlparser.statement.select.PlainSelect)2 Select (net.sf.jsqlparser.statement.select.Select)2 ArrayList (java.util.ArrayList)1 JSQLParserException (net.sf.jsqlparser.JSQLParserException)1 Expression (net.sf.jsqlparser.expression.Expression)1 Function (net.sf.jsqlparser.expression.Function)1 LongValue (net.sf.jsqlparser.expression.LongValue)1 ExpressionList (net.sf.jsqlparser.expression.operators.relational.ExpressionList)1 Column (net.sf.jsqlparser.schema.Column)1 SelectExpressionItem (net.sf.jsqlparser.statement.select.SelectExpressionItem)1 SelectItem (net.sf.jsqlparser.statement.select.SelectItem)1 Top (net.sf.jsqlparser.statement.select.Top)1 DBException (org.jkiss.dbeaver.DBException)1 DBCStatement (org.jkiss.dbeaver.model.exec.DBCStatement)1 SQLQuery (org.jkiss.dbeaver.model.sql.SQLQuery)1