KajooBadam

Prepared Statement

Table in Oracle Database (Prepared Statement) :- Insert , Update , Dynamic_Insert & Select

Select( Ctrl+a) , Copy ( Ctrl+c ) and Run the code for practice

package preparedStatement;

import java.sql.*;
import java.util.Scanner;

public class prepared {

public static void main(String[] args) {String m;String choice; 
	try{
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","myresult ","myresult ");
		PreparedStatement pstmt=con.prepareStatement("insert into student values (?,?, ?, ?,?)");
		Statement stmt=con.createStatement();

do{
		Scanner sc=new Scanner(System.in);
		System.out.println("id  of student");
	    int a=sc.nextInt();
	    System.out.println("Name  of student");
	    String b=sc.next();
	    System.out.println("Subject of student");
	    String c=sc.next();
	    System.out.println("Marks  of student");
	    int d=sc.nextInt();
	    if(d > 60)
	    {m="Pass";}
	    else
	    {m="fail";}	
	    pstmt.setInt(1, a);
	    pstmt.setString(2, b);
	    pstmt.setString(3, c);
	    pstmt.setInt(4, d);
	    pstmt.setString(5, m);
	    pstmt.execute();
	    System.out.println("Do you want to continue y/n"); 
		//Scanner Stdin =new Scanner(System.in);
		choice =sc.next();
		}
		while(choice.charAt(0)!= 'n');
		System.out.println("record Entered");
	    
		String r="Select *from student ";
		   ResultSet rs =  stmt.executeQuery(r);
		   System.out.println("DATA  OF STUDENT TABLE ");
		   while(rs.next() )
		   {
		   	int a1= rs.getInt(1);
		   	System.out.print(a1+ "  " );
		   	String b1=rs.getString(2);
		   	System.out.print(b1 + "      ");
		   	String c1=rs.getString(3);
		   	System.out.print(c1 + " ");
		   	int d1= rs.getInt(4);
		   	System.out.print(d1+ "  " );
		   	String e1=rs.getString(5);
		   	System.out.print(e1 +" " );
		   	System.out.println("\n");
		}  
}

catch(Exception e)
	{System.out.println("some errror");
		}}}

Interface PreparedStatement

If we use statement interface the perfomance of the appilication is affected , if there are many queries of same type

Like 500 insert Query
Like 600 Delete Query

When ever a query is executed there are two process that take place between JDBC manager at java end and Database manager at DB end . two processes are Query parsing and execution plan for every Query. This is the performance overhead if we use statement interface . but when we use prepared statement interface these processes take place only once but they should be of same nature .

Queries

Insert
Delete
select
update

Query Format

Static Delete (Prepared)

Delete command Delete the values from the table according to user which is already Exist in oracle Database.

Query is:- delete from student where srno=" +a;

EX In Java:-

Dynamic Update (Prepared)

Update command is using for updating the values of the table dynamically which is already Exist in oracle Database.

Query is:- update emp set ename='Tom' where eid=101

EX In Java:-

Dynamic Insert (Prepared)

insert command is used to insert data in table by user through keyboard which is already Exist in oracle Database.

Query is:- Select *from student

EX In Java:-