JDBC對MySQL數據庫存儲過程的調用

一、MySQL數據庫存儲過程：

1、什麼是存儲過程

存儲過程（英文：Stored Procedure）是在大型數據庫系統中，為了完成特定功能而編寫的一組的SQL語句集。存儲過程經編譯存儲在數據庫中，用戶通過指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）來執行它。

2、與一般SQL語句相比，使用存儲過程有哪些優點，有哪些缺點

優點:
1）、減少了腳本的執行環節，縮短了獲取數據的時間。存儲過程只在創建的時進行編譯，在調用使用的時候直接執行，不需再次編譯；而一般SQL語句每次執行前都需要編譯一次，故效率沒有存儲過程高；
2）、減少網絡傳輸量，提高了傳輸速度。存儲過程編譯後存儲在數據庫服務器上，使用的時候只需要指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）就可以了；而一般SQL語句需要將所執行語句字符串傳輸到數據庫服務器端，相比於存儲過程而言向數據庫服務端傳送的字符串長度比較大；
3）、安全性比較高。為存儲過程參數賦值只能使用問號傳參的形式（這一點可以通過下面JDBC對MySQL數據庫存儲過程的調用例子體現出來），這樣可以防止SQL注入式攻擊；一般SQL語句也可以做到防止SQL注入式攻擊，但是並不是必須的。可以將Grant、Deny以及Revoke權限應用於存儲過程，即言可以設定只有某些用戶才具有對指定存儲過程的使用權；
缺點:
1）、如果在一個程序系統中大量的使用存儲過程，當程序交付使用的時候隨著客戶需求的增加會導致數據結構的變化，接著就是存儲過程的修改，這樣系統維護就會越來越難並且代價也會越來越大。

3、怎樣創建存儲過程及創建存儲過程需要注意的地方

存儲過程的創建格式為：

create procedure 存儲過程名([[IN |OUT |INOUT ] 參數名 數據類形...])
begin
存儲過程體
end

創建存儲過程的具體例子見下面JDBC對MySQL數據庫存儲過程的調用例子；

需要注意的地方：見下面JDBC對MySQL數據庫存儲過程的調用例子內創建存儲過程語句中的注釋；

二、JDBC對MySQL數據庫存儲過程的調用：

為了更加直觀的介紹JDBC如何實現對MySQL數據庫存儲過程的調用，這裡直接以例子的形式展示。

1、沒有任何輸入和輸出參數的存儲過程

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;

/*
運行本程序前需執行的腳本：
DELIMITER $$//<span style="font-family: Arial, Helvetica, sans-serif;">DELIMITER和$$之間要麼沒有空格要麼只有一個空格，如果有多個空格，在執行創建存儲過程語句時你會發現這樣是不能創建成功的</span>
CREATE PROCEDURE noParam()
BEGIN
SELECT AVG(price) AS priceAvg FROM fruit;
END$$
DELIMITER ;
*/

/**
 * 沒有任何輸入和輸出參數的存儲過程
 * 
 * @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("產品的平均價格是：" + resultSet.getDouble("priceAvg") + "元");
		}
		LinkDB.close(connection, callableStatement, resultSet);
	}
}

2、只有兩個輸入參數的存儲過程

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;

/*
運行本程序前需執行的腳本：
DELIMITER $$
CREATE PROCEDURE inTwoParam(IN fruitName VARCHAR(12),IN fruitPrice DECIMAL(9,2))//說明：fruitPrice參數的數據類型與price列的數據類型不一致（price的類型為（8,2）），對這一現象應該有所感悟。
BEGIN
SELECT * FROM fruit WHERE NAME LIKE CONCAT('%',fruitName,'%') AND price < fruitPrice;//注意：CONCAT('%',fruitName,'%')不能為'%'+fruitName+'%'
END$$
DELIMITER ;
*/

/**
 * 只有兩個輸入參數的存儲過程
 * 
 * @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, "蓮");
		callableStatement.setDouble(2, 88.88);//對DECIMAL類型的屬性設值要使用setDouble方法。
		callableStatement.execute();
		ResultSet resultSet = callableStatement.getResultSet();
		System.out.println("名稱包含‘蓮’字且價格小於88.88元的水果有：");
		while (resultSet.next()) {
			System.err.println("名稱：" + resultSet.getString("name") +"、價格：" + resultSet.getDouble("price") + "元"+"、產地：" + resultSet.getString("address"));
		}
		LinkDB.close(connection, callableStatement, resultSet);
	}
}

3、只有兩個輸出參數的存儲過程

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;

/*
運行本程序前需執行的腳本：
DELIMITER $$
CREATE PROCEDURE outTwoParam(OUT fruitName VARCHAR(12),OUT fruitPrice DECIMAL(5,3) )
BEGIN
SELECT name INTO fruitName FROM fruit WHERE name='蓮霧';
SELECT price INTO fruitPrice FROM fruit WHERE NAME='蓮霧';
END $$
DELIMITER ;
注意：上面兩條查詢語句不能合成一個SQL語句——SELECT NAME INTO fruitName, price INTO fruitPrice FROM fruit WHERE NAME='蓮霧';
*/

/**
 * 只有兩個輸出參數的存儲過程
 * 
 * @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.execute();
		String fruitName = callableStatement.getString(1);
		double fruitPrice = callableStatement.getDouble(2);// 獲取DECIMAL類型的屬性要使用getDouble方法。
		System.out.println("水果名稱：" + fruitName +"、水果價格：" + fruitPrice + "元");
		LinkDB.close(connection, callableStatement, null);
	}
}

4、含有一個輸入參數和一個輸出參數的存儲過程

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;

/*
運行本程序前需執行的腳本：
DELIMITER $$
CREATE PROCEDURE inOneParamAndOutOneParam(IN fruitName VARCHAR(12),OUT fruitPrice DECIMAL(7,3))
BEGIN
SELECT price FROM fruit WHERE NAME=fruitName INTO fruitPrice;
END $$
DELIMITER ;
*/
 
/**
 * 含有一個輸入參數和一個輸出參數的存儲過程
 * 
 * @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 = "蓮霧";
		callableStatement.setString(1, fruitName);
		callableStatement.registerOutParameter(2, Types.DECIMAL);
		callableStatement.execute();
		double fruitPrice=callableStatement.getDouble(2);//獲取DECIMAL類型的屬性要使用getDouble方法。
		System.out.println(fruitName+"的價格為："+fruitPrice+"元");
		LinkDB.close(connection, callableStatement, null);
	}
}

5、輸入參數即輸出參數的存儲過程

package com.ghj.packageoftest;

import java.sql.*;

import com.ghj.packageoftool.LinkDB;

/*
 運行本程序前需執行的腳本：
DELIMITER $$
CREATE PROCEDURE inOneParamISOutOneParam(INOUT fruitName VARCHAR(12))
BEGIN
SELECT NAME INTO fruitName FROM fruit WHERE NAME LIKE CONCAT('%', fruitName, '%') LIMIT 0,1;
END $$
DELIMITER ;
或
DELIMITER $$
CREATE PROCEDURE inOneParamISOutOneParam(INOUT fruitName VARCHAR(12))
BEGIN
SELECT NAME FROM fruit WHERE NAME LIKE CONCAT('%', fruitName, '%') LIMIT 0,1 INTO fruitName;
END $$
DELIMITER ;

注意上面查詢語句不能這樣寫：SELECT NAME FROM fruit WHERE NAME LIKE CONCAT('%', fruitName, '%') INTO fruitName LIMIT 0,1;
注意：對比3、4和5Java文件內創建存儲過程腳本中“INTO”關鍵字的位置你一定深有收獲，呵呵呵，偷點懶，在此就不總結了。
*/

/**
 * 輸入參數即輸出參數的存儲過程
 * 
 * @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, "蓮");
		callableStatement.registerOutParameter(1, Types.VARCHAR);
		callableStatement.execute();
		String fruitName = callableStatement.getString(1);
		System.out.println("表中水果名稱含有‘蓮’字的一中水果的名稱是：" + fruitName);
		LinkDB.close(con, callableStatement, null);
	}
}

說明：

1、如果把上面代碼拷貝下來你會發現缺少LinkDB類，現貼出該類：

package com.ghj.packageoftool;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 連接數據庫
 * 
 * @author GaoHuanjie
 */
public class LinkDB {

	/**
	 * 功能:獲取與MySql的連接
	 * 
	 * @author GaoHuanjie
	 */
	public static Connection getMySqlConnection() {
		Connection 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;
	}

	/**
	 * 關閉釋放所有的資源
	 * 
	 * @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、如果執行創建存儲過程的SQL腳本，你會發現缺少名為fruit類，現貼出創建該表的SQL語句：

CREATE DATABASE `test`;
USE `test`;
CREATE TABLE `fruit` (
  `id` char(36) NOT NULL COMMENT '標識',
  `name` varchar(12) NOT NULL COMMENT '名稱',
  `price` decimal(8,2) NOT NULL COMMENT '單價',
  `address` varchar(300) DEFAULT NULL COMMENT '產地',
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='水果表';

insert  into `fruit`(`id`,`name`,`price`,`address`) 
values 
('27640c30-8df5-4cf2-916e-c28e0b2b1b52','山竹','24.45','馬來西亞'),
('46ac8392-9922-4593-89a3-517a9e516733','菠蘿','19.41','巴西'),
('63061a9f-3a0e-4140-98e0-8b1e13e4eab3','哈密瓜','17.77','中國'),
('7ef0c286-b8b1-4e1e-9a8a-36bce703cf18','鳄梨','30.80','墨西哥'),
('a1cf5251-9311-4c7f-be10-3532d8c16291','樹莓','117.50','瑞士'),
('c397aed0-a39a-49c5-91ee-7fc0579ddb20','蓮霧','77.33','印度尼西亞'),
('e8068fa1-a8e7-4025-89e2-36c1d5d23c74','榴蓮','16.50','泰國');

【0分下載演示資源】