Examples with PlainSelect net.sf.jsqlparser.statement.select.PlainSelect used on opensource projects

Example 1 with PlainSelect

use of net.sf.jsqlparser.statement.select.PlainSelect 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);
    }
}

Example 2 with PlainSelect

use of net.sf.jsqlparser.statement.select.PlainSelect in project dal by ctripcorp.

the class SqlBuilder method pagingQuerySql.

/**
     * Re-build the query SQL to implement paging function. The new SQL
     * Statement will contains limit if the database type is MYSQL, CET wrapped
     * if database type is SQL Server. Note: the final SQL will contain two %s,
     * which should be replaced in run time.
     *
     * @param sql    The original SQL Statement
     * @param dbType The database type
     * @return Re-build SQL which contains limit if the database type is MYSQL,
     * CET wrapped if database type is SQL Server.
     * @throws Exception
     */
public static String pagingQuerySql(String sql, DatabaseCategory dbType, CurrentLanguage lang) throws Exception {
    String sql_content = sql.replace("@", ":");
    boolean withNolock = StringUtils.containsIgnoreCase(sql_content, "WITH (NOLOCK)");
    if (withNolock)
        sql_content = sql_content.replaceAll("(?i)WITH \\(NOLOCK\\)", "");
    StringBuilder sb = new StringBuilder();
    try {
        Select select = (Select) parserManager.parse(new StringReader(sql_content));
        PlainSelect plain = (PlainSelect) select.getSelectBody();
        if (dbType == DatabaseCategory.MySql) {
            sb.append(plain.toString());
            sb.append(lang == CurrentLanguage.Java ? mysqlPageClausePattern : mysqlCSPageClausePattern);
        } else if (dbType == DatabaseCategory.SqlServer) {
            sb.append(plain.toString());
            sb.append(lang == CurrentLanguage.Java ? sqlserverPagingClausePattern : sqlseverCSPagingClausePattern);
        } else {
            throw new Exception("Unknow database category.");
        }
    } catch (Exception e) {
        log.error("Paging the SQL Failed.", e);
        throw e;
    }
    return sb.toString().replace(":", "@");
}

Example 3 with PlainSelect

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

the class FunctionCountTest method test.

@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 4 with PlainSelect

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

the class FunctionCountTest method test2.

@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 5 with PlainSelect

use of net.sf.jsqlparser.statement.select.PlainSelect 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();
}