Java實現(xiàn)調(diào)用MySQL存儲過程詳解

更新時間：2016年11月16日 09:07:31 作者：shootercheng

相信大家都知道存儲過程是在大型數(shù)據(jù)庫系統(tǒng)中，一組為了完成特定功能的SQL語句集。存儲過程是數(shù)據(jù)庫中的一個重要對象，任何一個設(shè)計良好的數(shù)據(jù)庫應(yīng)用程序都應(yīng)該用到存儲過程。Java調(diào)用mysql存儲過程，實現(xiàn)如下，有需要的朋友們可以參考借鑒，下面來一起看看吧。

前言

存儲過程（Stored Procedure）是存儲在數(shù)據(jù)庫中經(jīng)過第一次編譯后再次調(diào)用不需要再次編譯，用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它。

Java調(diào)用MySQL的存儲過程，需要用JDBC連接，環(huán)境eclipse

首先查看MySQL中的數(shù)據(jù)庫的存儲過程，接著編寫代碼調(diào)用

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)

一、工程目錄結(jié)構(gòu)

二、Book.java

package com.scd.book;

public class Book {
 private String name; //圖書名稱
 private double price; //價格
 private int bookCount; //數(shù)量
 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 {
 /**
  * 獲取數(shù)據(jù)庫連接
  * @return Connection對象
  */
 public Connection getConnection()
 {
  Connection conn = null; //數(shù)據(jù)庫連接
  try
  {
   Class.forName("com.mysql.jdbc.Driver"); //加載數(shù)據(jù)庫驅(qū)動，注冊到驅(qū)動管理器
   /*數(shù)據(jù)庫鏈接地址*/
   String url = "jdbc:mysql://localhost:3306/book?useUnicode=true&characterEncoding=UTF-8";
   String username = "root";
   String password = "123456";
   /*創(chuàng)建Connection鏈接*/
   conn = DriverManager.getConnection(url, username, password); 
   
  }
  catch (ClassNotFoundException e){
   
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
  return conn; //返回數(shù)據(jù)庫連接
  
 }
 /**
  * 通過存儲過程查詢數(shù)據(jù)
  * @return List<Book>
  */
 public List<Book> findAll() 
 {
  List <Book> list = new ArrayList<Book>(); //實例化List對象
  Connection conn = getConnection(); //創(chuàng)建數(shù)據(jù)庫連接
  try
  {
   //調(diào)用存儲過程
   CallableStatement cs = conn.prepareCall("{call findAllBook()}");
   ResultSet rs = cs.executeQuery(); //執(zhí)行查詢操作，并獲取結(jié)果集
   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
 }
 /**
  * 主函數(shù) 調(diào)用存儲過程(測試使用)
  * @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, 控制臺輸出

五、執(zhí)行存儲過程中的 sql語句

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

總結(jié)

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

您可能感興趣的文章: