This week's book giveaway is in the OCAJP 8 forum. We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line! See this thread for details.
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?
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.
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.)
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.