Problem Statement:
Design and Develop SQL DDL statements which demonstrate the use of SQL objects such as Table, View , Index using Client-Data sever(two tier).
Relation Schema:
Create following tables in
oracle
Prof (id,pname,addr,city,age,DOJ,sal)
Works(id,dno)
Dept(dno,dname,addr,city)
Required Softwares:
1. mysql-connector-java-5.x.x
2. Eclipse
PROGRAM:
import java.io.*;
import java.sql.*;
public class Obiwan {
public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException
{
BufferedReader buf= new BufferedReader(new InputStreamReader(System.in));
String url="jdbc:mysql://localhost/*Database Name*;
String driver="com.mysql.jdbc.Driver";
String user="*Username*", password="*Password*";
Connection conn=null;
Statement stmt=null;
int ch=0;
do
{
System.out.println("1. CREATE A VIEW FOR PROF TABLE");
System.out.println("2. DISPLAYING THE VIEW");
System.out.println("3. UPDATING A VIEW");
System.out.println("4. DELETING USING VIEW");
System.out.println("5. DROPPING THE VIEW");
System.out.println("6. CREATING THE INDEX");
System.out.println("7. DISPLAYING THE INDEX");
System.out.println("8. DELETING THE INDEX");
System.out.println("9. EXIT");
System.out.println("Enter choice: ");
ch=Integer.parseInt(buf.readLine());
switch(ch)
{
case 1:
System.out.println("Creating view in given database...");
String sql = "CREATE VIEW record_prof AS SELECT id_prof,first_name,last_name,doj,dob,age,salary FROM prof";
stmt.executeUpdate(sql);
System.out.print("\n");
System.out.println("view created in given database...");
break;
case 2: System.out.println("DISPLAYING THE VIEW");
String sql1= "select * from record_prof";
ResultSet rs1 = stmt.executeQuery(sql1);
while(rs1.next())
{
int id = rs1.getInt("id_prof");
String First_Name = rs1.getString("first_name");
String Last_Name = rs1.getString("last_name");
int jid = rs1.getInt("age");
Date hih = rs1.getDate("dob");
Date gih = rs1.getDate("doj");
int kid = rs1.getInt("salary");
System.out.print("prof first name: " + First_Name+" ,");
System.out.print("prof last name: " + Last_Name+" ,");
System.out.print("prof ID: " + id +" ,");
System.out.print("date of joining: " + gih +"\n");
System.out.print("date of birth: " + hih +"\n");
System.out.print("age: " + jid +"\n");
System.out.print("salary: " + kid +"\n");
}
System.out.print("\n");
rs1.close();
break;
case 3: System.out.println("UPDATING A VIEW");
String sql2 = " UPDATE record_prof SET age = 31 WHERE first_name='Jayti'";
stmt.executeUpdate(sql2);
String sql3 =" update record_prof set salary=55000 where first_name='Jayti'";
stmt.executeUpdate(sql3);
String sql4 = "SELECT * from record_prof";
ResultSet rs2 = stmt.executeQuery(sql4);
while(rs2.next())
{
int id = rs2.getInt("id_prof");
String First_Name = rs2.getString("first_name");
String Last_Name = rs2.getString("last_name");
int jid = rs2.getInt("age");
Date hih = rs2.getDate("dob");
Date gih = rs2.getDate("doj");
int kid = rs2.getInt("salary");
System.out.print("prof first name: " + First_Name+" ,");
System.out.print("prof last name: " + Last_Name+" ,");
System.out.print("prof ID: " + id +" ,");
System.out.print("date of joining: " + gih +"\n");
System.out.print("date of birth: " + hih +"\n");
System.out.print("age: " + jid +"\n");
System.out.print("salary: " + kid +"\n");
}
System.out.print("\n\n");
rs2.close();
break;
case 4: System.out.println("DELETING USING VIEW");
System.out.println("\n");
String sql5 = "delete from record_prof where first_name='Zara'";
stmt.executeUpdate(sql5);
String sql6 = "SELECT * from record_prof";
ResultSet rs3 = stmt.executeQuery(sql6);
while(rs3.next())
{
int id = rs3.getInt("id_prof");
String First_Name = rs3.getString("first_name");
String Last_Name = rs3.getString("last_name");
int jid = rs3.getInt("age");
Date hih = rs3.getDate("dob");
Date gih = rs3.getDate("doj");
int kid = rs3.getInt("salary");
System.out.print("prof first name: " + First_Name+" ,");
System.out.print("prof last name: " + Last_Name+" ,");
System.out.print("prof ID: " + id +" ,");
System.out.print("date of joining: " + gih +"\n");
System.out.print("date of birth: " + hih +"\n");
System.out.print("age: " + jid +"\n");
System.out.print("salary: " + kid +"\n");
}
System.out.print("\n");
rs3.close();
break;
case 5 : System.out.println("DROPPING THE VIEW");
System.out.println("\n");
String sqqqql= "drop view record_prof";
stmt.executeUpdate(sqqqql);
System.out.println("VIEW IS DROPPED");
break;
case 6: System.out.println("CREATING THE INDEX");
System.out.println("\n");
String sqlo = "CREATE UNIQUE INDEX index_first ON prof ( id_prof, first_name)";
stmt.executeUpdate(sqlo);
System.out.println("INDEX CREATED SUCCESSfULLY");
break;
case 7 : System.out.println("DISPLAYING THE INDEX");
System.out.println("\n");
ResultSet indexInformation = null;
DatabaseMetaData meta = conn.getMetaData();
indexInformation = meta.getIndexInfo(conn.getCatalog(), null, "prof", true, true);
while (indexInformation.next()) {
String dbCatalog = indexInformation.getString("TABLE_NAME");
boolean dbNoneUnique = indexInformation.getBoolean("NON_UNIQUE");
String dbIndexName = indexInformation.getString("INDEX_NAME");
int dbPages = indexInformation.getInt("PAGES");
String dbColumnName = indexInformation.getString("COLUMN_NAME");
int dbCardinality = indexInformation.getInt("CARDINALITY");
short dbType = indexInformation.getShort("TYPE");
System.out.println("Table Name=" + dbCatalog);
System.out.println("nonUnique=" + dbNoneUnique);
System.out.println("pages=" + dbPages);
System.out.println("Index Name=" + dbIndexName);
System.out.println("cardinality=" + dbCardinality);
System.out.println("COlumn_name=" + dbColumnName);
System.out.println("TYPE=" + dbType);
System.out.println("\n");
}
break;
case 8: System.out.println("Deleting a index");
System.out.println("/n");
String sqqql="alter table prof drop index index_first";
stmt.executeUpdate(sqqql);
System.out.println(" INDEX DELETED SUCCESSFULLY");
case 9: System.out.println("EXIT");
break;
}
}while(ch!=9);
//STEP 6: Clean-up environment
stmt.close();
conn.close();
}
catch(SQLException se)
{
//Handle errors for JDBC
se.printStackTrace();
}
catch(Exception e)
{
//Handle errors for Class.forName
e.printStackTrace();
}
finally
{
//finally block used to close resources
try
{
if(stmt!=null)
stmt.close();
}
catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}
catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
}
private static void setDriver(String string) {
// TODO Auto-generated method stub
}
}
OUTPUT:
Statement stmt=null;
int ch=0;
do
{
System.out.println("1. CREATE A VIEW FOR PROF TABLE");
System.out.println("2. DISPLAYING THE VIEW");
System.out.println("3. UPDATING A VIEW");
System.out.println("4. DELETING USING VIEW");
System.out.println("5. DROPPING THE VIEW");
System.out.println("6. CREATING THE INDEX");
System.out.println("7. DISPLAYING THE INDEX");
System.out.println("8. DELETING THE INDEX");
System.out.println("9. EXIT");
System.out.println("Enter choice: ");
ch=Integer.parseInt(buf.readLine());
switch(ch)
{
case 1:
System.out.println("Creating view in given database...");
String sql = "CREATE VIEW record_prof AS SELECT id_prof,first_name,last_name,doj,dob,age,salary FROM prof";
stmt.executeUpdate(sql);
System.out.print("\n");
System.out.println("view created in given database...");
break;
case 2: System.out.println("DISPLAYING THE VIEW");
String sql1= "select * from record_prof";
ResultSet rs1 = stmt.executeQuery(sql1);
while(rs1.next())
{
int id = rs1.getInt("id_prof");
String First_Name = rs1.getString("first_name");
String Last_Name = rs1.getString("last_name");
int jid = rs1.getInt("age");
Date hih = rs1.getDate("dob");
Date gih = rs1.getDate("doj");
int kid = rs1.getInt("salary");
System.out.print("prof first name: " + First_Name+" ,");
System.out.print("prof last name: " + Last_Name+" ,");
System.out.print("prof ID: " + id +" ,");
System.out.print("date of joining: " + gih +"\n");
System.out.print("date of birth: " + hih +"\n");
System.out.print("age: " + jid +"\n");
System.out.print("salary: " + kid +"\n");
}
System.out.print("\n");
rs1.close();
break;
case 3: System.out.println("UPDATING A VIEW");
String sql2 = " UPDATE record_prof SET age = 31 WHERE first_name='Jayti'";
stmt.executeUpdate(sql2);
String sql3 =" update record_prof set salary=55000 where first_name='Jayti'";
stmt.executeUpdate(sql3);
String sql4 = "SELECT * from record_prof";
ResultSet rs2 = stmt.executeQuery(sql4);
while(rs2.next())
{
int id = rs2.getInt("id_prof");
String First_Name = rs2.getString("first_name");
String Last_Name = rs2.getString("last_name");
int jid = rs2.getInt("age");
Date hih = rs2.getDate("dob");
Date gih = rs2.getDate("doj");
int kid = rs2.getInt("salary");
System.out.print("prof first name: " + First_Name+" ,");
System.out.print("prof last name: " + Last_Name+" ,");
System.out.print("prof ID: " + id +" ,");
System.out.print("date of joining: " + gih +"\n");
System.out.print("date of birth: " + hih +"\n");
System.out.print("age: " + jid +"\n");
System.out.print("salary: " + kid +"\n");
}
System.out.print("\n\n");
rs2.close();
break;
case 4: System.out.println("DELETING USING VIEW");
System.out.println("\n");
String sql5 = "delete from record_prof where first_name='Zara'";
stmt.executeUpdate(sql5);
String sql6 = "SELECT * from record_prof";
ResultSet rs3 = stmt.executeQuery(sql6);
while(rs3.next())
{
int id = rs3.getInt("id_prof");
String First_Name = rs3.getString("first_name");
String Last_Name = rs3.getString("last_name");
int jid = rs3.getInt("age");
Date hih = rs3.getDate("dob");
Date gih = rs3.getDate("doj");
int kid = rs3.getInt("salary");
System.out.print("prof first name: " + First_Name+" ,");
System.out.print("prof last name: " + Last_Name+" ,");
System.out.print("prof ID: " + id +" ,");
System.out.print("date of joining: " + gih +"\n");
System.out.print("date of birth: " + hih +"\n");
System.out.print("age: " + jid +"\n");
System.out.print("salary: " + kid +"\n");
}
System.out.print("\n");
rs3.close();
break;
case 5 : System.out.println("DROPPING THE VIEW");
System.out.println("\n");
String sqqqql= "drop view record_prof";
stmt.executeUpdate(sqqqql);
System.out.println("VIEW IS DROPPED");
break;
case 6: System.out.println("CREATING THE INDEX");
System.out.println("\n");
String sqlo = "CREATE UNIQUE INDEX index_first ON prof ( id_prof, first_name)";
stmt.executeUpdate(sqlo);
System.out.println("INDEX CREATED SUCCESSfULLY");
break;
case 7 : System.out.println("DISPLAYING THE INDEX");
System.out.println("\n");
ResultSet indexInformation = null;
DatabaseMetaData meta = conn.getMetaData();
indexInformation = meta.getIndexInfo(conn.getCatalog(), null, "prof", true, true);
while (indexInformation.next()) {
String dbCatalog = indexInformation.getString("TABLE_NAME");
boolean dbNoneUnique = indexInformation.getBoolean("NON_UNIQUE");
String dbIndexName = indexInformation.getString("INDEX_NAME");
int dbPages = indexInformation.getInt("PAGES");
String dbColumnName = indexInformation.getString("COLUMN_NAME");
int dbCardinality = indexInformation.getInt("CARDINALITY");
short dbType = indexInformation.getShort("TYPE");
System.out.println("Table Name=" + dbCatalog);
System.out.println("nonUnique=" + dbNoneUnique);
System.out.println("pages=" + dbPages);
System.out.println("Index Name=" + dbIndexName);
System.out.println("cardinality=" + dbCardinality);
System.out.println("COlumn_name=" + dbColumnName);
System.out.println("TYPE=" + dbType);
System.out.println("\n");
}
break;
case 8: System.out.println("Deleting a index");
System.out.println("/n");
String sqqql="alter table prof drop index index_first";
stmt.executeUpdate(sqqql);
System.out.println(" INDEX DELETED SUCCESSFULLY");
case 9: System.out.println("EXIT");
break;
}
}while(ch!=9);
//STEP 6: Clean-up environment
stmt.close();
conn.close();
}
catch(SQLException se)
{
//Handle errors for JDBC
se.printStackTrace();
}
catch(Exception e)
{
//Handle errors for Class.forName
e.printStackTrace();
}
finally
{
//finally block used to close resources
try
{
if(stmt!=null)
stmt.close();
}
catch(SQLException se2){
}// nothing we can do
try{
if(conn!=null)
conn.close();
}
catch(SQLException se){
se.printStackTrace();
}//end finally try
}//end try
}
private static void setDriver(String string) {
// TODO Auto-generated method stub
}
}
OUTPUT:
