Jdbc Preparedstatement
Difference Between PreparedStatement and Statement
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.
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
| Statement | PreparedStatement | |
|---|---|---|
| 1 | Statement interface is slow because it compile the program for each execution | PreparedStatement interface is faster, because its compile the command for once. |
| 2 | We can not use ? symbol in sql command so setting dynamic value into the command is complex | We can use ? symbol in sql command, so setting dynamic value is simple. |
| 3 | We 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();
}
}