Jdbc Preparedstatement


Prev Tutorial Next Tutorial

Preparedstatement in JDBC

If the sql command is same then actually no need to compiling it for each time before it is executed. So the performance of an application will be Increased. In this case PreparedStatement is used.

PreparedStatement Interface is derived Interface of statement and CallableStatement is derived Interface of PreparedStatement.

callablestatement in jdbc

Why use PreparedStatement

We know that when working with Statement Interface of JDBC the sql command will be compiled first and then it is executed at database side even through the same sql command is execute repeatedly but each time the command is compiled and then executed at database. Due to this performance of application will be decreased So overcome this problem use PreparedStatement. In PreparedStatement, If the sql command is same then actually no need to compiling it for each time before it is executed.

In case of preparedStatement

  • First sql command is send to database for compilation and then compiled code will be stored in preparedStatement object.
  • The compiled code will be executed for n number of time without recompiling the sql command.
  • The criteria to use preparedStatement is when we want to execute same sql query for multiple times with different set of values.
  • Comparatively preparedStatement is faster than Statement Interface.

Difference between PreparedStatement and Statement

StatementPreparedStatement
1Statement interface is slow because it compile the program for each executionPreparedStatement interface is faster, because its compile the command for once.
2We can not use ? symbol in sql command so setting dynamic value into the command is complexWe can use ? symbol in sql command, so setting dynamic value is simple.
3We can not use statement for writing or reading binary data (picture)We can use PreparedStatement for reading or writing binary data.

Create an object of PreparedStatement

Syntax

Connection con; // con is reference of connection
PreparedStatement pstmt=con.prepareStatement("sql command");

Why use '?' symbol in PreparedStatement

To pre-compile a command only syntax of the command is required so we can use '?' symbol for value in the command. '?' symbol is called parameter or replacement operator or place-resolution operator.

Syntax

PreparedStatement pstmt=con.prepareStatement("Insert into student_table value(?, ?, ?)");

Note: In PreparedStatement only '?' symbol are allow, no other symbols are allowed.

Note: '?' is only for replacing value but not for table name or column names.

Note: '?' symbol are not allowed in DDL operation.

Setting value

We call setxxx() methods to set the value in place of ? symbols, before executing the command. Here pass two parameters for setxxx(), where first parameter is index and second is value. XXX means any data type.

Syntax

pstmt.setInt(1,102);

Example of PreparedStatement

import java.sql.*;
import javax.sql.*;//PreparedStatement;
import java.util.*;
class PrepardTest1 
{
Connection con;
void openConnection()throws Exception
{
Class.forName("oracle.jdbc.OracleDriver");
System.out.println("driver is loaded");
con=DriverManager.getConnection("jdbc:oracle:thin:@John-pc:1521:xe","system","system");
System.out.println("connection is opend");
}
void insertTest()throws Exception
{
PreparedStatement pstmt=con.prepareStatement("insert into student values(?,?,?) ");
Scanner s=new Scanner(System.in);
String Choice="yes";
while(Choice.equals("yes"))
{
System.out.println("enter student id");
int sid=s.nextInt();
System.out.println("enter student name");
String sname=s.next();
System.out.println("enter Student marks");
int marks=s.nextInt();

//setting the values
pstmt.setInt(1,sid);
pstmt.setString(2,sname);
pstmt.setInt(3,marks);
int i=pstmt.executeUpdate();
System.out.println(i+"Row inserted");
System.out.println("do you want to inset another row(Yes/no)");
Choice=s.next();
}//end while
pstmt.close();
}
void closeConnection()throws Exception
{
con.close();
System.out.println("connection is closed");
}
public static void main(String[] args) throws Exception
{ 
PrepardTest1 pt=new PrepardTest1();
pt.openConnection();
pt.insertTest();
pt.closeConnection();
}
}

Prev Tutorial Next Tutorial

Advertisements




Facebook Likes

Advertisements