Thanks Joseph and Trent,

 

I was hoping for a find function,which would make things much easier.  I have used a grid in the past (in FoxPro) which allows the user to scroll the table to the desired record.  I think this may be the way to go for a small recordset (500 or less records).

 

You say ...closing the recordset as quickly as possible....   I am using ResultSet.TYPE_SCROLL_SENSITIVE and ResultSet.CONCUR_UPDATABLE

It is my understanding to use the CONCUR_UPDATABLE I need to keep the connection open (this is a desktop application) so any updates that others make will be viewable or available within the resultset. Is this true?

 

Thanks for your help. You have given me much food for thought.

Keith

 

 

----------------------------------------------------------------------

Message: 1
Date: Wed, 11 Jan 2006 21:17:55 -0700
From: Joseph Sinclair
Subject: Re: swing / mysql / jdbc search question
To: Main PLUG discussion list
Message-ID: <43C5D873.2010907@stcaz.net>
Content-Type: text/plain; charset=UTF-8

To follow up on Trent's points:
JDBC does not provide any find within a recordset, you have to scroll through it and match the field yourself.
Alternatively you can read the recordset into a Map with the search field in the key and use the find method on Map.
The grid objects in Swing do not provide any sort of search function either, since the contents of each field are user-defined Objects, and a search would be quite difficult to do generically.

Trent is also quite correct in that you should be reading the recordset data into a Java Collection and closing the recordset as quickly as possible. You can always open an update transaction for record changes and requery when the user needs to refresh the data.

Trent Shipley wrote:
> 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.
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change you mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>