The usage of a CallableStatement is very similar to that of a PreparedStatement, except that a CallableStatement can be used to call a stored procedure.

Introduction to stored procedures Introduction to calls

SQL statements need to be compiled and then executed, while Stored Procedure is a set of SQL statements to complete a specific function. After compilation, Stored Procedure is Stored in the database. Users can call and execute the Stored Procedure by specifying its name and given parameters (if the Stored Procedure has any parameters).

Stored procedures are programmable functions that are created and stored in a database and can consist of SQL statements and control structures. Stored procedures are useful when you want to execute the same function on different applications or platforms, or encapsulate specific functionality. A stored procedure in a database can be thought of as a simulation of an object-oriented approach to programming that allows control over how data is accessed.

##### Benefits of stored procedures:

(1). Enhance the function and flexibility of SQL language: stored procedures can be written with control statements, which have strong flexibility and can complete complex judgments and complex operations.

(2). Standard component programming: after a stored procedure is created, it can be called many times in the program without having to rewrite the SQL statement of the stored procedure. Moreover, database professionals can modify stored procedures at any time without affecting the application source code.

(3) faster execution: If an operation involves a large amount of transaction-SQL code or is executed multiple times, stored procedures can be executed much faster than batch processes. Because stored procedures are precompiled. Queried when a stored procedure is run for the first time, the optimizer analyzes and optimizes it, and gives an execution plan that is eventually stored in system tables. Batch transaction-SQL statements, which are compiled and optimized each time they are run, are slower.

(4) Reduce network traffic: If the transaction-SQL statements involved in operations on the same database object (such as queries, modifications) are organized into stored procedures, then when the stored procedure is called on the customer’s computer, only the call statement is transmitted over the network, greatly reducing network traffic and reducing network load.

(5). Make full use of it as a security mechanism: By limiting the permission to execute a stored procedure, it can limit the access permission to the corresponding data, avoid the access of unauthorized users to the data, and ensure the data security.

Stored procedures are one of the most important features of a database. MySQL 5.0 did not support stored procedures, which made MySQL a very difficult application. Fortunately, MySQL 5.0 began to support stored procedures, which can greatly improve the processing speed of the database, and also improve the flexibility of database programming.

Create a MySQL stored procedure

DELIMITER //
create procedure findById(IN pid INTEGER)
BEGIN
SELECT * FROM `user` WHERE id= pid;
END //
DELIMITER;
Copy the code

###2. Call stored procedures

package com.jdbc;

import java.sql.*;

/**
 * Created by Fant.J.
 * 2018/3/5 20:14
 */
public class CallableStatementTest {
    static String url = "jdbc:mysql://localhost:3306/user";
    static String user = "root";
    static String password = "root";

    public static void main(String[] args) {


        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(url, user, password);
            String sql = "CALL findById(?) "; CallableStatement stmt = connection.prepareCall(sql); STMT. SetInt (1, 27); ResultSet resultSet = stmt.executeQuery();while (resultSet.next()){
                int id = resultSet.getInt("id");
                String username = resultSet.getString("username");
                String birthday = resultSet.getString("birthday");
                String sex = resultSet.getString("sex");
                String address = resultSet.getString("address");

                System.out.println("" + username + "" + birthday + "" + sex
                        + ""+ address); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }}}Copy the code
Fant.J reUseTest 2017-04-20 Male XXXXCopy the code

I’m sure you already know what it is to call a stored procedure, but you can see that this example is a special case, so I’ll explain the SQL in calling stored code in detail.

##### Details about stored procedure parameters

  • In: pass a parameter into the process. (Refer to the code I created IN pid INTEGER)
  • Out: passes parameters out of a process.
  • Inout: in and out

Compared with Oracle database, MySQL stored procedures are relatively weak and rarely used.

The created stored procedures are stored in the database’s data dictionary.