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 it's not necessary. 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

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;/* Scripts to be executed before running this program: DELIMITER $$//<span style= "font-family:arial, Helvetica, Sans-serif;" There is either no space between >delimiter and $$, or there is only one space, and if you have multiple spaces, you will find that you cannot create a successful </span>create PROCEDURE Noparam () When you execute the Create stored procedure statement. Beginselect AVG (Price) as priceavg from fruit; end$ $DELIMITER; *//** * No stored procedure for input and OUTPUT parameters * * @author Gaohuanjie */public class Noparam {public static void main (String ar Gs[]) 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. Stored procedures 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;/* script to be executed before running 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; *//** * Stored procedure with only two input parameters * * @author Gaohuanjie */public class Intwoparam {public stat IC 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 contains ' 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 be executed before running 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 Sqlexc eption {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);//Get a property of type decimal to use the GetDouble method. System.out.println ("Fruit name:" + fruitname + ", fruit Price:" + Fruitprice + "Yuan"); Linkdb.close (connection, callablestatement, null);}}

4. Stored procedure with one input parameter and one 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 be executed before running 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; * */** * stored procedure with one input parameter and one output parameter * * @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. The price of System.out.println (fruitname+) is:"+fruitprice+" Yuan "); Linkdb.close (connection, callablestatement, null);}}

5, the input parameter is the output parameter stored procedure

Package Com.ghj.packageoftest;import java.sql.*;import com.ghj.packageoftool.linkdb;/* script to be executed before running 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 Create stored procedure script in 3, 4, and 5Java files you must have a deep harvest, hehe, stealing a little lazy, this is not summed up. *//** * Input parameter is 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, "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. If you copy the above code, you will 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. If you execute the SQL script that created the stored procedure, you will find that the SQL statement that created the table is missing, which is called the Fruit class:

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=i Nnodb 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 ');

        " Span style= "Color:rgb (54,46,43); font-family:arial; line-height:18px; text-indent:28px ">0 Download demo resource "