Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC Help figuring it out

 
Ryan Arment
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need some help with JDBC. I am just starting out learning SQL and JDBC and I have having some trouble putting it all together. I have 2 tables, Student and Enrollment and I need to update them like it is the end of the semester. Add credit hours, update GPA and classification. But I am not sure how to make it work. Any help would be greatly appreciated!! I can get a resultset and print out the contents, but I'm not sure how to do a join and then modify the data within specifications (i.e. 30-45 credit hours is a sophmore, etc...) Here are the tables that I have to work with.

 
Santhosh ayiappan
Ranch Hand
Posts: 80
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can use the default join, (Inner join) to extract the data.

Have a look at the below link.

Joins


Regards
Santhosh
 
Ryan Arment
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Something like this?? It's giving me a syntax error on the ON part...

 
Ryan Arment
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Nevermind, I found my mistake in there. But my real question is how do I do my calculations and then update the DB. Is that all done inside the while loop?

 
Campbell Ritchie
Sheriff
Pie
Posts: 49789
69
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What was the error? Was it that there was no identifier after the as keyword?
 
Ryan Arment
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I had an as after the second table name, right before the ON statement. Now I need to figure out how to do the calculations.

I'm thinking about just going through the list for each thing I need to change and update it inside a loop.



And then going through it again with if statements to update the classification, etc...

I know this probably is not the best way to do it, but I'm not sure how else to do it...
 
Ryan Arment
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So this is what I came up with, and it does not work. Apparently when you are updating a table, you can't be using more than one table so my join breaks it. Any ideas on how to fix it??

 
Jan Cumps
Bartender
Posts: 2599
12
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ryan Arment wrote:Apparently when you are updating a table, you can't be using more than one table so my join breaks it. Any ideas on how to fix it??


Write a separate update statement to do the changes in the database. You can't update because the rows of your query do not represent a single row in the database. Your query is not updatable.
 
Ryan Arment
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok, I got it all fixed up but I have one small problem left. When I execute it, it updates every tuple to the values of the last tuple. I think it has something to do with my executeUpdate() placement, but I'm not sure where I need to move it to to make it work....

 
Jan Cumps
Bartender
Posts: 2599
12
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your update statement needs a where clause, so that only the correct record is updated. You are updating every record in the table, every time you go throught the loop.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic