Java Basic Series 12: Calling stored procedures in the database using the CallableStatement interface

Preface: The following are examples of stored procedures in MySQL

Introduction to a Stored procedure

The SQL statements that we commonly use to manipulate database languages need to be compiled and executed at the time of execution, while stored procedures (Stored Procedure) are sets of SQL statements that are compiled for specific functions and stored in the database after compilation. The user invokes execution by specifying the name of the stored procedure and the given parameter (if the stored procedure has parameters).

A stored procedure is a programmable function that is created and saved in the database. It can consist of SQL statements and some special control structures. Stored procedures are useful when you want to perform the same functions on different applications or platforms, or encapsulate specific functionality. Stored procedures in a database can be seen as simulations of object-oriented methods in programming. It allows control over how data is accessed.

stored procedures often have the following advantages:
i) stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written with flow control statements, with very high flexibility,complex judgments and more complex operations can be accomplished .。
II)Stored procedures allow standard components to be programmed. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement for the stored procedure. AndDatabase Professionals can modify stored procedures at any time and have no impact on application source code。
III)stored procedures enable faster execution。 If an operation contains a large number of Transaction-sql code or is executed more than once, the stored procedure is much faster than the batch execution. Because the stored procedure is precompiled. When you run a stored procedure for the first time, the optimizer optimizes it for analysis and gives the execution plan that is ultimately stored in the system table. The batch TRANSACTION-SQL statements are compiled and optimized each time they are run, relatively slowly.
IV)Stored procedures can reduce network traffic. For operations on the same database object, such as queries, modifications, if the TRANSACTION-SQL statement involved in this operation is an organized stored procedure, when the stored procedure is called on the client computer, only the calling statement is transmitted on the network, which greatly increases network traffic and reduces network load.
v)Stored procedures can be used as a security mechanism to make full use of them. The system administrator restricts the access to the corresponding data by executing the permission of a stored procedure, avoids the unauthorized user's access to the data, and ensures the security of the data.

(PS: The above introduction refer to: http://www.jb51.net/article/30825.htm)

Simple use of two stored procedures

(1) Format:

Format created by the MySQL stored procedure: Create PROCEDURE procedure name ([process parameters [,...]])
[Features ...] Process Body

(2) Create a stored procedure MyProc and test it:

Mysql> delimiter // create procedure myproc (IN P1 INT,INOUT P2  int,out p3 int) Begin select p1,p2,p3; set p1 = 10; set  p2 = 20;  SET p3 = 30; end//query ok, 0 rows affectedmysql> set  @P1  = 11//query ok,  0 rows affectedmysql> SET  @P2  = 22//query ok, 0 rows  affectedmysql> call myproc (@P1, @P2, @P3)//+----+----+------+| p1 | p2 |  p3   |+----+----+------+| 11 | 22 | null |+----+----+------ +1 row in setquery ok, 0 rows affectedmysql> select  @P1, @P2, @ p3//+-----+-----+-----+|  @P1  |  @P2  |  @P3  |+-----+-----+-----+|  11  |  20 |  30 |+-----+-----+-----+1 row&Nbsp;in setmysql> 

Note:

i): The default statement terminator in MySQL is a semicolon (;), and the SQL statement in the stored procedure needs to end with a semicolon. So in order to avoid conflicts, first use "DELIMITER//" to set MySQL terminator to "//"

II) Three variables are defined in the stored procedure "MyProc" above, using the three types of in, InOut, and out, respectively, stating that the meanings are:

In input parameter: The value that represents the parameter must be specified when the stored procedure is called, and the value of the parameter modified in the stored procedure cannot be returned as the default value

out output parameter: This value can be changed inside the stored procedure and can be returned

inout input and OUTPUT parameters: specified on invocation, and can be changed and returned  
III) "SET @P1 = 11" This form indicates that a user variable named "@P1" is defined, while "select P1,p2,p3; "Represents the output P1, P2. The value of P3 after executing "call MyProc (@P1, @P2, @P3)" is null because P3 has not been assigned at the time of the output; "Select @P1, @P2, @P3;" , you can find The value of the @P1 is not changed because the in type simply passes the value into the stored procedure, The value of @P2 and @p3 is changed because it has been re-assigned in the stored procedure

three manipulating stored procedures using the CallableStatement interface

callablestatement is primarily calling stored procedures in the database, using callablestatement can receive the return value of the procedure, the sample code is as follows:

package javase.jdbc;import java.sql.callablestatement;import java.sql.connection;import  java.sql.sqlexception;import java.sql.types;public class procdemo {/** *  JDBC call to MySQL stored procedure example  * */public static void main (String[] args)  {/** *  delimiter //    create procedure myproc (IN p1 int,INOUT  p2 int,out p3 int)    BEGIN   SELECT p1,p2,p3;    set p1 = 10;   set p2 = 20;   set  p3 = 30;   end   // *  * */string sql  =  "{Call myproc (?,?,?)}";   //Call stored procedure myprocconnection connection = jdbcconnection.getconnection (); try { Callablestatement cstatement = connection.preparecall (SQL); Cstatement.setint (1, 11); cStatEment.setint (2, 22); Cstatement.registeroutparameter (2, types.integer);   // Sets the return value type Cstatement.registeroutparameter (3, types.integer);   //sets the return value type Cstatement.execute ();   //executes the stored procedure System.out.println ("InOut return value: "  + cstatement.getint ("P2")); System.out.println ("Out return value: "  + cstatement.getint ("P3")); Cstatement.close (); Connection.close () ;}  catch  (sqlexception e)  {e.printstacktrace ();}}

Output:

return value of InOut: 20OUT return Value: 30

This article is from "Zifangsky's personal blog" blog, make sure to keep this source http://983836259.blog.51cto.com/7311475/1762942

Java Basic Series 12: Calling stored procedures in the database using the CallableStatement interface