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 specific function. Stored procedures are compiled and stored in the database. The user runs it by specifying the name of the stored procedure and giving the number of parameters (assuming that the stored procedure has a reference).

2, compared with the general SQL statement. What are the advantages of using stored procedures? What are the drawbacks?

Strengths:
1), reduce the operation of the script, shorten the time to obtain data. Stored procedures are compiled only when they are created, and run directly when the call is used. There is no need to compile again, and the general SQL statement needs to be compiled one time before each run, so the efficiency is no higher than the stored procedure.
2), reduce network transmission volume, improve the transmission speed.

The stored procedure is compiled and stored on the database server, and is used only when the name of the stored procedure is specified and the parameter is given (assuming that the stored procedure has a reference), whereas a generic SQL statement is required to transfer the statement string that is running to the database server side. Compared to the stored procedure, the length of the string transmitted to the database server is relatively large;
3), the security is relatively high. Assigning values to stored procedure parameters can only be used in the form of a question mark (which can be demonstrated by the following JDBC sample invocation of the MySQL database stored procedure ), which prevents SQL injection attacks, and general SQL statements can prevent SQL injection attacks. But it's not necessary. The ability to apply grant, DENY, and revoke permissions to a stored procedure, meaning that only certain users have access to the specified stored procedure;
Disadvantages:
1), assume that in a program system, a large number of use of stored procedures, when the application delivery when the use of customer requirements will lead to changes in the data structure, followed by changes in the stored procedures. This will make system maintenance more difficult and costly.

3. How to create a stored procedure and create a stored procedure where you need to be aware

The stored procedure is created in the following format:

& nbsp       create Procedure Stored procedure name ([[In | Out | INOUT] "parameter data class ...])
        begin
         stored procedure body
        end

A detailed sample of creating a stored procedure is shown in the following JDBC sample invocation of the MySQL database stored procedure.

Note: see the following JDBC call to the MySQL database stored procedure in the sample create stored procedure statement in the gaze;

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, it is shown directly in the sample form.

1, no no matter what input and output parameters of the stored procedures

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;/* script to run before running this program: DELIMITER $$// <span style= "font-family:arial, Helvetica, Sans-serif;" There is either no space between >delimiter and $$, or just a space, assuming that there are multiple spaces, you will find that you cannot create a successful </span>create PROCEDURE Noparam () When you run the Create stored procedure statement. Beginselect AVG (Price) as priceavg from fruit; end$ $DELIMITER; *//** * No matter what input and output parameters stored procedures * * @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.execute (); ResultSet ResultSet = Callablestatement.getresultset (); while (Resultset.next ()) {System.out.println ("Average price of product is:" + Resultset.getdouble ("Priceavg") + "Yuan");} Linkdb.close (Connection, CallableStatement, ResultSet);}}

2, only two input parameters of the stored procedure

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;/* script to execute before executing this program: DELIMITER $$ CREATE PROCEDURE Intwoparam (in Fruitname VARCHAR (a), in Fruitprice DECIMAL (9,2))// Note: The data type of the Fruitprice parameter is inconsistent with the data type of the price column (the type of Price is (8,2)), which should be a sentiment. Beginselect * from fruit WHERE NAME like CONCAT ('% ', fruitname, '% ') and price < fruitprice;//Note: CONCAT ('% ', fruitname, '% ') cannot be '% ' +fruitname+ '% ' end$ $DELIMITER; *//** * Only two input parameters stored procedure * * @author Gaohuanjie */public class Intwoparam {public STA tic void Main (String args[]) throws SQLException {Connection Connection = linkdb.getmysqlconnection (); String Procstr = "{Call Intwoparam (?
,?)}"; CallableStatement callablestatement = Connection.preparecall (PROCSTR); callablestatement.setstring (1, "Lotus"); Callablestatement.setdouble (2, 88.88);//The SetDouble method is used to set the value of the decimal type. Callablestatement.execute (); ResultSet ResultSet = Callablestatement.getresultset (); System.out.println ("The name includes ' Lotus ' character and the price is less than 88.88 Yuan fruit:"); while (Resultset.next ()) {System.err.println ("Name:" + Resultset.getstring ("name") + ", Price:" + resultset.getdouble ("prices") + "Yuan" + ", Origin:" + resultset.getstring ("Address");} Linkdb.close (Connection, CallableStatement, ResultSet);}}

3, only two output parameters of the stored procedure

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;/* script to execute before executing this program: DELIMITER $ $CREATE PROCEDURE Outtwoparam (out Fruitname VARCHAR (in), Out Fruitprice DECIMAL (5,3)) Beginselect name to Fruitname from fruit WH ERE name= ' Lotus fog '; SELECT price to Fruitprice from fruit WHERE name= ' lotus fog '; END $ $DELIMITER; Note: The above two query statements cannot synthesize an SQL statement--select the NAME into Fruitname, the price into the fruitprice from fruit WHERE name= ' lotus fog '; *//* * * Only two output parameters stored procedure * * @author Gaohuanjie */public class Outtwoparam {public static void main (String args[]) throws Sqlex ception {Connection Connection = linkdb.getmysqlconnection (); String Prostr = "{Call Outtwoparam (?
,?)}"; CallableStatement callablestatement = Connection.preparecall (PROSTR); Callablestatement.registeroutparameter (1, Types.varchar); Callablestatement.registeroutparameter (2, Types.decimal); Callablestatement.execute (); String fruitname = callablestatement.getstring (1);d ouble fruitprice = callablestatement.getdouble (2);// Gets the attribute of type decimal to use the GetDouble method.
System.out.println ("Fruit name:" + fruitname + ", fruit Price:" + Fruitprice + "Yuan"); Linkdb.close (connection, callablestatement, null);}}

4. A 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;/* script to execute before executing this program: DELIMITER $ $CREATE PROCEDURE Inoneparamandoutoneparam (in Fruitname VARCHAR (a), out Fruitprice DECIMAL (7,3)) Beginselect price from fruit WHERE name=fruitname into Fruitprice; END $ $DELIMITER; * */** * contains an input parameter and an output parameter stored procedure * * @author Gaohuanjie */public class Inoneparamandoutoneparam {public STA tic void Main (String args[]) throws SQLException {Connection connection=linkdb.getmysqlconnection (); CallableStatement Callablestatement=null; String procstr= "{Call Inoneparamandoutoneparam (?
,?)}"; Callablestatement=connection.preparecall (PROCSTR); String fruitname = "lotus Fog"; callablestatement.setstring (1, fruitname); Callablestatement.registeroutparameter (2, Types.decimal); Callablestatement.execute ();d ouble fruitprice=callablestatement.getdouble (2);// Gets the attribute of type decimal to use the GetDouble method.
System.out.println (fruitname+ "The Price is:" +fruitprice+ "Yuan"); Linkdb.close (connection, callablestatement, null);}}

5, the input parameter is the output parameters of the stored procedure

Package Com.ghj.packageoftest;import java.sql.*;import com.ghj.packageoftool.linkdb;/* script to execute before executing this program: DELIMITER $$ CREATE PROCEDURE Inoneparamisoutoneparam (INOUT fruitname VARCHAR) beginselect NAME into fruitname from fruit WHERE NAM E like CONCAT ('% ', fruitname, '% ') LIMIT 0, 1; END $ $DELIMITER; DELIMITER $ $CREATE PROCEDURE inoneparamisoutoneparam (INOUT fruitname VARCHAR) beginselect NAME From Fruit WHERE NAME is like CONCAT ('% ', fruitname, '% ') LIMIT 0,1 into fruitname; END $ $DELIMITER; Note that the above query statement cannot be written like this: SELECT NAME from Fruit WHERE NAME is CONCAT ('% ', fruitname, '% ') into Fruitname LIMIT 0,1 Note: The location of the "into" keyword in the creation of stored procedure scripts in 3, 4, and 5Java files you must have a good harvest. Oh ah, steal a little lazy. It is not summed up here. *//** * Input parameters is the stored procedure for 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," Lotus "); Callablestatement.registeroutparameter (1, Types.varchar); Callablestatement.execute (); String fruitname = callablestatement.getstring (1); System.out.println ("Table fruit name contains ' lotus ' word of one of the fruit name is:" + fruitname); Linkdb.close (con, callablestatement, null);}}

Description

1. Assuming you copy the above code, you'll find that the missing LINKDB class, now post this class:

Package Com.ghj.packageoftool;import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.preparedstatement;import java.sql.resultset;import java.sql.sqlexception;/** * Connection Database * * @author GaoHuanjie * * public class Linkdb {/** * function: Get connection to MySQL * * @author gaohuanjie */public static Connection getmysqlconnection () {Connect Ion connection = NULL; String url = "Jdbc:mysql://localhost:3306/test"; 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 ;}  /** * off Release all resources * * @author Gaohuanjie */public static void Close (Connection con, preparedstatement PS, ResultSet 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. Assuming that you run the SQL script that created the stored procedure, you will find the missing class named fruit. now paste the SQL statement that created the table:

CREATE DATABASE ' test '; Use ' test '; CREATE TABLE ' fruit ' (  ' id ' char ($) NOT null COMMENT ' identity ',  ' name ' varchar () NOT null COMMENT ' name ',  ' price ' Decimal (8,2) 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 ', ' mangosteen ', ' 24.45 ', ' Malaysia '), (' 46ac8392-9922-4593-89a3-517a9e516733 ', ' pineapple ', ' 19.41 ', ' Brazil '), (' 63061a9f-3a0e-4140-98e0-8b1e13e4eab3 ', ' cantaloupe ', ' 17.77 ', ' China '), (' 7ef0c286-b8b1-4e1e-9a8a-36bce703cf18 ', ' avocado ', ' 30.80 ', ' Mexico '), (' a1cf5251-9311-4c7f-be10-3532d8c16291 ', ' Raspberry ', ' 117.50 ', ' Switzerland '), (' C397aed0-a39a-49c5-91ee-7fc0579ddb20 ', ' Lotus fog ', ' 77.33 ', ' Indonesia '), (' E8068fa1-a8e7-4025-89e2-36c1d5d23c74 ', ' durian ', ' 16.50 ', ' Thailand ');

" 0 min Download Demo Resources "

JDBC calls to the MySQL database stored procedure