Populate a DefaultMutableTreeNode from database

Question

I am fairly new to Java and I want to create a DefaultMutableTreeNode class that contains Map objects.

I wrote a TreeNodeMap class that inherits from a more generic TreeNode class which in turn inherits from DefaultMutableTreeNode.

The TreeNodeMap class contains the method populate 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).

Note:If you don't care for my verbose explanation of the method skip directly to the code from here

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 populate 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.

code (Remember, I'm new to JAVA so any feedback is appreciated)

public void populate( boolean isRoot, String parentFieldId, String parentFieldLabel, String childFieldId, String childFieldLabel, 
            int parentId, String tableName, String whereClause, String orderByClause, String additionalColumns, List<Map<String, String>> 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 && additionalColumns.trim().length() > 0)
                treeNodeSql +=  " ," + additionalColumns;

            treeNodeSql += " From " + tableName + " WHERE 1=1";

            if(whereClause != null && whereClause.trim().length() > 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 && orderByClause.trim().length() > 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 > 0){
                ps.setInt(queryParams.size()+1, parentId);
            }
            rs = ps.executeQuery();

            while(rs.next()){
                HashMap<String, Object> childNodeData = new HashMap<String, Object>(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) {  }
        }
    }   

The initial call to the method looks something like this:

treeNode.populate(true, "supervisor_id", "supervisor", "employee_id", "employee", 0, "vw_employee", null, null, null, queryParams);

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.

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?

Answer 1

Your performance concern may be warranted. Instead,

1. Implement TreeModel, as shown in this FileTreeModel. In this way, only visible nodes need be queried. There's a related example here.

2. Pass javax.sql.DataSource, rather than Connection.

3. Your class that implements TreeModel can encapsulate any cached data, but also provide some means to update stale entries. After displaying cached values, consider using SwingWorker to refresh the model, as shown here. Instead of List<Record>, you might want to look at Map<PrimaryKey, Record>.