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

Example 11 with OrderByElement

use of net.sf.jsqlparser.statement.select.OrderByElement in project JSqlParser by JSQLParser.

the class SelectASTTest method testSelectASTCommentLF.

@Test
public void testSelectASTCommentLF() throws JSQLParserException {
    String sql = "SELECT  /* testcomment */ \n a,  b FROM  -- testcomment2 \n mytable \n order by   b,  c";
    StringBuilder b = new StringBuilder(sql);
    Statement stmt = CCJSqlParserUtil.parse(sql);
    Select select = (Select) stmt;
    PlainSelect ps = (PlainSelect) select.getSelectBody();
    for (SelectItem item : ps.getSelectItems()) {
        SelectExpressionItem sei = (SelectExpressionItem) item;
        Column c = (Column) sei.getExpression();
        SimpleNode astNode = c.getASTNode();
        assertNotNull(astNode);
        b.setCharAt(astNode.jjtGetFirstToken().absoluteBegin - 1, '*');
    }
    for (OrderByElement item : ps.getOrderByElements()) {
        Column c = (Column) item.getExpression();
        SimpleNode astNode = c.getASTNode();
        assertNotNull(astNode);
        b.setCharAt(astNode.jjtGetFirstToken().absoluteBegin - 1, '#');
    }
    assertEquals("SELECT  /* testcomment */ \n *,  * FROM  -- testcomment2 \n mytable \n order by   #,  #", b.toString());
}

Example 12 with OrderByElement

use of net.sf.jsqlparser.statement.select.OrderByElement in project JSqlParser by JSQLParser.

the class ExpressionDeParser method visit.

@Override
public void visit(AnalyticExpression aexpr) {
    String name = aexpr.getName();
    Expression expression = aexpr.getExpression();
    Expression offset = aexpr.getOffset();
    Expression defaultValue = aexpr.getDefaultValue();
    boolean isAllColumns = aexpr.isAllColumns();
    KeepExpression keep = aexpr.getKeep();
    ExpressionList partitionExpressionList = aexpr.getPartitionExpressionList();
    List<OrderByElement> orderByElements = aexpr.getOrderByElements();
    WindowElement windowElement = aexpr.getWindowElement();
    buffer.append(name).append("(");
    if (expression != null) {
        expression.accept(this);
        if (offset != null) {
            buffer.append(", ");
            offset.accept(this);
            if (defaultValue != null) {
                buffer.append(", ");
                defaultValue.accept(this);
            }
        }
    } else if (isAllColumns) {
        buffer.append("*");
    }
    buffer.append(") ");
    if (keep != null) {
        keep.accept(this);
        buffer.append(" ");
    }
    switch(aexpr.getType()) {
        case WITHIN_GROUP:
            buffer.append("WITHIN GROUP");
            break;
        default:
            buffer.append("OVER");
    }
    buffer.append(" (");
    if (partitionExpressionList != null && !partitionExpressionList.getExpressions().isEmpty()) {
        buffer.append("PARTITION BY ");
        List<Expression> expressions = partitionExpressionList.getExpressions();
        for (int i = 0; i < expressions.size(); i++) {
            if (i > 0) {
                buffer.append(", ");
            }
            expressions.get(i).accept(this);
        }
        buffer.append(" ");
    }
    if (orderByElements != null && !orderByElements.isEmpty()) {
        buffer.append("ORDER BY ");
        orderByDeParser.setExpressionVisitor(this);
        orderByDeParser.setBuffer(buffer);
        for (int i = 0; i < orderByElements.size(); i++) {
            if (i > 0) {
                buffer.append(", ");
            }
            orderByDeParser.deParseElement(orderByElements.get(i));
        }
        if (windowElement != null) {
            buffer.append(' ');
            buffer.append(windowElement);
        }
    }
    buffer.append(")");
}

Example 13 with OrderByElement

use of net.sf.jsqlparser.statement.select.OrderByElement in project JSqlParser by JSQLParser.

the class OrderByDeParser method deParse.

public void deParse(boolean oracleSiblings, List<OrderByElement> orderByElementList) {
    if (oracleSiblings) {
        buffer.append(" ORDER SIBLINGS BY ");
    } else {
        buffer.append(" ORDER BY ");
    }
    for (Iterator<OrderByElement> iter = orderByElementList.iterator(); iter.hasNext(); ) {
        OrderByElement orderByElement = iter.next();
        deParseElement(orderByElement);
        if (iter.hasNext()) {
            buffer.append(", ");
        }
    }
}

Example 14 with OrderByElement

use of net.sf.jsqlparser.statement.select.OrderByElement in project raml-module-builder by folio-org.

the class PostgresClient method parseQuery.

/**
 * returns ParsedQuery with:
 * 1. Original query stripped of the order by, limit and offset clauses (if they existed in the query)
 * 2. Original query stripped of the limit and offset clauses (if they existed in the query)
 * 3. where clause part of query (included in the stripped query)
 * 4. original order by clause that was removed (or null)
 * 5. original limit clause that was removed (or null)
 * 6. original offset clause that was removed (or null)
 * @param query
 * @return
 */
static ParsedQuery parseQuery(String query) {
    List<OrderByElement> orderBy = null;
    net.sf.jsqlparser.statement.select.Limit limit = null;
    Expression where = null;
    net.sf.jsqlparser.statement.select.Offset offset = null;
    long start = System.nanoTime();
    String queryWithoutLimitOffset = "";
    try {
        try {
            net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(query);
            Select selectStatement = (Select) statement;
            orderBy = ((PlainSelect) selectStatement.getSelectBody()).getOrderByElements();
            limit = ((PlainSelect) selectStatement.getSelectBody()).getLimit();
            offset = ((PlainSelect) selectStatement.getSelectBody()).getOffset();
            where = ((PlainSelect) selectStatement.getSelectBody()).getWhere();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
        int startOfLimit = getLastStartPos(query, "limit");
        if (limit != null) {
            String suffix = Pattern.compile(limit.toString().trim(), Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfLimit)).replaceFirst("");
            query = query.substring(0, startOfLimit) + suffix;
        } else if (startOfLimit != -1) {
            // offset returns null if it was placed before the limit although postgres does allow this
            // we are here if offset appears in the query and not within quotes
            query = query.substring(0, startOfLimit) + Pattern.compile("limit\\s+[\\d]+", Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfLimit)).replaceFirst("");
        }
        int startOfOffset = getLastStartPos(query, "offset");
        if (offset != null) {
            String suffix = Pattern.compile(offset.toString().trim(), Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfOffset)).replaceFirst("");
            query = query.substring(0, startOfOffset) + suffix;
        } else if (startOfOffset != -1) {
            // offset returns null if it was placed before the limit although postgres does allow this
            // we are here if offset appears in the query and not within quotes
            query = query.substring(0, startOfOffset) + Pattern.compile("offset\\s+[\\d]+", Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfOffset)).replaceFirst("");
        }
        queryWithoutLimitOffset = query;
        // in the rare case where the order by clause somehow appears in the where clause
        int startOfOrderBy = getLastStartPos(query, "order by");
        if (orderBy != null) {
            StringBuilder sb = new StringBuilder("order by[ ]+");
            int size = orderBy.size();
            for (int i = 0; i < size; i++) {
                sb.append(orderBy.get(i).toString().replaceAll(" ", "[ ]+"));
                if (i < size - 1) {
                    sb.append(",?[ ]+");
                }
            }
            String regex = escape(sb.toString().trim());
            query = query.substring(0, startOfOrderBy) + Pattern.compile(regex, Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfOrderBy)).replaceFirst("");
        } else if (startOfOrderBy != -1) {
            // offset returns null if it was placed before the limit although postgres does allow this
            // we are here if offset appears in the query and not within quotes
            query = query.substring(0, startOfOrderBy) + Pattern.compile("order by.*", Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfOrderBy)).replaceFirst("");
        }
    } catch (Exception e) {
        log.error(e.getMessage(), e);
    }
    ParsedQuery pq = new ParsedQuery();
    pq.setCountFuncQuery(query);
    pq.setQueryWithoutLimOff(queryWithoutLimitOffset);
    if (where != null) {
        pq.setWhereClause(where.toString());
    }
    if (orderBy != null) {
        pq.setOrderByClause(orderBy.toString());
    }
    if (limit != null) {
        pq.setLimitClause(limit.toString());
    }
    if (offset != null) {
        pq.setOffsetClause(offset.toString());
    }
    long end = System.nanoTime();
    log.debug("Parse query for count_estimate function (ns) " + (end - start));
    return pq;
}

Example 15 with OrderByElement

use of net.sf.jsqlparser.statement.select.OrderByElement in project raml-module-builder by folio-org.

the class PostgresClientTest method parseQuery.

@ParameterizedTest
@ValueSource(strings = { // "SELECT * FROM table WHERE items_mt_view.jsonb->>' ORDER BY items_mt_view.jsonb->>\\'aaa\\'  ORDER BY items2_mt_view.jsonb' ORDER BY items_mt_view.jsonb->>'aaa limit' OFFSET 31 limit 10",
"SELECT * FROM table WHERE items_mt_view.jsonb->>'title' LIKE '%12345%' ORDER BY items_mt_view.jsonb->>'title' DESC OFFSET 30 limit 10", "select jsonb,_id FROM counter_mod_inventory_storage.item  WHERE jsonb@>'{\"barcode\":4}' order by jsonb->'a'  asc, jsonb->'b' desc, jsonb->'c'", "select jsonb,_id FROM counter_mod_inventory_storage.item  WHERE jsonb @> '{\"barcode\":4}' limit 100 offset 0", // "SELECT * FROM table WHERE items0_mt_view.jsonb->>' ORDER BY items1_mt_view.jsonb->>''aaa'' ' ORDER BY items2_mt_view.jsonb->>' ORDER BY items3_mt_view.jsonb->>''aaa'' '",
"SELECT _id FROM test_tenant_mod_inventory_storage.material_type  WHERE jsonb@>'{\"id\":\"af6c5503-71e7-4b1f-9810-5c9f1af7c570\"}' LIMIT 1 OFFSET 0 ", "select * from diku999_circulation_storage.audit_loan WHERE audit_loan.jsonb->>'id' = 'cf23adf0-61ba-4887-bf82-956c4aae2260 order by created_date LIMIT 10 OFFSET 0' order by created_date LIMIT 10 OFFSET 0 ", "select * from slowtest99_mod_inventory_storage.item where (item.jsonb->'barcode') = to_jsonb('1000000'::int)  order by a LIMIT 30;", "SELECT  * FROM slowtest_cql5_mod_inventory_storage.item  WHERE lower(f_unaccent(item.jsonb->>'default')) LIKE lower(f_unaccent('true')) ORDER BY lower(f_unaccent(item.jsonb->>'code')) DESC, item.jsonb->>'code' DESC LIMIT 10 OFFSET 0" // "SELECT * FROM harvard_mod_configuration.config_data  WHERE ((true) AND ( (config_data.jsonb->>'userId' ~ '') IS NOT TRUE)) OR (lower(f_unaccent(config_data.jsonb->>'userId')) ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]])joeshmoe($|[[:punct:]]|[[:space:]])')))  ORDER BY lower(f_unaccent(item.jsonb->>'code')) DESC, item.jsonb->>'code' DESC LIMIT 10 OFFSET 0",
})
void parseQuery(String query) throws JSQLParserException {
    long start = System.nanoTime();
    List<String> facets = new ArrayList<>();
    facets.add("barcode");
    facets.add("materialTypeId");
    List<FacetField> facetList = FacetManager.convertFacetStrings2FacetFields(facets, "jsonb");
    FacetManager.setCalculateOnFirst(0);
    ParsedQuery pQ = PostgresClient.parseQuery(query);
    // buildFacetQuery("tablename", pQ, facetList, true, query);
    net.sf.jsqlparser.statement.Statement statement = CCJSqlParserUtil.parse(query);
    Select selectStatement = (Select) statement;
    List<OrderByElement> orderBy = ((PlainSelect) selectStatement.getSelectBody()).getOrderByElements();
    net.sf.jsqlparser.statement.select.Limit limit = ((PlainSelect) selectStatement.getSelectBody()).getLimit();
    net.sf.jsqlparser.statement.select.Offset offset = ((PlainSelect) selectStatement.getSelectBody()).getOffset();
    // in the rare case where the order by clause somehow appears in the where clause
    if (orderBy != null) {
        int startOfOrderBy = PostgresClient.getLastStartPos(query, "order by");
        StringBuilder sb = new StringBuilder("order by[ ]+");
        int size = orderBy.size();
        for (int i = 0; i < size; i++) {
            sb.append(orderBy.get(i).toString().replaceAll(" ", "[ ]+"));
            if (i < size - 1) {
                sb.append(",?[ ]+");
            }
        }
        String regex = sb.toString().trim();
        query = query.substring(0, startOfOrderBy) + Pattern.compile(regex, Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfOrderBy)).replaceFirst("");
    }
    int startOfLimit = PostgresClient.getLastStartPos(query, "limit");
    if (limit != null) {
        query = query.substring(0, startOfLimit) + Pattern.compile(limit.toString().trim(), Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfLimit)).replaceFirst("");
    } else if (startOfLimit != -1) {
        // offset returns null if it was placed before the limit although postgres does allow this
        // we are here if offset appears in the query and not within quotes
        query = query.substring(0, startOfLimit) + Pattern.compile("limit\\s+[\\d]+", Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfLimit)).replaceFirst("");
    }
    if (offset != null) {
        int startOfOffset = PostgresClient.getLastStartPos(query, "offset");
        query = query.substring(0, startOfOffset) + Pattern.compile(offset.toString().trim(), Pattern.CASE_INSENSITIVE).matcher(query.substring(startOfOffset)).replaceFirst("");
    }
    long end = System.nanoTime();
    log.info(query + " from " + (end - start));
}