table sorting limit cache size - how?

Hi,

I need sorting on table with container (SQLContainer). I did own implementation FreeformStatementDelegate and I see that it sorts only 200 items.

I read this doc about Page Length and Cache Size:

https://vaadin.com/book/-/page/sqlcontainer.caching.html

I did:

	table.setPageLength(30);
	table.setCacheRate(0);
	table.refreshRowCache();

How can I change this limit? Where I did mistake? Is it possible to change this behaviour?

There is code:

	Object[] DATA_COLS_OBJ = new Object[]
 {
		"Id", "col1", "col2", "col3","col4", "col5", "col6","col7", "col8"
	};

	String sQuery = "SELECT * FROM view1";
	FreeformQuery query = new FreeformQuery(sQuery, connectionPool);
	query.setDelegate(new MyMSSQLGenerator("view1", "Id", Arrays.asList(DATA_COLS_OBJ)));
	container = new SQLContainer(query);

	...
	table.setContainerDataSource(container);

Code: MyMSSQLGenerator.java

public class MyMSSQLGenerator implements FreeformStatementDelegate {

	private static final long serialVersionUID = 1L;

	private List<Filter> filters;
	private List<OrderBy> orderBys;
	private List<Object> fields;
	private final String tableName;
	private final String idFieldName;

	public MyMSSQLGenerator(String tableName, String idFieldName,List<Object> list) {
		super();
		this.tableName = tableName;
		this.idFieldName = idFieldName;
		this.fields = list;
	}

	public String getQueryString(int offset, int limit)
			throws UnsupportedOperationException {
		throw new UnsupportedOperationException("Use getQueryStatement method.");
	}

	public String getCountQuery() throws UnsupportedOperationException {
		throw new UnsupportedOperationException("Use getCountStatement method.");
	}

	public void setFilters(List<Filter> filters)
			throws UnsupportedOperationException {
		this.filters = filters;
	}

	public void setOrderBy(List<OrderBy> orderBys)
			throws UnsupportedOperationException {
		this.orderBys = orderBys;
	}

	public int storeRow(Connection conn, RowItem row)
			throws UnsupportedOperationException, SQLException {
		Statement statement = conn.createStatement();

		String query = null;
		if (row.getId() instanceof TemporaryRowId) {
			query = getInsertQuery(row);
		} else {
			query = getUpdateQuery(row);
		}
		int retval = statement.executeUpdate(query);
		statement.close();
		return retval;
	}

	private String getUpdateQuery(RowItem row) {
		StringBuffer update = new StringBuffer("UPDATE " + tableName + " SET ");
		String lastField = (String) fields.get(fields.size() - 1);

		int iCountFields = fields.size() - 1;
		for (int i = 0; i <= iCountFields; i++) {
			String field = (String) fields.get(i);
			if (i >= 1) {
				appendUpdateValue(update, row, field);
				if (field != lastField) {
					update.append(", ");
				}
			}
		}

		update.append(" WHERE " + idFieldName + " = ").append(
				row.getItemProperty(idFieldName));
		return update.toString();
	}

	private String getInsertQuery(RowItem row) {
		StringBuffer insert = new StringBuffer("INSERT INTO " + tableName + "(");
		String lastField = (String) fields.get(fields.size() - 1);

		int iCountFields = fields.size() - 1;
		for (int i = 0; i <= iCountFields; i++) {
			String field = (String) fields.get(i);
			if (i >= 1) {
				insert.append(field);

				if (field != lastField) {
					insert.append(", ");
				}
			}
		}
		insert.append(") VALUES (");
		iCountFields = fields.size() - 1;

		for (int i = 0; i <= iCountFields; i++) {
			String field = (String) fields.get(i);
			if (i >= 1) {
				appendInsertValue(insert, row, field);
				if (field != lastField) {
					insert.append(", ");
				}
			}
		}
		insert.append(")");
		return insert.toString();
	}

	private void appendUpdateValue(StringBuffer update, RowItem row,
			String propId) {
		update.append(propId).append(" = ");
		Object val = row.getItemProperty(propId).getValue();
		if (val != null) {
			if (val instanceof String) {
				update.append("'").append(val).append("'");
			} else if (val instanceof Date) {
				SimpleDateFormat df = new SimpleDateFormat("yyyy-dd-MM");
				update.append("'").append(df.format(val)).append("'");
			} else {
				update.append(val);
			}
		} else {
			update.append("NULL");
		}
	}

	private void appendInsertValue(StringBuffer insert, RowItem row,
			String propId) {
		Object val = row.getItemProperty(propId).getValue();
		if (val != null) {
			if (val instanceof String) {
				insert.append("'").append(val).append("'");
			} else if (val instanceof Date) {
				SimpleDateFormat df = new SimpleDateFormat("yyyy-dd-MM");
				insert.append("'").append(df.format(val)).append("'");
			} else {
				insert.append(val);
			}
		} else {
			insert.append("NULL");
		}
	}

	public boolean removeRow(Connection conn, RowItem row)
			throws UnsupportedOperationException, SQLException {
		Statement statement = conn.createStatement();
		int rowsChanged = statement.executeUpdate("DELETE FROM " + tableName
				+ " WHERE " + idFieldName + " = "
				+ row.getItemProperty(idFieldName));
		statement.close();
		return rowsChanged == 1;
	}

	public String getContainsRowQueryString(Object... keys)
			throws UnsupportedOperationException {
		throw new UnsupportedOperationException(
				"Please use getContainsRowQueryStatement method.");
	}

	public StatementHelper getQueryStatement(int offset, int limit)
			throws UnsupportedOperationException {
		StatementHelper result = new StatementHelper();

		StringBuffer query = new StringBuffer(
				"select z.* from ( select ROW_NUMBER() OVER (ORDER BY "
						+ fields.get(0) + " ) AS row, a.* FROM ( SELECT ");

		String lastField = (String) fields.get(fields.size() - 1);

		for (Object field : fields) {
			query.append((String) field);

			if (field != lastField) {
				query.append(", ");

			}
		}
		query.append(" FROM ");
		query.append(tableName + " ");

		query.append(" ) a ) z ");
		if (offset != 0 || limit != 0) {
			query.append(" WHERE z.row > " + offset + " AND z.row <= "
					+ (offset + limit));
		}
		if (filters != null) {
			String sFilter = QueryBuilder.getWhereStringForFilters(filters,
					result);

			String sTmpQuery = query.toString();
			if (sTmpQuery.contains("WHERE")) {
				sFilter = sFilter.replace("WHERE", "AND ");
			}
			query.append(sFilter);
		}
		query.append(getOrderByString());

		result.setQueryString(query.toString());
		return result;
	}

	private String getOrderByString() {
		StringBuffer orderBuffer = new StringBuffer("");
		if (orderBys != null && !orderBys.isEmpty()) {
			orderBuffer.append(" ORDER BY ");
			OrderBy lastOrderBy = orderBys.get(orderBys.size() - 1);
			for (OrderBy orderBy : orderBys) {
				orderBuffer.append(orderBy.getColumn());
				if (orderBy.isAscending()) {
					orderBuffer.append(" ASC");
				} else {
					orderBuffer.append(" DESC");
				}
				if (orderBy != lastOrderBy) {
					orderBuffer.append(", ");
				}
			}
		}
		return orderBuffer.toString();
	}

	public StatementHelper getCountStatement()
			throws UnsupportedOperationException {
		StatementHelper result = new StatementHelper();
		StringBuffer query = new StringBuffer("SELECT COUNT(*) FROM "
				+ tableName + " ");
		if (filters != null) {
			query.append(QueryBuilder.getWhereStringForFilters(filters, result));
		}
		result.setQueryString(query.toString());

		return result;
	}

	public StatementHelper getContainsRowQueryStatement(Object... keys)
			throws UnsupportedOperationException {
		StatementHelper sh = new StatementHelper();
		StringBuffer query = new StringBuffer("SELECT ");
		String lastField = (String) fields.get(fields.size() - 1);
		for (Object field : fields) {
			query.append((String) field);
			if (field != lastField) {
				query.append(", ");
			}
		}
		query.append(" FROM " + tableName + " WHERE " + idFieldName + " = ?");
		sh.addParameterValue(keys[0]
);
		sh.setQueryString(query.toString());
		return sh;
	}
}

Best regards,
Paul

Do anyone know where is mistake?