Java调用MySQL存储过程

Joy8132 8年前
   <p>Java调用MySQL的存储过程,需要用JDBC连接,环境eclipse</p>    <p>首先查看MySQL中的数据库的存储过程,接着编写代码调用</p>    <pre>  <code class="language-java">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    |  +------+-------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+  3 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    |  +-------------+------------------------+---------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+  1 row in set (0.00 sec)  </code></pre>    <p><strong>1.工程目录结构</strong></p>    <p><img src="https://simg.open-open.com/show/e1bc3acf7af89f117de51c43a2e00aaa.png"></p>    <p><strong>2.Book.java</strong></p>    <pre>  <code class="language-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;      }    }  </code></pre>    <p><strong>3.FindBook.java</strong></p>    <pre>  <code class="language-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();          }      }  }  </code></pre>    <p><strong>4.右键 Run As --> Java Application, 控制台输出</strong></p>    <p><img src="https://simg.open-open.com/show/99a8af72417ffbd7b207117f3b270156.png"></p>    <p><strong>5.执行存储过程中的 sql语句</strong></p>    <pre>  <code class="language-java">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 |  +------------------+-------+-----------+----------+  4 rows in set (0.00 sec)  </code></pre>    <p> </p>    <p>来自:http://www.cnblogs.com/shootercheng/p/6058130.html</p>    <p> </p>