java 是一种可以撰写跨平台应用软件的面向对象的程序设计语言,是由 Sun Microsystems 公司于 1995 年 5 月推出的 Java 程序设计语言和 Java 平台(即 JavaEE(j2ee), JavaME(j2me), JavaSE(j2se))的总称。
相信大家都知道存储过程是在大型数据库系统中,一组为了完成特定功能的 SQL 语句集。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。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)
总结
来源: http://www.phperz.com/article/17/1130/359390.html