provider.setWhereClause(whereClause); provider.setSortKeys(sortKeys); if (StringUtils.hasText(selectClause)) {
@Before public void setUp() { if (pagingQueryProvider == null) { throw new IllegalArgumentException("pagingQueryProvider can't be null"); } pagingQueryProvider.setSelectClause("id, name, age"); pagingQueryProvider.setFromClause("foo"); pagingQueryProvider.setWhereClause("bar = 1"); Map<String, Order> sortKeys = new LinkedHashMap<>(); sortKeys.put("id", Order.ASCENDING); pagingQueryProvider.setSortKeys(sortKeys); pageSize = 100; }
@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 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")); }
@Test public void testGenerateTopJumpQueryDescending() { sortKeys.put("ID", Order.DESCENDING); AbstractSqlPagingQueryProvider qp = new TestSqlPagingQueryProvider("FOO", "BAR", sortKeys); String query = SqlPagingQueryUtils.generateTopJumpToQuery(qp, "TOP 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")); }
@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 public void testGenerateTopJumpToQuery() { AbstractSqlPagingQueryProvider qp = new TestSqlPagingQueryProvider("FOO", "BAR", sortKeys); assertEquals("SELECT TOP 100, 1 ID FROM BAR ORDER BY ID ASC", SqlPagingQueryUtils .generateTopJumpToQuery(qp, "TOP 100, 1")); qp.setWhereClause("BAZ IS NOT NULL"); assertEquals("SELECT TOP 100, 1 ID FROM BAR WHERE BAZ IS NOT NULL ORDER BY ID ASC", SqlPagingQueryUtils.generateTopJumpToQuery(qp, "TOP 100, 1")); }
provider.setWhereClause(this.whereClause); provider.setGroupClause(this.groupClause); provider.setSortKeys(this.sortKeys);
@Test public void testGenerateLimitJumpToQuery() { AbstractSqlPagingQueryProvider qp = new TestSqlPagingQueryProvider("FOO", "BAR", sortKeys); assertEquals("SELECT ID FROM BAR ORDER BY ID ASC LIMIT 100, 1", SqlPagingQueryUtils .generateLimitJumpToQuery(qp, "LIMIT 100, 1")); qp.setWhereClause("BAZ IS NOT NULL"); assertEquals("SELECT ID FROM BAR WHERE BAZ IS NOT NULL ORDER BY ID ASC LIMIT 100, 1", SqlPagingQueryUtils.generateLimitJumpToQuery(qp, "LIMIT 100, 1")); }
@Test public void testGenerateTopSqlQueryDescending() { sortKeys.put("ID", Order.DESCENDING); AbstractSqlPagingQueryProvider qp = new TestSqlPagingQueryProvider("FOO", "BAR", sortKeys); assertEquals("SELECT TOP 100 FOO FROM BAR ORDER BY ID DESC", SqlPagingQueryUtils.generateTopSqlQuery(qp, false, "TOP 100")); assertEquals("SELECT TOP 100 FOO FROM BAR WHERE ((ID < ?)) ORDER BY ID DESC", SqlPagingQueryUtils .generateTopSqlQuery(qp, true, "TOP 100")); qp.setWhereClause("BAZ IS NOT NULL"); assertEquals("SELECT TOP 100 FOO FROM BAR WHERE BAZ IS NOT NULL ORDER BY ID DESC", SqlPagingQueryUtils .generateTopSqlQuery(qp, false, "TOP 100")); assertEquals("SELECT TOP 100 FOO FROM BAR WHERE (BAZ IS NOT NULL) AND ((ID < ?)) ORDER BY ID DESC", SqlPagingQueryUtils.generateTopSqlQuery(qp, true, "TOP 100")); }
@Test public void testGenerateRowNumSqlQueryDescending() { sortKeys.put("ID", Order.DESCENDING); AbstractSqlPagingQueryProvider qp = new TestSqlPagingQueryProvider("FOO", "BAR", sortKeys); assertEquals( "SELECT * FROM (SELECT FOO FROM BAR ORDER BY ID DESC) WHERE ROWNUMBER <= 100", SqlPagingQueryUtils.generateRowNumSqlQuery(qp, false, "ROWNUMBER <= 100")); assertEquals( "SELECT * FROM (SELECT FOO FROM BAR ORDER BY ID DESC) WHERE ROWNUMBER <= 100 AND ((ID < ?))", SqlPagingQueryUtils.generateRowNumSqlQuery(qp, true, "ROWNUMBER <= 100")); qp.setWhereClause("BAZ IS NOT NULL"); assertEquals( "SELECT * FROM (SELECT FOO FROM BAR WHERE BAZ IS NOT NULL ORDER BY ID DESC) WHERE ROWNUMBER <= 100", SqlPagingQueryUtils.generateRowNumSqlQuery(qp, false, "ROWNUMBER <= 100")); assertEquals( "SELECT * FROM (SELECT FOO FROM BAR WHERE BAZ IS NOT NULL ORDER BY ID DESC) WHERE ROWNUMBER <= 100 AND ((ID < ?))", SqlPagingQueryUtils.generateRowNumSqlQuery(qp, true, "ROWNUMBER <= 100")); }
@Test public void testGenerateLimitSqlQuery() { AbstractSqlPagingQueryProvider qp = new TestSqlPagingQueryProvider("FOO", "BAR", sortKeys); assertEquals("SELECT FOO FROM BAR ORDER BY ID ASC LIMIT 100", SqlPagingQueryUtils.generateLimitSqlQuery(qp, false, "LIMIT 100")); assertEquals("SELECT FOO FROM BAR WHERE ((ID > ?)) ORDER BY ID ASC LIMIT 100", SqlPagingQueryUtils .generateLimitSqlQuery(qp, true, "LIMIT 100")); qp.setWhereClause("BAZ IS NOT NULL"); assertEquals("SELECT FOO FROM BAR WHERE BAZ IS NOT NULL ORDER BY ID ASC LIMIT 100", SqlPagingQueryUtils .generateLimitSqlQuery(qp, false, "LIMIT 100")); assertEquals("SELECT FOO FROM BAR WHERE (BAZ IS NOT NULL) AND ((ID > ?)) ORDER BY ID ASC LIMIT 100", SqlPagingQueryUtils.generateLimitSqlQuery(qp, true, "LIMIT 100")); }
@Test public void testGenerateTopSqlQuery() { AbstractSqlPagingQueryProvider qp = new TestSqlPagingQueryProvider("FOO", "BAR", sortKeys); assertEquals("SELECT TOP 100 FOO FROM BAR ORDER BY ID ASC", SqlPagingQueryUtils.generateTopSqlQuery(qp, false, "TOP 100")); assertEquals("SELECT TOP 100 FOO FROM BAR WHERE ((ID > ?)) ORDER BY ID ASC", SqlPagingQueryUtils .generateTopSqlQuery(qp, true, "TOP 100")); qp.setWhereClause("BAZ IS NOT NULL"); assertEquals("SELECT TOP 100 FOO FROM BAR WHERE BAZ IS NOT NULL ORDER BY ID ASC", SqlPagingQueryUtils .generateTopSqlQuery(qp, false, "TOP 100")); assertEquals("SELECT TOP 100 FOO FROM BAR WHERE (BAZ IS NOT NULL) AND ((ID > ?)) ORDER BY ID ASC", SqlPagingQueryUtils.generateTopSqlQuery(qp, true, "TOP 100")); }
@Test public void testGenerateRowNumSqlQuery() { AbstractSqlPagingQueryProvider qp = new TestSqlPagingQueryProvider("FOO", "BAR", sortKeys); assertEquals( "SELECT * FROM (SELECT FOO FROM BAR ORDER BY ID ASC) WHERE ROWNUMBER <= 100", SqlPagingQueryUtils.generateRowNumSqlQuery(qp, false, "ROWNUMBER <= 100")); assertEquals( "SELECT * FROM (SELECT FOO FROM BAR ORDER BY ID ASC) WHERE ROWNUMBER <= 100 AND ((ID > ?))", SqlPagingQueryUtils.generateRowNumSqlQuery(qp, true, "ROWNUMBER <= 100")); qp.setWhereClause("BAZ IS NOT NULL"); assertEquals( "SELECT * FROM (SELECT FOO FROM BAR WHERE BAZ IS NOT NULL ORDER BY ID ASC) WHERE ROWNUMBER <= 100", SqlPagingQueryUtils.generateRowNumSqlQuery(qp, false, "ROWNUMBER <= 100")); assertEquals( "SELECT * FROM (SELECT FOO FROM BAR WHERE BAZ IS NOT NULL ORDER BY ID ASC) WHERE ROWNUMBER <= 100 AND ((ID > ?))", SqlPagingQueryUtils.generateRowNumSqlQuery(qp, true, "ROWNUMBER <= 100")); }
provider.setWhereClause(whereClause); provider.setSortKeys(sortKeys); if (StringUtils.hasText(selectClause)) {
provider.setWhereClause(this.whereClause); provider.setGroupClause(this.groupClause); provider.setSortKeys(this.sortKeys);