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

前言

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

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

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

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

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