JDBC CONNECTIVITY - ASSIGNMENT 2
DBMS
Submitted Byr
Kavin.T - 71762108019
PROBLEM STATEMENT:
Schema:
Stud(stud_id,Stud_name,birth_date, total_credits);
Course(course_id,course_name, credits);
Takes(stud_id,course_id,sec_id,sem,year,grade)
1. Write a Java program using JDBC to
display the student details from the stud
relation for the ‘stud_id’ given as an input
from the terminal/console. Additionally, fetch
the course relation and display ‘course_id’,
‘course_name’ and ‘credits’ for all the
courses offered.
SQL CODE:
create table stud(stud_id varchar(37) not null,stud_name varchar(90),DOB
date,total_credits int,primary key(stud_id));
insert into stud values('001','Dharunika','2007-08-17',8);
insert into stud values('002','Kavi','2007-08-18',6);
insert into stud values('003','Hari','2007-08-17',7);
insert into stud values('004','Bala','2007-08-17',7);
select * from stud;
Create table Course(Course_id varchar(90) not null,course_name
varchar(90),credits int,primary key(Course_id));
insert into Course values('21AD41','Dis Maths',4);
insert into Course values('21AD42','DBMS',4);
insert into Course values('21AD43','ML',3);
insert into Course values('21AD44','Data Networks',3);
select * from Course;
create table Takes(stud_id varchar(90),course_id varchar(90),sec_id
varchar(90),sem varchar(90),year int,grade varchar(4),foreign key(stud_id)
references stud(stud_id),foreign key(course_id) references course(Course_id));
insert into Takes values('001','21AD41','SEC2','II',1,'A+');
insert into Takes values('001','21AD42','SEC2','II',1,'A');
insert into Takes values('003','21AD42','SEC1','III',2,'O');
insert into Takes values('003','21AD43','SEC1','III',2,'A');
insert into Takes values('002','21AD43','SEC2','II',1,'A');
insert into Takes values('002','21AD44','SEC2','II',1,'O');
insert into Takes values('004','21AD44','SEC1','IV',2,'B+');
insert into Takes values('004','21AD41','SEC1','IV',2,'O');
SELECT * FROM TAKES;
JDBC CODE :
public static void showDetails() throws Exception {
String url= "jdbc:mysql://localhost:3306/mydb";
String userName= "root";
String password = "Kavin@2003";
Scanner myObj=new Scanner([Link]);
[Link]("Enter the ID: ");
String id=[Link]();
String query="select course_id from Takes where stud_id=?";
Connection con =
[Link](url,userName,password);
PreparedStatement pst=[Link](query);
[Link](1, id);
ResultSet rs=[Link]();
while([Link]()) {
String cour_id=[Link](1);
String query2="select course_id,course_name,credits from
Course where course_id=?";
PreparedStatement pst1=[Link](query2);
[Link](1, cour_id);
ResultSet rs1=[Link]();
while([Link]()) {
[Link]("Course ID : "+[Link](1));
[Link]("Course Name: "+[Link](2));
[Link]("Course Credits: "+[Link](3));
}
;
[Link]();
[Link]();
2. Write a Java program using JDBC to update
the grade of a student as follows: take as
input from the terminal/console `stud_id
course_id sec_id semester year grade`
(separated by spaces) and update the ’
grade’ of an existing tuple in the takes
relation; but also, depending on change from
pass to fail or vice-versa, update the
students `tot_cred` correctly.
- -- null and F are fail grades, all other grades
are pass grades; grade values provided by
the user above can be assumed to be valid
grades, and you can assume the user will not
type null as a grade.
- -- We assume for simplicity that a student
who has already passed the course will not
be allowed to take it again.
- -- You can assume that tot_cred is not null;
JDBC CODE :
static int credits,total;
static String grd;
public static void updatecommit() throws Exception {
String url= "jdbc:mysql://localhost:3306/mydb";
String userName= "root";
String password = "Kavin@2003";
Connection con =
[Link](url,userName,password);
Scanner myObj=new Scanner([Link]);
[Link]("Enter the Student ID: ");
String stud_id=[Link]();
[Link]("Enter the Course ID: ");
String cour_id=[Link]();
[Link]("Enter the Section ID: ");
String sec_id=[Link]();
[Link]("Enter the Semester: ");
String sem=[Link]();
[Link]("Enter the Grade: ");
String grade=[Link]();
[Link]("Enter the Year: ");
int year=[Link]();
String query="update Takes set grade=? where stud_id=? and
course_id=? and sec_id=? and sem=? and year=?;";
PreparedStatement pst=[Link](query);
[Link](1, grade);
[Link](2,stud_id);
[Link](3, cour_id);
[Link](4, sec_id);
[Link](5,sem);
[Link](6, year);
String query3="select grade from Takes where stud_id=? and
course_id=? and sec_id=? and sem=? and year=?;";
PreparedStatement pst3=[Link](query3);
[Link](1,stud_id);
[Link](2, cour_id);
[Link](3, sec_id);
[Link](4,sem);
[Link](5, year);
ResultSet rs3=[Link]();
while([Link]()) {
grd=[Link](1);
}
int res=[Link]();
if(res==0) {
[Link](" Record Not Found ");
}
[Link](" OLD GRADE : "+grd);
[Link](" NEW GRADE : "+grade);
if([Link]("F") && ![Link]("F")) {
String query1="select credits from Course where
course_id=?;";
PreparedStatement pst1=[Link](query1);
[Link](1, cour_id);
ResultSet rs1=[Link]();
while([Link]()) {
credits=[Link](1);
String query2="select total_credits from stud where
stud_id=?";
PreparedStatement pst2=[Link](query2);
[Link](1, stud_id);
ResultSet rs2=[Link]();
while([Link]()) {
total=[Link](1);
}
[Link](" OLD CREDITS : "+ total);
total=total-credits;
[Link](" NEW CREDITS : " + total);
String query4="update stud set total_credits=? where
stud_id=?";
PreparedStatement pst4=[Link](query4);
[Link](1, total);
[Link](2, stud_id);
int res2=[Link]();
[Link](" ROWS AFFECTED IN STUD TABLE : "+res2);
}
if(![Link]("F") && [Link]("F")) {
String query1="select credits from Course where
course_id=?;";
PreparedStatement pst1=[Link](query1);
[Link](1, cour_id);
ResultSet rs1=[Link]();
while([Link]()) {
credits=[Link](1);
}
String query2="select total_credits from stud where
stud_id=?";
PreparedStatement pst2=[Link](query2);
[Link](1, stud_id);
ResultSet rs2=[Link]();
while([Link]()) {
total=[Link](1);
}
[Link](" OLD CREDITS : "+ total);
total=total+credits;
[Link](" NEW CREDITS : " + total);
String query4="update stud set total_credits=? where
stud_id=?";
PreparedStatement pst4=[Link](query4);
[Link](1, total);
[Link](2, stud_id);
int res2=[Link]();
[Link](" ROWS AFFECTED IN STUD TABLE : "+res2);
}
[Link](" ROWS AFFECTED IN TAKES TABLE : "+res);
[Link]();
[Link]();
Before Updation in SQL:
After Updation In SQL:
