/** * Print the pagination SQL using HSQL syntax "SELECT LIMIT <first> <max>". */ @Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); } if (max <= 0 || !(this.shouldUseRownumFiltering())) { super.printSQLSelectStatement(call, printer, statement); return; } statement.setUseUniqueFieldAliases(true); printer.printString("SELECT LIMIT "); printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); printer.printString(" "); printer.printParameter(DatabaseCall.MAXROW_FIELD); Writer writer = printer.getWriter(); // Need to trim the SELECT from the SQL. printer.setWriter(new StringWriter()); call.setFields(statement.printSQL(printer)); String sql = printer.getWriter().toString(); printer.setWriter(writer); printer.printString(sql.substring(6, sql.length())); call.setIgnoreFirstRowMaxResultsSettings(true); }
/** * Print the pagination SQL using HSQL syntax "SELECT LIMIT {@literal <first> <max>}". */ @Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); } if (max <= 0 || !(this.shouldUseRownumFiltering())) { super.printSQLSelectStatement(call, printer, statement); return; } statement.setUseUniqueFieldAliases(true); printer.printString("SELECT LIMIT "); printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); printer.printString(" "); printer.printParameter(DatabaseCall.MAXROW_FIELD); Writer writer = printer.getWriter(); // Need to trim the SELECT from the SQL. printer.setWriter(new StringWriter()); call.setFields(statement.printSQL(printer)); String sql = printer.getWriter().toString(); printer.setWriter(writer); printer.printString(sql.substring(6, sql.length())); call.setIgnoreFirstRowSetting(true); call.setIgnoreMaxResultsSetting(true); }
/** * Print the pagination SQL using HSQL syntax "SELECT LIMIT {@literal <first> <max>}". */ @Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); } if (max <= 0 || !(this.shouldUseRownumFiltering())) { super.printSQLSelectStatement(call, printer, statement); return; } statement.setUseUniqueFieldAliases(true); printer.printString("SELECT LIMIT "); printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); printer.printString(" "); printer.printParameter(DatabaseCall.MAXROW_FIELD); Writer writer = printer.getWriter(); // Need to trim the SELECT from the SQL. printer.setWriter(new StringWriter()); call.setFields(statement.printSQL(printer)); String sql = printer.getWriter().toString(); printer.setWriter(writer); printer.printString(sql.substring(6, sql.length())); call.setIgnoreFirstRowSetting(true); call.setIgnoreMaxResultsSetting(true); }
/** * Print the pagination SQL using Postgres syntax * " LIMIT <max> OFFSET <first>". */ @Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); } if (max <= 0 || !(this.shouldUseRownumFiltering())) { super.printSQLSelectStatement(call, printer, statement); return; } statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(LIMIT); printer.printParameter(DatabaseCall.MAXROW_FIELD); printer.printString(OFFSET); printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); call.setIgnoreFirstRowMaxResultsSettings(true); }
/** * Print the pagination SQL using H2 syntax " LIMIT <max> OFFSET <first>". */ @Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); } if (max <= 0 || !(this.shouldUseRownumFiltering())) { super.printSQLSelectStatement(call, printer, statement); return; } statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(" LIMIT "); printer.printParameter(DatabaseCall.MAXROW_FIELD); printer.printString(" OFFSET "); printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); call.setIgnoreFirstRowMaxResultsSettings(true); }
/** * Print the pagination SQL using Symfoware syntax " WITH OPTION LIMIT ( * {@literal <max>})". There is no equivalent to 'OFFSET'.<br> * Even though most performance benefit comes from including the offset in * the SQL statement, for this platform the benefit of including LIMIT is * that it allows pagination with result sets with scrollable cursors too * (which the Symfoware JDBC driver's setMaxRows method does not support). */ @Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); } // only MaxRows can be worked into the statement if (max > 0 && this.shouldUseRownumFiltering()) { statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(" WITH OPTION LIMIT ("); printer.printParameter(DatabaseCall.MAXROW_FIELD); printer.printString(")"); call.setIgnoreMaxResultsSetting(true); } else { // use JDBC absolute (and setMaxRows) methods for pagination super.printSQLSelectStatement(call, printer, statement); } }
/** * Print the pagination SQL using Symfoware syntax " WITH OPTION LIMIT ( * {@literal <max>})". There is no equivalent to 'OFFSET'.<br> * Even though most performance benefit comes from including the offset in * the SQL statement, for this platform the benefit of including LIMIT is * that it allows pagination with result sets with scrollable cursors too * (which the Symfoware JDBC driver's setMaxRows method does not support). */ @Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); } // only MaxRows can be worked into the statement if (max > 0 && this.shouldUseRownumFiltering()) { statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(" WITH OPTION LIMIT ("); printer.printParameter(DatabaseCall.MAXROW_FIELD); printer.printString(")"); call.setIgnoreMaxResultsSetting(true); } else { // use JDBC absolute (and setMaxRows) methods for pagination super.printSQLSelectStatement(call, printer, statement); } }
/** * Print the pagination SQL using Postgres syntax * " LIMIT {@literal <max> OFFSET <first>}". */ @Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); } if (max <= 0 || !(this.shouldUseRownumFiltering())) { super.printSQLSelectStatement(call, printer, statement); return; } statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(LIMIT); printer.printParameter(DatabaseCall.MAXROW_FIELD); printer.printString(OFFSET); printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); call.setIgnoreFirstRowSetting(true); call.setIgnoreMaxResultsSetting(true); }
/** * Print the pagination SQL using Postgres syntax * " LIMIT {@literal <max> OFFSET <first>}". */ @Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); } if (max <= 0 || !(this.shouldUseRownumFiltering())) { super.printSQLSelectStatement(call, printer, statement); return; } statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(LIMIT); printer.printParameter(DatabaseCall.MAXROW_FIELD); printer.printString(OFFSET); printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); call.setIgnoreFirstRowSetting(true); call.setIgnoreMaxResultsSetting(true); }
@Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; int firstRow = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); firstRow = statement.getQuery().getFirstResult(); } if (max <= 0 || !(this.shouldUseRownumFiltering())) { super.printSQLSelectStatement(call, printer, statement); return; } statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(LIMIT); printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); printer.printString(", "); printer.printParameter(DatabaseCall.MAXROW_FIELD); call.setIgnoreFirstRowMaxResultsSettings(true); }
/** * Print the pagination SQL using H2 syntax " LIMIT {@literal <max> OFFSET <first>}". */ @Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); } if (max <= 0 || !(this.shouldUseRownumFiltering())) { super.printSQLSelectStatement(call, printer, statement); return; } statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(" LIMIT "); printer.printParameter(DatabaseCall.MAXROW_FIELD); printer.printString(" OFFSET "); printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); call.setIgnoreFirstRowSetting(true); call.setIgnoreMaxResultsSetting(true); }
/** * Print the pagination SQL using H2 syntax " LIMIT {@literal <max> OFFSET <first>}". */ @Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); } if (max <= 0 || !(this.shouldUseRownumFiltering())) { super.printSQLSelectStatement(call, printer, statement); return; } statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(" LIMIT "); printer.printParameter(DatabaseCall.MAXROW_FIELD); printer.printString(" OFFSET "); printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); call.setIgnoreFirstRowSetting(true); call.setIgnoreMaxResultsSetting(true); }
@Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); } if (max <= 0 || !(this.shouldUseRownumFiltering())) { super.printSQLSelectStatement(call, printer, statement); statement.appendForUpdateClause(printer); return; } statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(LIMIT); printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); printer.printString(", "); printer.printParameter(DatabaseCall.MAXROW_FIELD); statement.appendForUpdateClause(printer); call.setIgnoreFirstRowSetting(true); call.setIgnoreMaxResultsSetting(true); }
statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(" LIMIT ");
/** * Print the pagination SQL using FB syntax " ROWS {@literal <max> TO <first>}". */ @Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; int firstRow = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); firstRow = statement.getQuery().getFirstResult(); } // Both must be set, otherwise if one is set next, the old cached SQL would be used. if (!shouldUseRownumFiltering() || (max <= 0) || (firstRow <= 0)) { super.printSQLSelectStatement(call, printer, statement); return; } statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(" ROWS ("); printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); printer.printString(" + 1) TO "); printer.printParameter(DatabaseCall.MAXROW_FIELD); call.setIgnoreFirstRowSetting(true); call.setIgnoreMaxResultsSetting(true); }
/** * Print the pagination SQL using FB syntax " ROWS <max> TO <first>". */ @Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; int firstRow = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); firstRow = statement.getQuery().getFirstResult(); } // Both must be set, otherwise if one is set next, the old cached SQL would be used. if (!shouldUseRownumFiltering() || (max <= 0) || (firstRow <= 0)) { super.printSQLSelectStatement(call, printer, statement); return; } statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(" ROWS ("); printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); printer.printString(" + 1) TO "); printer.printParameter(DatabaseCall.MAXROW_FIELD); call.setIgnoreFirstRowMaxResultsSettings(true); }
return; } else if ( max > 0 ){ statement.setUseUniqueFieldAliases(true); printer.printString("SELECT * FROM (SELECT * FROM (SELECT "); printer.printString("EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM ("); statement.setUseUniqueFieldAliases(true); printer.printString("SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM ("); call.setFields(statement.printSQL(printer));
statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(" LIMIT ");
/** * Print the pagination SQL using FB syntax " ROWS {@literal <max> TO <first>}". */ @Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; int firstRow = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); firstRow = statement.getQuery().getFirstResult(); } // Both must be set, otherwise if one is set next, the old cached SQL would be used. if (!shouldUseRownumFiltering() || (max <= 0) || (firstRow <= 0)) { super.printSQLSelectStatement(call, printer, statement); return; } statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(" ROWS ("); printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); printer.printString(" + 1) TO "); printer.printParameter(DatabaseCall.MAXROW_FIELD); call.setIgnoreFirstRowSetting(true); call.setIgnoreMaxResultsSetting(true); }
@Override public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) { int max = 0; int firstRow = 0; if (statement.getQuery() != null) { max = statement.getQuery().getMaxRows(); firstRow = statement.getQuery().getFirstResult(); } if (max <= 0 || !(this.shouldUseRownumFiltering())) { super.printSQLSelectStatement(call, printer, statement); statement.appendForUpdateClause(printer); return; } statement.setUseUniqueFieldAliases(true); call.setFields(statement.printSQL(printer)); printer.printString(LIMIT); printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD); printer.printString(", "); printer.printParameter(DatabaseCall.MAXROW_FIELD); statement.appendForUpdateClause(printer); call.setIgnoreFirstRowSetting(true); call.setIgnoreMaxResultsSetting(true); }