aspose file tools*
The moose likes JDBC and the fly likes incrementing numbers in database when a new object is added Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "incrementing numbers in database when a new object is added" Watch "incrementing numbers in database when a new object is added" New topic
Author

incrementing numbers in database when a new object is added

Heather Dennison
Greenhorn

Joined: Jan 27, 2011
Posts: 16
1) find what the current maximum record number is.
2) Add 1 to it and store in a variable.
3) write new record using the variable
4) query table using the variable

I am trying to update data in a database.
As a student is added to the database, the student number is given 1 unit above the old student number. Can you help me with the java code to add a student that is given the student number?
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60991
    
  65

So what's your plan? How are you going to go about it? What part are you having trouble planning?


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Heather Dennison
Greenhorn

Joined: Jan 27, 2011
Posts: 16
I am having trouble with the code... I keep setting the number instead of incrementing it..
I am a beginner and this is confusing to me.. I know what I want it to do.. but it's not doing it. Here is the question:

Add a new student to the student table with a student number (stno) one number above the current highest stno. So if the highest student number is 200 then the next student would be 201. The code must work for any number of student records, in other words DO NOT hard code the student number in your code.
Display via query the record just created via the previous step.


Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Let me first mention that you wouldn't do it that way in the real world. So do you really have to do it that way? Let's suppose you do. Then you have some of the right code there, only you seem to be doing the insert first, and finding the maximum student number second. Which doesn't match your four steps from your original post.

Your code to find the maximum number looks okay to me. Put that first. Then after that, do the database insert using that maximum number. The code you have there for inserting isn't that helpful, because everything is hard-coded and now you have a variable to go in there. I would use a PreparedStatement to insert that variable, and also all of the other columns, rather than what you have there. That's going to be a bit of a change, but it will make things much easier.

(As for how you would do it in the real world: you would define the student number in the database to be an auto-increment column, and let the database figure out the next number.)
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60991
    
  65

Paul Clapham wrote:(As for how you would do it in the real world: you would define the student number in the database to be an auto-increment column, and let the database figure out the next number.)

Or, depending upon the database, use a sequence.
ntumba lobo
Ranch Hand

Joined: Oct 21, 2008
Posts: 180
if you have DB2 use identy,
if you have mysql use a column with AUTO INCREMENT constraint
etc ...

whatever your database there is always a mechanism to manage the primary key for you transparently.
It is easier to make use it (less portable though)


SCJP 5 , SCWCD 5, SCEA 5
Heather Dennison
Greenhorn

Joined: Jan 27, 2011
Posts: 16
For a beginner in Java.. the responses did not make much sense to me. But after researching and trial and error.. I got it to work-->FINALLY! Thanks for your help. Here is the final code that I used.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1649
    
  14

Hi Heather,

Glad you got your code to work, but like the others said, that is definitely not the way to do this kind of thing in the real world. Your approach has various problems e.g. what if somebody else uses the same code to create a record after you fetched the latest number and before you insert your new record? You would both get the same max value, so you would both try to create a new record with the same new value for the student number i.e. you get duplicates.

Depending on your database, there is usually some kind of "autoincrement"(MySQL, PostgreSQL) or "sequence" (Oracle) functionality available that will automatically generate a value for a numeric ID like this and guarantee that the value is unique. Also, object-relational mapping (ORM) tools for Java, such as Hibernate, can usually handle this kind of thing for you and will know which option to choose based on your database. So wherever possible, you should make sure you use the available tools to generate unique values for numeric IDs that will be used as primary keys in your database. Not only is it safer, more robust and more maintainable, but it's also a lot easier to implement.

I know you said you're just starting out with Java, but from what I've seen this kind of error is actually quite common, even with professional Java developers who often don't know much about databases. So you might find it's better to learn the right way to do things from the start, rather than hit problems when you have to do things for real later on. Good luck with your Java, anyway.

No more Blub for me, thank you, Vicar.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: incrementing numbers in database when a new object is added