Hello, everyone. I`m trying to implement lazy loading tree with FreeformQuery.
When displaying the root of the tree I’m watching on MySQL server next queries:
8 Query rollback
8 Query select count(*) from map as m1 WHERE m1.parentid = 0
8 Query rollback
8 Query SET autocommit=0
8 Query select m1.id, m1.name as name, m2.id is not null as hasChildren from map as m1 left join map as m2 on m1.id=m2.parentid WHERE m1.parentid = 0 group by m1.id order by m1.name asc
8 Query commit
8 Query rollback
8 Query select id,parentid,name from map as m1 where m1.id = 90
8 Query rollback
8 Query select count(*) from map as m1
8 Query rollback
8 Query SET autocommit=0
8 Query select m1.id, m1.name as name, m2.id is not null as hasChildren from map as m1 left join map as m2 on m1.id=m2.parentid group by m1.id order by m1.name asc limit 200 offset 0
8 Query commit
8 Query rollback
8 Query select id,parentid,name from map as m1 where m1.id = 30
8 Query rollback
8 Query SET autocommit=0
8 Query select m1.id, m1.name as name, m2.id is not null as hasChildren from map as m1 left join map as m2 on m1.id=m2.parentid group by m1.id order by m1.name asc limit 200 offset 200
8 Query commit
8 Query rollback
8 Query select id,parentid,name from map as m1 where m1.id = 259
8 Query rollback
8 Query SET autocommit=0
8 Query select m1.id, m1.name as name, m2.id is not null as hasChildren from map as m1 left join map as m2 on m1.id=m2.parentid group by m1.id order by m1.name asc limit 200 offset 400
8 Query commit
8 Query rollback
8 Query SET autocommit=0
8 Query select m1.id, m1.name as name, m2.id is not null as hasChildren from map as m1 left join map as m2 on m1.id=m2.parentid group by m1.id order by m1.name asc limit 200 offset 600
8 Query commit
8 Query rollback
8 Query SET autocommit=0
8 Query select m1.id, m1.name as name, m2.id is not null as hasChildren from map as m1 left join map as m2 on m1.id=m2.parentid group by m1.id order by m1.name asc limit 200 offset 800
8 Query commit
8 Query rollback
Why are other elements are loaded from a table??
FreeformQuery query2 = new FreeformQuery("select * from map", pool, "id");
query2.setDelegate(new LocationStatementDelegate());
LocationSQLContainer cont = new LocationSQLContainer(query2);
Tree tree = new Tree("map");
tree.setItemCaptionPropertyId("name");
tree.setItemCaptionMode(AbstractSelect.ITEM_CAPTION_MODE_PROPERTY);
tree.setImmediate(true);
tree.setSelectable(true);
tree.setContainerDataSource(cont);
public class LocationSQLContainer extends SQLContainer implements Container.Hierarchical
{
private SQLContainer childrenContainer;
public LocationSQLContainer(QueryDelegate delegate) throws SQLException
{
super(delegate);
childrenContainer = new SQLContainer(getQueryDelegate());
}
@Override
public Collection<?> getChildren(Object itemId)
{
ArrayList<Object> myList = new ArrayList<Object>();
Item item = getItem(itemId);
if(item != null)
{
Equal myFilter = new Equal("m1.parentid", item.getItemProperty("id").getValue());
childrenContainer.addContainerFilter(myFilter);
myList.addAll(childrenContainer.getItemIds());
childrenContainer.removeContainerFilters("m1.parentid");
}
else
System.out.println("Item in getChildren() is null! - "+itemId.toString());
return myList;
}
@Override
public Object getParent(Object itemId)
{
return getContainerProperty(itemId, "parentid");
}
@Override
public Collection<?> rootItemIds()
{
Equal myFilter = new Equal("m1.parentid", 0);
addContainerFilter(myFilter);
ArrayList<Object> myList = new ArrayList<Object>();
myList.addAll(getItemIds());
removeContainerFilters("m1.parentid");
return myList;
}
@Override
public boolean setParent(Object itemId, Object newParentId) throws UnsupportedOperationException
{
return true;
}
@Override
public boolean areChildrenAllowed(Object itemId)
{
// if((Long)getItem(itemId).getItemProperty("hasChildren").getValue()==1)
// return true;
return true;
}
@Override
public boolean setChildrenAllowed(Object itemId, boolean areChildrenAllowed) throws UnsupportedOperationException
{
return true;
}
@Override
public boolean isRoot(Object itemId)
{
return true;
}
@Override
public boolean hasChildren(Object itemId)
{
return true;
}
}
public class LocationStatementDelegate implements FreeformStatementDelegate
{
private List<Filter> filters;
//private List<OrderBy> orderBys;
public LocationStatementDelegate()
{
QueryBuilder.setStringDecorator(new StringDecorator("", ""));
}
@Override
public StatementHelper getQueryStatement(int offset, int limit) throws UnsupportedOperationException
{
StatementHelper sh = new StatementHelper();
StringBuilder query = new StringBuilder("select m1.id, m1.name as name, m2.id is not null as hasChildren " +
"from map as m1 left join map as m2 on m1.id=m2.parentid");
if (filters != null)
query.append(QueryBuilder.getWhereStringForFilters(filters, sh));
query.append(" group by m1.id order by m1.name asc");
//query.append(generateOrderBy());
if(limit != 0)
{
query.append(" limit ").append(limit);
query.append(" offset ").append(offset);
}
sh.setQueryString(query.toString());
System.out.println(query.toString());
return sh;
}
@Override
public StatementHelper getCountStatement() throws UnsupportedOperationException
{
StatementHelper sh = new StatementHelper();
StringBuilder query = new StringBuilder("select count(*) from map as m1");
if (filters != null)
query.append(QueryBuilder.getWhereStringForFilters(filters, sh));
sh.setQueryString(query.toString());
System.out.println(query.toString());
return sh;
}
@Override
public StatementHelper getContainsRowQueryStatement(Object... keys) throws UnsupportedOperationException
{
StatementHelper sh = new StatementHelper();
StringBuilder query = new StringBuilder("select id,parentid,name from map as m1 where m1.id = ?");
sh.addParameterValue(keys[0]
);
sh.setQueryString(query.toString());
System.out.println(query.toString());
return sh;
}
@Override
public String getQueryString(int offset, int limit) throws UnsupportedOperationException
{
throw new UnsupportedOperationException("Not supported yet.");
}
@Override
public String getCountQuery() throws UnsupportedOperationException
{
throw new UnsupportedOperationException("Not supported yet.");
}
@Override
public void setFilters(List<Filter> filters) throws UnsupportedOperationException
{
this.filters = filters;
}
@Override
public void setOrderBy(List<OrderBy> orderBys) throws UnsupportedOperationException
{
//this.orderBys = orderBys;
}
@Override
public int storeRow(Connection conn, RowItem row) throws UnsupportedOperationException, SQLException
{
throw new UnsupportedOperationException("Not supported yet.");
}
@Override
public boolean removeRow(Connection conn, RowItem row) throws UnsupportedOperationException, SQLException
{
throw new UnsupportedOperationException("Not supported yet.");
}
@Override
public String getContainsRowQueryString(Object... keys) throws UnsupportedOperationException
{
throw new UnsupportedOperationException("Not supported yet.");
}
}