Databases
StackOverflow2013
Postsdbo
POJava app representation of the database table self join
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJava app representation of the database table self join
    primarykey
    data
    text
    <p>I have a database table with the following structure:</p> <pre><code>CREATE TABLE `Professions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `profession` varchar(254) NOT NULL, `profession_type` enum('system','custom') NOT NULL, PRIMARY KEY (`id`), KEY `profession_index` (`profession`) ) ENGINE=InnoDB AUTO_INCREMENT=296 DEFAULT CHARSET=utf8 CREATE TABLE `Professions_Professions` ( `parent_id` bigint(20) unsigned NOT NULL, `child_id` bigint(20) unsigned NOT NULL, PRIMARY KEY (`parent_id`,`child_id`), UNIQUE KEY `child_id_UNIQUE` (`child_id`), KEY `parent_id_index` (`parent_id`), CONSTRAINT `fk_Professions_Professions1` FOREIGN KEY (`parent_id`) REFERENCES `Professions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_Professions_Professions2` FOREIGN KEY (`child_id`) REFERENCES `Professions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 </code></pre> <p>To get the whole structure of Professions i've wrote the following SQL:</p> <pre><code>SELECT id, profession, profession_type, parent_id FROM Professions P LEFT OUTER JOIN Professions_Professions PP ON PP.child_id = P.id; </code></pre> <p>Now, i want to represent that type of structure in the Java app (i have almost the same table structure for locations).<br> It looks reasonable for me to create some kind of a tree that doesn't have a root, but a list of elements that acts as a root. Each element will implement the 'Node' interface that will allow to get its parent and the list of childs. Since this should be general solution i would like to have a generic tree.<br> Also i would like to have a procedures to walk up and down through this tree and perform some Actions (interface(s)) (on each child and on each list of childs - 'nodal' and 'nodal list' actions) that could return some value or not and could take any number of parameters.<br> If the action will have a return value, i would like its return type to be specified by the Action implementation.<br> I have tried to implement this and ended up with the following code (there should be many kludges...):</p> <pre><code>// Removed the generic because i was unable to figure out how to make it all work ... public interface TreeNode { public void setParent(Object parent); public void setChildList(List childList); public Object getParent(); public List getChildList(); } public class Tree&lt;T extends TreeNode&gt; { private List&lt;T&gt; tree; public Tree(List&lt;T&gt; treeNodeList) { /* Ensuse that the list is not empty */ if((null != treeNodeList) &amp;&amp; (!treeNodeList.isEmpty())) { tree = new ArrayList&lt;T&gt;(); /* Link the elements and create the tree */ for(T node:treeNodeList) { /* If the current node has a parent */ if(null != node.getParent()) { /* Look the actual parent object in the initial node list */ int parentIndex = treeNodeList.indexOf(node.getParent()); if(-1 != parentIndex) { T parent = treeNodeList.get(parentIndex); /* Set the actual parent */ node.setParent(parent); /* Add node to parent children list */ if(null == parent.getChildList()) { parent.setChildList(new ArrayList&lt;T&gt;()); } parent.getChildList().add(node); } } else { /* Add element to tree root list */ tree.add(node); } } } } public List&lt;?&gt; getRootList() { return tree; } public void forEachChild(List&lt;T&gt; rootList, SimpleNodeTreeAction&lt;T&gt; action, Object... args) { if((null != rootList) &amp;&amp; (!rootList.isEmpty())) { Object[] localArgs = null; for(T node:rootList) { /* Store the local copy of args */ if(null != args) { localArgs = args.clone(); } action.doAction(node, localArgs); forEachChild(node.getChildList(), action, localArgs); } } } public void forEachChild(T rootNode, SimpleNodeTreeAction&lt;T&gt; action, Object... args) { if(null != rootNode) { forEachChild(rootNode.getChildList(), action, args); } } public void forEachChild(SimpleNodeTreeAction&lt;T&gt; action, Object... args) { forEachChild(tree, action, args); } public Object forEachChild(List&lt;T&gt; rootList, NodeTreeAction&lt;T&gt; action, Object... args) { Object result = null; if((null != rootList) &amp;&amp; (!rootList.isEmpty())) { Object[] localArgs = null; for(T node:rootList) { /* Store the local copy of args */ if(null != args) { localArgs = args.clone(); } result = action.doAction(node, localArgs); if(null == result) { result = forEachChild(node.getChildList(), action, localArgs); if(null != result) { break; } } else { break; } } } return result; } public Object forEachChild(T rootNode, NodeTreeAction&lt;T&gt; action, Object... args) { Object result = null; if(null != rootNode) { result = forEachChild(rootNode.getChildList(), action, args); } return result; } public Object forEachChild(NodeTreeAction&lt;T&gt; action, Object... args) { return forEachChild(tree, action, args); } public void forEachChildList(List&lt;T&gt; rootList, SimpleNodeListTreeAction&lt;T&gt; action, Object... args) { if((null != rootList) &amp;&amp; (!rootList.isEmpty())) { action.doAction(rootList, args); for(T node:rootList) { forEachChildList(node.getChildList(), action, args); } } } public void forEachChildList(T rootNode, SimpleNodeListTreeAction&lt;T&gt; action, Object... args) { if(null != rootNode) { forEachChildList(rootNode.getChildList(), action, args); } } public void forEachChildList(SimpleNodeListTreeAction&lt;T&gt; action, Object... args) { forEachChildList(tree, action, args); } public Object forEachChildList(List&lt;T&gt; rootList, NodeListTreeAction&lt;T&gt; action, Object... args) { Object result = null; if((null != rootList) &amp;&amp; (!rootList.isEmpty())) { result = action.doAction(rootList, args); if(null == result) { for(T node:rootList) { result = action.doAction(node.getChildList(), args); if(null != result) { break; } } } } return result; } public Object forEachChildList(T rootNode, NodeListTreeAction&lt;T&gt; action, Object... args) { Object result = null; if(null != rootNode) { result = forEachChildList(rootNode.getChildList(), action, args); } return result; } public Object forEachChildList(NodeListTreeAction&lt;T&gt; action, Object... args) { return forEachChildList(tree, action, args); } public void forEachParent(T rootNode, SimpleNodeTreeAction&lt;T&gt; action, Object... args) { if(null != rootNode) { T parent = (T) rootNode.getParent(); while(null != parent) { action.doAction(parent, args); parent = (T) parent.getParent(); } } } public Object forEachParent(T rootNode, NodeTreeAction&lt;T&gt; action, Object... args) { Object result = null; if(null != rootNode) { T parent = (T) rootNode.getParent(); while(null != parent) { result = action.doAction(parent, args); if(null == result) { parent = (T) parent.getParent(); } else { break; } } } return result; } public Object search(final T searchObject) { return forEachChild( new NodeTreeAction&lt;T&gt;() { @Override public Object doAction(T node, Object... args) { if(node.equals(searchObject)) { return node; } else { return null; } } }, searchObject); } public void sort(final Comparator comparator) { forEachChildList( new SimpleNodeListTreeAction&lt;T&gt;() { @Override public void doAction(List node, Object... args) { Collections.sort(node, comparator); } }, (Object) null); } public void sort() { forEachChildList( new SimpleNodeListTreeAction&lt;T&gt;() { @Override public void doAction(List node, Object... args) { Collections.sort(node); } }, (Object) null); } } public interface NodeTreeAction&lt;T extends TreeNode&gt; { public Object doAction(T node, Object... args); } public interface SimpleNodeTreeAction&lt;T extends TreeNode&gt; { public void doAction(T node, Object... args); } public interface NodeListTreeAction&lt;T extends TreeNode&gt; { public Object doAction(List&lt;T&gt; node, Object... args); } public interface SimpleNodeListTreeAction&lt;T extends TreeNode&gt; { public void doAction(List&lt;T&gt; node, Object... args); } </code></pre> <p>Can anyone point me on how i could make this (let's say not perfect) solution become better (for example return the actual type object from the methods, not the Objects as it is right now). I would also very appreciate if anyone will suggest a better solution to this problem.</p> <p>Thanks in advance...</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USIvan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COUse an existing & tested tree implementation instead of redoing one yourself? For example, there is a good tree implementation in the Swing package. Although mostly used in conjunction with a GUI component, it can be used on a stand alone basis and contains no strictly GUI-related code.
      singulars
      1. POJava app representation of the database table self join
      1. USassylias
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload