Assert.state(provider!=null, "Should not happen: missing PagingQueryProvider for DatabaseType="+type); provider.setFromClause(fromClause); provider.setWhereClause(whereClause); provider.setSortKeys(sortKeys); if (StringUtils.hasText(selectClause)) { provider.setSelectClause(selectClause); provider.setGroupClause(groupClause); provider.init(dataSource);
@Test public void testQueryContainsSortKeyDesc(){ pagingQueryProvider.getSortKeys().put("id", Order.DESCENDING); String s = pagingQueryProvider.generateFirstPageQuery(pageSize).toLowerCase(); assertTrue("Wrong query: "+s, s.contains("id desc")); }
@Test public void testFirstPageSqlWithAliases() { Map<String, Order> sorts = new HashMap<>(); sorts.put("owner.id", Order.ASCENDING); this.pagingQueryProvider = new MySqlPagingQueryProvider(); this.pagingQueryProvider.setSelectClause("SELECT owner.id as ownerid, first_name, last_name, dog_name "); this.pagingQueryProvider.setFromClause("FROM dog_owner owner INNER JOIN dog ON owner.id = dog.id "); this.pagingQueryProvider.setSortKeys(sorts); String firstPage = this.pagingQueryProvider.generateFirstPageQuery(5); String jumpToItemQuery = this.pagingQueryProvider.generateJumpToItemQuery(7, 5); String remainingPagesQuery = this.pagingQueryProvider.generateRemainingPagesQuery(5); assertEquals("SELECT owner.id as ownerid, first_name, last_name, dog_name FROM dog_owner owner INNER JOIN dog ON owner.id = dog.id ORDER BY owner.id ASC LIMIT 5", firstPage); assertEquals("SELECT owner.id FROM dog_owner owner INNER JOIN dog ON owner.id = dog.id ORDER BY owner.id ASC LIMIT 4, 1", jumpToItemQuery); assertEquals("SELECT owner.id as ownerid, first_name, last_name, dog_name FROM dog_owner owner INNER JOIN dog ON owner.id = dog.id WHERE ((owner.id > ?)) ORDER BY owner.id ASC LIMIT 5", remainingPagesQuery); }
public static String generateRowNumSqlQueryWithNesting(AbstractSqlPagingQueryProvider provider, String innerSelectClause, String outerSelectClause, boolean remainingPageQuery, String rowNumClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT ").append(outerSelectClause).append(" FROM (SELECT ").append(outerSelectClause) .append(", ").append(StringUtils.hasText(provider.getGroupClause()) ? "MIN(ROWNUM) as TMP_ROW_NUM" : "ROWNUM as TMP_ROW_NUM"); sql.append(" FROM (SELECT ").append(innerSelectClause).append(" FROM ").append(provider.getFromClause()); buildWhereClause(provider, remainingPageQuery, sql); buildGroupByClause(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); sql.append(")) WHERE ").append(rowNumClause); return sql.toString(); }
/** * Generate SQL query string using a TOP clause * * @param provider {@link AbstractSqlPagingQueryProvider} providing the * implementation specifics * @param topClause the implementation specific top clause to be used * @return the generated query */ public static String generateTopJumpToQuery(AbstractSqlPagingQueryProvider provider, String topClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT ").append(topClause).append(" ").append(buildSortKeySelect(provider)); sql.append(" FROM ").append(provider.getFromClause()); sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); buildGroupByClause(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); return sql.toString(); }
@Override @Test public void testGenerateFirstPageQueryWithGroupBy() { pagingQueryProvider.setGroupClause("id, dep"); String sql = "SELECT id, name, age FROM foo WHERE bar = 1 GROUP BY id, dep ORDER BY id ASC LIMIT 100"; String s = pagingQueryProvider.generateFirstPageQuery(pageSize); assertEquals(sql, s); }
@Override @Test public void testGenerateRemainingPagesQueryWithGroupBy() { pagingQueryProvider.setGroupClause("id, dep"); String sql = "SELECT * FROM (SELECT id, name, age FROM foo WHERE bar = 1 GROUP BY id, dep) AS MAIN_QRY WHERE ((id > ?)) ORDER BY id ASC LIMIT 100"; String s = pagingQueryProvider.generateRemainingPagesQuery(pageSize); assertEquals(sql, s); }
sortKeys.put("NAME", Order.ASCENDING); sortKeys.put("CODE", Order.DESCENDING); queryProvider.setSortKeys(sortKeys); queryProvider.setSelectClause("select NAME, CODE, sum(VALUE)"); queryProvider.setGroupClause("NAME, CODE"); List<Map<String, Object>> list = jdbcTemplate.queryForList(queryProvider.generateFirstPageQuery(pageSize)); logger.debug("First page result: " + list); assertEquals(pageSize, list.size()); queryProvider, list); assertNotSame(oldValues, startAfterValues); list = jdbcTemplate.queryForList(queryProvider.generateRemainingPagesQuery(pageSize), getParameterList(null, startAfterValues).toArray()); count += list.size();
@Test public void testGenerateJumpToItemQueryForTableQualifierReplacement() { pagingQueryProvider.setFromClause("foo_e E, foo_i I"); pagingQueryProvider.setWhereClause("E.id=I.id"); Map<String, Order> sortKeys = new HashMap<>(); sortKeys.put("E.id", Order.DESCENDING); pagingQueryProvider.setSortKeys(sortKeys); String sql="SELECT TMP_SUB.id FROM ( SELECT E.id, ROW_NUMBER() OVER ( ORDER BY id DESC) AS ROW_NUMBER FROM foo_e E, foo_i I WHERE E.id=I.id) AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER = 1 ORDER BY TMP_SUB.id DESC"; String s = pagingQueryProvider.generateJumpToItemQuery(45, pageSize); assertEquals(sql, s); }
@Test @Override public void testGenerateJumpToItemQueryForFirstPageWithGroupBy() { pagingQueryProvider.setGroupClause("dep"); String sql = "SELECT id FROM ( SELECT id, ROW_NUMBER() OVER ( ORDER BY id ASC) AS ROW_NUMBER FROM foo WHERE bar = 1 GROUP BY dep) WHERE ROW_NUMBER = 1 ORDER BY id ASC"; String s = pagingQueryProvider.generateJumpToItemQuery(45, pageSize); assertEquals(sql, s); }
/** * Generate SQL query string using a TOP clause * * @param provider {@link AbstractSqlPagingQueryProvider} providing the * implementation specifics * @param remainingPageQuery is this query for the remaining pages (true) as * opposed to the first page (false) * @param topClause the implementation specific top clause to be used * @return the generated query */ public static String generateGroupedTopSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, String topClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT ").append(topClause).append(" * FROM ("); sql.append("SELECT ").append(provider.getSelectClause()); sql.append(" FROM ").append(provider.getFromClause()); sql.append(provider.getWhereClause() == null ? "" : " WHERE " + provider.getWhereClause()); buildGroupByClause(provider, sql); sql.append(") AS MAIN_QRY "); sql.append("WHERE "); buildSortConditions(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); return sql.toString(); }
provider.setSelectClause("SELECT ID, FIRST, SECOND, THIRD"); provider.setFromClause("FOO"); provider.setSortKeys(sortKeys);
@Test public void testGenerateLimitJumpQueryDescending() { sortKeys.put("ID", Order.DESCENDING); AbstractSqlPagingQueryProvider qp = new TestSqlPagingQueryProvider("FOO", "BAR", sortKeys); String query = SqlPagingQueryUtils.generateLimitJumpToQuery(qp, "LIMIT 100, 1"); assertTrue("Wrong query: " + query, query.contains("ID DESC")); assertEquals("Wrong query: " + query, 0, StringUtils.countOccurrencesOf(query, "ASC")); assertEquals("Wrong query: " + query, 1, StringUtils.countOccurrencesOf(query, "DESC")); qp.setWhereClause("BAZ IS NOT NULL"); assertTrue("Wrong query: " + query, query.contains("ID DESC")); }
/** * Generate SQL query string using a TOP clause * * @param provider {@link AbstractSqlPagingQueryProvider} providing the * implementation specifics * @param remainingPageQuery is this query for the remaining pages (true) as * opposed to the first page (false) * @param topClause the implementation specific top clause to be used * @return the generated query */ public static String generateTopSqlQuery(AbstractSqlPagingQueryProvider provider, boolean remainingPageQuery, String topClause) { StringBuilder sql = new StringBuilder(); sql.append("SELECT ").append(topClause).append(" ").append(provider.getSelectClause()); sql.append(" FROM ").append(provider.getFromClause()); buildWhereClause(provider, remainingPageQuery, sql); buildGroupByClause(provider, sql); sql.append(" ORDER BY ").append(buildSortClause(provider)); return sql.toString(); }
@Test public void testGenerateFirstPageQueryWithMultipleSortKeys() { Map<String, Order> sortKeys = new LinkedHashMap<>(); sortKeys.put("name", Order.ASCENDING); sortKeys.put("id", Order.DESCENDING); pagingQueryProvider.setSortKeys(sortKeys); String s = pagingQueryProvider.generateFirstPageQuery(pageSize); assertEquals(getFirstPageSqlWithMultipleSortKeys(), s); }
@Test public void testGenerateRemainingPagesQueryWithMultipleSortKeys() { Map<String, Order> sortKeys = new LinkedHashMap<>(); sortKeys.put("name", Order.ASCENDING); sortKeys.put("id", Order.DESCENDING); pagingQueryProvider.setSortKeys(sortKeys); String s = pagingQueryProvider.generateRemainingPagesQuery(pageSize); assertEquals(getRemainingSqlWithMultipleSortKeys(), s); }
@Test public void testGenerateJumpToItemQueryWithMultipleSortKeys() { Map<String, Order> sortKeys = new LinkedHashMap<>(); sortKeys.put("name", Order.ASCENDING); sortKeys.put("id", Order.DESCENDING); pagingQueryProvider.setSortKeys(sortKeys); String s = pagingQueryProvider.generateJumpToItemQuery(145, pageSize); assertEquals(getJumpToItemQueryWithMultipleSortKeys(), s); }
List<Map.Entry<String, Order>> keys = new ArrayList<>(provider.getSortKeys().entrySet()); List<String> clauses = new ArrayList<>(); clause.append(entry.getKey()); clause.append(" = "); clause.append(provider.getSortKeyPlaceHolder(entry.getKey())); clause.append(provider.getSortKeyPlaceHolder(keys.get(i).getKey()));
@Test @Override public void testGenerateFirstPageQuery() { String sql = "SELECT * FROM (SELECT id, name, age FROM foo WHERE bar = 1 ORDER BY id ASC) WHERE ROWNUM <= 100"; String s = pagingQueryProvider.generateFirstPageQuery(pageSize); assertEquals(sql, s); pagingQueryProvider.setWhereClause(""); String sql2 = "SELECT * FROM (SELECT id, name, age FROM foo ORDER BY id ASC) WHERE ROWNUM <= 100"; String s2 = pagingQueryProvider.generateFirstPageQuery(pageSize); assertEquals(sql2, s2); }
@Test @Override public void testGenerateRemainingPagesQuery() { String sql = "SELECT TOP 100 id, name, age FROM foo WHERE (bar = 1) AND ((id > ?)) ORDER BY id ASC"; String s = pagingQueryProvider.generateRemainingPagesQuery(pageSize); assertEquals("", sql, s); }