Java实现调用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)

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

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

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)