JDBC advanced Stored Procedure + callablestatement

1. What is stored procedure

First, we will introduce the database storageProgramThe database storage program is also known as a storage module-a computer program stored and executed by the database server (with a series of different names ).Source code(Sometimes) it may be that the binary compiled version almost always occupies the tablespace of the database server system, and the program is always executed in the memory address of the process or thread on the database server.

There are three types of database storage programs, and the stored procedures described in this article are one of them, and the other two are storage functions and triggers.

Stored procedures are the most common storage programs. stored procedures are program units that can accept multiple input and output parameters and be executed upon requests. It is written in advance using SQL statements, stored with a specified name, compiled and stored in the database. To use a stored procedure, you only need to pass in the name of the specified stored procedure and give a parameter (if it is a stored procedure with a parameter) to execute it.

Ii. Advantages of Stored Procedures

1. Database execution speed can be improved: The stored procedure can contain multiple execution commands and can be used only once compiled. If commands in the stored procedure are divided into SQL statements, compilation and execution are required. This takes a lot of time and slows down the program, reduces Program performance;

2. Convenient for developers:It can be reused only once written in the stored procedure, which greatly reduces the workload of developers.

3. High Security:Stored Procedures can pass in parameters when calling. Therefore, you can limit whether a person can call the stored procedure based on the input parameters to improve data security.

3. Create a stored procedure

Stored procedures can be divided into four types: non-parameter stored procedures, input-value stored procedures, output-value stored procedures, and stored procedures with both input parameters and output values. For more information about how to create a stored procedure, see MySQL Stored Procedure programming (e-book download ). The following uses the previousArticleThe database in JDBC connection to MySQL database and example is used as a basis to demonstrate the creation of a stored procedure in the staff table using a specific instance.

1. How to Create a village error:You can use the create procedure, create function, or create trigger statement to create a bucket. You can directly input these statements into the MySQL command line, but this is not practical for the general storage program size, therefore, we recommend that you use the text editor to create a text file to accommodate our storage program. Then we can use the command line client and other tools to submit this file. We will use MySQL query browser as the text editor. If you don't have this tool, you can find it in the resources uploaded in my space (http://download.csdn.net/detail/cxwen78/3681275 ). You can also use the editor on any operating system, such
VI, emacs, or notepad. Of course, we like MySQL query browser because it has built-in help systems, syntax highlighting, SQL statement execution capabilities, and other functions.

2. Create a stored procedure in MySQL query Browser:Download MySQL query browser and run it after installation. Find the "myuser" database in the schemata view, right-click the "staff" table, and choose create new procedure. In the displayed dialog box, enter the name of the stored procedure (for example, addstaff)> click Create procedure to create a stored procedure:

3. Write the stored procedure: After creation, a stored procedure template is displayed. we modify the template as follows:

 
Delimiter $ drop procedure if exists 'myuser '. 'addstaff' $ create procedure 'myuser '. 'addstaff' (in sname varchar (50), in age int, in sex varchar (5), in address varchar (50), In depart varchar (50), In worklen int, in wage int, out Sid INT) Begin insert into staff (name, age, sex, address, depart, worklen, wage) values (sname, age, sex, address, depart, worklen, wage); select last_insert_id () into Sid; end $ delimiter;
 

CodeExplanation:

1) . Delimiter $: defines the delimiter as "$". In this way, ";" is no longer the statement Terminator, but "$ "; 2) . Drop procedure if exists 'myuser'. 'addstaff' $: determines if the Stored Procedure addstaff already exists. Delete it; 3) . Create procedure 'myuser '. 'addstaff' (in sname varchar (50), in age int, in sex varchar (5), in address varchar (50), In depart varchar (50), In worklen int, in wage int, out Sid INT): this is a process with input parameters and output parameters. Myuse: Database Name; addstaff: name of the stored procedure;
In sname varchar (50): In indicates that the "sname" parameter is an input parameter, and varchar (50) indicates a value type, similar to int. The first seven parameters are input parameters; out Sid INT: Out indicates that the "Sid" parameter is an output parameter, and INT indicates the value type; 4) . Begin and end are SQL statement sets. 5) . Insert into staff (name, age, sex, address, depart, worklen, wage) values (sname, age, sex, address, depart, worklen, wage);: Insert a record, the value of each field is recorded by the input parameter, that is, the field name is in the first square brackets, and the second square brackets must be the value of the input parameter; the compilation and execution process code: press the execute button to execute the stored procedure. If an error occurs in your code, the query browser will display an error at the bottom and highlight the row in which the error occurs. Otherwise, on the left-side schemata tab, you will find that your stored procedure has been successfully created. :

4. Call the Stored Procedure1. Create a new class, such as proceduretest. The implementation of the class is as follows. The statement used to execute SQL statements is callablestatement, which is an interface specifically used to execute SQL stored procedures.

Package COM. serein. JDBC; import Java. SQL. *; import com.sun.org. apache. xalan. internal. TC. compiler. util. type; public class proceduretest {// database connection public static connection getconnection () {connection conn = NULL; try {class. forname ("com. mySQL. JDBC. driver "); // load Mysql Data driver conn = drivermanager. getconnection ("JDBC: mysql: // localhost: 3306/myuser", "root", "root"); // create a data connection} catch (exception e) {system. out. Println ("database connection failed") ;}return conn ;}// list all stored procedure names in the database public static void get_allproname (connection con) {try {databasemetadata MD = con. getmetadata (); // obtain the database metadata resultset = md. getprocedures (null, null, "%"); // obtain the description of all stored procedures system. out. println ("database's existing stored procedure name:"); // display the stored procedure name, which is located in the Third Field of the result set while (resultset. next () {string procname = resultset. getstring (3); system. out. print (procname + "\ n");} system. Out. println ();} catch (sqlexception e) {e. printstacktrace () ;}/// * // call the Stored Procedure public static void call_procedure (connection con) throws exception {callablestatement CST = NULL; // callablestatement is a subclass of statement system. out. println ("start to execute stored procedures"); try {// call a stored procedure with no parameters CST = con. preparecall ("{call addstaff (?,?,?,?,?,?,?,?)} "); // 8? ID as a placeholder // set the input parameter value Cst. setstring (1, "Tina"); Cst. setint (2, 23); Cst. setstring (3, "W"); Cst. setstring (4, "Shanghai"); Cst. setstring (5, "Personnel"); Cst. setint (6, 1); Cst. setint (7, 3000); Cst. registeroutparameter (8, type. internal); // The registration output contains cst.exe cute (); // execute int insertid = Cst. getint (8); // obtain the output parameter value system. out. println ("the last staff ID is:" + insertid); // print the input parameter} catch (sqlexception e) {e. printstacktrace ();} finally {Cst. close ();} system. out. println ("stored procedure execution ended");} public static void main (string [] ARGs) throws exception {connection conn = NULL; try {conn = getconnection (); // get the database connection get_allproname (conn); // list all the Stored Procedure names of the database call_procedure (conn); // call the Stored Procedure} catch (exception E1) {Throw E1 ;} finally {Conn. close (); // close the database connection }}}

2. Running and Result

Run the result displayed on the "console": view the "staff" table in the database and insert it successfully: