Results 1 to 6 of 6
  1. #1
    mellymel is offline Member
    Join Date
    Sep 2010
    Posts
    3
    Rep Power
    0

    Default Is there a better way to compare and search for DB records

    Hey All,

    I wrote this application at my job that update records in a database. I store my db records in an Arraylist and the xml records with the update information are stored in a map. I just iterate through my dbrecords and compare the primary key field to the ones in my map.
    ex:

    (for all my db records x )
    foundRec = xmlMap.get(x.key)
    if (foundRec != null )
    do some update etc...


    This works fine, and seems to be pretty fast. Is there a better way to do this? I'm always open to new ways of searching or comparing data. Or maybe someone has done something similar in the past and used a totally different and more efficient way.

  2. #2
    travishein's Avatar
    travishein is offline Senior Member
    Join Date
    Sep 2009
    Location
    Canada
    Posts
    684
    Rep Power
    5

    Default

    I guess if there was ever a large number of records, such as millions, or how ever many that would exceed the available memory on a given system, fetching the results into a map before processing them might not work right.

    In this case, create a worker function to handle the result of a single row, which takes a JDBC ResultSet object (or perhaps a mapped bean if you unload the data from a single into a structure) and then have this method do the processing for the single row. This keeps from needing to have the pre-populated map of the entire rows found in the database.

    There are frameworks that facilitate this too, for example, the MyBatis project (formerly Ibatis) contains an API to execute a query thru a ResultHandler. Getting into using their framework takes a bit of learning, though using it with springframework is helpful too. It provides a nice way to separate the SQL query from the part that invokes it, from the part that handles each row.

  3. #3
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Just so I understand this, you have a bunch of updates in xml format?
    But not all these updates are valid (ie they reference ids that may not exist in the database)?
    How many invalid ones do you tend to get compared to valid ones?
    Are you batching the updates?

  4. #4
    mellymel is offline Member
    Join Date
    Sep 2010
    Posts
    3
    Rep Power
    0

    Default

    Yes Tolls, I'm basically updating specific record in my database, based on the one in my update xml file. So only matching records will be updated, no batching because the xml file ID have to be padded first to match my database ID, it's a big mess lol. Working with different vendors, everyone has their own format.

    For example I have to do this before inserting into my map:

    (for each xml db record to update x)
    {
    padd(x); // modify the id so that it matches the way we store it
    xmlMap.put(x.key,x);
    }
    Only then do my records match and I can find which one to update.
    Also I forgot to mention my update steps is basically only generating sql update statements. Due to permissions, we cannot directly update our DBs.
    so I'm not updating on the fly or anything like that.

    But I see what travishein is saying, I will look into that framework or a worker function because in the long run the way I'm implementing it wont work for large set of data. Thanks for the input, that's kind of what I was looking for :)

    Thanks,
    Mel

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default

    Oh...blimey.
    So the suggestion I was going to make of simply writing your updates and batching them, completely ignoring the whole map-of-db thing, won't work.

    To be honest I still don;t actually understand why you need the Map? Surely an "UPDATE some_table SET something WHERE id = non-existent-id" will simply not update anything, so isn't a problem? I don't get the "filtering out non-existent ids" part.

  6. #6
    mellymel is offline Member
    Join Date
    Sep 2010
    Posts
    3
    Rep Power
    0

    Default

    My xml file contain other type of records sorry should have mentioned that too lol, I told you it's a huge mess. So I have 10k+ records in a xml file, type A and type B and type C. I only want to Type B and the Type B in our DB has keys of 7 characters while in the file it has 6 ( hence the padding). Then they there's a tag element I have to check ( is it type B? then grab it, transform it and then stick it into my map. I could have used a list but then searching is slow unless I sort etc.. So I figure if I stick the records into a map and just compare the keys, would be faster. I'm mapping the records to update in the xml, not my actual records in the DB.

    I store my database records in a List , and the xml update records in a map. Then I just iterate for each db records is there an update ?
    for ( all my db records...)
    dbCandidate = dbrecords.get(i); //ArrayList
    xmlCandidate = xmlrecords.get(dbCandidate.getPartnerLocationCode( )); //map
    if ( xmlCandidate != null )
    //generate update sql for that db records with values from xmlCandidate
    }
    Then I create the sql update like you showed up there. if there is no update then I do nothing which is fine. But if the records are large then it's like what travishein said. Create some type of bean and process each single row instead loading all into a map or list etc.. even though I'm not directly updating it would still be the same issue.

Similar Threads

  1. Throttling records
    By jitman in forum New To Java
    Replies: 0
    Last Post: 03-11-2010, 06:18 PM
  2. Binary search tree search method
    By chopo1980 in forum New To Java
    Replies: 2
    Last Post: 12-10-2009, 01:42 AM
  3. Replies: 0
    Last Post: 10-29-2009, 09:28 AM
  4. How to delete the records
    By kiran kumar in forum Java Servlet
    Replies: 6
    Last Post: 11-09-2008, 01:16 PM
  5. Replies: 5
    Last Post: 08-26-2008, 03:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •