Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2010
    Posts
    12
    Rep Power
    0

    Default Need to Renumber an Attribute

    So I have a program that uses a DB to store information about various A/V equipment on campus. Each record houses the name, model, location, etc about the item, as well as an arbitrary ID number. The ID number is used by the program to allow it to step through each record, and populate things like combo-boxes and a display that allows me to input data for each item in the database. All of this works lovely. The problem arises when I use the program to delete a record, and the ID numbers are no longer 0,1,2, ... n. Say I delete whichever record is number 2. Then I'm stuck with 0, 1, 3, etc; I can't have numbers being skipped, so this breaks my implementations.

    To me at least, the easy fix is to renumber the ID after a record is removed, so that once again I have a situation where it is 0,1,2, ... n with no numbers being skipped. The problem I'm facing is how that is accomplished in Java. I was under the impression that I could run a query, get a result set, make my change to the result set, and then update the result set so that it would change the source too. Nothing that I've tried, however, works. I either get null pointers or error in row. I did have minor success one time, where it renumbered every ID to the same thing. Unfortunately I can't remember what I did, because I could have tweaked that to make it work. Anyone have any thoughts, or could possibly point out a different approach? This is using an Access database, by the way.

  2. #2
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,746
    Rep Power
    19

    Default

    Quote Originally Posted by crazysniper308 View Post
    So I have a program that uses a DB to store information about various A/V equipment on campus. Each record houses the name, model, location, etc about the item, as well as an arbitrary ID number. The ID number is used by the program to allow it to step through each record, and populate things like combo-boxes and a display that allows me to input data for each item in the database. All of this works lovely. The problem arises when I use the program to delete a record, and the ID numbers are no longer 0,1,2, ... n. Say I delete whichever record is number 2. Then I'm stuck with 0, 1, 3, etc; I can't have numbers being skipped, so this breaks my implementations.
    Why?
    Why do they have to be consecutive?
    That there seems to be a bug in your system.

    I can think of no system that I've worked on that requires the IDs to be consecutive. The only ones I've heard of are accounting ones to deal with some odd Italian law around invoice numbers, and that involved creating dummy (void) invoices to fill the gaps.

  3. #3
    Join Date
    Mar 2010
    Posts
    12
    Rep Power
    0

    Default

    I realize it's probably not the best way to do it, however it's just so easy to use a for loop in this circumstance. That's what I'm doing -- using the incremental number in a for loop to be passed to my query as the ID number. From there, it gets the record, and puts its data into the fields of the GUI. When I don't have a nice set of consecutive numbers, that doesn't quite work.

    I could rework how it populates fields in the GUI, but it seems like that would take a bit of time and require more code; hence why making it renumber itself is the simplest solution that I could think of. It seemed like a good idea. Is there a better way to do that sort of thing?

  4. #4
    cultclassic is offline Member
    Join Date
    Mar 2011
    Posts
    64
    Rep Power
    0

    Default

    Renumbering the IDs on a database is extremely bad idea. Relational databases go to great lengths to preserve the integrity of the data, and the they depend on the unique id's to achieve that. In your case it looks like you have a simple table, but in the future you may have other tables, whose data are referenced through the ID's of this table. Even if you have a simple table, it is important to learn the right way of doing these kind of things.

    .....I was under the impression that I could run a query, get a result set, make my change to the result set,....
    It sounds like you are using a SELECT statement. Can't you just step through the result set, ignoring the Id's?

  5. #5
    Join Date
    Mar 2010
    Posts
    12
    Rep Power
    0

    Default

    Quote Originally Posted by cultclassic View Post
    Renumbering the IDs on a database is extremely bad idea. Relational databases go to great lengths to preserve the integrity of the data, and the they depend on the unique id's to achieve that. In your case it looks like you have a simple table, but in the future you may have other tables, whose data are referenced through the ID's of this table. Even if you have a simple table, it is important to learn the right way of doing these kind of things.
    Yes, I am aware that renumbering a unique ID would be bad. However the ID's are not actually unique identifiers for the records. The name is, when I actually have a need for it. If it ever does grow to incorporate multiple tables, I definitely won't be relying on an arbitrary surrogate key. I do appreciate the insight though ;)

    It sounds like you are using a SELECT statement. Can't you just step through the result set, ignoring the Id's?
    That is correct, and is what I had tried to do. I reviewed a few examples, and it just never quite worked out. Code like this:

    ResultSet (Java 2 Platform SE v1.4.2)
    Java Examples - How do I make updates in Updatable ResultSet?

    I'm guessing I just made a dumb oversight though, because looking at those pieces of code it would seem what I did should have worked.

  6. #6
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,746
    Rep Power
    19

    Default

    Quote Originally Posted by crazysniper308 View Post
    I realize it's probably not the best way to do it, however it's just so easy to use a for loop in this circumstance. That's what I'm doing -- using the incremental number in a for loop to be passed to my query as the ID number. From there, it gets the record, and puts its data into the fields of the GUI. When I don't have a nice set of consecutive numbers, that doesn't quite work.
    Why not simply get all the data in one go?
    That's the way it's usually done, then populate your GUI while looping over the resultset (or better, a List of some class that represents that data).

  7. #7
    Join Date
    Mar 2010
    Posts
    12
    Rep Power
    0

    Default

    So the better way would be to start the program, grab all necessary data from the database, and then populate things when necessary using the result set? I'll give that a shot; I think it might clean up the code a bit too.

  8. #8
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    11,746
    Rep Power
    19

    Default

    No.
    Grab the data when you need to display it.

    You do not hold onto a result set in any case. You use it to populate something (usually a List of some object class that is your model), and it's that something that you use in the rest of your system.

    Java Code:
    rs = myStatement.executeQuery();
    while (rs.next()) {
        MyClass data = new MyClass();
        data.setSomething(rs.getXXXX(1));  // repeat for all the data
        myList.add(data);
    }
    ... tidy up and close things
    return myList;
    That myList is then handed off to whatever is displaying it, or to whatever is going to process it.

  9. #9
    Join Date
    Mar 2010
    Posts
    12
    Rep Power
    0

    Default

    Gotcha, that makes sense. This little project was started to give me some extra practice, so I didn't have a real sense of how things should be done. Appreciate it! :cool:

  10. #10
    Join Date
    Mar 2010
    Posts
    12
    Rep Power
    0

    Default

    Just wanted to follow up and say I have it working as you suggested and it works great. Thanks again!

Similar Threads

  1. undefine attribute name in this TLD
    By tushar_java in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 09-03-2010, 03:19 PM
  2. Undefined attribute name
    By Dieter in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 05-03-2010, 10:44 AM
  3. how to read an attribute of an xml tag in jsp.
    By himacherla in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 07-21-2009, 06:27 AM
  4. how to add an attribute with sax?
    By cecily in forum New To Java
    Replies: 3
    Last Post: 07-19-2007, 04:09 AM
  5. Problem with Attribute in JSP
    By Albert in forum JavaServer Pages (JSP) and JSTL
    Replies: 1
    Last Post: 07-13-2007, 03:11 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
  •