AIM:
To write a program to manipulate the Book database.
PROCEDURE:
1.CODE WINDOW:
Step 1: Start IBM Websphere Application by selecting Start à All Programs à IBM
webSphere Studio à Application Developer 5.0.
Step 2: A dialog box appear in that enter the directory for that session and click OK.
Step 3: A window appear in that select Fileà Newà Project.
Step 4: In the new project dialog box select Java from the left pane and Java project from
the right Pane and then click next button.
Step 5: Enter a project name and click finish .
Step 6: Click on the new class Icon.
Step 7:In the new dialog box and give a name to the class as student and select the modifier
as public and super class as import java.sql.*.
Step 8: Select the type of stub as public static void main(String args[]) and click ok
Step 9:A skeleton of the java program is appear in the window .Type the program in that window.
2.JDBC CONNECTIVITY:
Step 1:Start .
Step 2: Declare the necessary variables.
Step 3: Register the driver using the following statement.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Step 4:Create a connection using the following statement.
Connection con=DriverManager.getConnection("jdbc:odbc:dsnname",”userid”,”password”);
Step 5:STATIC SQL
The code for execute the static query is given below
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery( SQL);
Step 6:DYNAMIC SQL
The code for execute dynamic sql query is given below
PreparedStatement ps=con.prepareStatement(SQL);
count=ps.executeUpdate() ;
Step 7 : Display the choices as follows
1.Insert
2.Delete
3.Update
4.Search
5.Other queries
Step 8: Get the choice from the user.
Step 9: Using switch case perform their corresponding operations, by using the following queries.
i.For Insertion the code is given below
PreparedStatement ps=con.prepareStatement("Insert into book values(" + id +",' " + name + "','"+ author + "','" + isbn +"'," + edition +",'"+ publish +"'," + rate +")");
count=ps.executeUpdate() ;
ii .For Deletion the code is given below
PreparedStatement ps=con.prepareStatement("delete from book where id= "+ id +"");
count=ps.executeUpdate() ;
iii.For Updation the coding is given below
PreparedStatement ps=con.prepareStatement("update book set id=" + id +",name=' " + name + "',author='"+ author + "',isbn=' " + isbn +"',edition='" + edition +"',rate="+ rate +" where id = "+ id1 +"");
count=ps.executeUpdate() ;
iv.For searching the coding is given below
ResultSet rs=stmt.executeQuery( "Select * from book where id ="+id+"");
v.For other queries the coding is given below
boolean b =stmt.execute(""+ str1+"");
3.EXECUTION WINDOW:
Step 1:Select projectà Rebuild all.
Step 2:Select Runà Run asà java Application.
Step 3:Enter the input in the console.
Step 4:Output is displayed in the console window.
PROGRAM:
import java.io.*;
import java.sql.*;
/**
* @author students
*
* To change this generated comment edit the template variable "typecomment":
* Window>Preferences>Java>Templates.
* To enable and disable the creation of type comments go to
* Window>Preferences>Java>Code Generation.
*/
public class book
{
public static void main(String[] args)throws Exception
{
int ch,ch1,id,edition,ch2,id1,count;
float rate;
String c,name,author,isbn,publish,str,str1,str2;
do
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:book1");
Statement stmt=con.createStatement();
System.out.println("1.Insert");
System.out.println("2.Delete");
System.out.println("3.Edit");
System.out.println("4.Search");
System.out.println("5.Execute other query");
System.out.println("Enter the choice ");
BufferedReader d1=new BufferedReader(new InputStreamReader(System.in));
str=d1.readLine();
ch=Integer.parseInt(str);
switch(ch)
{
case 1:
{
System.out.println("Enter the Book id ");
str=d1.readLine();
id=Integer.parseInt(str);
System.out.println("Enter the Book Name ");
name=d1.readLine();
System.out.println("Enter the Author ");
author=d1.readLine();
System.out.println("Enter the ISBN ");
isbn=d1.readLine();
System.out.println("Enter the Edition ");
str=d1.readLine();
edition=Integer.parseInt(str);
System.out.println("Enter the Publisher ");
publish=d1.readLine();
System.out.println("Enter the price");
str=d1.readLine();
rate=Float.parseFloat(str);
PreparedStatement ps=con.prepareStatement("Insert into book values(" + id +",' " + name + "','"+ author + "','" + isbn +"'," + edition +",'"+ publish +"'," + rate +")");
count=ps.executeUpdate() ;
System.out.println(count +" record inserted successfully");
ps.close();
break;
}
case 2:
{
System.out.println("Enter the book id to delete");
str=d1.readLine();
id=Integer.parseInt(str);
PreparedStatement ps=con.prepareStatement("delete from book where id= "+ id +"");
count=ps.executeUpdate() ;
System.out.println(count +" record deleted successfully");
break;
}
case 3:
{
System.out.println("Enter the book ID to update");
str=d1.readLine();
id1=Integer.parseInt(str);
System.out.println("Enter the Book id ");
str=d1.readLine();
id=Integer.parseInt(str);
System.out.println("Enter the Book Name ");
name=d1.readLine();
System.out.println("Enter the Author ");
author=d1.readLine();
System.out.println("Enter the ISBN ");
isbn=d1.readLine();
System.out.println("Enter the Edition ");
str=d1.readLine();
edition=Integer.parseInt(str);
System.out.println("Enter the Publisher ");
publish=d1.readLine();
System.out.println("Enter the price ");
str=d1.readLine();
rate=Float.parseFloat(str);
PreparedStatement ps=con.prepareStatement("update book set id=" + id +",name=' " + name + "',author='"+ author + "',isbn=' " + isbn +"',edition='" + edition +"',rate="+ rate +" where id = "+ id1 +"");
count=ps.executeUpdate() ;
System.out.println(count +" record updated successfully");
break;
}
case 4:
{
System.out.println("1.Search by Book ID ");
System.out.println("2.Search by Book name");
System.out.println("Enter your choice");
str=d1.readLine();
int ch5=Integer.parseInt(str);
if(ch5==1)
{
System.out.println("enter the Book ID to search");
str=d1.readLine();
id=Integer.parseInt(str);
ResultSet rs=stmt.executeQuery( "Select * from book where id ="+id+"");
while(rs.next())
{
System.out.println("Book ID :"+rs.getInt(1));
System.out.println("Book Name :"+rs.getString(2));
System.out.println("Author :"+rs.getString(3));
System.out.println("ISBN :"+rs.getString(4));
System.out.println("Edition :"+rs.getInt(5));
System.out.println("Publisher :"+rs.getString(6));
System.out.println("Rate :"+rs.getFloat(7));
}
}
else
{
System.out.println("enter the Book name to search");
str1=d1.readLine();
ResultSet rs1=stmt.executeQuery( "Select * from book where name ='"+ str1 +"'");
while(rs1.next())
{
System.out.println("Book ID :"+rs1.getInt(1));
System.out.println("Book Name :"+rs1.getString(2));
System.out.println("Author :"+rs1.getString(3));
System.out.println("ISBN :"+rs1.getString(4));
System.out.println("Edition :"+rs1.getInt(5));
System.out.println("Publisher :"+rs1.getString(6));
System.out.println("Rate :"+rs1.getFloat(7));
}
}
break;
}
case 5:
{
System.out.println("Enter the query");
str1=d1.readLine();
try
{
boolean b =stmt.execute(""+ str1+"");
if(b==true)
System.out.println("The query is executed successfully");
}
catch(SQLException e)
{
System.out.println("The query is not executed successfully");
}
break;
}
}
stmt.close();
con.close();
System.out.println("Do you want to continue");
c=d1.readLine();
ch2=Integer.parseInt(c);
}while(ch2==1);
}
}
0 comments:
Post a Comment