JDBC calls to the MySQL database stored procedure

First, the MySQL database stored procedures:

1. What is a stored procedure

The stored procedure (English: Stored Procedure) is a set of SQL statements written in a large database system to complete a particular function. The stored procedure is compiled and stored in the database, and the user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters).

2. What are the advantages and disadvantages of using stored procedures compared to general SQL statements?

Advantages:
1), reduce the execution of the script, shorten the time to obtain data. The stored procedure compiles only when it is created, executes directly when the call is used, does not need to be compiled again, and the general SQL statement needs to be compiled once before each execution, so the efficiency has no high storage process.
2), reduce network transmission volume, improve the transmission speed. The stored procedure is compiled and stored on the database server, when it is used, only the name of the stored procedure is specified and parameters are given (if the stored procedure has parameters), whereas a generic SQL statement needs to transfer the executed statement string to the database server side. Compared to the stored procedure, the length of the string transmitted to the database server is larger;
3), the security is relatively high. Assigning values to stored procedure parameters can only use the form of a question mark argument (which can be demonstrated by the following JDBC invocation example of a MySQL database stored procedure), which prevents SQL injection attacks, and general SQL statements can prevent SQL injection attacks, but are not required. Grant, deny, and revoke permissions can be applied to stored procedures, meaning that only certain users have access to the specified stored procedure;
Disadvantages:
1), if the use of a large number of stored procedures in a program system, when the application is delivered when the customer needs increase will lead to changes in data structure, followed by changes in the stored procedures, so that system maintenance will become more and more difficult and the cost will be greater.

3, how to create stored procedures and create stored procedures need to pay attention to the place

The stored procedure is created in the following format:

Create procedure stored procedure name ([[In | Out | INOUT] Parameter name Data class ...])
Begin
Stored Procedure Body
End

For a specific example of creating a stored procedure, see the following example of a JDBC call to a MySQL database stored procedure;

Note: See the following JDBC call to the MySQL database stored procedure example to create a note in a stored procedure statement;

Second, JDBC calls to the MySQL database stored procedure:

In order to more intuitively describe how JDBC implements the call to the MySQL database stored procedure, this is shown directly in the form of an example.

1. Stored procedures without any input and output parameters

1 Drop PROCEDURE if EXISTS jdbcprocnoinandout; 2 Create PROCEDURE jdbcprocnoinandout () 3 BEGIN 4     Select *  from test.test; 5 End;

Here is the Java code:

1Connection connectionnoinandout =myconnection.getconnection ();2String Jdbcprocnoinandout = "{call Jdbcprocnoinandout ()}";3CallableStatement csnoinandout =Connectionnoinandout.preparecall (jdbcprocnoinandout);4 Csnoinandout.execute ();5ResultSet rsnoinandout =Csnoinandout.getresultset ();6          while(Rsnoinandout.next ()) {7System.out.println ("Jdbcprocnoinandout:" +rsnoinandout.getstring ("id") + "--------" +rsnoinandout.getstring (" Value1 "));8         }9Myconnection.closeconnection (Connectionnoinandout, Csnoinandout, rsnoinandout);

2. Stored procedures with only two input parameters

1 Drop PROCEDURE if EXISTSJdbcprocallin;2 Create PROCEDUREJdbcprocallin (IDint, value1VARCHAR( -))3 BEGIN4     Insert  intoTest.testValues(id,value1);5     Select *  fromtest.test;6 End;

Java code:

1Connection Connectionallin =myconnection.getconnection ();2String Jdbcprocallin = "{call Jdbcprocallin (?,?)}";3CallableStatement Csallin =Connectionallin.preparecall (Jdbcprocallin);4Csallin.setint (1, 1);5Csallin.setstring (2, "ASDF");6 Csallin.execute ();7ResultSet Rsallin =Csallin.getresultset ();8          while(Rsallin.next ()) {9System.out.println ("Jdbcprocallin:" +rsallin.getstring ("id") + "--------" +rsallin.getstring ("value1"));Ten         } OneMyconnection.closeconnection (Connectionallin, Csallin, Rsallin);

3, an input parameter of an output parameter stored procedure

1 Drop PROCEDURE if EXISTSjdbcprocinandout;2 Create PROCEDUREJdbcprocinandout (inchIdVARCHAR( -), Out value1VARCHAR( -))3 BEGIN4     SetValue1=CONCAT ('I was:', id);5     Selectvalue1;6 End;

Java code:

1Connection connectioninandout =myconnection.getconnection ();2String Jdbcprocinandout = "{call Jdbcprocinandout (?,?)}";3CallableStatement csinandout =Connectioninandout.preparecall (jdbcprocinandout);4Csinandout.setstring (1, "123123");5Csinandout.registeroutparameter (2, Types.varchar);6 Csinandout.execute ();7ResultSet rsinandout =Csinandout.getresultset ();8          while(Rsinandout.next ()) {9System.out.println ("Jdbcprocinandout:" +csinandout.getstring ("value1"));Ten         } OneMyconnection.closeconnection (Connectioninandout, Csinandout, rsinandout);

4, two output parameters of the stored procedure

1 Drop PROCEDURE if EXISTSjdbcprocallout;2 Create PROCEDUREJdbcprocallout (out OutidVARCHAR( -), Out Outvalue1VARCHAR( -))3 BEGIN4     Select *  intoOutid,outvalue1 fromTest.test limit1;5     Selectoutid,outvalue1;6 End;

Java code:

1Connection connectionallout =myconnection.getconnection ();2String Jdbcprocallout = "{call Jdbcprocallout (?,?)}";3CallableStatement csallout =Connectionallout.preparecall (jdbcprocallout);4Csallout.registeroutparameter (1, Types.varchar);5Csallout.registeroutparameter (2, Types.varchar);6 Csallout.execute ();7ResultSet rsallout =Csallout.getresultset ();8          while(Rsallout.next ()) {9System.out.println ("Jdbcprocallout:" +csallout.getstring ("Outid") + "--------" +csallout.getstring ("Outvalue1"));Ten         } OneMyconnection.closeconnection (Connectionallout, Csallout, rsallout);

A stored procedure notation with output parameters is called in the database, for example, just two

1 Call Jdbcprocinandout ('lily',@value1); 2 Call Jdbcprocallout (@vid,@vvalue);
--The output parameter must be preceded by an "@" symbol, and the variable name can be arbitrarily written with a valid variable.

Attached MyConnection class

1  Packagenet.lily.test;2 3 ImportJava.sql.*;4 5  Public classmyconnection {6     7      Public StaticConnection getconnection () {8Connection Connection =NULL;9String url = "jdbc:mysql://127.0.0.1:3306/test?useunicode=true&characterencoding=utf-8& Useoldaliasmetadatabehavior=true ";TenString user = "root"; OneString pwd = "123456"; AString drivername = "Com.mysql.jdbc.Driver"; -         Try { - Class.forName (drivername); theConnection =drivermanager.getconnection (URL, user, pwd); -}Catch(ClassNotFoundException e) { - e.printstacktrace (); -}Catch(SQLException e) { + e.printstacktrace (); -         } +         returnconnection; A     } at  -      Public Static voidcloseconnection (Connection con, preparedstatement PS, ResultSet rs) { -         if(rs! =NULL) { -             Try { - rs.close (); -}Catch(SQLException e) { in e.printstacktrace (); -             } to         } +         if(PS! =NULL) { -             Try { the ps.close (); *}Catch(SQLException e) { $ e.printstacktrace ();Panax Notoginseng             } -         } the         if(Con! =NULL) { +             Try { A con.close (); the}Catch(SQLException e) { + e.printstacktrace (); -             } $         } $     } -}

JDBC calls to the MySQL database stored procedure