swing / mysql / jdbc search question

Thu Jan 12 17:53:01 MST 2006

Keith,

From my limited access to Java documentation, if you use a scrollable, 
updatable recordset/cursor then you need to leave the record set OPEN to work 
with the cursor.  Leaving a scrollable recordset open will not do anything 
terrible to the database.   

With a scrollable record JDBC is hiding transaction management from you.  This 
involves some overhead and expense (but in the real world is probably always 
worthwhile if it saves enough programmer time).  Nevertheless, some manuals 
advise avoiding scrollable recordsets or cursors because they involve so many 
hidden trips to the database.  Scrollable recordsets can be a particular 
problem if bandwidth from the database to the application is limited OR if 
the database is already working near capacity.  Still, the general rule is 
code fast, optimize later.

But for what you describe, I don't think a scrollable recordset gets you much.  
You can't treat the recordset like a MySQL table that can be arbitrarily 
searched.  

The other issue raised by Judd is whether your MySQL JDBC supports scrolling, 
updatable recordsets.  It is common NOT to support scrolling.  Scrolling is 
magic done by the JDBC layer and not the database.  So if you really want 
scrolling we need to know what MySQL-JDBC tool you are using (there is likely 
more than one out there).

On Thursday 2006-01-12 08:07, Judd Pickell wrote:
> 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