• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

incrementing numbers in database when a new object is added

 
Heather Dennison
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 64959
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So what's your plan? How are you going to go about it? What part are you having trouble planning?
 
Heather Dennison
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 21116
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 64959
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 180
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)
 
Heather Dennison
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic