public Object clone() { return new PageControl(pageNumber, pageSize, getOrderingFieldsAsArray()); }
private static String addLimitOffsetToQuery(String query, PageControl pageControl) { StringBuilder queryWithPagingSorting = new StringBuilder(query.length() + 50); queryWithPagingSorting.append(query); // for postgres, first order by queryWithPagingSorting.append(getOrderByFragment(pageControl.getOrderingFieldsAsArray())); // for postgres, then paginate queryWithPagingSorting.append(" LIMIT ").append(pageControl.getPageSize()); queryWithPagingSorting.append(" OFFSET ").append(pageControl.getStartRow()); return queryWithPagingSorting.toString(); } }
public static String addOracleNativePagingSortingToQuery(String query, PageControl pageControl) { StringBuilder queryWithPagingSorting = new StringBuilder(query.length() + 50); int minRowNum = pageControl.getStartRow() + 1; int maxRowNum = minRowNum + pageControl.getPageSize() - 1; // pagination calculations based off of double-projection of the results queryWithPagingSorting.append("SELECT outerResults.* FROM ( "); queryWithPagingSorting.append("SELECT innerResults.*, ROWNUM rnum FROM ( "); queryWithPagingSorting.append(query); // for oracle, order by occurs at the end of the original query, whether grouped or not queryWithPagingSorting.append(getOrderByFragment(pageControl.getOrderingFieldsAsArray())); queryWithPagingSorting.append(" ) innerResults "); // for oracle, paginate high off of the inner projection queryWithPagingSorting.append(" WHERE ROWNUM <= ").append(maxRowNum); // for oracle, paginate low off of the outer projection queryWithPagingSorting.append(" ) outerResults "); queryWithPagingSorting.append(" WHERE rnum >= ").append(minRowNum); return queryWithPagingSorting.toString(); }
public static String addSQLServerNativePagingSortingToQuery(String query, PageControl pageControl, boolean alternatePagingStyle) { StringBuilder queryWithPagingSorting = new StringBuilder(query.length() + 50); int minRowNum = pageControl.getStartRow() + 1; int maxRowNum = minRowNum + pageControl.getPageSize() - 1; String orderByClause = getOrderByFragment(pageControl.getOrderingFieldsAsArray()); if (alternatePagingStyle) { int index = findSelectListEndIndex(query); String selectList = query.substring(0, index); String restOfQuery = query.substring(index); queryWithPagingSorting.append("SELECT singleResults.* FROM ( "); queryWithPagingSorting.append(selectList); queryWithPagingSorting.append(", ROW_NUMBER() OVER( " + orderByClause + " ) AS rownum "); queryWithPagingSorting.append(restOfQuery); queryWithPagingSorting.append(") AS singleResults "); } else { queryWithPagingSorting.append("SELECT outerResults.* FROM ( "); queryWithPagingSorting.append(" SELECT innerResults.*, "); queryWithPagingSorting.append(" ROW_NUMBER() OVER( " + orderByClause + " ) AS rownum "); queryWithPagingSorting.append(" FROM ( " + query + " ) AS innerResults "); queryWithPagingSorting.append(" ) AS outerResults "); } queryWithPagingSorting.append("WHERE rownum <= ").append(maxRowNum); queryWithPagingSorting.append(" AND rownum >= ").append(minRowNum); return queryWithPagingSorting.toString(); }
/** * Used to create queries to use with the {@link org.rhq.core.domain.util.PageControl} objects. The query will already have its sort column * and order appended as well as having its first result and max results set according to the page control data. * * @param entityManager your entity manager * @param queryName name of the query * @param pageControl the controls on the paging and sorting * * @return a preconfigured query for ordered pagination */ public static Query createQueryWithOrderBy(EntityManager entityManager, String queryName, PageControl pageControl) { Query query; if (pageControl.getPrimarySortColumn() != null) { query = createQueryWithOrderBy(entityManager, queryName, pageControl.getOrderingFieldsAsArray()); } else { StackTraceElement caller = new Throwable().fillInStackTrace().getStackTrace()[1]; LOG.warn("Queries should really supply default sort columns. Caller did not: " + caller); // Use the standard named query if no sorting is specified query = entityManager.createNamedQuery(queryName); } setDataPage(query, pageControl); return query; }
private Query buildQuery(String queryString, PageControl pageControl) { boolean first = true; StringBuilder queryBuilder = new StringBuilder(queryString); for (OrderingField orderingField : pageControl.getOrderingFieldsAsArray()) { if (first) { // TODO GH: We could see if there already is an order by clause and contribute or override it queryBuilder.append(" ORDER BY "); first = false; } else { queryBuilder.append(", "); } queryBuilder.append(orderingField.getField()).append(" ").append(orderingField.getOrdering()); } Query query = entityManager.createQuery(queryBuilder.toString()); if (pageControl.getPageSize() > 0) { query.setFirstResult(pageControl.getStartRow()); query.setMaxResults(pageControl.getPageSize()); } return query; }