@Test public void testLimitOnlyNoOffset() { a(dialect.formSelect("pages", null, "", list("page_id"), 10, -1)).shouldBeEqual( "SELECT TOP 10 * FROM pages ORDER BY page_id"); }
@Override public String formSelect(String tableName, String[] columns, String subQuery, List<String> orderBys, long limit, long offset) { boolean needLimit = limit != -1; boolean needOffset = offset != -1; if(needOffset && (orderBys == null || orderBys.isEmpty())) { throw new DBException("MSSQL offset queries require an order by column."); } boolean keepSelect = false; StringBuilder fullQuery = new StringBuilder(); if (needOffset) { fullQuery.append("SELECT " + getSQColumns(columns) + " FROM (SELECT ROW_NUMBER() OVER (ORDER BY "); join(fullQuery, orderBys, ", "); fullQuery.append(") AS rownumber,"); } else if (needLimit) { fullQuery.append("SELECT TOP ").append(limit); } else { keepSelect = true; } if (tableName == null) { if (keepSelect) { fullQuery.append(subQuery); } else { Matcher m = selectPattern.matcher(subQuery); if (m.find()) { fullQuery.append(' ').append(subQuery.substring(m.end())); } else { fullQuery.append(subQuery); } } } else { if (keepSelect) { fullQuery.append("SELECT"); } fullQuery.append(getAllColumns(columns)).append(" FROM ").append(tableName); appendSubQuery(fullQuery, subQuery); } if (needOffset) { if (needLimit) { fullQuery.append(") AS sq WHERE rownumber BETWEEN ").append(offset + 1) .append(" AND ").append(limit + offset); } else { fullQuery.append(") AS sq WHERE rownumber >= ").append(offset + 1); } } else { appendOrderBy(fullQuery, orderBys); } return fullQuery.toString(); }
MSSQLDialect extends DefaultDialect { @Override public String formSelect(String tableName, String[] columns, String subQuery, List<String> orderBys, long limit, long offset) { boolean needLimit = limit != -1; boolean needOffset = offset != -1; if(needOffset && (orderBys == null || orderBys.isEmpty())) { throw new DBException("MSSQL offset queries require an order by column."); } boolean keepSelect = false; StringBuilder fullQuery = new StringBuilder(); if (needOffset) { fullQuery.append("SELECT " + getSQColumns(columns) + " FROM (SELECT ROW_NUMBER() OVER (ORDER BY "); join(fullQuery, orderBys, ", "); fullQuery.append(") AS rownumber,"); } else if (needLimit) { fullQuery.append("SELECT TOP ").append(limit); } else { keepSelect = true; } if (tableName == null) { if (keepSelect) { fullQuery.append(subQuery); } else { Matcher m = selectPattern.matcher(subQuery); if (m.find()) { fullQuery.append(' ').append(subQuery.substring(m.end())); } else { fullQuery.append(subQuery); } } } else { if (keepSelect) { fullQuery.append("SELECT"); } fullQuery.append(getAllColumns(columns)).append(" FROM ").append(tableName); appendSubQuery(fullQuery, subQuery); } if (needOffset) { if (needLimit) { fullQuery.append(") AS sq WHERE rownumber BETWEEN ").append(offset + 1) .append(" AND ").append(limit + offset); } else { fullQuery.append(") AS sq WHERE rownumber >= ").append(offset + 1); } } else { appendOrderBy(fullQuery, orderBys); } return fullQuery.toString(); } }
MSSQLDialect extends DefaultDialect { @Override public String formSelect(String tableName, String[] columns, String subQuery, List<String> orderBys, long limit, long offset) { boolean needLimit = limit != -1; boolean needOffset = offset != -1; if(needOffset && (orderBys == null || orderBys.isEmpty())) { throw new DBException("MSSQL offset queries require an order by column."); } boolean keepSelect = false; StringBuilder fullQuery = new StringBuilder(); if (needOffset) { fullQuery.append("SELECT " + getSQColumns(columns) + " FROM (SELECT ROW_NUMBER() OVER (ORDER BY "); join(fullQuery, orderBys, ", "); fullQuery.append(") AS rownumber,"); } else if (needLimit) { fullQuery.append("SELECT TOP ").append(limit); } else { keepSelect = true; } if (tableName == null) { if (keepSelect) { fullQuery.append(subQuery); } else { Matcher m = selectPattern.matcher(subQuery); if (m.find()) { fullQuery.append(' ').append(subQuery.substring(m.end())); } else { fullQuery.append(subQuery); } } } else { if (keepSelect) { fullQuery.append("SELECT"); } fullQuery.append(getAllColumns(columns)).append(" FROM ").append(tableName); appendSubQuery(fullQuery, subQuery); } if (needOffset) { if (needLimit) { fullQuery.append(") AS sq WHERE rownumber BETWEEN ").append(offset + 1) .append(" AND ").append(limit + offset); } else { fullQuery.append(") AS sq WHERE rownumber >= ").append(offset + 1); } } else { appendOrderBy(fullQuery, orderBys); } return fullQuery.toString(); }  }
MSSQLDialect extends DefaultDialect { @Override public String formSelect(String tableName, String[] columns, String subQuery, List<String> orderBys, long limit, long offset) { boolean needLimit = limit != -1; boolean needOffset = offset != -1; if(needOffset && (orderBys == null || orderBys.isEmpty())) { throw new DBException("MSSQL offset queries require an order by column."); } boolean keepSelect = false; StringBuilder fullQuery = new StringBuilder(); if (needOffset) { fullQuery.append("SELECT " + getSQColumns(columns) + " FROM (SELECT ROW_NUMBER() OVER (ORDER BY "); join(fullQuery, orderBys, ", "); fullQuery.append(") AS rownumber,"); } else if (needLimit) { fullQuery.append("SELECT TOP ").append(limit); } else { keepSelect = true; } if (tableName == null) { if (keepSelect) { fullQuery.append(subQuery); } else { Matcher m = selectPattern.matcher(subQuery); if (m.find()) { fullQuery.append(' ').append(subQuery.substring(m.end())); } else { fullQuery.append(subQuery); } } } else { if (keepSelect) { fullQuery.append("SELECT"); } fullQuery.append(getAllColumns(columns)).append(" FROM ").append(tableName); appendSubQuery(fullQuery, subQuery); } if (needOffset) { if (needLimit) { fullQuery.append(") AS sq WHERE rownumber BETWEEN ").append(offset + 1) .append(" AND ").append(limit + offset); } else { fullQuery.append(") AS sq WHERE rownumber >= ").append(offset + 1); } } else { appendOrderBy(fullQuery, orderBys); } return fullQuery.toString(); }  @Override String formSelect(String tableName, String[] columns, String subQuery, List<String> orderBys, long limit, long offset); @Override Object overrideDriverTypeConversion(MetaModel mm, String attributeName, Object value); }
MSSQLDialect extends DefaultDialect { @Override public String formSelect(String tableName, String[] columns, String subQuery, List<String> orderBys, long limit, long offset) { boolean needLimit = limit != -1; boolean needOffset = offset != -1; if(needOffset && (orderBys == null || orderBys.isEmpty())) { throw new DBException("MSSQL offset queries require an order by column."); } boolean keepSelect = false; StringBuilder fullQuery = new StringBuilder(); if (needOffset) { fullQuery.append("SELECT " + getSQColumns(columns) + " FROM (SELECT ROW_NUMBER() OVER (ORDER BY "); join(fullQuery, orderBys, ", "); fullQuery.append(") AS rownumber,"); } else if (needLimit) { fullQuery.append("SELECT TOP ").append(limit); } else { keepSelect = true; } if (tableName == null) { if (keepSelect) { fullQuery.append(subQuery); } else { Matcher m = selectPattern.matcher(subQuery); if (m.find()) { fullQuery.append(' ').append(subQuery.substring(m.end())); } else { fullQuery.append(subQuery); } } } else { if (keepSelect) { fullQuery.append("SELECT"); } fullQuery.append(getAllColumns(columns)).append(" FROM ").append(tableName); appendSubQuery(fullQuery, subQuery); } if (needOffset) { if (needLimit) { fullQuery.append(") AS sq WHERE rownumber BETWEEN ").append(offset + 1) .append(" AND ").append(limit + offset); } else { fullQuery.append(") AS sq WHERE rownumber >= ").append(offset + 1); } } else { appendOrderBy(fullQuery, orderBys); } return fullQuery.toString(); }  @Override String formSelect(String tableName, String[] columns, String subQuery, List<String> orderBys, long limit, long offset); @Override Object overrideDriverTypeConversion(MetaModel mm, String attributeName, Object value);  }