swing / mysql / jdbc search question

[Trent Shipley]

On Wednesday 2006-01-11 16:05, keith smith wrote:
> Hi Judd,
>
>   The only jdbc/swing/mysql example I can find of a search does show a
> query that shows the user only the subset.
>
>   What I would like to do is a little more fluid.  I would like to have the
> entire recordset available for the customer and allow then to do a search
> which moves the record pointer to the first match and returns control to
> the user who can move next or previous or perform another search.
>
>   FoxPro, m$-access, vbasic/ms-sql, and vbasic/jet allow the programmer to
> write code that moves the record pointer by doing a simple find or search.
> This allows the user full access to the entire recordset.
>
>   Any input is much appreciated
>
>   Thanks,
>   Keith
>
>   >How about simplifying it by requerying the database with the added
> >
> >condition
> >of what you want to search for?

I know nothing about Java and little about MySQL.  However, assuming a simple 
read-forward static database cursor the textbook solution is exactly what has 
been proposed.

Present the user with a spreadsheet grid of the superset data.  Select a 
widget that allows limited user queries.  Generate parameterized SQL queries  
to get desired subset from a second trip to the database.  Then you would 
manually write code to position the cursor and highlight the proper rows.

If you have the proper database privileges and you either need a stable 
snapshot of the data, have a very large initial data set, or want to use the 
data repeatedly, another three-visit solution involves building a temporary 
table.  After building the temp table you would query it for your initial 
set.  You would requery the temp table to get user desired sub-sets.  Again, 
positioning the cursor and highlighting rows in SWING needs to be done after  
a manual comparison, no automagic.


If (big if) the JDBC implementation for MySQL supports dynamic cursors 
(scrollable result sets) then you can economize by moving the cursor if you 
opt to build your own search(es) in JAVA instead of requerying MySQL.  If you 
use scrollable cursors for you application adding a dummy column to the 
select 

SELECT ... ,'false' subselect FROM ...
may help.

Of course, what you really want is something like Access' "Find" feature for 
result sets. Have fun reading documentation.  My bet is that JDBC offers no 
_find_ function because it would be inferior and redundant to SQL.  


Finally, SWING's grid or table or whatever might well provide a _find_ 
function since it is common enough on spreadsheets.  This is also where you 
can write your own to work on the data in the widget rather than in the 
result set.  (Unless you are being an annoying developer who keeps their 
transactions open, you should either be using a scrolling cursor or have 
closed your forward only cursor.)  Note that that database people usually 
scratch their heads at in-spreadsheet find functions when perfectly good SQL 
platforms are at hand.