Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

incrementing numbers in database when a new object is added

 
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?
 
Marshal
Posts: 67451
173
Mac Mac OS X IntelliJ IDE jQuery Java
  • 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.


 
Marshal
Posts: 25815
69
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
Marshal
Posts: 67451
173
Mac Mac OS X IntelliJ IDE jQuery Java
  • 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.
 
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.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • 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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    Bookmark Topic Watch Topic
  • New Topic