Difference between Statement Vs PreparedStatement Vs CallableStatement in java

JDBC API  provides 3 different interfaces to execute different SQL Queries. They are:
1) Statement: Statement interface is used to execute normal SQL Queries.
2) PreparedStatement: It is used to execute dynamic or parametrized SQL Queries.
3) CallableStatement: It is used to execute the Stored Procedure.

In this post, we will discuss the differences between Statement vs PreparedStatement vs CallableStatement in detail.

1) Statement:

In JDBC Statement is an Interface. By using Statement object we can send our SQL Query to Database. At the time of creating a Statement object, we are not required to provide any Query. Statement object can work only for static query.

Whenever we are using execute() method, every time Query will be compiled and executed. Because Query will be compiled every time, its performance is low. Best choice for Statement object, if you want to work with multiple queries.


Example:

import java.sql.*;
import java.util.*;
class Example{
public static void main(String args[])throws Exception{
class.forName("com.mysql.jdbc.Driver");
System.out.println("Driver loaded");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/emp_record","root"," ");
System.out.println("Connection Established");

Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from traning");

System.out.println("Rollno  Student_Name  Stream   Percentage");
System.out.println("------------------------------------------");
while(rs.next()){
System.out.println(rs.getInt(1)+ " " +rs.getString(2)+ " " +rs.getString(3)+ " "+rs.getFloat(4));
}
}
}

2) PreparedStatement:

PreparedStatement is an interface, which is available in java.mysql package. It extends the Statement interface.

Benefits of Prepared Statement:

  • It can be used to execute dynamic and parametrized SQL Query.
  • PreparedStatement is faster then Statement interface. Because in Statement Query will be compiled and execute every time, while in case of Prepared Statement Query won't be compiled every time just executed.
  •  It can be used for both static and dynamic query.
  • In case of Prepared Statement no chance of SQL Injection attack. It is some kind of problem in database programming.

Suppose, I have an SQL Query. In this SQL Query, we have to use username and password. This query is checking username and password is valid or not. Because the end user provided input the query behavior is changing, it is not checking username and password is valid or not. If you change the behavior of the SQL query by adding special character in end user provided input this problem is known as SQL Injection attack.

Example:

import java.sql.*;
import java.util.*;
class Example{
public static void main(String args[])throws Exception{
class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/emp_record","root"," ");
String sqlquery = "insert into employee values(? ? ? ?)";
preparedStatement pst = con.preparedStatement(sqlquery);
Scanner sc = new Scanner(System.in);

while(true){
System.out.println("Enter employee number: ")
int eno = sc.nextInt();
System.out.println("Enter employee name: ")
String ename = sc.next();
System.out.println("Enter employee salary: ")
double esal = sc.nextDouble();
System.out.println("Enter employee address: ")
String eaadr = sc.next();

pst.setInt(1,eno);
pst.setString(2,ename);
pst.setDouble(3, esal);
pst.setString(4, eddr);
pst.excecuteUpdate();

System.out.println("Record inserted succesfully ");
System.out.println("Do you want to insert more records[yes/no]");
String option = sc.next();
if(option.equalsIgnoreCase("no")){
break;
}
}
}
}

3) CallableStatement:

CallableStatement in JDBC is an interface present in a java.sql package and it is the child interface of Prepared Statement. Callable Statement is used to execute the Stored Procedure and functions. Similarly to method stored procedure has its own parameters. Stored Procedure has 3 types of parameters.

1) IN parameter: IN parameter is used to provide input values.
2) OUT parameter: OUT parameter is used to collect output values.
3) INOUT parameter: It is used to provide input and to collect output values.

The driver software vendor is responsible for providing the implementations for Callable Statement interface. If Stored Procedure has OUT parameter then to hold that output value we should register every OUT parameter by using registerOutParameter() method of CallableStatement. CallableStatement interface is better then Statement and PreparedStatement because its call the stored procedure which is already compiled and stored in the database.

let's take an example, I created a stored procedure called addpro() which can take 2 input values and provides the addition of 2 number as an output.


Here I will call the stored procedure addpro() and set the input values by setter method and execute the stored procedure. This procedure will give the addition of 2 numbers.

Statement vs PreparedStatement vs CallableStatement in java

Statement vs PreparedStatement vs CallableStatement in java

Statement vs PreparedStatement vs CallableStatement in java
          Fig: Statement vs PreparedStatement vs CallableStatement in java
Difference between Statement Vs PreparedStatement Vs CallableStatement in java Difference between Statement Vs PreparedStatement Vs CallableStatement in java Reviewed by Prashant Srivastava on September 14, 2018 Rating: 5

1 comment:

Powered by Blogger.