File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes JDBC Help figuring it out Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Help figuring it out" Watch "JDBC Help figuring it out" New topic
Author

JDBC Help figuring it out

Ryan Arment
Greenhorn

Joined: Oct 03, 2011
Posts: 7
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

Joined: Jan 30, 2007
Posts: 80

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

Joined: Oct 03, 2011
Posts: 7
Something like this?? It's giving me a syntax error on the ON part...

Ryan Arment
Greenhorn

Joined: Oct 03, 2011
Posts: 7
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

Joined: Oct 13, 2005
Posts: 37953
    
  22
What was the error? Was it that there was no identifier after the as keyword?
Ryan Arment
Greenhorn

Joined: Oct 03, 2011
Posts: 7
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

Joined: Oct 03, 2011
Posts: 7
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

Joined: Dec 20, 2006
Posts: 2491
    
    8

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.


OCUP UML fundamental and ITIL foundation
youtube channel
Ryan Arment
Greenhorn

Joined: Oct 03, 2011
Posts: 7
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

Joined: Dec 20, 2006
Posts: 2491
    
    8

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.
 
subject: JDBC Help figuring it out
 
Similar Threads
CRUD operations in Hibernate on 2 tables
Many-to-Many Relation
HQL query required with left outer join
JPA: Map composite primary key with one field being foreign key
Problems with .sql(script files) and .properties files. [Using Netbeans]