JDBC APPLICATION FOR STUDENT DATA SOURCE
AIM:
To write a program to manipulate the Student 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 Personal values(" + no +",' " + name + "',"+ age + ",' " + gender +"','" + add +"',' "+ phone +"')");
count=ps.executeUpdate() ;
ii .For Deletion the code is given below
PreparedStatement ps=con.prepareStatement("delete from personal where regno= "+ no +"");
count=ps.executeUpdate() ;
iii.For Updation the coding is given below
PreparedStatement ps=con.prepareStatement("update Personal set regno=" + no +",name=' " + name + "',age="+ age + ",gender=' " + gender +"',address='" + add +"',phoneno=' "+ phone +"' where regno = "+ no1+"");
count=ps.executeUpdate() ;
iv.For searching the coding is given below
ResultSet rs=stmt.executeQuery( "Select * from Personal where regno ="+no+"");
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 Student
{
public static void main(String[] args)throws Exception
{
int ch,ch1,no,age,count,no1,ch2;
String name,add,str,phone,gender,str1,c;
do
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:student");
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 register Number ");
str=d1.readLine();
no=Integer.parseInt(str);
System.out.println("Enter the Name ");
name=d1.readLine();
System.out.println("Enter the Age ");
str=d1.readLine();
age=Integer.parseInt(str);
System.out.println("Enter the Gender ");
gender=d1.readLine();
System.out.println("Enter the Address ");
add=d1.readLine();
System.out.println("Enter the Phone Number ");
phone=d1.readLine();
PreparedStatement ps=con.prepareStatement("Insert into Personal values(" + no +",' " + name + "',"+ age + ",' " + gender +"','" + add +"',' "+ phone +"')");
count=ps.executeUpdate() ;
System.out.println(count +" record inserted successfully");
ps.close();
break;
}
case 2:
{
System.out.println("Enter the register number to delete");
str=d1.readLine();
no=Integer.parseInt(str);
PreparedStatement ps=con.prepareStatement("delete from personal where regno= "+ no +"");
count=ps.executeUpdate() ;
System.out.println(count +" record deleted successfully");
break;
}
case 3:
{
System.out.println("Enter the register number to update");
str=d1.readLine();
no1=Integer.parseInt(str);
System.out.println("Enter the register Number ");
str=d1.readLine();
no=Integer.parseInt(str);
System.out.println("Enter the Name ");
name=d1.readLine();
System.out.println("Enter the Age ");
str=d1.readLine();
age=Integer.parseInt(str);
System.out.println("Enter the Gender ");
gender=d1.readLine();
System.out.println("Enter the Address ");
add=d1.readLine();
System.out.println("Enter the Phone Number ");
phone=d1.readLine();
PreparedStatement ps=con.prepareStatement("update Personal set regno=" + no +",name=' " + name + "',age="+ age + ",gender=' " + gender +"',address='" + add +"',phoneno=' "+ phone +"' where regno = "+ no1+"");
count=ps.executeUpdate() ;
System.out.println(count +" record updated successfully");
break;
}
case 4:
{
System.out.println("1.Search by Register number ");
System.out.println("2.Search by name");
System.out.println("Enter your choice");
str=d1.readLine();
int ch5=Integer.parseInt(str);
if(ch5==1)
{
System.out.println("enter the register number to search");
str=d1.readLine();
no=Integer.parseInt(str);
ResultSet rs=stmt.executeQuery( "Select * from Personal where regno ="+no+"");
while(rs.next())
{
System.out.println("Register No :"+rs.getInt(1));
System.out.println("Name :"+rs.getString(2));
System.out.println("Age :"+rs.getString(3));
System.out.println("Gender :"+rs.getString(4));
System.out.println("Address :"+rs.getString(5));
System.out.println("Phone No :"+rs.getString(6));
}
}
else
{
System.out.println("enter the name to search");
str1=d1.readLine();
ResultSet rs1=stmt.executeQuery( "Select * from Personal where name ='"+ str1 +"'");
while(rs1.next())
{
System.out.println("Register No :"+rs1.getInt(1));
System.out.println("Name :"+rs1.getString(2));
System.out.println("Age :"+rs1.getString(3));
System.out.println("Gender :"+rs1.getString(4));
System.out.println("Address :"+rs1.getString(5));
System.out.println("Phone No :"+rs1.getString(6));
}
}
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);
}
}
OUTPUT:
1.Insert
2.Delete
3.Edit
4.Search
5.Execute other query
Enter the choice
1
Enter the register Number
6
Enter the Name
arun
Enter the Age
21
Enter the Gender
male
Enter the Address
salem
Enter the Phone Number
1 record inserted successfully
Do you want to continue
1
1.Insert
2.Delete
3.Edit
4.Search
5.Execute other query
Enter the choice
3
Enter the register number to update
6
Enter the register Number
87
Enter the Name
arun
Enter the Age
22
Enter the Gender
male
Enter the Address
salem
Enter the Phone Number
2948747
1 record updated successfully
Do you want to continue
1
1.Insert
2.Delete
3.Edit
4.Search
5.Execute other query
Enter the choice
4
1.Search by Register number
2.Search by name
Enter your choice
1
enter the register number to search
87
Register No :87
Name : arun
Age :22
Gender : male
Address :salem
Phone No : 2948747
Do you want to continue
1
1.Insert
2.Delete
3.Edit
4.Search
5.Execute other query
Enter the choice
4
1.Search by Register number
2.Search by name
Enter your choice
2
enter the name to search
arun
Register No :87
Name : arun
Age :22
Gender : male
Address :salem
Phone No : 2948747
Do you want to continue
1
1.Insert
2.Delete
3.Edit
4.Search
5.Execute other query
Enter the choice
5
Enter the query
select * from personal;
The query is executed successfully
Do you want to continue
1
1.Insert
2.Delete
3.Edit
4.Search
5.Execute other query
Enter the choice
2
Enter the register number to delete
87
1 record deleted successfully
Do you want to continue
2
2 comments:
Nice one
great, working...
Post a Comment