In this article, we will use JDBC API to prepare batch to execute database stored procedure in MySQL database from Java application (i.e.; using CallableStatement Interface)

Q) How to prepare and execute batch using JDBC API ?

To prepare batch: use addBatch(); method of CallableStatement which is inherited from java.sql.PreparedStatement

void addBatch() throws SQLException

To execute batch: use executeBatch(); method of CallableStatement which is inherited from java.sql.Statement

int[] executeBatch() throws SQLException

1. Pre-requisite:

Java JDK 1.8.0_77

MySQL database 5.5.16

Eclipse Luna IDE 4.4.0

mysql-connector-java-5.1.38.jar file

2. Database parameters:

Let us move on and code an example to connect MySQL database from Java application to invoke or call database stored procedure using JDBC API. But before that, we will list down required things to connect database

database server IP or address (localhost)

sever port (3306)

database name (PLAYER_INFO)

username (root)

password (root@123)

Note: All bold are database values to connect MySQL database

3. Stored Procedure

Set of DML statements like insert, update and delete with business logic completely on database side

With performance boost as these statements are pre-compiled

3.2 Advantages of using stored procedure

Increases performance, as these are pre-compiled

Faster execution

Business logic completely inside database with extra-level of security

If there are any changes in business logic, all changes need to be done at database end and not making Java code dirty