import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.ResultSet;
import
java.sql.Statement;
import
java.util.Scanner;
import
java.sql.PreparedStatement;
public
class
Materialised_View {
public
static
void
main(String[] args)
{
Connection conn;
ResultSet rs;
Statement stmt, stmt1;
String query =
"select * from student;"
;
Scanner sc =
new
Scanner(System.in);
System.out.println(
"Student Table is Created as Follows:\n"
);
System.out.println
(
"*******Student*********\nsid\tsname\taddress\n---- "
);
try
{
DriverManager.registerDriver(
new
com.mysql.jdbc.Driver());
conn = DriverManager.getConnection
(
"jdbc:mysql:// localhost:3306/adbms_practicals"
,
"root"
,
"kernel"
);
stmt = conn.createStatement();
rs = stmt.executeQuery(
"select * from student;"
);
while
(rs.next()) {
System.out.println(rs.getString(
1
) +
"\t"
+ rs.getString(
2
) +
"\t"
+ rs.getString(
3
));
}
System.out.println(
"\n\nView of this Student Table is As Follows:"
);
rs = stmt.executeQuery(
"select * from Student_Data2;"
);
System.out.println
(
"\n***********Student View*******\nsid\tsname\taddress\n-------"
);
while
(rs.next()) {
System.out.println(rs.getString(
1
) +
"\t"
+ rs.getString(
2
) +
"\t"
+ rs.getString(
3
));
}
while
(
true
) {
System.out.println("Menu:\n1.Insert\n2.Delete\n3.
See Changes in View Student_Data2\n4.Exit\nEnter Choice:");
int
ch = sc.nextInt();
int
sid;
String sname =
""
;
String address =
""
;
switch
(ch) {
case
1
:
System.out.println(
"Enter sid:"
);
sid = sc.nextInt();
System.out.println(
"Enter sname:"
);
sname = sc.nextLine();
System.out.println(
"Enter address:"
);
address = sc.nextLine();
PreparedStatement st3 = conn.prepareStatement
(
"insert into student values(?, ?, ?);"
);
st3.setInt(
1
, sid);
st3.setString(
2
, sname);
st3.setString(
3
, address);
st3.executeUpdate();
PreparedStatement st1 = conn.prepareStatement
(
"insert into Student_Data2 values(?, ?, ?);"
);
st1.setInt(
1
, sid);
st1.setString(
2
, sname);
st1.setString(
3
, address);
st1.executeUpdate();
System.out.println(
"row inserted"
);
break
;
case
2
:
System.out.println(
"Enter sid to be deleted:"
);
sid = sc.nextInt();
PreparedStatement st4 = conn.prepareStatement
(
"delete from Student_Data2 where sid=?;"
);
st4.setInt(
1
, sid);
PreparedStatement st2 = conn.prepareStatement
(
"delete from student where sid=?"
);
st2.setInt(
1
, sid);
st2.executeUpdate();
System.out.println(
"row deleted"
);
break
;
case
3
:
System.out.println("\n\nChanges Done in the
Student table also Affects the View Student_Data2");
rs = stmt.executeQuery(
"select * from Student_Data2;"
);
System.out.println
(
"\n*****Student*******\nsid\tsname\taddress\n--------"
);
while
(rs.next()) {
System.out.println(rs.getString(
1
) +
"\t"
+ rs.getString(
2
) +
"\t"
+ rs.getString(
3
));
}
break
;
case
4
:
System.exit(
0
);
break
;
}
}
}
catch
(Exception e) {
System.out.println(e);
}
}
}