Original Link: http://www.yiidian.com/jdbc/jdbc-callablestatement.html

1 CallableStatement interface

The CallableStatement interface is used to call stored procedures and functions.

By using stored procedures and functions, we can write business logic on the database, which will improve performance because they are precompiled.

2 What is the difference between stored procedures and functions?

3 Get the CallableStatement object

The prepareCall() method of the Connection interface returns a CallableStatement object.The syntax is as follows:

public CallableStatement prepareCall("{ call procedurename(?,?...?)}");

An example code to get the CallableStatement object is given below:

CallableStatement stmt=con.prepareCall("{call myprocedure(?,?)}");

The code above calls a stored procedure that receives two parameters, possibly input or output: myprocedure.

4 Examples of calling stored procedures

4.1 Create Stored Procedures

In the MySQL test database, execute the following SQL to create the pro_insert_user stored procedure:

DELIMITER $$
CREATE PROCEDURE pro_insert_user(username VARCHAR(50),PASSWORD VARCHAR(50))
BEGIN

INSERT INTO t_user(username,PASSWORD) VALUES(username,PASSWORD); 

END $$

The table structure of the t_user table involved in the stored procedure is as follows:

CREATE TABLE `t_user` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `username` varchar(50) DEFAULT NULL,
   `password` varchar(50) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

4.2 Write sample code

CallProDemo:

package com.yiidian;

import java.io.*;
import java.sql.*;

/**
 * A little tutorial web - http://www.yiidian.com
 */
public class CallProDemo {
    public static void main(String args[])throws Exception {
        try {
            Class.forName("com.mysql.jdbc.Driver");

            Connection con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test", "root", "root");

            CallableStatement stmt=con.prepareCall("{call pro_insert_user(?,?)}");
            stmt.setString(1,"mark");
            stmt.setString(2,"123");
            stmt.execute();

            System.out.println("Stored procedure call succeeded!");

            con.close();
        }catch(Exception e){
            System.out.println(e);
        }
    }
}

4.3 Running tests

After executing the program, check to see if there is one more record in the t_user table:

5 Examples of calling functions

In the following example, we call the pro_sum function, which takes two input parameters and returns the sum of the given numbers.Here, we use the registerOutParameter() method of the CallableStatement interface, which registers output parameters with their corresponding types.It provides CallableStatement with information about the type of result displayed.

The Types class defines many constants such as INTEGER, VARCHAR, FLOAT, DOUBLE, BLOB, CLOB, and so on.

5.1 Create Stored Procedures

In the MySQL test database, execute the following SQL to create the fun_sum function:

DELIMITER $$
CREATE FUNCTION fun_sum(n1 INT,n2 INT)
RETURNS INT
BEGIN

DECLARE total INT;  
SET total = n1+n2;  
RETURN total;

END $$

5.2 Write sample code

CallFunDemo:

package com.yiidian;

import java.io.*;
import java.sql.*;

/**
 * A little tutorial web - http://www.yiidian.com
 */
public class CallFunDemo {
    public static void main(String args[])throws Exception {
        try {
            Class.forName("com.mysql.jdbc.Driver");

            Connection con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test", "root", "root");

            CallableStatement stmt=con.prepareCall("{?= call fun_sum(?,?)}");
            stmt.setInt(2,10);
            stmt.setInt(3,43);
            stmt.registerOutParameter(1,Types.INTEGER);
            stmt.execute();

            System.out.println(stmt.getInt(1));

            con.close();
        }catch(Exception e){
            System.out.println(e);
        }
    }
}

5.3 Running tests