【Java】程式碼呼叫MySQL儲存過程

 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 | [email protected] | 2016-09-04 11:13:31 | 2016-09-04 11:13:31 | DEFINER       |         | gbk                  | gbk_chinese_ci       | utf8_general_ci    |
| book | pro_test    | PROCEDURE | [email protected] | 2016-11-13 08:27:17 | 2016-11-13 08:27:17 | DEFINER       |         | gbk                  | gbk_chinese_ci       | utf8_general_ci    |
| book | pro_user    | PROCEDURE | [email protected] | 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)

1.工程目錄結構

  

2.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;
}
}

3.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();
}
}
}

4.右鍵 Run As –> Java Application, 控制檯輸出

5.執行儲存過程中的 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)