JDBC對MySQL資料庫預存程序的調用

標籤：varchar   大量   state   介紹   位置   net   throw   post   next   

        一、MySQL資料庫預存程序：

        1、什麼是預存程序

        預存程序（英文：Stored Procedure）是在大型資料庫系統中，為了完畢特定功能而編寫的一組的SQL語句集。預存程序經編譯儲存在資料庫中。使用者通過指定預存程序的名字並給出參數（假設該預存程序帶有參數）來運行它。

        2、與一般SQL語句相比。使用預存程序有哪些長處。有哪些缺點

        長處:
        1）、降低了指令碼的運行環節，縮短了擷取資料的時間。預存程序僅僅在建立的時進行編譯，在調用使用的時候直接運行。不需再次編譯；而一般SQL語句每次運行前都須要編譯一次，故效率沒有預存程序高。
        2）、降低網路傳輸量，提高了傳輸速度。

預存程序編譯後儲存在資料庫server上，使用的時候僅僅須要指定預存程序的名字並給出參數（假設該預存程序帶有參數）就能夠了；而一般SQL語句須要將所運行語句字串傳輸到資料庫server端。相比於預存程序而言向資料庫服務端傳送的字串長度比較大；
        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()BEGINSELECT 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）），對這一現象應該有所感悟。BEGINSELECT * 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) )BEGINSELECT 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))BEGINSELECT 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))BEGINSELECT NAME INTO fruitName FROM fruit WHERE NAME LIKE CONCAT('%', fruitName, '%') LIMIT 0,1;END $$DELIMITER ;或DELIMITER $$CREATE PROCEDURE inOneParamISOutOneParam(INOUT fruitName VARCHAR(12))BEGINSELECT 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”keyword的位置你一定深有收穫。呵呵呵，偷點懶。在此就不總結了。*//** * 輸入參數即輸出參數的預存程序 *  * @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分下載示範資源】

JDBC對MySQL資料庫預存程序的調用