JDBC Help figuring it out

[Ryan Arment]

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.

create table Student (
StudentID char (9) not null references ID,
Classification char (10),
GPA double,
MentorID char (9) references InstructorID,
CreditHours int,
primary key(StudentID));

create table Enrollment (
CourseCode char (6) NOT NULL,
SectionNo int NOT NULL,
StudentID char (9) NOT NULL references Student,
Grade char (4) NOT NULL,
primary key (CourseCode, StudentID),
foreign key (CourseCode, SectionNo) references Offering);

[Santhosh ayiappan]

You can use the default join, (Inner join) to extract the data.

Have a look at the below link.

Joins


Regards
Santhosh

[Ryan Arment]

Something like this?? It's giving me a syntax error on the ON part...

ResultSet rs1 = stmt2.executeQuery("SELECT Student.StudentID, Enrollment.CourseCode, Enrollment.Grade FROM Enrollment as e INNER JOIN Student as ON Enrollment.StudentID=Student.StudentID ");

[Ryan Arment]

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?

int sID;
			String cCode;
			String sGrade; 
			while (rs1.next()) {
				sID = rs1.getInt("StudentID");
				cCode = rs1.getString("CourseCode");
				sGrade = rs1.getString("Grade");
				// Do the calculations and update here??  Or where??
			}

[Campbell Ritchie]

What was the error? Was it that there was no identifier after the as keyword?

[Ryan Arment]

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.

 
while (rs1.next()) {
sCreditHours = sCreditHours + 3;

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]

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??

Statement stmt1 = conn1
					.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_UPDATABLE);
			ResultSet rs1 = stmt1
					.executeQuery("SELECT Classification, GPA, CreditHours, Grade FROM Enrollment INNER JOIN Student ON Enrollment.StudentID=Student.StudentID");
			double sGPA = 0;
			String sGrade;
			String sClassification;
			int sCreditHours = 0;
			double gradeDecimal = 0;
			while (rs1.next()) {
				sGrade = rs1.getString("Grade");
				sGPA = rs1.getDouble("GPA");
				sClassification = rs1.getString("Classification");
				sCreditHours = rs1.getInt("CreditHours");

				sCreditHours = sCreditHours + 3;
				rs1.updateInt("CreditHours", sCreditHours);

				if (sCreditHours <= 29) {
					sClassification = "Freshman";
				} else if (sCreditHours >= 30 && sCreditHours <= 59) {
					sClassification = "Sophmore";
				} else if (sCreditHours >= 60 && sCreditHours <= 89) {
					sClassification = "Junior";
				} else {
					sClassification = "Senior";
				}
				rs1.updateString("Classification", sClassification);

				if (sGrade.equals("A")) {
					gradeDecimal = 4.00;
				} else if (sGrade.equals("A-")) {
					gradeDecimal = 3.66;
				} else if (sGrade.equals("B+")) {
					gradeDecimal = 3.33;
				} else if (sGrade.equals("B")) {
					gradeDecimal = 3.00;
				} else if (sGrade.equals("B-")) {
					gradeDecimal = 2.66;
				} else if (sGrade.equals("C+")) {
					gradeDecimal = 2.33;
				} else if (sGrade.equals("C")) {
					gradeDecimal = 2.00;
				} else if (sGrade.equals("C-")) {
					gradeDecimal = 1.66;
				} else if (sGrade.equals("D+")) {
					gradeDecimal = 1.33;
				} else if (sGrade.equals("D")) {
					gradeDecimal = 1.00;
				} else if (sGrade.equals("F")) {
					gradeDecimal = 0.00;
				}
				sGPA = ((sGPA * sCreditHours) + (3 * gradeDecimal));
				rs1.updateFloat("GPA", (float) sGPA);

				// System.out.println(sID + " " + cCode + " " + sGrade);
				rs1.updateRow();
			}
			stmt1.close();

[Jan Cumps]

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]

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....

Statement stmt1 = conn1
					.createStatement();
			ResultSet rs1 = stmt1
					.executeQuery("SELECT StudentID, GPA, CreditHours FROM Student");
			String sGrade = null;
			String sClassification;
			double gradeDecimal = 0;
			while (rs1.next()) {
				int numOfClasses = 0;
				double tempGrade = 0;
				double tempGPA = 0;
				String sID = rs1.getString("StudentID");
				PreparedStatement stmt2 = conn1
						.prepareStatement("SELECT Grade FROM Enrollment WHERE StudentID = ?");
				stmt2.setString(1, sID);
				ResultSet rs2 = stmt2.executeQuery();
				while (rs2.next()) {
					++numOfClasses;
					sGrade = rs2.getString("Grade");
					if (sGrade.equals("A")) {
						gradeDecimal = 4.00;
					} else if (sGrade.equals("A-")) {
						gradeDecimal = 3.66;
					} else if (sGrade.equals("B+")) {
						gradeDecimal = 3.33;
					} else if (sGrade.equals("B")) {
						gradeDecimal = 3.00;
					} else if (sGrade.equals("B-")) {
						gradeDecimal = 2.66;
					} else if (sGrade.equals("C+")) {
						gradeDecimal = 2.33;
					} else if (sGrade.equals("C")) {
						gradeDecimal = 2.00;
					} else if (sGrade.equals("C-")) {
						gradeDecimal = 1.66;
					} else if (sGrade.equals("D+")) {
						gradeDecimal = 1.33;
					} else if (sGrade.equals("D")) {
						gradeDecimal = 1.00;
					} else if (sGrade.equals("F")) {
						gradeDecimal = 0.00;
					}
					if (numOfClasses == 1) {
						tempGrade = gradeDecimal;
						tempGPA = gradeDecimal;
					} else {
						tempGrade = tempGrade + gradeDecimal;
						tempGPA = tempGrade / (numOfClasses);
					}
//					System.out.println(numOfClasses + " " + gradeDecimal + " " + tempGrade + " " + tempGPA);
				}
				double originalGPA = rs1.getDouble("GPA");
				int originalCreditHours = rs1.getInt("CreditHours");
				System.out.println(originalCreditHours + " " + originalGPA);
				int totalCreditHours = originalCreditHours + (numOfClasses * 3);
				double newGPA = Math.round((originalGPA * originalCreditHours) + (tempGPA * numOfClasses))
						/ (totalCreditHours);
//				int newCreditHours = originalCreditHours + totalCreditHours;

				if (totalCreditHours <= 29) {
					sClassification = "Freshman";
				} else if (totalCreditHours >= 30 && totalCreditHours <= 59) {
					sClassification = "Sophmore";
				} else if (totalCreditHours >= 60 && totalCreditHours <= 89) {
					sClassification = "Junior";
				} else {
					sClassification = "Senior";
				}
				System.out.println(totalCreditHours + " " + newGPA + " " + sClassification);

				PreparedStatement stmt3 = conn1
						.prepareStatement("Update Student SET CreditHours = ?, GPA = ?, Classification = ?");
				stmt3.setInt(1, totalCreditHours);
				stmt3.setDouble(2, newGPA);
				stmt3.setString(3, sClassification);
				stmt3.executeUpdate();
				stmt2.close();
				stmt3.close();
			}
			stmt1.close();
			conn1.close();

[Jan Cumps]

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.