title: Populate a DefaultMutableTreeNode from database
body: <p>I am fairly new to Java and I want to create a DefaultMutableTreeNode class that contains Map objects.</p>
  
  <p>I wrote a TreeNodeMap class that inherits from a more generic TreeNode class which in turn inherits from DefaultMutableTreeNode.</p>
  
  <p>The TreeNodeMap class contains the method <em>populate</em> that takes a rather long list of parameters. I plan to improve the methods readability by converting some of those parameters into a single object and overloading the method so that I don't have to pass a set of nulls in the first call (is recursive).</p>
  
  <p>Note:<em>If you don't care for my verbose explanation of the method skip directly to the code from here</em></p>
  
  <p>Initially I create an empty node that will only contain children but no data, this is ROOT. The idea is to have a method that allows the caller to pass any query with 'field_id', 'field_label', 'parent_id' columns. On the initial call the query is passed with a Where clause where the 'parent_id is null' hence getting all nodes with no parent. All these nodes are added to the ROOT node. While iterating over the Resultset the <em>populate</em> method for each node is called passing now a 'Where clause' where parent_id = [current node id] hence getting all the children for that node. This will happen recursively until all nodes are created with a hierarchy.</p>
  
  <p><em>code</em>
  (Remember, I'm new to JAVA so any feedback is appreciated)</p>
  
  <pre><code>public void populate( boolean isRoot, String parentFieldId, String parentFieldLabel, String childFieldId, String childFieldLabel, 
              int parentId, String tableName, String whereClause, String orderByClause, String additionalColumns, List&lt;Map&lt;String, String&gt;&gt; queryParams) throws SQLException, Exception{
          ResultSet rs = null;
          Connection con = null;
          PreparedStatement ps = null;
          try{
  
              DBConnection dbConnection = new DBConnection("localhost", 3306, "root", "password", "test", DBDrivers.DBTYPE_MYSQL);
              con = dbConnection.getConnection();
  
              String treeNodeSql = "Select " + parentFieldId + ", " + parentFieldLabel + 
                                                  ", " + childFieldId + ", " + childFieldLabel;
              if(additionalColumns != null &amp;&amp; additionalColumns.trim().length() &gt; 0)
                  treeNodeSql +=  " ," + additionalColumns;
  
              treeNodeSql += " From " + tableName + " WHERE 1=1";
  
              if(whereClause != null &amp;&amp; whereClause.trim().length() &gt; 0 ){
                  treeNodeSql += " AND " + whereClause;
              }
  
              if(isRoot){
                  treeNodeSql += " AND " + parentFieldId + " is null";
              }else{
                  if(parentFieldId == null || parentFieldId.trim().length() == 0)
                      throw new Exception(" The populate() method requires a parentId when isRoot is false.");
                  treeNodeSql += " AND " + parentFieldId + " = ?";
              }
                  //order clause append
              if(orderByClause != null &amp;&amp; orderByClause.trim().length() &gt; 0)
                  treeNodeSql += " " + orderByClause;
  
                  //prepare statement
               ps = con.prepareStatement(treeNodeSql); 
              int ixParam = 0;
  
                  for(Map qParam : queryParams){
                      if(qParam.get("datatype") == "int"){
                          ps.setInt(++ixParam, Integer.parseInt((String) qParam.get("value")));
                      }else if(qParam.get("datatype") == "string"){
                          ps.setString(++ixParam, (String) qParam.get("value"));
                      }
                  }
  
              out.println(treeNodeSql);
              if(parentId &gt; 0){
                  ps.setInt(queryParams.size()+1, parentId);
              }
              rs = ps.executeQuery();
  
              while(rs.next()){
                  HashMap&lt;String, Object&gt; childNodeData = new HashMap&lt;String, Object&gt;(4);
                  childNodeData.put("parentFieldId", parentFieldId);
                  childNodeData.put("parentFieldIdValue", Integer.toString(rs.getInt(parentFieldId)));
                  childNodeData.put("parentFieldLabel", parentFieldLabel);
                  childNodeData.put("parentFieldLabelValue", rs.getString(parentFieldLabel));
                  childNodeData.put("childFieldId", childFieldId);
                  childNodeData.put("childFieldIdValue", Integer.toString(rs.getInt(childFieldId)));
                  childNodeData.put("childFieldLabel", childFieldLabel);
                  childNodeData.put("childFieldLabelValue", rs.getString(childFieldLabel));
  
                  out.println("parentId: " + rs.getInt(parentFieldId)
                                  + ", parentLabel: " + rs.getString(parentFieldLabel)
                                  + ", childId: " + rs.getInt(childFieldId)
                                  + ", childLabel: " + rs.getString(childFieldLabel));
                  TreeNodeMap childNode = new TreeNodeMap(childNodeData);
                  this.add(childNode);
                  childNode.populate(false, parentFieldId, parentFieldLabel, childFieldId, childFieldLabel, rs.getInt(childFieldId), tableName, whereClause, orderByClause, additionalColumns, queryParams);
              }
          }catch(SQLException e){
              throw e;
  
          } catch (ClassNotFoundException e) {
              // TODO Auto-generated catch block
              out.println(e.getCause());
              out.println(e.getMessage());
              e.printStackTrace();
              throw e;
          }finally {
              try { rs.close(); } catch (Exception e) {  }
              try { ps.close(); } catch (Exception e) {  }
              try { con.close(); } catch (Exception e) {  }
          }
      }   
  </code></pre>
  
  <p>The initial call to the method looks something like this:</p>
  
  <pre><code>treeNode.populate(true, "supervisor_id", "supervisor", "employee_id", "employee", 0, "vw_employee", null, null, null, queryParams);
  </code></pre>
  
  <p>The problem I see with this approach is that the DB will be queried X amount of times and with a big data set I'd think this will cause some problems. I'm also opening a connection For each query! So I thought about passing the Connection to the method once it was created (in the recursive calls), but then I'm not sure how to close it appropriately. I could write a conditional to check if the node is the ROOT, then close the connection but then what happens if the code fails in between.</p>
  
  <p>So FINALLY my questions:
  1- What's the best way to approach this?
  2- Should I pass the connection around so that only one connection remains open during the population of the tree? If Yes, then how do I close it properly.
  3- Should I cache the Resultset into an ArrayList and use that instead?</p>