Java调用MySQL的存储过程

矫情吗；* 2022-07-14 07:30 183阅读 0赞

Java调用MySQL的存储过程，需要用JDBC连接，环境eclipse

首先查看MySQL中的数据库的存储过程，接着编写代码调用

1.工程目录结构

![图片描述][20161113101407700]

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, 控制台输出

![图片描述][20161113101511513]

4.执行存储过程中的 sql语句

[20161113101407700]: /images/20220714/dd177ae33c2b4ecb8b5366fd90912dae.png
[20161113101511513]: /images/20220714/7fadd5146cbc4f60bbb4e49fb9768d14.png