Java调用MySQL存储过程 Java实现调用MySQL存储过程详解
shootercheng 人气:0想了解Java实现调用MySQL存储过程详解的相关内容吗，shootercheng在本文为您仔细讲解Java调用MySQL存储过程的相关知识和一些Code实例，欢迎阅读和指正，我们先划重点：mysql,存储过程,java,mysql,存储过程,java,调用存储过程，下面大家一起来学习吧。
前言
存储过程（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存储过程的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流。
加载全部内容