Important Notice - Forums is archived
To simplify things and help our users to be more productive, we have archived the current forum and focus our efforts on helping developers on Stack Overflow. You can post new questions on Stack Overflow or join our Discord channel.

Vaadin lets you build secure, UX-first PWAs entirely in Java.
Free ebook & tutorial.
SQLContainer/FreeformQuery/FreeformStatementDelegate incorrect work
Hello everybody!
Ive faced a problem that I really dont know how to resolve yet. So any helps will be very appreciated!
Im using vaadin 6.7.4.
I have a FreeformQuery datasource that has a FreeformStatementDelegate implementation that handles the data.
This datasource is bound to a Table. All works well until I have too many rows to display, i.e. the SQLContainer has to fetch just a part of the data to display (lazy loading).
In this case, I get only part of the data displayed within the table (look at the pic - only bottom of the rows is filled with data, the upper part is "empty" )
Here is my implementation class for FreeformStatementDelegate interface:
public class TableStatementDelegate implements FreeformStatementDelegate {
private static final long serialVersionUID = 1L;
private List<Filter> filters;
private List<OrderBy> orderBys;
private final String tableName;
private final String idFieldName;
private List<String> fields;
public TableStatementDelegate(String tableName, String idFieldName,
List<String> fields) {
super();
this.tableName = tableName;
this.idFieldName = idFieldName;
this.fields = fields;
}
public String getQueryString(int offset, int limit)
throws UnsupportedOperationException {
throw new UnsupportedOperationException("Use getQueryStatement method.");
}
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();
}
private String getFiltersString() {
StringBuffer filterBuffer = new StringBuffer("");
if (filters != null && !filters.isEmpty()) {
Filter lastFilter = filters.get(filters.size() - 1);
filterBuffer.append(" WHERE ");
for (Filter filter : filters) {
filterBuffer.append(filter.toString());
if (filter != lastFilter) {
filterBuffer.append(" AND ");
}
}
}
return filterBuffer.toString();
}
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 getInsertQuery(RowItem row) {
StringBuffer insert = new StringBuffer("INSERT INTO " + tableName + "(");
String lastField = fields.get(fields.size() - 1);
for (String field : fields) {
insert.append(field);
if (field != lastField) {
insert.append(", ");
}
}
insert.append(") VALUES (");
for (String field : fields) {
appendInsertValue(insert, row, field);
if (field != lastField) {
insert.append(", ");
}
}
insert.append(")");
return insert.toString();
}
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("dd.MM.yyyy");
insert.append("TO_DATE('").append(df.format(val))
.append("', 'dd.mm.yyyy')");
} else {
insert.append(val);
}
} else {
insert.append("NULL");
}
}
private String getUpdateQuery(RowItem row) {
StringBuffer update = new StringBuffer("UPDATE " + tableName + " SET ");
String lastField = fields.get(fields.size() - 1);
for (String field : fields) {
appendUpdateValue(update, row, field);
if (field != lastField) {
update.append(", ");
}
}
update.append(" WHERE " + idFieldName + " = ").append(
row.getItemProperty(idFieldName));
return update.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("dd.MM.yyyy");
update.append("TO_DATE('").append(df.format(val))
.append("', 'dd.mm.yyyy')");
} else {
update.append(val);
}
} else {
update.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 ");
String lastField = fields.get(fields.size() - 1);
for (String field : fields) {
query.append(field);
if (field != lastField) {
query.append(", ");
}
}
query.append(" FROM ");
query.append(tableName + " ");
if (filters != null) {
query.append(QueryBuilder.getWhereStringForFilters(filters, result));
}
query.append(getOrderByString());
if (offset != 0 && limit != 0) {
query.insert(0, "select * from ( select a.*, rownum r from ( ");
query.append(" ) a where rownum < " + (offset + limit - 1)
+ " ) where r > " + offset);
}
System.out.println(query);
result.setQueryString(query.toString());
return result;
}
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());
System.out.println(query);
return result;
}
public StatementHelper getContainsRowQueryStatement(Object... keys)
throws UnsupportedOperationException {
StatementHelper sh = new StatementHelper();
StringBuffer query = new StringBuffer("SELECT ");
String lastField = fields.get(fields.size() - 1);
for (String field : fields) {
query.append(field);
if (field != lastField) {
query.append(", ");
}
}
query.append(" FROM " + tableName + " WHERE " + idFieldName + " = ?");
sh.addParameterValue(keys[0]);
sh.setQueryString(query.toString());
System.out.println(query + " " + keys[0]);
return sh;
}
}
And here is how I bind the table and datasource:
public class TesttablesourceApplication extends Application {
private static final long serialVersionUID = 1L;
private SQLContainer personContainer = null;
@Override
public void init() {
Window mainWindow = new Window("Testtablesource Application");
mainWindow.setContent(getContent());
setMainWindow(mainWindow);
}
public VerticalLayout getContent() {
try {
J2EEConnectionPool pool = new J2EEConnectionPool("MedvedJNDI");
String[] fields = { "idcity", "namecity" };
FreeformQuery query = new FreeformQuery("select * from dpc.MIS_SP_CITY",
pool, "idcity");
TableStatementDelegate td = new TableStatementDelegate("dpc.MIS_SP_CITY",
"idcity", Arrays.asList(fields));
query.setDelegate(td);
personContainer = new SQLContainer(query);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
VerticalLayout content = new VerticalLayout();
Table table = new Table("");
table.setWidth("100%");
table.setHeight("170px");
table.setContainerDataSource(personContainer);
content.addComponent(table);
return content;
}
}
The problem seems to have resolved! I noticed that offset and limit parameters had never been taken into account when I generated the result in getQueryStatement method.
I changed the method getQueryStatement in the delegate implementation as follows:
if (offset != 0 || limit != 0) and stuff got to work.
Still, I dont understand the weird behavior of the table showing only bottom of table data.
I found another bug in the method getQueryStatement(int offset, int limit) in the article above. I was getting an infinite fetch loop after scrolling down to the second page. The last "if" block should look like this:
if (offset != 0 || limit != 0) {
int start = offset + 1;
int end = offset + limit;
sb.insert(0, "select * from ( select a.*, rownum r from ( ");
sb.append(" ) a ) where r between ").append(start).append(" and ").append(end);
}