Java调用MySQL的存储过程

来源：互联网发布：数字规律软件编辑：程序博客网时间：2024/05/29 18:29

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

1.工程目录结构

图片描述

2.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;    }}

2.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();        }    }}

3.右键 Run As –> Java Application, 控制台输出

图片描述

4.执行存储过程中的 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 |+------------------+-------+-----------+----------+4 rows in set (0.00 sec)

0 0