景颇族的风俗习惯,长沙医科大学,4444kkkcon
前言
存储过程（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存储过程的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流。
如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复
apollo与springboot集成实现动态刷新配置的教程详解
网友评论