title: Java / MySQL - doing a select on a ResultSet
body: <p>So I've done a massive select on MySQL and got back a lot of data - which is ordered by index.<br>
  For example:</p>
  
  <pre><code>select * from nodes where config_id = 1;  
  </code></pre>
  
  <p>Gives (only relevancies shown)  </p>
  
  <pre><code>| definition_id (PK) | position | parent | 
  -------------------------------------------
     90                      1         0 &lt;&lt; "root"
     08                      2         0
     34                      3         0
     22                      4         0
     17                      1         7  &lt;&lt; another defn_id
     38                      2         7
     23                      3         7
     07                      1         90
  </code></pre>
  
  <p>If this makes no sense, imagine defining a tree, where 90, 8, 34 and 22 are children of the root.<br>
  7 is then a child of 90, and 17, 38, 23 are children of 7 (grandchildren of the root).  </p>
  
  <p>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.<br>
  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.  </p>
  
  <p>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.  </p>
  
  <p>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?  </p>
  
  <p>Eternal love and appreciation in exchange for thoughts/comments.</p>