Difference between Statement Vs PreparedStatement Vs CallableStatement in Java | Check Definitions & Example Programs

Difference between statement preparedstatement and callablestatement: In this tutorial, we will discuss the differences between Statement vs PreparedStatement vs CallableStatement in detail. Actually, JDBC API implements three diverse 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 parameterized SQL Queries.
  3. CallableStatement: It is used to execute the Stored Procedure.

This Java Tutorial on Statement Vs PreparedStatement Vs CallableStatement Includes

Difference between Statement vs PreparedStatement vs CallableStatement in Java

The following table helps you understand the difference between Statement and PreparedStatement and CallableStatement Interfaces:

Statement vs PreparedStatement vs CallableStatement in java

What is a Statement Interface?

In JDBC Statement is an Interface. With the help of the Statement object, we can send our SQL Query to Database. While creating a Statement object, we are not required to give any Query. Statement object can run only for the static query.

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

Example of Java Statement Interface:

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));
}
}
}

Also Check:

What is the PreparedStatement Interface in Java?

PreparedStatement is an interface, which is available in java.mysql package. It extends the Statement interface. The below-listed points are some of the benefits of the JDBC Prepared Statement.

Benefits of Prepared Statement

  • PreparedStatement Interface is utilized to execute dynamic or parameterized SQL queries.
  • PreparedStatement is faster than the 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 queries.
  • In the 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 a username and password. This query is checking the username and password are valid or not. Because the end user-provided input the query behavior is changing, it is not checking username and password valid or not. If you change the behavior of the SQL query by adding a special character in end user-provided input this problem is known as SQL Injection attack.

Example of PreparedStatement Interface in Java:

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;
}
}
}
}

What is CallableStatement in JDBC?

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: The 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 the Callable Statement interface. If the Stored Procedure has an OUT parameter then to hold that output value we should register every OUT parameter by using the registerOutParameter() method of CallableStatement.

CallableStatement interface is better than Statement and PreparedStatement because it calls the stored procedure which is already compiled and stored in the database.

Example of Java Callable Statement Interface Which is Used to execute:

//Creating CallableStatement object 
CallableStatement GFG = con.prepareCall("{call anyProcedure(?, ?, ?)}");

//Use GFG.setter() methods to pass IN parameters

//Use GFG.registerOutParameter() method to register OUT parameters

//Executing the CallableStatement
GFG.execute();

//Use GFG.getter() methods to retrieve the result

Conclusion on statement preparedstatement and callablestatement in Java

Java Programming learners and experts can collect complete information about the difference between statement preparedstatement and callablestatement in Java from the above stuff. Grab the chance and improve your knowledge on differences between statement, the preparedstatement, and the callablestatement in java. Also, you can easily find the SQL queries, SQL statement, and how to use the database from our website @Btechgeeks.com