Statement + Prepared Statement + Callable Statement In Java

Hi All,

Greeting of the day!

Today we will be exploring Statement, PreparedStatment & CallableStatement in java.

So let's start.

Statement, PreparedStatment & CallableStatement all interface of java.sql

So when to use which of the above interfaces?

Statement -- is used when we want to execute static queries without any dynamic values.
PreparedStatement-- extends Statement and is basically used when we want to execute dynamic query/parameterize query in which we have to pass parameters at run time.

A prepared statement is used when we are executing the same query multiple times, it's recommended over Statement in such a scenario because the performance of PreparedStatement is high as a query is already precompiled a query plan is generated only once. After subsequent execution use the same query plan.

CallableStatement--extends PreparedStatment & is used to call stored procedures and functions.

Statement

How to execute a query using Statement in java?

We need to follow 3 steps.

Load driver & create an instance of Connection.
Get an instance of a Statement using Connection.
Execute the query using Statement.

Example

		//Load driver & create instance of Connection.
		
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_db", "admin", "admin");
		
		//Get instance of Statement using Connection.
		String sql = "Select * from RECIPE" ;
		Statement pstmt = connection.createStatement();
		
		//Execute query using Statement.
		ResultSet resultSet=pstmt.executeQuery(sql);

PreparedStatment

How to execute the query using PreparedStatment

We need to follow 4 steps.

Load driver & create an instance of Connection.
Create PreparedStatement instance & SQL Query.
Replace parameters with dynamic values.
Execute query.

Example

		//Load driver & create instance of Connection.
		
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_db", "admin", "admin");
		
		//Create PreparedStatement instance & SQL Query
		String sql = "UPDATE RECIPE SET RECIPE_NAME=?,RECIPE_PRICE=? where Recipe_Id=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		
		//Replace paramenters with dynamic values
		pstmt.setString(1, recipe.getRecipeName());
		pstmt.setInt(2, recipe.getRecipePrice());
		pstmt.setInt(3, recipe.getRecipeId());
		
		//Execute query
		pstmt.executeUpdate();

So here you can see we are placing? for input parameters and later we will set this parameter before executing the query.

CallableStatement

CallableStatement is used to execute stored procedures or functions. We can pass IN & OUT parameters in callable statements which are used in the procedure.

Let's take the example of the below procedure.

		
CREATE OR REPLACE PROCEDURE add_recipe
(recipe_name IN RECIPE.Recipe_Name%TYPE,
 recipe_price IN RECIPE.Recipe_Price%TYPE,
 result OUT VARCHAR2)
AS
BEGIN
  INSERT INTO recipe (Recipe_Name,Recipe_Price) 
  values (recipe_name,recipe_price);
  commit;
  
  result := 'SUCCESS';
  
EXCEPTION
  WHEN OTHERS THEN 
  result := 'ERROR';
  ROLLBACK;
END;

How to call stored procedure using CallableStatement in java

Below are the steps we need to follow for using CallableStatement.

Load driver & create an instance of Connection.
Create CallableStatement instance & with a call to the stored procedure.
Register IN & OUT parameters.
Execute call.
Fetch output parameter

So here we need to register 2 IN parameters & 1 OUT parameter. Below is code how to use CallableStatement to call the above procedure

		
		Connection connection = DatabaseUtil.getConnection();
		String sql = "{call add_recipe(?,?)}";
		CallableStatement callableStatement = connection.prepareCall(sql);
		callableStatement.setString(1, recipe.getRecipeName());
		callableStatement.setInt(2,recipe.getRecipeId());
		callableStatement.registerOutParameter(3, Types.VARCHAR);
		callableStatement.executeUpdate();
		
		//Get output parameter
		
		String result=callableStatement.getString(3);
		System.out.println("Output is "+result);

So we have explored all three interfaces. Let me know if you have any questions.

You can refer below useful post

Spring Boot REST API Export To Excel Using Apache POI

Spring Rest API + CRUD Example + Spring Boot + Oracle Database + Spring JPA

Spring MVC CRUD + Spring Boot + Oracle + Thymeleaf

Hibernate CRUD Example Using JSP & Servlet + MySQL + Maven

Java JDBC CRUD Example + Using JSP & Servlet + MySQL

Thanks

Happy Learning!