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
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: 36513
    
  16
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: 2477
    
    7

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: 2477
    
    7

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