JDBC batch update with CallableStatement

By | July 18, 2018

In previous couple posts we learnt about JDBC batch update with Statement and  JDBC batch update with PreparedStatement

When you want to send a bunch of SQL queries to database in one go(one database call) to execute that’s what batch concept comes.
This reduces network traffic and improves performance of your application

In this post we will learn how to perform batch update using java.sql.CallableStatement

As you know that  java.sql.CallableStatement deals with stored procedure.
so Here we have one stored procedure updateSalById(IN sal DOUBLE,IN empId INT) which takes two input parameters first input parameter is the employee new salary which we want to update in database and second input parameter is the employee id whose salary we want to update.Here basically we will update more than one employees salary in single database call using Jdbc batch concept.

Project structure in eclipse:

DB Script to create employee_table and stored procedure(database schema jdbcdb)

DBUtil.java class which is responsible to connect with MySQL database.
this util class uses getConnection() method of java.sql.DriverManager class
public static Connection getConnection(String url,String user, String password) throws SQLException
This method takes three parameters database URL,username and password.
Here database username =”root”,password  = “root”  what password had supplied during MYSQL database installation time and finally database URL =“jdbc:mysql://localhost:3306/jdbcdb” Where jdbc is the API, mysql is the database, localhost is the server name on which mysql  database server is running, we may also use IP address instead machine name, 3306 is the port number and jdbcdb(Make you you have created this schema in MySQL database) is the database name. You may use any database name, in that case, You need to replace the jdbcdb with your database name.

Client program 

In this client program we have called stored procedure updateSalById(IN sal DOUBLE,IN empId INT) .Here basically we are going to update salary of more  than one employees based on his/her employee id.

Every time after setting employee new salary and id. which we have added in CallableStatement object(cs) by calling addBatch() method and finally when we call executeBatch() method after that all these updates will send to database in one go.

After running client program you will get below output on IDE console:

Before executing  ClientTest .java MYSQL database schema(jdbc) employee_table content

After executing  ClientTest .java MYSQL database schema(jdbc) employee_table content

You can see employee salary is updated from 70000 to 71000
But there is no employees with id 2 and 3 so there is no update for them.

That’s all for this topic JDBC batch update with CallableStatement.

Leave a Reply

Your email address will not be published. Required fields are marked *