In this article, we will use JDBC API to call stored procedure in MySQL database from Java application i.e.; using CallableStatement Interface
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.1 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
- Stored Procedure: spInsertNewPlayerRecord
use PLAYER_INFO; delimiter / CREATE PROCEDURE spInsertNewPlayerRecord( IN name VARCHAR(50), IN age INT, IN matches INT) BEGIN INSERT INTO PLAYER (NAME, AGE, MATCHES) VALUES (name, age, matches); END; / delimiter ;
3.2 Calling Stored Procedure using JDBC CallableStatement Interface
- As we are ready with required things to connect MySQL database from Java application
- Syntax: To invoke database stored procedure use below syntax
{call spInsertNewPlayerRecord(?, ?, ?)}
- Let us code a simple example using CallableStatement Interface
InvokeStoredProcedureUsingJDBCCallableStatement.java
package in.bench.resources.mysql.db.proc; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class InvokeStoredProcedureUsingJDBCCallableStatement { public static void main(String[] args) { // variables Connection connection = null; CallableStatement callableStatement = null; // Step 1: Loading or registering MySQL JDBC driver class try { Class.forName("com.mysql.jdbc.Driver"); } catch(ClassNotFoundException cnfex) { System.out.println("Problem in loading MySQL JDBC driver"); cnfex.printStackTrace(); } // Step 2: Opening database connection try { // Step 2.A: Create and get connection using DriverManager connection = DriverManager.getConnection( "jdbc:mysql://localhost:3306/PLAYER_INFO", "root", "root@123"); // Step 2.B: Creating JDBC CallableStatement callableStatement = connection .prepareCall("{call spInsertNewPlayerRecord(?, ?, ?)}"); // set values for new player record callableStatement.setString(1, "Michael Vaughan"); callableStatement.setInt(2, 41); callableStatement.setInt(3, 82); // Step 2.C: Executing CallableStatement int result = callableStatement.executeUpdate(); System.out.println(result + " indicates stored procedure execution is successful"); } catch(SQLException sqlex){ sqlex.printStackTrace(); } finally { // Step 3: Closing database connection try { if(null != connection) { // cleanup resources, once after processing callableStatement.close(); // and then finally close connection connection.close(); } } catch (SQLException sqlex) { sqlex.printStackTrace(); } } } }
Output:
1 indicates stored procedure execution is successful
3.3 Database Screen Capture:
4. Download:
Read Also:
- JDBC Tutorial index
- Introduction to JDBC
- Different types of JDBC drivers
- JDBC Driver list for all leading databases
- Basic components of JDBC
- JDBC connection steps
- MySQL database connection steps
- Oracle database connection steps
References:
- https://docs.oracle.com/javase/tutorial/jdbc/basics/storedprocedures.html
- https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-connect-drivermanager.html
- http://www.benchresources.net/spring-jdbc-introduction-and-jdbc-example-without-spring/
- https://docs.oracle.com/javase/tutorial/jdbc/basics/gettingstarted.html
- https://docs.oracle.com/cd/E11882_01/java.112/e16548/overvw.htm#JJDBC28025
- http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html
- https://en.wikipedia.org/wiki/JDBC_driver
- http://www.devx.com/tips/Tip/28818
- https://docs.oracle.com/javase/7/docs/api/java/lang/Class.html#forName(java.lang.String)
Happy Coding !!
Happy Learning !!