Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC Help figuring it out

 
Ryan Arment
Greenhorn
Posts: 7
  • 0
  • 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
  • 0
  • 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
  • 0
  • 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
  • 0
  • 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: 47243
52
  • 0
  • 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
  • 0
  • 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
  • 0
  • 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: 2577
11
C++ Linux Netbeans IDE
  • 0
  • 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
  • 0
  • 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: 2577
11
C++ Linux Netbeans IDE
  • 0
  • 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.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic