Java实现调用MySQL存储过程详解

前言

存储过程（Stored Procedure）是存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

Java调用MySQL的存储过程，需要用JDBC连接，环境eclipse

首先查看MySQL中的数据库的存储过程，接着编写代码调用

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84

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语句

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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存储过程的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流。