Hi.
I would like to populate tree component from Database please tell me in which way is better to do. Do I have to create several tables for that or It’s possible to do using one table. Which way is more comfortable to use using SQLContainer. And Is there an example populating tree using SQLContainer.
Please help me with this question.
Best regards,
Manvel Saroyan
Hi,
I think we discussed the subject on the Skype channel some while ago.
For a quick recap, in my opinion, the best way to do this is to extend SQLContainer and implement the Hierarchical interface. The implementation of the interface is really database-specific, as it depends on the representation of the hierarchy in your database.
I think that the easiest way to represent hierarchy would be to have a “parentID” column in the table. Another option would be a separate table that has “parentID” and “childID” columns. Whichever representation you have, you can probably make a query that gives the other kind of representation.
Probably the only potentially hard task is listing the children in the getChildren() method, as you may have to iterate over the container to get them. The second representation might make this task easier, but would require another query.
I don’t know of a ready example, if you’re looking for such. Perhaps somebody else here has one.
Hi Marko,
Thank you for fast reply.
Yes, but after skype channel I was having some difficulties to creat that connection. I Thought that maybe someone will post some code from their sollutions.
Now I went quite a forward with that coonection. Here is how.
It’s the class you told me to create:
public class HierarchicalConnection extends SQLContainer implements Container.Hierarchical {
public HierarchicalConnection(QueryDelegate delegate) throws SQLException {
super(delegate);
}
public Collection<?> getChildren(Object itemId) {
ArrayList<Object> myList = new ArrayList<Object>();
/// CREATING ITERATOR TO ITERATE TO COMPARE PARENTID WITH ID's ///
for (Object id : getItemIds()) {
if ((Integer) getItem(id).getItemProperty("parentID").getValue() == (Integer) getItem(itemId).getItemProperty("id").getValue()){
myList.add(id);
}
}
return myList;
}
public Object getParent(Object itemId) {
return getContainerProperty(itemId, "parentID");
}
public Collection<?> rootItemIds() {
ArrayList<Object> myList = new ArrayList<Object>();
//// ITERATING TO GET ROOT ITEMS /////
Object aa= 0;
for (Object id : getItemIds()) {
if ((Integer) getItem(id).getItemProperty("parentID").getValue()==0){
System.out.println(getItem(id).getItemProperty("parentID"));
myList.add(id);
}
}
return myList;
}
public boolean setParent(Object itemId, Object newParentId)
throws UnsupportedOperationException {
return true;
}
public boolean areChildrenAllowed(Object itemId) {
if (Integer.parseInt(getItem(itemId).getItemProperty("leaf").toString())==1) {
return false;
}
return true;
}
public boolean setChildrenAllowed(Object itemId, boolean areChildrenAllowed)
throws UnsupportedOperationException {
return true;
}
public boolean isRoot(Object itemId) {
return true;
}
public boolean hasChildren(Object itemId) {
return true;
}
}
And here is what I get :
So It works, But it works only for a small database table, but my real table contains 1000 rows. When I Use it with tableQuery and click on nodes. My JVM and MYSQL processes go to near 50% of CPU and operations are done veryslowly (displaying childes).
How can I figure out this situation.
I think If I’ll create 3 tables for each my nodes maybe it’ll be solution ???
What you think ?
Best Ragards,
Manvel Saroyan
I’ve already solved the problem about big database.
I’ve created another container of that table and added filter on that container and got the children nodes :
SQLContainer child = myCont.getChildrenPlan(); /// ANOTHER CONTAINER
Filter aa = new Filter("parentID", ComparisonType.EQUALS, (Integer) getItem(itemId).getItemProperty("id").getValue());
child.addFilter(aa);
myList.addAll(child.getItemIds());
child.removeAllContainerFilters("parentID");
So it’s begin works very fast. I think the problem was because Iterator. I think it was to hard to iterate over big table for JVM. But for MYSQL it wasn’t a problem.
Thanks to all developers for ideas.
Here is final Code FOR people who will search for sollution:
public class HierarchicalSQLContainer extends SQLContainer implements Container.Hierarchical {
private SQLContainer childrenContainer;
public HierarchicalSQLContainer(QueryDelegate delegate) throws SQLException {
super(delegate);
childrenContainer = new SQLContainer(getQueryDelegate());
}
public Collection<?> getChildren(Object itemId) {
Filter myFilter = new Filter("parentID", ComparisonType.EQUALS,
(Integer) getItem(itemId).getItemProperty("id").getValue());
childrenContainer.addFilter(myFilter);
ArrayList<Object> myList = new ArrayList<Object>();
myList.addAll(childrenContainer.getItemIds());
childrenContainer.removeContainerFilters("parentID");
return myList;
}
public Object getParent(Object itemId) {
return getContainerProperty(itemId, "parentID");
}
public Collection<?> rootItemIds() {
Filter myFilter = new Filter("parentID", ComparisonType.EQUALS,0);
addFilter(myFilter);
ArrayList<Object> myList = new ArrayList<Object>();
myList.addAll(getItemIds());
removeContainerFilters("parentID");
return myList;
}
public boolean setParent(Object itemId, Object newParentId)
throws UnsupportedOperationException {
return true;
}
public boolean areChildrenAllowed(Object itemId) {
if ((Integer)getItem(itemId).getItemProperty("leaf").getValue()==1) {
return false;
}
return true;
}
public boolean setChildrenAllowed(Object itemId, boolean areChildrenAllowed)
throws UnsupportedOperationException {
return true;
}
public boolean isRoot(Object itemId) {
return true;
}
public boolean hasChildren(Object itemId) {
return true;
}
}
Excellent. Also good that you posted the solution.
Another lower-complexity (NlogN) way of listing the children would have been collecting them in a HashMap<Object,List>, where the first object is the item ID of the parent, and the ones in the list the item IDs of the children.
Good idea Prophet, I’ll follow it 
Hi,
It is throwing error (cannot instantiate the type Container.Filter) in the below statement.
Filter myFilter = new Filter(“parentID”, ComparisonType.EQUALS,
(Integer) getItem(itemId).getItemProperty(“id”).getValue());
Are you using Filter from com.vaadin.data.Container.Filter or com.vaadin.addon.sqlcontainer.query ?
If it is com.vaadin.addon.sqlcontainer.query again it is throwing error (cannot convert Integer to String).
Also it is throwing error (method undefined) in the below statement.
childrenContainer.addFilter(myFilter);
I am using the jars vaadin-sqlcontainer-1.1.0.jar & vaadin-6.6.7.jar (also tried with vaadin-6.7.0.jar). Do I need to add some other jars as well?
Br,
Sankar
Hi:
How do you use aftewards the HierarchicalSQLContainer class?..I’m having some trouble with this.
Here’s is what I do in the application class:
HierarchicalSQLContainer Container;
...// more code here
try {
Container = new HierarchicalSQLContainer(q); // where q is a tablequery referenced to the connection pool
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
TreeTable tree=new TreeTable(Container); // not exactly like this, but that is the idea
layout.addComponent(tree);
My problem is that nothing appears, I don’t know why…
thank for any support you could give me
Hugo
Bonjour,
I faced the same issue, I supposed because the posted code was for an older release of vaadin.
I solved it like this :
Compare.Equal myFilter = new Compare.Equal(“parentID”, (Integer) getItem(itemId).getItemProperty(“id”).getValue());
childrenContainer.addContainerFilter(myFilter);
Gilles
Can you possibly show an example of how to use this structure.
My thoughts were to create a method like so in my data class
public HierarchicalSQLContainer GetQueryContainerTest(String query)
{
TableQuery tableQuery = new TableQuery(query, connectionPool, new OracleGenerator());
HierarchicalSQLContainer Container = null;
try {
Container = new HierarchicalSQLContainer(tableQuery);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return Container;
}
Then call this from my Tree Class using
setContainerDataSource(DB.data.GetQueryContainerTest(tableName));
It looks to me like this OracleGenerator is causing an issue.
Can you please advise?
Kind Regards,
Douglas