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

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

+-------------+------------------------+---------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

| fibYWsyndAllBook | 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; //数量

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

*/

public List findAll()

{

List list = new ArrayList(); //实例化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 | http:// 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存储过程的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。