Java / MySQL - doing a select on a ResultSet

So I've done a massive select on MySQL and got back a lot of data - which is ordered by index.

For example:

select * from nodes where config_id = 1;  

Gives (only relevancies shown)

| definition_id (PK) | position | parent | 
-------------------------------------------
   90                      1         0 << "root"
   08                      2         0
   34                      3         0
   22                      4         0
   17                      1         7  << another defn_id
   38                      2         7
   23                      3         7
   07                      1         90

If this makes no sense, imagine defining a tree, where 90, 8, 34 and 22 are children of the root.

7 is then a child of 90, and 17, 38, 23 are chi开发者_StackOverflow中文版ldren of 7 (grandchildren of the root).

In order to process this, we find all the nodes with the parent 0, add them in, then look at all those nodes, see if they have any children (by grabbing their from parent, or seeing no values and treating it as a leaf). If they have children, add them on, and continue recursively until the tree is built.

This isn't the most efficient data store, but one of the requirements is "a single line change" to move a sub-set of nodes and all children. I'd have rather defined a string 0.1.6 etc, but thats the way things are.

So this works fairly well in testing, but when we need to do this 100,000 times a day (without caching - no point, all slightly different) - we need to do this with as few hits to the database as possible (i.e. ONE). Easy, you say, just grab the whole lot and process. But, they're not all order - as you can see above, 90 is below 7. Yes, that examples connived, but it illustrates the problem that we need some sort of order.

Basically, in a nutshell, the question is, is their an easy (and preferably cheap) way of doing a sub-select on the ResultSet - i.e. grab all results where parent == 7 into another ResultSet and work on that?

Eternal love and appreciation in exchange for thoughts/comments.

---

I'm not aware of a way to do a sub-select on a ResultSet. If you want to keep to one query you could manage it all in memory with something like this:

// class to represent your rows
class Row {
    int definitionId;
    int position;
    int parent;
}

// map of parents to list of their rows
Map<Integer, List<Row>> parentMap = new HashMap<Integer, List<Row>>();

// iterate over all results, build row objects and populate the map
while (rs.next()) {
    Row row = new Row();
    row.definitionId = rs.getInt("definition_id");
    row.position = rs.getInt("position");
    row.parent = rs.getInt("parent");

    // find the list of rows for the parent, create it if it doesn't exist
    List<Row> rows = parentMap.get(row.parent);
    if (rows == null) {
        rows = new ArrayList<Row>();
        parentMap.put(row.parent, rows);
    }

    // add row to the list for its parent
    rows.add(row);
}

// find all rows for parent == 7
for (Row row : parentMap.get(7)) {
    // process row
}

---

We do something very similar at my job. We have the advantage that we are using oracle, so the "start with...connect by" syntax is a blessing. However, we only use that to load into an in memory data structure which supports tree type functions like get_children, get siblings, get parents, etc.

I would look more toward using the db to load some object that supports those types of functions. I'm not good with java so i'm not sure of the implementation, but hopefully this gets you started.

---

You can do it on DBMS level, look here for example on MySQL http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/.

WhiteFang34's answer is good, because mySQL doesn't support hierarchical queries (you can find it in ORACLE: start ... connect by). May be it will be implemented in future, but now we can use only workarounds (such as on link). Basic idea is to use memory on machine where DBMS runs and where recursive query implemented by vendor (with optimization, strong algorithms, support, etc.).

Additional workaround is writing stored procedure.

---

You could do your first select into a temporary table

http://dev.mysql.com/doc/refman/5.1/en/create-table.html

and then do your secondary selects on the temporary table. Temporary tables are "per-connection" so this may or may not work for you.