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

標籤：結構   代碼   tab   word   tle   targe   sql資料庫   如何   127.0.0.1   

 一、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、沒有任何輸入和輸出參數的預存程序

1 drop PROCEDURE if EXISTS jdbcprocnoinandout;2 create PROCEDURE jdbcprocnoinandout()3 BEGIN4     select * from test.test;5 end;

下面是Java代碼：

1 Connection connectionNoInAndOut = MyConnection.getConnection();2         String jdbcprocNoInAndOut = "{call jdbcprocnoinandout()}";3         CallableStatement csNoInAndOut = connectionNoInAndOut.prepareCall(jdbcprocNoInAndOut);4         csNoInAndOut.execute();5         ResultSet rsNoInAndOut = csNoInAndOut.getResultSet();6         while (rsNoInAndOut.next()) {7             System.out.println("jdbcprocnoinandout："+rsNoInAndOut.getString("id")+"--------"+rsNoInAndOut.getString("value1"));8         }9         MyConnection.closeConnection(connectionNoInAndOut, csNoInAndOut, rsNoInAndOut);

2、只有兩個輸入參數的預存程序

1 drop PROCEDURE if EXISTS jdbcprocallin;2 create PROCEDURE jdbcprocallin(id int, value1 VARCHAR(20))3 BEGIN4     insert into test.test values(id,value1);5     select * from test.test;6 end;

Java代碼：

 1 Connection connectionAllIn = MyConnection.getConnection(); 2         String jdbcprocAllIn = "{call jdbcprocallin(?,?)}"; 3         CallableStatement csAllIn = connectionAllIn.prepareCall(jdbcprocAllIn); 4         csAllIn.setInt(1, 1); 5         csAllIn.setString(2, "asdf"); 6         csAllIn.execute(); 7         ResultSet rsAllIn = csAllIn.getResultSet(); 8         while (rsAllIn.next()) { 9             System.out.println("jdbcprocallin："+rsAllIn.getString("id")+"--------"+rsAllIn.getString("value1"));10         }11         MyConnection.closeConnection(connectionAllIn, csAllIn, rsAllIn);

3、一個輸入參數一個輸出參數的預存程序

1 drop PROCEDURE if EXISTS jdbcprocinandout;2 create PROCEDURE jdbcprocinandout(in id VARCHAR(20), out value1 VARCHAR(20))3 BEGIN4     set value1 = CONCAT(‘我是:‘,id);5     select value1;6 end;

Java代碼：

 1 Connection connectionInAndOut = MyConnection.getConnection(); 2         String jdbcprocInAndOut = "{call jdbcprocinandout(?,?)}"; 3         CallableStatement csInAndOut = connectionInAndOut.prepareCall(jdbcprocInAndOut); 4         csInAndOut.setString(1, "123123"); 5         csInAndOut.registerOutParameter(2, Types.VARCHAR); 6         csInAndOut.execute(); 7         ResultSet rsInAndOut = csInAndOut.getResultSet(); 8         while (rsInAndOut.next()) { 9             System.out.println("jdbcprocinandout："+csInAndOut.getString("value1"));10         }11         MyConnection.closeConnection(connectionInAndOut, csInAndOut, rsInAndOut);

4、兩個輸出參數的預存程序

1 drop PROCEDURE if EXISTS jdbcprocallout;2 create PROCEDURE jdbcprocallout(out outid VARCHAR(20), out outvalue1 VARCHAR(20))3 BEGIN4     select * into outid,outvalue1 from test.test limit 1;5     select outid,outvalue1 ;6 end;

Java代碼：

 1 Connection connectionAllOut = MyConnection.getConnection(); 2         String jdbcprocAllOut = "{call jdbcprocallout(?,?)}"; 3         CallableStatement csAllOut = connectionAllOut.prepareCall(jdbcprocAllOut); 4         csAllOut.registerOutParameter(1, Types.VARCHAR); 5         csAllOut.registerOutParameter(2, Types.VARCHAR); 6         csAllOut.execute(); 7         ResultSet rsAllOut = csAllOut.getResultSet(); 8         while (rsAllOut.next()) { 9             System.out.println("jdbcprocallout："+csAllOut.getString("outid")+"--------"+csAllOut.getString("outvalue1"));10         }11         MyConnection.closeConnection(connectionAllOut, csAllOut, rsAllOut);

資料庫中調用帶有輸出參數的預存程序寫法，例如剛才兩個帶有輸出參數的

1 call jdbcprocinandout(‘lily‘,@value1);2 call jdbcprocallout(@vid,@vvalue);
-- 輸出參數前面必須帶“@”符號，變數名可以隨便寫一個合法變數都可以。

 

附上MyConnection類

 1 package net.lily.test; 2  3 import java.sql.*; 4  5 public class MyConnection { 6      7     public static Connection getConnection() { 8         Connection connection = null; 9         String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useOldAliasMetadataBehavior=true";10         String user = "root";11         String pwd = "123456";12         String driverName = "com.mysql.jdbc.Driver";13         try {14             Class.forName(driverName);15             connection = DriverManager.getConnection(url, user, pwd);16         } catch (ClassNotFoundException e) {17             e.printStackTrace();18         } catch (SQLException e) {19             e.printStackTrace();20         }21         return connection;22     }23 24     public static void closeConnection(Connection con, PreparedStatement ps, ResultSet rs) {25         if (rs != null) {26             try {27                 rs.close();28             } catch (SQLException e) {29                 e.printStackTrace();30             }31         }32         if (ps != null) {33             try {34                 ps.close();35             } catch (SQLException e) {36                 e.printStackTrace();37             }38         }39         if (con != null) {40             try {41                 con.close();42             } catch (SQLException e) {43                 e.printStackTrace();44             }45         }46     }47 }

 

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