Java實現調用MySQL存儲過程詳解

前言

存儲過程（Stored Procedure）是存儲在數據庫中經過第一次編譯後再次調用不需要再次編譯，用戶通過指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）來執行它。

Java調用MySQL的存儲過程，需要用JDBC連接，環境eclipse

首先查看MySQL中的數據庫的存儲過程，接著編寫代碼調用

mysql> show procedure status;+------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |+------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+| book | findAllBook | PROCEDURE | root@localhost | 2016-09-04 11:13:31 | 2016-09-04 11:13:31 | DEFINER | | gbk | gbk_chinese_ci | utf8_general_ci || book | pro_test | PROCEDURE | root@localhost | 2016-11-13 08:27:17 | 2016-11-13 08:27:17 | DEFINER | | gbk | gbk_chinese_ci | utf8_general_ci || book | pro_user | PROCEDURE | root@localhost | 2016-11-13 08:44:34 | 2016-11-13 08:44:34 | DEFINER | | gbk | gbk_chinese_ci | utf8_general_ci |+------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+rows in set (0.01 sec)mysql> show create procedure findAllBook;+-------------+------------------------+---------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |+-------------+------------------------+---------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+| findAllBook | NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `findAllBook`()begin select * from tb_books;end | gbk | gbk_chinese_ci | utf8_general_ci |+-------------+------------------------+---------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+row in set (0.00 sec)

一、工程目錄結構

二、Book.java

 package com.scd.book;public class Book { private String name; //圖書名稱private double price; //價格private int bookCount; //數量private String author; //作者public String getName() { //System.out.println(name); return name; } public void setName(String name) { this.name = name; } public double getPrice() { return price; } public void setPrice(double price) { this.price = price; } public int getBookCount() { return bookCount; } public void setBookCount(int bookCount) { this.bookCount = bookCount; } public String getAuthor() { return author; } public void setAuthor(String author) { //System.out.println(author); this.author = author; }}

三、FindBook.java

 package com.scd.book;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;public class FindBook { /** * 獲取數據庫連接* @return Connection對象*/ public Connection getConnection() { Connection conn = null; //數據庫連接try { Class.forName("com.mysql.jdbc.Driver"); //加載數據庫驅動，註冊到驅動管理器/*數據庫鏈接地址*/ String url = "jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=UTF-8"; String username = "root"; String password = "123456"; /*創建Connection鏈接*/ conn = DriverManager.getConnection(url, username, password); } catch (ClassNotFoundException e){ e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return conn; //返回數據庫連接} /** * 通過存儲過程查詢數據* @return List<Book> */ public List<Book> findAll() { List <Book> list = new ArrayList<Book>(); //實例化List對象Connection conn = getConnection(); //創建數據庫連接try { //調用存儲過程CallableStatement cs = conn.prepareCall("{call findAllBook()}"); ResultSet rs = cs.executeQuery(); //執行查詢操作，並獲取結果集while(rs.next()) { Book book = new Book(); //實例化Book對象book.setName(rs.getString("name")); //對name屬性賦值book.setPrice(rs.getDouble("price")); //對price屬性賦值book.setBookCount(rs.getInt("bookCount")); //對bookCount屬性賦值book.setAuthor(rs.getString("author")); //對author屬性賦值list.add(book); } }catch(Exception e) { e.printStackTrace(); } return list; //返回list } /** * 主函數調用存儲過程(測試使用) * @param args */ public static void main(String[] args) { FindBook fb = new FindBook(); //System.out.println(fb.findAll()); for (Book book : fb.findAll()) { System.out.print(book.getName() + "--" + book.getPrice() + "--"); System.out.print(book.getBookCount() + "--" + book.getAuthor()); System.out.println(); } }}

四、右鍵Run As --> Java Application, 控制台輸出

五、執行存儲過程中的sql語句

mysql> select * from tb_books;+------------------+-------+-----------+----------+| name | price | bookCount | author |+------------------+-------+-----------+----------+| Java叢入門到精通| 56.78 | 13 | Mr. Sun || 數據結構| 67.3 | 8962 | Mr. Sun || 編譯原理| 78.66 | 5767 | Mr. Sun || 數據結構| 67.42 | 775 | Mr.Cheng |+------------------+-------+-----------+----------+rows in set (0.00 sec)mysql> call findAllBook();+------------------+-------+-----------+----------+| name | price | bookCount | author |+------------------+-------+-----------+----------+| Java叢入門到精通| 56.78 | 13 | Mr. Sun || 數據結構| 67.3 | 8962 | Mr. Sun || 編譯原理| 78.66 | 5767 | Mr. Sun || 數據結構| 67.42 | 775 | Mr.Cheng |+------------------+-------+-----------+----------+rows in set (0.00 sec)

總結

以上就是Java調用MySQL存儲過程的全部內容，希望本文的內容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流。