JDBC call to MySQL database Stored Procedures

I. MySQL database stored procedures:

1. What is a stored procedure?

Stored Procedure (Stored Procedure) is a set of SQL statements written to complete specific functions in a large database system. Stored procedures are compiled and stored in databases. You can specify the name of a stored procedure and provide parameters (if the stored procedure has parameters) to execute it.

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

Advantages:
1) reduces the execution of scripts and shortens the time for obtaining data. The stored procedure is compiled only when it is created. It is executed directly when it is called and used without re-compilation. Generally, the SQL statement needs to be compiled once before each execution, so the efficiency is not as high as the stored procedure;
2) reduces the amount of network transmission and increases the transmission speed. The stored procedure is stored on the database server after compilation. You only need to specify the name of the stored procedure and provide parameters (if the stored procedure has parameters; in general, SQL statements need to transmit the executed statement string to the database server, which is longer than the stored procedure to the database server;
3) high security. Assigning values to stored procedure parameters can only be passed through question marks (this can be reflected through the following JDBC call example for the MySQL database Stored Procedure), which can prevent SQL injection attacks; general SQL statements can also prevent SQL injection attacks, but they are not necessary. Grant, Deny, and Revoke permissions can be applied to stored procedures, that is, only some users can have the right to use the specified stored procedure;
Disadvantages:
1) if a large number of stored procedures are used in a program system, changes in the data structure will occur as the customer's requirements increase when the program is delivered and used, followed by modifications to the stored procedure, in this way, system maintenance becomes more and more difficult and the cost will increase.

3. Notes for creating a stored procedure and creating a stored procedure

The format for creating a stored procedure is:

Create procedure stored procedure name ([[IN | OUT | INOUT] Parameter Name Data class...])
Begin
Stored Procedure body
End

For details about how to create a stored procedure, see the following example of JDBC calling the MySQL database stored procedure;

Note: For more information about how to create a stored procedure statement in the example of JDBC calling the MySQL database stored procedure, see the comment below;

Ii. JDBC call to the MySQL database stored procedure:

To give a more intuitive introduction to how JDBC can call the MySQL database stored procedure, we will present it in the form of an example here.

1. No input or output parameters are stored.

Package com. ghj. packageoftest; import java. SQL. callableStatement; import java. SQL. connection; import java. SQL. resultSet; import java. SQL. SQLException; import com. ghj. packageoftool. linkDB;/* the script to be executed before running the program: DELIMITER $ // <span style = "font-family: Arial, Helvetica, sans-serif; "> there is either no space or only one space between DELIMITER and $. If there are multiple spaces, when executing the stored PROCEDURE creation statement, you will find that the creation cannot be successful. </span> create procedure noParam () beginselect avg (price) AS priceAvg FROM fruit; END $ DELIMITER; * // *** no input or output parameters Stored Procedure ** @ author GaoHuanjie */public class NoParam {public static void main (String args []) throws SQLException {Connection connection = LinkDB. getMySqlConnection (); String proStr = "{call noParam}"; CallableStatement callableStatement = connection.prepareCall(proStr);callableStatement.exe cute (); ResultSet resultSet = callableStatement. getResultSet (); while (resultSet. next () {System. out. println ("the average price of the product is:" + resultSet. getDouble ("priceAvg") + "");} LinkDB. close (connection, callableStatement, resultSet );}}

2. Stored Procedure with only two input parameters

Package com. ghj. packageoftest; import java. SQL. callableStatement; import java. SQL. connection; import java. SQL. resultSet; import java. SQL. SQLException; import com. ghj. packageoftool. linkDB;/* the script to be executed before running the program: DELIMITER $ create procedure inTwoParam (IN fruitName VARCHAR (12), IN fruitPrice DECIMAL () // description: the data type of the fruitPrice parameter is inconsistent with that of the price column (the price type is (8, 2). I should be aware of this phenomenon. BEGINSELECT * FROM fruit where name like concat ('%', fruitName, '%') AND price <fruitPrice; // Note: CONCAT ('%', fruitName, '% ') cannot be '%' + fruitName + '%' END $ DELIMITER; * // *** stored procedure with only two input parameters ** @ author GaoHuanjie */public class InTwoParam {public static void main (String args []) throws SQLException {Connection connection = LinkDB. getMySqlConnection (); String procStr = "{call inTwoParam (?,?)} "; CallableStatement callableStatement = connection. prepareCall (procStr); callableStatement. setString (1, "Lian"); callableStatement. setDouble (2, 88.88); // you must use the setDouble method to set the value of a DECIMAL type attribute. CallableStatement.exe cute (); ResultSet resultSet = callableStatement. getResultSet (); System. out. println ("Fruits whose names contain the word 'loan' and whose prices are less than 88.88 RMB include:"); while (resultSet. next () {System. err. println ("name:" + resultSet. getString ("name") + ", price:" + resultSet. getDouble ("price") + "Yuan" + ", origin:" + resultSet. getString ("address");} LinkDB. close (connection, callableStatement, resultSet );}}

3. Stored Procedure with only two output parameters

Package com. ghj. packageoftest; import java. SQL. callableStatement; import java. SQL. connection; import java. SQL. SQLException; import java. SQL. types; import com. ghj. packageoftool. linkDB;/* the script to be executed before running the program: DELIMITER $ create procedure outTwoParam (OUT fruitName VARCHAR (12), OUT fruitPrice DECIMAL (5, 3 )) BEGINSELECT name INTO fruitName FROM fruit WHERE name = 'lian Fu'; SELECT price INTO fruitPrice FROM fruit where name = 'lian Fog '; END $ DELIMITER; note: the preceding two query statements cannot combine an SQL statement -- SELECT NAME INTO fruitName, price INTO fruitPrice FROM fruit WHERE NAME = 'lian Fu '; * // *** stored procedure with only two output parameters ** @ author GaoHuanjie */public class OutTwoParam {public static void main (String args []) throws SQLException {Connection connection = LinkDB. getMySqlConnection (); String proStr = "{call outTwoParam (?,?)} "; CallableStatement callableStatement = connection. prepareCall (proStr); callableStatement. registerOutParameter (1, Types. VARCHAR); callableStatement. registerOutParameter (2, types.decimal?callablestatement.exe cute (); String fruitName = callableStatement. getString (1); double fruitPrice = callableStatement. getDouble (2); // use the getDouble method to obtain DECIMAL type attributes. System. out. println ("fruit name:" + fruitName + ", fruit price:" + fruitPrice + "Yuan"); LinkDB. close (connection, callableStatement, null );}}

4. Stored Procedure containing an input parameter and an output parameter

Package com. ghj. packageoftest; import java. SQL. callableStatement; import java. SQL. connection; import java. SQL. SQLException; import java. SQL. types; import com. ghj. packageoftool. linkDB;/* the script to be executed before running the program: DELIMITER $ create procedure inOneParamAndOutOneParam (IN fruitName VARCHAR (12), OUT fruitPrice DECIMAL )) BEGINSELECT price FROM fruit where name = fruitName INTO fruitPrice; END $ DELIMITER; * // *** contains an input parameter and The stored procedure of an output parameter ** @ author GaoHuanjie */public class InOneParamAndOutOneParam {public static void main (String args []) throws SQLException {Connection connection = LinkDB. getMySqlConnection (); CallableStatement callableStatement = null; String procStr = "{call inOneParamAndOutOneParam (?,?)} "; CallableStatement = connection. prepareCall (procStr); String fruitName = "Lian Wu"; callableStatement. setString (1, fruitName); callableStatement. registerOutParameter (2, types.decimal?callablestatement.exe cute (); double fruitPrice = callableStatement. getDouble (2); // use the getDouble method to obtain DECIMAL type attributes. System. out. println (fruitName + "Price:" + fruitPrice + "Yuan"); LinkDB. close (connection, callableStatement, null );}}

5. input parameters are stored as output parameters.

Package com. ghj. packageoftest; import java. SQL. *; import com. ghj. packageoftool. linkDB;/* the script to be executed before running the program: DELIMITER $ create procedure inOneParamISOutOneParam (INOUT fruitName VARCHAR (12 )) beginselect name into fruitName FROM fruit where name like concat ('%', fruitName, '%') LIMIT 0, 1; END $ DELIMITER; or DELIMITER $ create procedure inOneParamISOutOneParam (INOUT fruitName VARCHAR (12) beginselect name from fr Uit where name like concat ('%', fruitName, '%') LIMIT 0, 1 INTO fruitName; END $ DELIMITER; note that the preceding query statement cannot be written LIKE this: select name from fruit where name like concat ('%', fruitName, '%') INTO fruitName LIMIT 0, 1; note: compare the locations of the "INTO" keyword in the creation of Stored Procedure scripts in the 3, 4, and 5 java files. You must have a deep harvest. * // *** Input parameter refers to the stored procedure of the output parameter ** @ author GaoHuanjie */public class InOneParamISOutOneParam {public static void main (String args []) throws SQLException {Connection con = LinkDB. getMySqlConnection (); CallableStatement callableStatement = null; String procStr = "{call inOneParamISOutOneParam (?)} "; CallableStatement = con. prepareCall (procStr); callableStatement. setString (1, "Lian"); callableStatement. registerOutParameter (1, Types.VARCHAR);callableStatement.exe cute (); String fruitName = callableStatement. getString (1); System. out. println ("the name of the fruit in the table containing the word 'loan' is:" + fruitName); LinkDB. close (con, callableStatement, null );}}

Note:

1. if you copy the above Code, you will find that the LinkDB class is missing and the class is pasted:

Package com. ghj. packageoftool; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. SQLException;/*** connect to database ** @ author GaoHuanjie */public class LinkDB {/*** function: obtain the Connection ** @ author GaoHuanjie */public static Connection getMySqlConnection () {connection Connection = null; String url = "jdbc: MySql: // localhost: 3306/tes T "; String user =" root "; String pwd =" "; String driverName =" com. mysql. jdbc. driver "; try {Class. forName (driverName); connection = DriverManager. getConnection (url, user, pwd);} catch (ClassNotFoundException e) {e. printStackTrace ();} catch (SQLException e) {e. printStackTrace ();} return connection;}/*** close releasing all resources ** @ author GaoHuanjie */public static void close (Connection con, PreparedStatement ps, R EsultSet rs) {if (rs! = Null) {try {rs. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (ps! = Null) {try {ps. close () ;}catch (SQLException e) {e. printStackTrace () ;}} if (con! = Null) {try {con. close () ;}catch (SQLException e) {e. printStackTrace ();}}}}

2. If you execute the SQL script used to create the stored procedure, you will find that the fruit class is missing and the SQL statement used to create the table is displayed:

Create database 'test'; USE 'test'; create table 'failed' ('id' char (36) not null comment' id', 'name' varchar (12) not null comment 'name', 'price' decimal (300) not null comment 'unit price', 'address' varchar () default null comment' origin ', primary key ('id') ENGINE = InnoDB default charset = utf8 COMMENT = 'fruit table'; insert into 'fruit' ('id', 'name', 'price ', 'address') values ('27640c30-8df5-4cf2-916e-c28e0b2b1b52 ', 'bambo', '24. 45', 'Malaysia '), ('46ac8392-9922-4593-89a3-517a9e516733', 'pineapple ', '19. 41 ', 'Brazil'), ('63061a9f-3a0e-4140-98e0-8b1e13e4eab3 ', 'cantalou', '17. 77 ', 'China'), ('7ef0c286-b8b1-4e1e-9a8a-36bce703cf18', 'avocado ', '30. 80', 'Mexico '), ('a1cf5379- 9311-4c7f-be10-0000d8c16291', 'Raspberry ', '123. 50', 'Swiss '), ('csf-aed0-a39a-49c5-91ee-7fc0579ddb20', 'lian Fu', '77. 33 ', 'indonesia'), ('e8068fa1-a8e7-4025-89e2-36c1d5d23c74 ', 'durian', '16. 50', 'thailand ');

[Download demo resources with 0 points]