swing / mysql / jdbc search question

Thu Jan 12 08:07:50 MST 2006

I have used Mysql before with Java, and with PHP, but have never tried the
options you listed here for Mysql. As I was doing just basic updating and
data calling. So I went to the website for the JDBC driver:
http://dev.mysql.com/doc/refman/5.0/en/cj-jdbc-reference.html

What I find interesting is that there is mention of implementation of these.
Although if they are following the full JDBC API they should be implemented.
But since Mysql does not actually support cursors, I would have to wonder if
the resultset can be maintianed (
http://dev.mysql.com/doc/refman/5.0/en/cj-faq.html <-- very last entry). If
anyone has any other resources on the mysql jdbc implementation I would love
to read it (I always love learning new things). But based on my limited
knowledge it would be my opinion that the only way to know if the data has
been updated is to either stream it (
http://dev.mysql.com/doc/refman/5.0/en/cj-implementation-notes.html <--
under ResultSet) which would give you the chance to see updated info as the
data is being queried or just do a requery every X number of seconds.

But if I may go back to the original point, this would still lead me to
believe it would be much easier coding wise, and management wise, to use a
query to do your search rather than the resultset itself. If you are using
MySQL, speed is certianly not an issue as your query can be pulled typically
in half a seconds time (including populating the view in Swing). That is
MySQLs strong point is it's query time. Although it's downfall is it's
simplified query structure (which thankfully has been being addressed).

I hope this helps.

Sincerely,
Judd Pickell

On 1/12/06, keith smith <klsmith2020 at yahoo.com> wrote:
>
> 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 at 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 at lists.plug.phoenix.az.us
> > To subscribe, unsubscribe, or to change you mail settings:
> > http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
> >
>
> ------------------------------
> Yahoo! Photos – Showcase holiday pictures in hardcover
> Photo Books<http://us.rd.yahoo.com/mail_us/taglines/photobooks/*http://pa.yahoo.com/*http://us.rd.yahoo.com/mail_us/taglines/photos/evt=38088/*http://pg.photos.yahoo.com/ph//page?.file=photobook_splash.html>.
> You design it and we'll bind it!
>
>
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss at lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change  you mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.plug.phoenix.az.us/pipermail/plug-discuss/attachments/20060112/7fcba858/attachment-0001.htm